Creating SQL Server Tables

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.

The New Table window appears.

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 text and ntext, this value can be changed and specifies the maximum number of characters the column will 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 (or ‘ABC’, ‘   ‘, ) are three different things. For example, while 0 and may seem similar, 0 typically indicates “False” and 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 this.

There are 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 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 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 – is 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 – is 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 . 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 must 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. will set the column to use the same collation setting as the database the table is located in.

Once you have added all the columns you can click the save icon (left most, looks like a disk) on the toolbar and you will be prompted to enter a name for the table.

Once you enter a name and click OK, you can close the New Table screen by clicking the lower, inner, X at the upper right. You can also use the “Window” menu to switch between windows or arrange them.

After refreshing the Tables list (F5) the table we just created appears in SQL Server Enterprise Manager.

You can always go back and edit a table by right clicking it in Enterprise Manager and selecting “Design Table”.

One last thing, using enterprise manager is not the only way to create a table – you can also use the CREATE TABLE statement. For more information see CREATE TABLE and ALTER TABLE in the SQL Server books online.