From the course: SQL Server 2014 Essential Training

Creating a database - SQL Server Tutorial

From the course: SQL Server 2014 Essential Training

Start my 1-month free trial

Creating a database

“

- Now it's time to create a database. Here in Microsoft SQL Server Management Studio, I'm going to right-click on Databases and select the top option of New Database. That'll give me a dialogue and on the first page the general page, I'll need to give the database a name. I'm gonna go with LyndaDemo for my name. You can really name it whatever you want, it doesn't effect functionality. For the Owner right now it's going with just the phrase Default. I'm gonna click on the three little dots all the way to the right and that'll open up another dialogue where I can select which login will be the owner of this database. I know I have a login called Martin, so I'll type that in. Hit Check Names and go ahead and select that owner. Below that it's asking me for the database files. It has come up with some default recommendations. One file to hold the ROW data and one file to hold the LOG. It's asking me for a size for each of these and it's going with the default of 4 MB for the ROW data and 1 MB for the LOG data. I feel that's fairly small. Both of these files will grow automatically. However, every time it grows, we have the risk of becoming fragmented, so if we start off with a larger size we will then need to grow less often. That will reduce the chances of fragmentation. If you have a good estimate of how big your database is likely to grow, go ahead and select that as the initial size. For example, we could go with 25 MB for the ROWS and 25 for the LOG. The next column has some auto grow information. Right now it's set to grow 1 MB at a time with a maximum size of Unlimited. We can change that by clicking on the button with three little dots. That'll open up another dialogue where we can choose to grow either in megabytes or in percent. I usually go in percent and about a 10% growth is adequate. We can limit the file size. If you're very tight on disk space and you want to limit how big this database grows, you can do that here. Just be aware that if your database got to that size then you would no longer be able to insert any new records and that would severely limit our functionality, so I usually set this to Unlimited growth, but on a database that I know is going to grow, I try and monitor this and check it often to make sure it's not getting close to filling up the disk. Then it's asking me for a path. The default path is something that you know you're going to have permission to. You can really put these files in any path. The only limitation would be you'll have to set the proper permissions on that folder first. Mine has defaulted to putting the ROW and the LOG in the same directory. That's fine for this kind of a demonstration. Now in production on a database that we wanted to get the best performance, we would probably want to move either the data or the ROW onto a separate set of drives. Not just to a separate folder, but actually onto a separate drive. That will increase performance If we have ROW data on one drive and logging data on a different drive. For now I'll stick with this default. I'll hit OK at the bottom. This will take a minute or two depending on the size you set. SQL Server will have to go out and create these files and the bigger the file is, the longer it will take. Now over here to the left, under Databases I see LyndaDemo, so that is a success. If you don't see that come up right away, you'll just wanna right-click on Databases and go to Refresh. It should come up there. Also, we can look at the folder and this should've come up in the DATA folder and yes, we see a new file, LyndaDemo.mdf and it is about 25 MB, which is exactly what we suggested. Because I have the LOG in the same folder, I also see the LOG there, LyndaDemo_log.ldf and it's also the size I told it to be, 25 MB. Yours might be in a different directory, but both of these files will exist now on your computer. We have successfully created a new database.

Contents