Chapter 05 - Creating Database Tables and Using Datatypes

The total storage of a smalldatetime datatype value is 4 bytes. SQL Server uses 2 bytes to store the number of days after the base date of January 1, 1900. Time is stored in the other 2 bytes as the number of minutes after midnight. The accuracy of the smalldatetime datatype is 1 minute. You can use smalldatetime to store values that are within its more-limited range and lesser precision when compared to datetime.

Use the smalldatetime datatype instead of the datetime datatype to store values in half the storage space.

In the following example, one column is defined by using the datetime datatype, and the second column is defined using the smalldatetime datatype. After the table is created, a minimum value is inserted into each column of a single row for the respective datatypes.

create table date_table
(date1 datetime,date2 smalldatetime)
insert into date_table
values ('Jan 1 1753','Jan 1 1900')
select * from date_table
date1                       date2
--------------------------- ---------------------------
Jan 1 1753 12:00AM          Jan 1 1900 12:00AM
(1 row(s) affected)

insert into date_table
values ('May 19 1994', 'Jun 7 2079')
Msg 296, Level 16, State 3

insert into date_table
values ('Jan 1 10000','May 19 1994')
Msg 241, Level 16, State 3

Specialized Datatypes

Although you'll probably find that you can use the integer, floating-point, character, and date/time datatype formats for storing 90 percent of your data, in some cases you'll probably need a more custom solution.

In these cases, you can use one or more of the specialized datatypes. For example, you may need to store only data that can be represented as true or false, yes or no. Since this is a binary condition, you may decide to create a custom datatype. As another example, you may need to store sets of data in a column that's larger than the 255-character limitation of the conventional character datatypes. Several additional datatypes are available to allow you to choose the best datatype for storing your information.

bit

While it is not explicitly stated in the SQL Server documentation, the bit datatype corresponds to the boolean datatype in other DBMSes and programming languages.

You can also use a single byte to define up to eight different bit columns of a table by using the bit datatype. The amount of space allocated for one or more bits is a single byte, and the bit columns don't have to be contiguous. If you define nine columns of a table by using the bit datatype, 2 bytes are used for the total of nine bit datatypes.

SQL Server stores information about columns defined using bit datatypes in the syscolumns system table by storing an offset to the bit column in the status column. You can't define an index that uses a column defined as a bit datatype.

timestamp

The counter value inserted by SQL Server into a timestamp column specifies the sequence of operations that SQL Server has performed. Values entered into a timestamp column are stored in a varbinary(8) format, not a datetime or smalldatetime format. Null values are permitted in a timestamp column by default. A timestamp value isn't a date and time, but it's always unique within the table and database. You can define only a single column of a table as a timestamp.

Timestamps are often used to ensure that a row can be uniquely identified. If you're updating columns in a row, it's a common practice to specify the timestamp field in the where clause of your update statement. This will make sure that you update only one row of the table. You can be assured of the uniqueness of the value because the server will maintain and update it any time you insert or update a row.

Timestamps are also used—again, as part of the where clause—to prevent two people from updating the same row. Because the timestamp is updated automatically whenever an update is made to the row, you can be sure that you're not going to overwrite someone else's information. If someone else updates a row that you're now working on, when they save their update, the row's timestamp will be updated, no longer matching your copy. When you issue the update command to save your changes, the where clause will fail because it can't find the specific row that you retrieved. Timestamps are excellent, server-maintained ways to make sure that you have a unique row identifier.

If you define a column with the column name timestamp and don't specify a datatype, the column is defined using the timestamp datatype. You can display the current timestamp value that's applied to the next timestamp column of a row that's updated or to a new row added using the global system variable @@dbts.

You can use a select statement to reference the global variable @@dbts by using the syntax

select @@dbts

0x01000000a3d2ae08

binary(n)

You must enter the first binary value preceded with 0x. You can enter binary data using the characters 0 through 9 and A through F. For example, enter the value A0 by preceding it with 0x, in the form 0xA0. If you enter values greater than the length that you defined, the values are truncated. Values are also padded with zeros after the least significant digit.

