SQL Server 2000 Administration in 15 Minutes a Week: Table Basics (Part 1) - Page 4
August 22, 2002
Once you have a logical design to work from, the process of creating your tables is quite simple. Start by opening Enterprise Manager, expand the server and database you would like to add a table too, then right click "Tables" and select "New Table..." from the menu.
Click to Enlarge
The New Table window appears and should look something like the following:
Each row represents an individual column of the table being created/edited.
"Column Name" is used to enter the name of the column.
"Data Type" sets the type of data the column will contain.
"Length" specifies the length (or 'n') of the column. For some data types, such as char and varchar, this value can be changed and specifies the maximum number of characters the column can store. For other data types, such as int and decimal, that don't have a length, "Length" shows the size (in bytes) the column will need.
"Allow Nulls" determines whether a particular column can be left blank. A check indicates nulls are allowed in that column. That is, if a row is added/edited, the row is not required to have a value in that field. If there is no checkmark in Allow Nulls, any rows that are added or edited are required to have a value entered for that field. Remember: 1, 0, and <NULL> (or 'ABC', ' ', <NULL>) are three different things. For example, while 0 and <NULL> may seem similar, 0 typically indicates "False" and <NULL> indicates "No Value." It is important to keep this concept in mind; it will have implications for us further down the road.
A table that holds employee data may look something like:
There are a few more things to note here.
First, if you have ever used Access before, you should recognize the key icon on the left of the EmployeeID row. This indicates what row(s) of the table make(s) up the primary key (i.e. the column(s) that are used to uniquely identify every row in a table). To set the primary key, select the appropriate row (you can hold down the Ctrl key to select more than one row at a time) and then click the "Set primary key" icon (looks like a key) in the toolbar.
Next, as you move from row to row, you will notice that the available options on the "Columns" tab change. Lets take a look at these options individually:
Description - A space for you to enter any comments about what the column is used for. Anything entered in this box will have no effect on SQL Server or the table.
Default Value - The value that will be entered if no other value is specified when a row is added to the table. For example, I could set the default value of the Title column to "staff." Each time an employee gets added and no title is entered, "staff" will automatically be substituted in place of <NULL>. In addition, you are not limited to just text for the default value, you can also use functions. Some more common ones include GETDATE() that returns the current system date/time and NEWID() that returns a new globally unique identifier. Note that if you set the column's "Identity" property to yes, the default value is unavailable.
Precision/Scale - Used to set the precision and scale values of the decimal and numeric data types. These options are not available for other data types. For more information on precision and scale, see the decimal data type in the data types table at the beginning of this article.
Identity/Seed/Increment - Similar to the AutoNumber option in Access. If a column's Identity option is set to true, SQL Server will automatically generate a new number for each row added to the table in this column. A table can have only one column defined with the Identity property set to yes, and that column must use the decimal, int, numeric, smallint, bigint, or tinyint data type. The Seed value specifies what number SQL Server should start at. The Increment value specifies the number that should be added to the Seed value to determine successive identity numbers. Note that Identity columns are only guaranteed to generate unique numbers within one table - two tables both with an Identity column may (or may not) generate the same numbers. Another thing to keep in mind is that if the table experiences many deletions, large numbering gaps may occur because deleted identity values are not reused.
Is RowGuid - Specifies that this column contains the GUID for the rows in this table. Only one table can have its Is RowGuid value set to yes, and the data type for this column must be set to uniqueidentifier. In addition, you should also set the default value for this column to use the NEWID() function. Unlike an Identity value, GUID values are guaranteed to (aka "should") be unique for every row, in every table, in every database, on every computer in the world.
Formula - Used to enter a formula to make this a computed column. Instead of just storing data, a computed column takes a formula (like LastName & ', ' & FirstName) and generates a value depending on the formula you entered. In addition, computed columns are generated on the fly, so updating one column that the formula references will update the computed column automatically.
Collation - Gives the ability to set the collation for each individual column of a table. <database default> will set the column to use the same collation setting as the database the table is located in.
Page 5: Creating Tables (Continued)
» See All Articles by Columnist Michael Aubert