Join Adam Wilbert for an in-depth discussion in this video Creating your first table in SSMS, part of Database Foundations: Core Concepts.
- Now we have a database for H+4 that's ready to go. But a database without any tables is a little bit pointless. So lets go ahead and add a table and some data to give this thing something to store. I'm going to go into the H+4 database that we created in the last movie. I'll go ahead and expand these options here. Inside we have the different objects within this database. In this case I'm going to go to Tables. In order to create a new Table, what I can do is right-click on that Table's group, and choose Table from the pop-up menu. Notice that as I go to other options down here in the database, for instance, if I go to Views and right-click, it says New View.
And if I right-click on the database, it says, New Database. Even though the Table's options doesn't say New Table, it's exactly what it does. It does create a New Table. So when I selected that option it created this new table design interface right over here on the right. Now the upper half of this screen is dedicated to defining the different columns that are going to make up this table. We'll define the column name here. The data type that it's going to store. And whether we want to allow no values or blanks in that data row. Down below, we have some additional properties for each column. Lets go ahead and fill a data table that will store information about our various products.
The first field is going to be ProductID. If I go over the data type, we can choose what type of data we want to store in this field. Now we're going to talk about data types in depth in the next course in this series. But let me give you a quick overview. For this product ID, we want to sort a numerical value. And the most popular type of numerical value is the Integer or int data type. We can either use the drop-down menu here and selct int, from the list here. Or I can just type in int in this data type box, and select it that way. For the product ID we're going to establish this as the primary key for the table.
So it's going to uniquely identify each of our products in this table. Since that's the case, I'm going to go over here and make sure this is selected. And go up here to the tool bar and choose the set primary key button here. That'll apply a key icon to this particular row. And it'll force us to have to enter in a value. It wont allow no. So you see that check box automatically turned off. Now there's one other thing that I want to set for this particular column. Lets come down here to the Common Properties. And 'm going to click here and drag up to give myself some more room. In the Column Properties, I'm going to scroll through this list until I find a property called Identity Specification, here.
Lets go ahead and twirl this arrow down here to exceed the other options down below. The first option says Is Identity. And you can see it's currently set to No. I want to change this to Yes. I'll go over here to the far right, and using the drop-down list, I'll choose Yes. When we set this as an Identity field, basically what we're saying is that we're not going to supply our own Integer values every time we create a new record. We're going to let SQL Server handle that for us. The Identity Seed down here, this third option, is the first number for the first product that gets entered.
Instead of one, I want to start on product number 1,000. The Identity Increment is going to be the number that's going to be incremented by for each additional product. So in this case we're setting the Identity Specification to Yes. The Identity Seed to 1,000. And the Identity Increment to one. That means that the first product that we get entered will automatically be numbered product ID number 1,000. And the second product we enter, will be item 1,001. So our Product ID column is finished. Lets go ahead and add some more columns. I'll come back up here to the next row. And we'll define our second column as the ItemName field.
The data type for ItemName is going to be a text field. So we can go over here to data types and we can see some of our options. The first one I want to look at is called char. This is a character field, and the default is going to be storing 10 characters. Any time you see the number in the parenthesis here, that's how many individual characters is going to store. We can change this number to whatever we want. For instance, instead of char(10), I can change it to char(50). The char data type will store exactly this number of characters in the field. Now, since my item names are going to be of a variable length, each item might have more or less characters than the item that precedes it, I want to go with a different data type here.
So instead of char, I'm going to choose the data type of varchar. Lets go ahead and scroll down here at the bottom. And we'll choose varchar. You'll see that that defaults to a length of 50. Again, we can change that to whatever we want. If I want to start a variable character length of 100, I can do it that way. Now there's two additional options here that we have for text fields. Instead of varchar, what I can do is specify and nvarchar data type. The n indicates that this character data type will be able to store unicode characters. That way if I have any products that come from Spain, or France, or some country that uses unique characters with accent marks, then this data type will allow me to store that name here instead of my data table.
Again, I don't want to have an entry where I don't have the item name. So I'm going to turn off this check box here to allow nulls. That'll force me to enter in a value. Alright, lets go down to our third column, and we're going to type in Who. This column is going to define who this product is designed for. Wether they're men, women, or is a unisex product. In this case I'm going to choose char and then in parenthesis I'm going to type in the one. This will indicate that I can only store a single character. So in our case that'll be an M, a W, or a U. I'm going to leave Allow Nulls on because if this doesn't apply to a particular product, I don't want to have to be forced to enter this in.
So I'm going to leave that check box on. Lets come down here to the next column. This is going to be Price. And we'll come over here to data type, and we're going to choose my favorite data type which is money. Again, I'm going to make sure that we force a price to be identified for every product. So I'm going to turn this check box off, so that we can't leave it blank. So there's our data table. We can either go ahead and save it now, or we can close it and save it in the pop-up box that comes up. I'm just going to choose to save the table by going up to the Save icon over here. And I'm going to name this table Products.
Lets go ahead and say OK. And that gets saved and we can close it here. And go take a look at our tables. Now you notice that our table isn't automatically showing up here. And that's the case with a lot of different objects that you'll create inside of Management Studio. Often times what you'll have to do is right-click on the collection here and choose Refresh from the pop-up menu. That'll show you any objects that have recently been updated or added. Notice that we can also go up here to the button in the object explorer and choose Refresh from here. And we also have the same option in the View menu, and Refresh down here at the bottom.
Notice that we have a shortcut key of F5. And that's one that I'm going to recommend that you commit to memory because it's something that you're going to do a lot inside of Management Studio. Refreshing this preview in Object Explorer. So F5 will refresh your view. Lets go ahead and flight off of this. Now that we have our data table, lets go ahead and add in a product. In order to add a product, I'll right-click on the table itself here, this divio.Products table. And I'm going to choose Edit Top 200 Rows. That'll show me a data table of the existing data and allow me to enter in new values.
Now notice if I go here to the product ID and try to type in a number, it's not going to let me do that. That's because we previously set this up as the identity field. So it's going to manage the sequence numbers for us. Lets go over here and enter in an item name. In this case I'm going to type in Thermal Vest. Lets go over here to the next column. We're going to type in a value of U, because this is a Unisex product. And over here on price, I'm going to go ahead and type in 95. When I get done, I can click into the next row, and those values will get stored into the database.
Notice also that when we entered in that value that the product ID updated to that seed value of 1,000 that we established earlier. So those are the basics of creating a new table and entering values directly into the table. Lets go ahead and close this out here. And we'll leave it here for the next movie.
Note: This course will also prepare certification candidates for the Microsoft Technology Associate Exam 98-364, Database Administration Fundamentals.
- Understanding flat file, hierarchical, and relational databases
- Installing SQL Server 2014 Express
- Creating your first database and tables
- Creating and editing database objects
- Writing commands in DML
- Selecting and inserting data
- Updating and deleting data
- Establishing relationships and file naming conventions