Databases Reference
In-Depth Information
parentheses, along with any constraint definitions. You can see that the com-
mand creates four columns named SerialNumber, Model, Year, and Class, set-
ting the data type and nullability for each. “NOT NULL” says a value must be
provided during data entry or modification. “NULL” means that you can leave
the column blank during data entry and a null value will be entered as the
default. The primary key is identified as SerialNumber. The command is creat-
ing a clustered index for the primary key, so the table data will be physically
sorted in primary key order. The ON [PRIMARY] clause at the end specifies the
physical storage location, in this case, specifying the default storage location.
As you learned in Chapter 4, a null value is a special type of value. In some
cases, you want to set the column value as unknown or undefined rather than
as what you might consider “nothing” values of zero or an empty string. A null
value represents “no value,” either unknown, undefined, or no value applied.
Even though it stands for “nothing,” a null can be significant.
Consider an inventory table. One of the columns you might have is the
QuantityOnHand column, the number of items that you currently have in stock.
Typically, you would expect to see an integer value. A value of 0 indicates that
you do not currently have any of that item in stock. But what about a null value?
You could have a null entered automatically if a record is added to the table
without specifying an amount for QuantityOnHand. You could then retrieve a
list of items that you have never had in stock by retrieving all rows with a null
value in QuantityOnHand.
One place null values can be problematic is in text fields. You use charac-
ter data types to store text data. If you don't see a value, it could be a null, or
it could be a zero-length string, a valid character string that happens to contain
zero characters. The two might look the same to you, but a database server sees,
and sorts, the two differently. A query used to retrieve zero-length strings would
ignore null-valued fields. By the same token, a query retrieving records with a
null value would ignore zero-length strings.
There are several advantages to using SQL commands. They are typically
more powerful and more flexible than their GUI equivalents. They require min-
imal resources to run, so they can be used over a remote connection. You can
also use them inside of a
batch
file, which is a file that contains a set of exe-
cutable statements that can run as a group. In fact, you could create all of the
tables for a database, and the database itself, using a batch. You could even send
the batch to a user in another location as an email attachment and let that user
run the batch locally.
The biggest disadvantage is that some SQL commands can be complicated and
difficult to use. In order to use them effectively, you need to understand the state-
ment syntax and available options. The order in which options are specified in
SQL commands can even be an issue. Command documentation can sometimes
run on for several pages for a single command. In the case of creating tables, for
example, you need to know the available data types and how they are specified.
