From the course: Microsoft SQL Server 2019 Essential Training

Table column properties - SQL Server Tutorial

From the course: Microsoft SQL Server 2019 Essential Training

Start my 1-month free trial

Table column properties

“

- [Instructor] Beyond the name and data type of our table columns, there's a whole suite of properties that we can configure that'll further control what data can get stored in the database. Take a look at this, I'm going to expand the LandonHotel 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've 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 nvarchar 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, 0 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 in to our table's 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 are 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 1,000, 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 that 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 a good practice to make the table's 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 the 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 Guest table and select Top 1000 Rows again. And here we can see the GuestID has 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.

Contents