| Executive Summary: You can use two techniques to create tables. The first technique uses T-SQL's CREATE TABLE command. The second technique uses T-SQL's SELECT INTO command. |
Up until now, the examples in the
T-SQL 101 series have accessed
tables that already existed or were
created by running T-SQL code included with the
lesson. Now I’ll show you two techniques you can use
to create your own tables. The first technique uses the
CREATE TABLE command. The second technique
uses a SELECT INTO command.
With the CREATE TABLE command, you specify
the new table name and define the table’s column structure.
With the SELECT INTO command, you use a
SELECT statement to query a database and store the
results in a new table, as specified by the INTO clause.
The column definitions of the new table exactly match
the column definitions in the result set.
At this point, you might be wondering, “Isn’t it
a little archaic to manually create tables with T-SQL
statements when there are graphical tools you can use
to create tables?” My response is that no matter what
tool you use to design tables, the tables don’t actually
get created in the database without T-SQL statements
being generated, submitted, and executed. Much like
an artisan using a hand planer rather than an electric
sander to smooth wooden surfaces, I find that creating
tables “the old-fashioned way” leads to a better
understanding of the tables and the columns therein.
Manually typing the T-SQL code forces you to slow
down and seriously consider the names and data types
you assign to each column, and how they relate to other
tables in the database. A T-SQL table creation script
also documents the table’s structure and lets you easily
recreate the table.
The Prerequisites
To run the examples in this lesson, make sure your database
contains the Employee table created in Lesson 3
and the Movie and Genre tables created in Lesson 5.
If you haven’t created these tables, you’ll find the code
in the 99448.zip file, which you can download by going
to www.sqlmag.com, entering 99448 in the InstantDoc
ID box, and clicking the 99448.zip hotlink.
Using the CREATE TABLE
Command
CREATE TABLE is one of those syntax-heavy commands
that can be a bit intimidating at first. Instead
of rehashing the excellent documentation for this
command in SQL Server 2005 Books Online (BOL)
at msdn2.microsoft.com/en-us/library/ms174979.aspx,
I’ll just cover the basics to get you started creating your
own tables. After you become familiar with the basics,
I highly encourage you to delve into this command’s
complexities.
The basic syntax of the CREATE TABLE command
is
CREATE TABLE DatabaseName.
SchemaName.TableName
(
ColumnName1 datatype,
ColumnName2 datatype,
ColumnName3 datatype
)
DatabaseName.SchemaName.TableName is referred
to as the fully qualified table name, where
- DatabaseName is the name of the database in which
the new table will reside. This argument is optional.
If left out, the table will be created in the current
database.
- SchemaName is the
schema to which the
table will belong. A
schema is a collection
of uniquely named
objects that aren’t
automatically tied to
a particular user. This
argument is optional. If left out, the table will be owned by the dbo schema,
which is the default schema of the database owner
(DBO). Note that in SQL Server 2000, you need
to specify the table’s owner rather than the schema
name as the second element in the fully qualified
table name. A table’s owner has full rights to alter
the table’s structure and contents, including the right
to delete it. In SQL Server 2005, Microsoft opted to
separate users and schemas, so the second element
in the fully qualified table name became the schema
name. The reason for this change is beyond the
scope of this introductory lesson. If you’d like to
learn why, read “User-Schema Separation” in SQL
Server 2005 BOL at msdn2.microsoft.com/en-us/
library/ms190387.aspx.
- TableName is the name of the table you want to
create. Table names can be a maximum length of
128 characters and must follow the rules of identifiers.
If you’re unfamiliar with identifiers, see SQL
Server 2005 BOL’s documentation at msdn2.micro
soft.com/en-us/library/ms175874.aspx.
Inside the parentheses is where you list your column
definitions, each of which must include a column
name and column data
type. Column names are
limited to 128 characters,
must be unique within the
table, and must follow the
identifier rules. Column
data types must be valid
SQL Server data types
(e.g., integer, varchar,
char). If you’re unfamiliar
with SQL Server’s
data types, you can read
about them in
SQL Server 2005
BOL at msdn
.microsoft.com/
en-us/library/
ms187752.aspx.
When defining
columns, you can
also specify constraints,
whether
null values are
allowed, and
other optional
arguments (more
on this shortly).
Now that
all the technical
information is
out of the way,
it’s time to start
having fun creating your own tables. The following
examples assume that the current database is MyDB
and you’re the DBO, so the optional DatabaseName
and SchemaName arguments aren’t included. When
you run the script, SQL Server will create the table,
which will be owned by the dbo schema, in MyDB. The
MyDB database needs to include the Employee table
from Lesson 3 and the Movie and Genre tables from
Lesson 5.
Some of the examples provided in Lesson 4 used
the MovieReview table, which contained four columns:
EmployeeID (employee IDs), Genre (textual descriptions
for movies’ genres), MovieName (textual descriptions
for movies’ names), and Stars (movies’ ratings,
where 1 star is the worst rating and 5 stars is the best
rating). Let’s create a new version of the MovieReview
table that contains IDs rather than textual descriptions
for the movie names and that doesn’t include a Genre
column. (The genre information will be obtained later
through a join operation.)
Continued on page 2
Prev. page
[1]
2
3
next page 
