Datasheet

in how the data is treated. The classic text, ntext, and image data types are Large Object (LOB) data types
and can’t typically be used with parameters. The new variable data types with the (max) option are
Large Value Types (LVT) and can be used with parameters just like the smaller sized types. This brings
a myriad of opportunities to the developer. Large Value Types can be updated or inserted without the
need of special handling through STREAM operations. STREAM operations are implemented through
an application programming interface (API) such as OLE DB or ODBC and are used to handle data in
the form of a Binary Large Object (BLOB). T-SQL cannot natively handle BLOBs, so it doesn’t support
the use of BLOBs as T-SQL parameters. SQL Server 2005’s new Large Value Types are implemented as a
Character Large Object (CLOB) and can be interpreted by the SQL engine.
Nullability
All rows from the same table have the same set of columns. However, not all columns will necessarily
have values in them. For example, a new employee is hired, but he has not been assigned an extension
yet. In this case, the extension column may not have any data in it. Instead, it may contain NULL, which
means the value for that column was not initialized. Note that a NULL value for a string column is dif-
ferent from an empty string. An empty string is defined; a NULL is not. You should always consider a
NULL as an unknown value. When you design your tables you need to decide whether or not to allow a
NULL condition to exist in your columns. NULLs can be allowed or disallowed on a column-by-column
basis, so your employee table design could look like that shown in Figure 1-6.
Figure 1-6
Relationships
Relational databases are all about relations. To manage these relations you use common keys. For exam-
ple, your employees sell products to customers. This process involves multiple entities:
❑ The employee
❑ The product
❑ The customer
❑ The sale
To identify which employee sold which product to a customer you need some way to link all the entities
together. These links are typically managed through the use of keys, primary keys in the parent table
and foreign keys in the child table.
As a practical example you can revisit the employee example. When your employee sells a product, his
or her identifying information is added to the Sale table to record who the responsible employee was,
as illustrated in Figure 1-7. In this case the Employee table is the parent table and the Sale table is the
child table.
13
Introducing Transact-SQL and Data Management Systems
04_57955x ch01.qxd 9/19/05 12:43 PM Page 13