Here's another example. A column defined as binary(1) can store up to the maximum value of ff. In the following example, a table is defined with two columns with the datatypes binary(1) and binary(2). Three insert statements are used to enter successive pairs of values of 0, 1, ff, and fff in both columns.

create table binarytable
(x binary(1),y binary(2))
insert into binarytable
values (0x0,0x0)
insert into binarytable
values (0x1,0x1)
insert into binarytable
values (0xff,0xff)
insert into binarytable
values (0xfff,0xfff)
select * from binarytable
...
x    y
---- ------
0x00 0x0000
0x01 0x0100
0xff 0xff00
0x0f 0x0fff
(4 row(s) affected)

varbinary(n)

Unlike the binary datatype, varbinary datatype storage is limited to just enough space for the length of the actual value. Like the binary datatype, you must enter the first binary value preceded with 0x. You can enter binary data using the characters 0 through 9 and A through F. If you enter values that are greater than the maximum length you defined, the values are truncated.

In the following example, a table is defined with two columns with the varbinary(1) and varbinary(2) datatypes. Three insert statements are used to enter successive pairs of values of 0, 1, ff, and fff in both columns.

create table varbinarytable
(x varbinary(1),y varbinary(2))
insert into varbinarytable
values (0x0,0x0)
insert into varbinarytable
values (0x1,0x1)
insert into varbinarytable
values (0xff,0xff)
insert into varbinarytable
values (0xfff,0xfff)
select * from varbinarytable
...
x    y
---- ------
0x00 0x00
0x01 0x01
0xff 0xff
0x0f 0x0fff

text and image Datatypes

image datatypes are sometimes used for embedded OLE objects that are part of a row.

text

You can store an entire resume in a single column value of a table row.

Your data is stored in fixed-length strings of characters in an initially allocated 2K (2,048 bytes) unit. Additional 2K units are dynamically added and are linked together. The 2K data pages are logically—but not necessarily physically—contiguous. If you use an insert statement to insert data into a column defined as text, you must enclose the data within single quotation marks.

If you define a column using the text datatype and permit nulls, using an insert statement to place a null value in the column doesn't allocate even a single 2K page, which saves space. However, any update statement will allocate at least one 2K page for the text column regardless of any value that may or may not be supplied for that column.

image

Your data is stored in fixed-length byte strings in an initially allocated 2K (2,048 bytes) units. Additional 2K units are dynamically added and are linked together like the pages for a text column. The 2K data pages are logically—but not necessarily physically—contiguous.

Using text and image Datatypes

In the following example, two table columns are defined using image and text. Values are inserted into each column of a single row using an insert statement. The row is then retrieved from the table with a select statement.

