Once the database is
created, you're ready to begin adding objects to it. Let's begin by creating
some
Tables using SQL's
CREATE TABLE statement. To ensure that those tables are created in the new
database, be sure to
change the current database focus to SQLSERVERQUEST before issuing any of these
commands.
You can do this two
ways: You can execute a USE command—USE SQLSERVERQUEST— in the query edit window prior to executing any other
commands, or (assuming you're using Query Analyzer) you can select the new database from the DB:
combo-box on the edit window's toolbar (select <Refresh> from this list
if your new database is not
visible at rst).
The DB: combo-box
reflects the currently selected database, so be sure it points to SQLSERVERQUEST
Before proceeding.
Execute the
following command to create the customers table:
USE SQLSERVERQUEST —
Change the current database context to SQLSERVERQUEST
GO
CREATE TABLE
customers
(
CustomerNumber int
NOT NULL,
LastName char(30)
NOT NULL,
FirstName char(30)
NOT NULL,
StreetAddress
char(30) NOT NULL,
City char(20) NOT
NULL,
State char(2) NOT
NULL,
Zip char(10) NOT
NULL
)
Once the customers table
is built, create the orders table using similar syntax:
CREATE TABLE orders
(
OrderNumber int NOT
NULL,
OrderDate datetime
NOT NULL,
CustomerNumber int
NOT NULL,
ItemNumber int NOT
NULL,
Amount numeric(9,2)
NOT NULL
)
Most SQL concepts
can be demonstrated using three or fewer tables, so we'll create a third table.
Create
The items table
using this command:
CREATE TABLE items
(
ItemNumber int NOT
NULL,
Description char(30)
NOT NULL,
Price numeric(9,2)
NOT NULL
)
These commands are
fairly self-explanatory. The only element that might look a little strange if you're
new to SQL Server is
the NOT NULL specification. The SQL NULL keyword is a special syntax token
that's
used to represent
unknown or nonexistent values. It is not the same as zero for integers or
blanks for
character string columns.
NULL indicates that a value is not known or completely missing from the
column—that it's not
there at all.
The difference
between NULL and zero is the difference between having a zero account balance
and
not having an
account at all. (See Chapter 3, "Missing Values," for more
information on NULLs.) The
NULL/NOT NULL
specification is used to control whether a column can store SQL's NULL token.
This is
formally referred to
as column nullability. It dictates whether the column can be truly empty. So,
you
could read NULL/NOT
NULL as NOT REQUIRED/REQUIRED, respectively. If a field can't contain NULL, it
can't be truly empty
and is therefore required to have some other value.
Note that you don't
have to specify column nullability when you create a table—SQL Server will
supply a
default setting if
it's omitted. The rules governing default column nullability go like this:
• If you explicitly
specify either NULL or NOT NULL, it will be used (if valid—see below).
• If a column is
based on a user-dened data type, that data type's nullability specification is
used.
• If a column has
only one nullability option, that option is used. Timestamp columns always
require
values, and bit
columns can require them as well, depending on the server compatibility setting
(specified via the
sp_dbcmptlevel system stored procedure).
• If the session
setting ANSI_NULL_DFLT_ON is set to true (it defaults to the setting specified
in the
database), column
nullability defaults to true. ANSI SQL species that columns are nullable by
default.
Connecting to SQL
Server via ODBC or OLEDB (which is the normal way applications connect) sets
ANSI_ NULL_DFLT_ON
to true by default, though this can be changed in ODBC data sources or by
the calling
application.
• If the database
setting ANSI null default is set to true (it defaults to false), column
nullability is set
totrue.
• If none of these
conditions species an ANSI NULL setting, column nullability defaults to false so
that
columns don't allow
NULL values.
TSQL Tutorial...
No comments:
Post a Comment