| Lesson 9 | Creating SQL-Server Tables |
| Objective | Identify key considerations for creating a table |
Creating Tables (Identify key considerations)
What are the primary considerations to take into account when creating a table?
Before you create a table, you must think about the data that it will store. Then you must consider what datatype should be used for each column of data. For example, suppose you want to store an employee's salary that never exceeds $200,000 per year. At first glance, you'd probably think that you should use the money datatype. However, as we saw in a previous module, the money datatype takes 8 bytes of storage, and it has a range of
Unless you are storing national deficit numbers, you really don't need such a large range, nor will you want to consume that much storage space.The smallmoney datatype, on the other hand, takes only 4 bytes of storage. It has a range of
Before you create a table, you must think about the data that it will store. Then you must consider what datatype should be used for each column of data. For example, suppose you want to store an employee's salary that never exceeds $200,000 per year. At first glance, you'd probably think that you should use the money datatype. However, as we saw in a previous module, the money datatype takes 8 bytes of storage, and it has a range of
-922,337,203,685,477.5808 to 922,337,203,685,477.5807.
Unless you are storing national deficit numbers, you really don't need such a large range, nor will you want to consume that much storage space.The smallmoney datatype, on the other hand, takes only 4 bytes of storage. It has a range of
-214,748.3648 to 214,748.3647,which is well within the range of values that you expect to store. That, in combination with the smaller storage requirement, makes it a much more sensible choice.
Composite primary keys
A primary key comprising more than one column is called a composite primary key. If you want to create a table that uses a composite primary key, you must use the CONSTRAINT clause to specify all the columns that it will comprise. To use the
You will see exactly how in the next lesson. In the next lesson, we'll dissect the Transact-SQL statement used to create a table.
CONSTRAINT clause, use the CONSTRAINT keyword after the last column definition. You must give it a unique name
within the table, like this:
CONSTRAINT pkey PRIMARY KEY (Column1, Column2, Column3)
You will see exactly how in the next lesson. In the next lesson, we'll dissect the Transact-SQL statement used to create a table.