create table imagetext_table
(image1 image,text1 text)
insert into imagetext_table
values ('123456789aczx+=\','12345678aczx+=')
select * from imagetext_table
image1                                 text1
-------------------------------------------------------
0x31323334353637383961637a782b3d5c     12345678aczx+=

 (1 row(s) affected)

Restrictions on text and image Columns

The amount of data that can be stored in a text or image table column makes each datatype unsuitable for use or manipulation in many Transact-SQL statements. This is simply because the amount of data that would have to be manipulated is too great. You can't specify a table column in an ORDER BY, GROUP BY, or compute clause that's a text or image datatype. SQL Server won't try to sort or group a table's rows using a column that can contain more than 4 billion bytes of data because too much data would have to be moved around and too large a space would have to be allocated in which to order the rows.

You also can't use a text or image column in a union unless it's a union all. You can't use a subquery that returns data values from a text or image datatype. You also can't use a text or image column in a where or having clause, unless the comparison operator like is used. You can't specify distinct followed by a table column that's defined as a text or image datatype.

Finally, you can't create an index or a primary or foreign key that's defined using a table column that you've defined as an image or text datatype.

money Datatype

smallmoney Datatype

You can store your monetary values in half the storage space if you choose the smallmoney rather than the money datatype.

When you add values to a table column defined as money or smallmoney, you must precede the most-significant digit with a dollar sign ($) or a sign of the defined monetary unit.

In the following example, a table is created with two columns that are defined using the money and smallmoney datatypes. In the first insert statements, values are incorrectly added because they aren't preceded with a dollar sign. A select statement shows that the values displayed are identical to those that were stored.

create table monetary_table
(money1 money,money2 smallmoney)
insert into monetary_table
values (16051.3455,16051.3455)
select * from monetary_table
money1                     money2
-------------------------- --------------------------
16,051.35                  16,051.35
(1 row(s) affected)

insert into monetary_table
values ($123,$123)
insert into monetary_table
values (922337203685477,214748.3647)
Msg 168, Level 15, State 1

A large monetary value, which is defined as a money datatype, is added to the first column. It's incorrectly entered because it isn't preceded by a dollar sign ($). The select statements show that the number is stored incorrectly. If you enter a value into a table column that's defined as money or smallmoney, it's stored as a floating-point datatype, which makes it subject to the rounding error.

insert into monetary_table
values (922337203685476.,0)
money1                     money2
-------------------------- ---------
16,051.35                  16,051.35
123.00                     123.00
922,337,203,685,475.98     0.00

insert into monetary_table
values ($922337203685476.,0)
select * from monetary_table
money1                     money2
-------------------------- ---------
16,051.35                  16,051.35
123.00                     123.00
922,337,203,685,475.98     0.00
922,337,203,685,476.00     0.00

insert into monetary_table
values ($922337203685477.5807,$214748.3647)
select * from monetary_table
money1                     money2
-------------------------- ---------
16,051.35                  16,051.35
123.00                     123.00
922,337,203,685,475.98     0.00
922,337,203,685,476.00     0.00
922,337,203,685,477.58     214,748.36

sysname Datatype

Understanding null and not null

The null characteristic for a table column allows you to omit the entry of a column value in the column. If you define the characteristic for a column as not null, Microsoft SQL Server won't allow you to omit a value for the column when you insert a row. The null characteristic provides a type of validation.

The default characteristic for a column is not null, which doesn't allow an undefined column value. A null that's defined for a column is stored differently than a space, a zero, or a null ASCII character (which is all zeros). The interpretation of a null entry is undefined or unavailable because no explicit or implicit value is assigned to the column when a row is inserted.

If you reference a row that contains a null, the entry (null) is displayed in place of a column value to indicate that there's no entry in the row for that column.

There are two ways to designate that a column or storage structure contains a null:

• If no data is entered in the row for that column and there's no default value for the column or datatype, a null is entered automatically. You can define a default value that's inserted automatically into the table column when a column value is omitted. A default value can be added in place of a null.

• You can enter a null explicitly by using null or null without quotation marks when a row is inserted into the table. If you enter null within quotation marks, it's stored as a literal string rather than a null.

In the following example, a table is created that permits a null entry for numeric integer datatypes and character datatypes. A null is explicitly inserted into both columns of a single row in the table. In the following example, a select statement displays (null) for both column values of the row.

create table nulltable
(x int null, y char(10) null)
insert into nulltable
values (null,null)
select * from nulltable
x           y          1
----------- ----------
(null)      (null)
(1 row(s) affected)

To continue the example, null is entered in the second column (y) because only the x column precedes the values clause. A null value is added to the second column y implicitly because no value is specified in the list port (values within parentheses separated by a comma) of the values clause.

insert into nulltable
(x)
values (5)
select * from nulltable
x              y
--------------------------
5               (null)
(2 row(s) affected)

ANSI Support for nulls

Use the following set command to cause nulls to be permitted automatically in table columns or user-defined datatypes:

SET ANSI_null_DFLT_ON

sp_dboption database-name, 'ANSI null default', true

If you've changed the null characteristic during a session or for a database, you can set it back to the default by using the commands

SET ANSI_null_DFLT_OFF

sp_dboption database-name, 'ANSI null default', false

null Manipulation

However, rows that contain multiple null values in a column referenced in an ORDER BY, GROUP BY, or DISTINCT clause of a select statement are treated as identical values. All three clauses group together rows with identical values. ORDER BY is used to sort rows, and, in the case of nulls, all entries in the same column are sorted together. Columns containing nulls appear at the beginning of a sequence of rows that are sorted in ascending order.

GROUP BY forms groups using identical values, and all nulls of a column are placed in a single group. The distinct keyword used in a select clause removes all duplicates from one or more column values and removes multiple null values as well. Columns that contain nulls are considered to be equal when you define an index that uses a null column.

See the sections in Chapter 6 entitled Using an ORDER BY Clause Using a GROUP BY Clause and Using DISTINCT to Retrieve Unique Column Values

If you perform computations with columns or other data structures that contain nulls, the computations evaluate to null. In the following example, the evaluation of the expression x=x+1 evaluates to null because the x column contains only a single row with a null defined for the x column:

select * from nulltable
where x=x+1
x           y
----------- ----------
(0 row(s) affected)

select * from employees
where badge=null
Msg 221, Level 16, State 1

Using ISNULL()

ISNULL(expression,value)

select x,ISNULL(x,531),y, ISNULL(y, 'NO ENTRY')
from nulltable
x                       y
----------- ----------- ---------- ----------
(null)      531         (null)     NO ENTRY
(1 row(s) affected)

identity Property

In the following example, the second column is defined with the property identity. After two rows are added to the table, a subsequent retrieval of the table rows shows that the identity column values were generated by the identity mechanism.

create table identity_table
(name char(15),row_number integer identity(1,1))
insert into identity_table
(name)
values ('Bob Smith')
insert into identity_table
(name)
values ('Mary Jones')
select * from identity_table
name            row_number
--------------- -----------
Bob Smith       1
Mary Jones      2

(2 row(s) affected)

You can use the keyword identitycol, as well as the name of the column, to reference the column of a table that's defined with the property identity.

Creating and Using Constraints

PRIMARY KEY Constraints

The definition of a PRIMARY KEY constraint on a single table doesn't by itself permit referential integrity. You must also define corresponding foreign keys in the tables whose rows will be combined with the table in which you define the PRIMARY KEY constraint.

The syntax of the PRIMARY KEY constraint is as follows:

CONSTRAINT constraint_name PRIMARY KEY CLUSTERED (column_name_1 column_name_n)

Create table employees4
(name char(20),department varchar(20),badge integer,
constraint badge_pays2 foreign key (badge) references employees4 (badge))

FOREIGN KEY constraint

The PRIMARY KEY and FOREIGN KEY constraints ensure that corresponding rows of associated tables continue to match so that they can be used in subsequent relational joins. The automatic updating of the corresponding columns of different tables after they're defined as PRIMARY KEY and FOREIGN KEY constraints is called declarative referential integrity, a feature added to SQL Server in version 6.0.

The syntax of the FOREIGN KEY constraint clause is as follows:

CONSTRAINT constraint_name FOREIGN KEY (column_name_1 column_name_n) REFERENCES 
_table_name (column_name_1 column_name_n)

Create table pays
(hours_worked integer, pay_rate integer,badge integer,
constraint badge_pays2 foreign key (badge) references employees4 (badge))

unique Constraint

Create table employees2
(name char(20), department varchar(20),badge integer,
constraint badge_nodupes unique nonclustered (badge))

check Constraint

The process of associating a check with a table column is called binding. You can define and associate multiple checks with a single column. A check can be defined for a column, even though a rule is already defined on the column. In the following example, a check constraint is defined on the column department to restrict subsequent entries to valid departments.

Create table employees5
(name char(20), department varchar(20),badge integer
check valid_department (department in 
[ccc] ('Sales','Field Service','Software','Logistics')))

You can often choose to implement a feature such as a restriction on the values that can be entered into a column in the way that you feel is the easiest to set up. For example, you can choose to restrict the values entered into the columns of a table by using a check constraint rather than by defining a rule and binding it to the column.

However, you should investigate each alternate mechanism, because one may be more appropriate for your use. Although a check constraint is quicker and simpler to set up to restrict the column values than a rule, a rule is more flexible in one way. After you define a rule, it can be bound to a column in multiple tables. A rule may prove more useful to you if you're going to use it to restrict column values on columns that are in multiple tables.

default Constraint

DEFAULT default_name value FOR column_name

Create table employees6
(name char(20),department varchar(20),badge integer,
DEFAULT department_default 'Sales' for department)

See Chapter 11, "Managing and Using Rules and Defaults"

Microsoft recommends that you use a default constraint rather than a default when you've defining a default value for a column in a single table because a default constraint is stored with the table, rather than as a separate database object. If you drop a table, the constraint is automatically dropped. When a table isdeleted, a default is bound to a column of the table isn't deleted.

With a default constraint, you can use a set of functions called niladic functions. A niladic function inserts a value that's generated by SQL Server.

Niladic functions allow a system-supplied value that's information about the current user or a timestamp to be inserted when no value is specified. The ANSI niladic functions that can be used with a default constraint are current_user(), session_user(), system_user(), user(), and current_timestamp(). current_user(), session_user(), and user() return the user name stored in the database of the user issuing an insert or update. The system_user() function returns the SQL Server logon ID of the user, and the current_timestamp() function returns the date in the same form that's returned by the getdate() function.

Creating User-Defined Datatypes

You can use a descriptive name for the user-defined datatype that describes the type of data that it contains.

Creating User-Defined Datatypes with sp_addtype

sp_addtype user_defined_datatype_name, system_datatype, null | null

An error message is generated if you specify not null for a column and don't create a default, or specify a value at insertion. You can also change the null or not null characteristic for a user-defined datatype when you define a column in a table.

In the following example, a user-defined datatype is created by using sp_addtype. The characteristics are displayed with sp_help. A new table is created in which the column is defined using the user-defined datatype.

sp_addtype names, 'char(15)', null
Type added.
sp_help names
Type_name     Storage_type    Length Nulls Default_name    Rule_name
------------- --------------- ------ ----- --------------- ---------
names          char            15     1     (null)          (null)
create table usertype_table
(charstring names)

insert into usertype_table
values ('this is a string')
select * from usertype_table
charstring
---------------
this is a strin
(1 row(s) affected)
sp_help usertype_table
Name                           Owner                          Type
------------------------------ ------------------------------ ------------
usertype_table                 dbo                            user table
Data_located_on_segment        When_created
------------------------------ ---------------------------
default                        May 19 1994 12:46PM
Column_name     Type            Length Nulls Default_name    Rule_name
--------------- --------------- ------ ----- --------------- -------------
charstring      names           15     1     (null)          (null)
Object does not have any indexes.
No defined keys for this object.

Creating User-Defined Datatypes with the Enterprise Manager

1. Expand the databases by clicking on the plus (+) box next to the Databases folder. Click a database to select it. Your user-defined datatype will be created in the selected database.

2. Choose User Defined Datatypes from the Manage menu.

3. Enter a name in the New Name field of the Manage User-Defined Datatypes dialog box.

4. Enter a system datatype in the Datatype field. Enter a length for the datatype in the Length field if you choose a datatype such as char or varchar. You can also decide to allow nulls, apply a previously defined rule or default value to the user-defined datatype, or specifiy an owner.

5. Click OK to complete the creation process.

Fig. 5.1 - You can list the user-defined datatypes from the Manage User-Defined Datatypes dialog box.

Dropping User-Defined Datatypes

sp_droptype typename

sp_droptype names
Type is being used. You cannot drop it.
object                  type owner             column               datatype
----------------------- ---- ----------------- -------------------- ---------
usertype_table          U    dbo               charstring           names
(1 row(s) affected)
drop table usertype_table

sp_droptype names
Type has been dropped.

1. Expand the databases by clicking on the plus (+) box next to the Databases folder. Click a database to select it. Your user-defined datatype will be created in the selected database.

2. Choose User Defined Datatypes from the Manage menu.

3. Select the user-defined datatype.

4. Click Drop.

Working with Datatypes

select name,type
from systypes
order by type
name                      type
------------------------- ----
image                     34
text                      35
timestamp                 37
varbinary                 37
intn                      38
sysname                   39
varchar                   39
binary                    45
char                      47
badge_type                47
tinyint                   48
bit                       50
smallint                  52
int                       56
badge_type2               56
smalldatetime             58
real                      59
money                     60
datetime                  61
float                     62
floatn                    109
moneyn                    110
datetimn                  111
smallmoney                122
(24 row(s) affected)

sp_helpsql 'datatype'
helptext
------------------------------------------------------------------------
Datatype
Datatype                   Definition

Binary(n)                 Fixed-length binary data. Maximum
                          length=255 bytes.
Bit                       A column that holds either 0 or 1.
Char(n)                   Character data. Maximum length=255
                          bytes.
Datetime                  Dates and times with accuracy to milliseconds.
Float                     Floating-point numbers.
Image                     Large amounts of binary data (up to
                          2,147,483,647 characters.)
Int                       Integers between 2,147,483,647 and
                          -2,147,483,648.
Money                     Dollar and cent values.
Real                      Floating point numbers with 7-digit precision.
Smalldatetime             Dates and times with accuracy to the minute.
Smallint                  Integers between 32,767 and -32,768.
Smallmoney                Monetary values between 214,748.3647 and
                          -214,748.3648.
Text                      Large amounts of character data (up to
                          2,147,483,647 characters.)
Timestamp                 Automatically updated when you
                          insert or update a row that has a timestamp
                          column, or use BROWSE mode in a
                          DB-LIBRARY application.
Tinyint                   Whole integers between 0 and 255.
Varbinary(n)              Variable-length binary data. Max
                          length=255 bytes.
Varchar(n)                Variable-length character data. Max
                          length=255 bytes.

Creating Tables and Defining Columns Through the Enterprise Manager

See the Chapter 1 section entitled SQL Enterprise Manager

1. Expand the databases by clicking on the plus (+) box next to the Databases folder. Click a database to select it. Your table will be created in the selected database.

2. Choose Tables from the Manage menu.

3. Enter a column name in the Column Name field.

4. Use the mouse or Tab key to move to the Datatype field. Select a datatype from the list that appears.

5. If the datatype that you've chosen requires the specification of a length, enter it into the Length field.

6. To allow null values, leave the Nulls field selected.

7. If you've previously defined default values, you can choose one in the Default field.

8. Repeat steps 3 through 7 to continue specifying up to 250 columns and their characteristics. Figure 5.2 shows the Manage Tables window after information for three columns is entered.

   Fig. 5.2 - The null property is automatically enabled for each column that you enter, though you can uncheck it.

9. Click the Save Table button on the Manage Tables toolbar to bring up the Specify Table Name dialog box.

10. Enter a name for the new table in the Specify Table Name dialog box (see fig. 5.3).

    Fig. 5.3 - Name the table to complete the operation.

11. Click OK. The new table appears as the selected table in the Manage Tables window.

You also can define properties on columns such as an identity, primary key, or constraint by clicking the Advanced Features tool on the toolbar of the Manage Tables window while you're defining the table. For example, in figure 5.4, a column has been defined as an identity column in the Identity Column combo box of the Manage Tables window after the Advanced Features tool is clicked.

Fig. 5.4 - You can specify an initial value (seed) and increment for your identity column.

Adding Data to a Table with insert

insert INTO table_name
(column_name_1,...,column_name_n)
VALUES ('string_1',...'lstring_')

You don't have to list the columns and their values in the same order as they were defined in the table. You must, however, enter the values in the VALUES clause in the correct order as the column names listed in the previous line; otherwise, data values may be inserted into the wrong columns.

In the following example, the columns are listed in the order in which they were defined in the table—but they don't have to follow the same order. The values are entered in the VALUES clause in an order that corresponds to the order of the columns named in the previous line.

insert into employees
(name, department, badge)
values ('Bob Smith', 'SALES', 1834)

insert into employees
(name, badge)
values ('Bob Mariah', 1999)

Table 5.4—Effect of null and Default Values on Table Column Entries

Column Characteristic(s)	User Entry	Resulting Entry
null defined	No default value defined	null
not null defined	No default value defined	Error, no row inserted
null defined	Default value defined	Default value
not null defined	Default value entered	Default value

Use (null) within the values list to insert a null into a column that has been defined to permit the null.

From Here...