Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In SQL Server 2008 Essential Training, Simon Allardice explores all the major features of SQL Server 2008 R2, beginning with core concepts: installing, planning, and building a first database. Explore how Transact-SQL is used to retrieve, update, and insert information, and gain insight into how to effectively administer databases. The course also covers features outside SQL Server's database engine, including technologies that have grown up around it: SQL Server Reporting Services and Integration Services. Exercise files are included with the course.
To create a database, we are really defining two different things. The idea of what our database is, our tables, our columns, our keys, and our relationships, but to do that, we first need to create the other piece. the more boring mundane piece. See the real deal is that all of our tables and all of the data in those tables that we want to store will end up being stored on files on the hard drive. So we need to say what those files are. We need to say where is this database going to be created.
So I have opened up SQL Server Management Studio. I'm going to right-click my Databases folder and say New Database, and a new window will appear that's going to ask me first off to give this thing a name. I just have to name this database. So I'm going call it TwoTreesTest. I am pretending this is a test database for the Two Trees organization. I am expecting to end up deleting this. So I'm not really too bothered about how it's created. I can see that down below it, it says this will have two database files, something called TwoTreesTest and TwoTreesTest_log.
This is the default that you will find that everything you end up putting in the database will be stored in a primary data file and a transaction log file. I am going to widen this window just a little wider so we can read it. It will even show you over here what the path to these files will be. The interesting stuff, however, is this part, the Initial Size and the Autogrowth, and probably between the two the Autogrowth is the big deal. Because if we are storing potentially thousands or even millions of rows of data in our tables and dozens of tables in our database, well, they are all going in this one file and it could grow pretty large.
We have this Autogrowth section here and if I click the ellipsis button, it will tell me what the behavior will be. We have Autogrowth enabled. What's going to happen is the file will start off at 2 MB and if it gets more data than that, it's going to start growing by 1 MB at a time. Now, if you knew you were inserting a lot of information, you might say, well I want it to grow by 16 MB or 32 MB at a time. We've also got this idea here of Unrestricted File Growth.
Autogrowth can be changed. You can see the second one here says this is allowed Autogrowth but it will grow, instead of MB, it will by 10% at a time. So, I am just going to click OK. I am not going to change either of that except the defaults unless you know differently. You can change your Autogrowth factors later on. If I go over to the left-hand side of this new database window, we do have a few options. I am not going to change anything here but here's where you could change to a different collation. Again, the collation is the idea of case sensitivity and accent sensitivity that the database management system will use when it's sorting, or ordering, or grouping stuff together.
And the third part here is what's called Filegroups. In this particular course, I'm not really going to get into Filegroups. You can just ignore them. As you are creating more complex databases, you will find that these are ways of abstracting the physical files that make up your database so you don't have to deal with the actual individual files on the hard drive. We are just using one. That's all I'm going to do with this particular course. I am going to click OK. And we now should have a database.
There we go, TwoTreesTest. I could right-click that and say Properties and you'll find a whole bunch of other information that you can now start to manipulate and play with. Again if you're new to SQL Server, I wouldn't suggest that you do, but you can see there are lot of the options that we had to answer can be changed in the future as we go on. I am going to just click OK there, because after all, although we have a database, a database that is devoid of tables isn't much use to anyone. So we'll go ahead and do that next.
Find answers to the most frequently asked questions about SQL Server 2008 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.