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.
One thing you should do on all of your tables, at least as a general rule, is defined what's called a primary key. This is a way that's going to identify how do we get to an individual role. One value that would always take us to just one row in that table. And your primary key can be any of the columns that you actually have defined if that makes sense. Sometimes it does, sometimes it doesn't. If I look at my Product Table, for example I have ProductID, ProductName, UnitPrice and UnitsInStock, and let's say that my business process was such that ProductIDs were decided around a conference table and manually entered into the database.
Well I could say then ProductID should be my primary key. One ProductID should always identify a row in this table in this database. Well I have it open in Design. I can right- click the column and just say Set primary key. Notice what happens to the Allow Nulls column when I do this. It's going to turn it off because when you think about it, it doesn't make sense. You can't really have a unique primary key if you're allowing that value to not be entered at all.
So I'm going to hit that and say cool, I'm done. A ProductID will be unique and it will be manually entered in for each product. I'm going to now try and save my changes and the problem that I run into, it will say "Saving changes is not permitted." The changes that I've made require the following table to be dropped and re-created. Now this is one of the problems here, one of the issues. It's why I say that you want to figure your database tables out before you build them, because you don't want to have to go back in and edit them multiple times.
Many of the changes that you might think are just a small change, according to the database are fairly big ones, and in fact SQL Server Management Studio is not letting me save that change. The change it cares about was not actually the primary key. It was me saying that Nulls were allowed and now they're not. Even though I have no data stored in this table yet, it's still considering that a really significant change so it's stopping me from doing it. Well I can tell SQL Server Management Studio to lighten up a little bit. I am going to go to my Tools > Options and find my Designers section where I have an option here that says Prevent saving changes that require table re-creation.
I am going to uncheck that, so I am saying yes, I can save a substantial change that requires the table to be re-created. You want to be careful with that one because obviously un-checking that allows you to make some fairly significant changes that you might not want to. So I'm going to now try that, save again, and we are good. W now have a primary key defined on that column. Well what about the other table? We have dbo.Employee here. I am going to right-click and select Design. I can scan the columns here and see that there is no primary key because you do get it just showing up there if you need to. And I can look at that and think, well, is the one that is obvious? And there isn't.
You could make a case that says well, perhaps first name and last name together could be a primary key, and in fact you can do something like that. It's what's called a compound key, but still in a big company there is no guarantee that you wouldn't get two people with the same first and last name. So here would be a good idea for just creating one. I am going to add an EmployeeID, and this one I'm going to say is just going to be an integer. It's just going to be a number. If we say that every employee has to have one, I'm going to uncheck this and say no, don't allow Nulls. Don't allow that missing value there.
And I could just right-click and name this as the primary key and that would work. However, it does mean that right now I would have to manually type this in or have an application that creates it. What a nice thing would be is can we get SQL Server to help us generate that EmployeeID every time a new employee is added. And not surprisingly, and I know where you think I'm going with this, you can. If I keep this column highlighted so EmployeeID is highlighted and then down in the Properties for the column we have a section called Identity Specification.
This is a little switch that we can flip and it says Is Identity from No to Yes. And then what it changes and it says you've got an Identity Seed and Identity Increment, which is basically what number do we start with and how much do we go up each time. So the default is we start at number 1 and add 1 to it every time around, but I can decide to start it any number. Let's start at 500 and go up by 3 each time around. I'm going to now just save these changes and we now have a primary key defined there.
Well it'd be nice to prove it. I'll go ahead and do that. I'm going to right-click the Employee table and say Edit the Top 200 Rows. That switches me into this editing view. I don't have any data yet, so let's put in our first piece. So the FirstName I'll say that will be Joe, LastName as West. You'll notice that every time I am entering something in I get the exclamation mark and it's telling me that the cell has changed but the change has not been committed to the database. The original data was NULL. I'll just continue on. That sounds all right.
I'll see the Title is a Manager, and the HireDate was the first of February in 2005. BirthDate was the first of January of 1970. Phone number was just a text value. I could put pretty much anything in here. The Status was the bit data type and that really means just expecting a Boolean value, either true or false. A lot of developers wonder should they put a 1 or a 0. Well you can just write the word true in there.
You might think well what happens if I didn't, if I just typed in the number 99? Well as soon as I try and tab off it, it's going to say, "I'm sorry this change value is not recognized as valid." You'd get this on any of these fields. The string was not recognized as a valid Boolean. Okay, that makes sense. I'll click OK and I'll type true, which should be a valid Boolean, and it allow me to tab off. Now you notice here I'm now on EmployeeID. This is the one that should be our identity and should be automatically created.
So I am not going to type anything in here. I am just going to tab, which would make me go to the next row. And when I do that we take off that first row, and by leaving the row all these changes should now be committed to the database, and if I move to the right a little bit I can see that I am actually Employee 500, that it's been entered correctly, and if I would to enter in more information, it will be 503, 506, 509, 512 because we said that the Identity Increment was a factor of 3 for that.
And the last thing I'm going to actually do here is change this EmployeeID and I want to show it first and I can just do that by rearranging my column. I am going to grab it and just drag it up to the top and save my changes. Just going to close these existing windows down right now and reopen the Employee table and I'll just say select the Top 1000 Rows. And what'll happen is it's bringing back that information where EmployeeID 500, all the data that I just entered in, and it does actually show me the command that is really being executed right now, which is just a Select statement.
We'll get into those a lot later. And you'll notice that I do have a Keys folder and I can expand it and it can show me PK_Employee, which means that the primary key for employee has been defined. Now there are different kinds of keys that you can create. You can also create what are called secondary keys, anytime you know you're going to be using a particular field to try and access a part of the database, but we'll talk about those a little later on. Primary keys are what we need to start dealing with first.
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.