Creating 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.
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
» 
Comment and Contribute
