One of the most common columns found in almost every relational database table is a unique identifier. In this video, learn how using the SQL Server IS IDENTITY feature can have the database management system automatically generate unique identifiers for all of your records.
- [Narrator] Beyond the Name and Data Type of our table columns, there's a whole suite of properties that we can configure that will further control what data can get stored in the database. Take a look at this. I'm going to expand the LadonHotel database, and then expand the Tables folder, and I'm going to right-click on the Guests table and go back into our designer. At the bottom of the screen, we have the additional Column Properties that I want to look at, and you can go ahead and expand this window up by dragging on this divider line to see more information down below. Now, at the top of this we have the General Properties, and these are properties that we have already defined up above, such as the column's name, it's data type, and the length.
Notice that the properties area breaks the data type and length into two different components. We have mvarchar here, and then the length of 50 here. What we enter in the table design grid up at the top is called a condensed data type that combines both of those values together. We can see that in a line further below in the column properties, called Condensed Data Type right here. Now, these properties are dynamic. So, for instance, if I go down to the bottom here, and I create a new column, and give it a decimal data type, and I'll just go ahead and accept the 18 comma zero notation there. If I go back and select that column, you'll see that we now have a precision and a scale here instead of length.
Or, if I change this column's data type to an int, and press enter and then go back and select it again, you'll notice that down below we don't have a length, or a precision, or a scale. And, that's because the int data type doesn't have any configurable options. So, here at the top, we have the general properties, and then down below we have some table designer properties, and you can scroll through this list to see some different things that we can add into our table of design. The one that I want to focus on is called Identity Specification. It's found right here. It's only available if you're using one of the integer data types. You can see that it has a little arrow next to it, which means that there's some additional properties that relate to it below.
So let's go ahead and twirl this open. That'll reveal the IS Identity Property, Identity Increment, and Identity Seed. So, what do these three properties do? One of the most common types of information that you'll want in all of your database tables is a unique identifier. A unique identifier will allow you to locate, unambiguously, a single record. And, it's one of the core requirements that make a relational database work. Things like employee IDs or people's Social Security Number, or driver's license number are all unique identifiers that help various organizations and governmental entities keep track of who is who.
And, because they're so common, SQL server has this capability to have the database engine automatically assign new, unique identifiers when new records are added to the database. So, let's go back up and give this column a name. I'm going to call it GuestID. Then, with the Data Type still set as int, I'm going to come down to the IS identity property, and change it from No, and using the drop-down menu, change it to Yes. That'll update the Identity Seed and the Identity Increment. The Identity Seed is basically just the starting number, and the increment is how much the system will increase each successive identifier that it assigns.
I'm going to set the Identity Seed to 1000. That way all of our guests will start with a four digit ID, and I'm going to leave the increment set to one. Let's go ahead and save the changes we've made to our table by clicking on the Save icon on the toolbar. Then, I'll come down to the Guest Table, and right-click on it, and choose Select Top 1000 Rows. That'll reveal the data that we've already stored in the table, and over here on the far right, we have the new GuestID column. We can see that each guest has been assigned their own unique ID number starting at 1000 for Katherine and incrementing by one for each guest.
Now, there's one last change that I want to make here. This isn't required, but it's usually good practice to make the tables unique identifier to be the first column in the table structure. So let's go ahead and close this tab here to return back into the designer. Then, I'll come to this selection box to the left of GuestID and give it a click to highlight that row. Then, I can click-and-drag and drop it here at the very top of the list. Let's go ahead and save that change to the table structure, and one more time, we can go ahead and close out of this tab. Then, right-click on the Guests Table and Select Top 1000 Rows again.
And, here we can see that the GuestID is moved to the beginning. So that's how you can use the Identity Specification Property to have SQL server automatically assign unique IDs to your records.
- Installing Management Studio
- Enabling features with SQL Server Configuration Monitor
- Creating a database
- Creating tables with Transact-SQL (T-SQL)
- Creating relationships between tables
- Creating views
- Creating user-defined scalar functions
- Backing up and restoring SQL Server
- Monitoring and management