Data Types and Table Structures

All the data in Microsoft® SQL Server™ 2000 databases is contained in objects called tables. Each table represents some type of object meaningful to the users. For example, in a school database you would find tables such as a class table, an instructor table, and a student table.

SQL Server tables have two main components:

• Columns

  Each column represents some attribute of the object modeled by the table, such as a parts table having columns for ID, color, and weight.

• Rows

  Each row represents an individual occurrence of the object modeled by the table. For example, the parts table would have one row for each part carried by the company.

Data Types

Because each column represents one attribute of an object, the data in each occurrence of the column is similar. One of the properties of a column is called its data type, which defines the type of data the column can hold. SQL Server has several base data types that can be specified for columns:

SQL Server 2000 also supports a table base data type, which can be used to store the result set of an SQL statement. The table data type cannot be used for columns in a table. It can only be used for Transact-SQL variables and the return values of user-defined functions. For more information, see Using Special Data.

Users can also create their own user-defined data types, for example:

-- Create a birthday data type that allows nulls.
EXEC sp_addtype birthday, datetime, 'NULL'
GO
-- Create a table using the new data type.
CREATE TABLE employee
   (emp_id         char(5),
   emp_first_name   char(30),
   emp_last_name   char(40),
   emp_birthday      birthday)

A user-defined data type makes a table structure more meaningful to programmers and helps ensure that columns holding similar classes of data have the same base data type.

SQL Server provides several data type synonyms to help support SQL-92 data type names not included as base data types, such as national character and character varying. When a synonym is specified in a CREATE TABLE statement, the column is assigned the base data type associated with the synonym. For more information, see Data Type Synonyms.

A domain is the set of all allowable values in a column. It includes not only the concept of enforcing data types, but also the values allowed in the column. For example, a part color domain would include both the data type, such as char(6), and the character strings allowed in the column, such as Red, Blue, Green, Yellow, Brown, Black, White, Teal, Grey, and Silver. Domain values can be enforced through mechanisms such as CHECK constraints and triggers.

When a column has been assigned a data type, all values placed into the column must be of that data type. SQL statements can specify that values of different data types be used as the source value only if SQL Server can implicitly convert the source value data type to the data type of the column. For example, SQL Server supports the implicit conversion of int values to decimal; therefore, SQL statements can specify int values as the value to be assigned to a decimal column.

The SQL Server 2000 sql_variant data type is a special data type that allows you to store values of multiple base data types in the same column. For example, you can store nchar values, int values, and decimal values in the same column. For more information, see Using sql_variant Data.

Null Values

Columns can either accept or reject null values. NULL is a special value in databases that represents the concept of an unknown value. NULL is not the same as a blank character or 0. Blank is actually a valid character, and 0 is a valid number. NULL simply represents the idea that we do not know what this value is. NULL is also different from a zero-length string. If a column definition contains the NOT NULL clause, you cannot insert rows having the value NULL for that row. If the column definition has only the NULL keyword, it accepts NULL values.

Allowing NULL values in a column can increase the complexity of any logical comparisons using the column. The SQL-92 standard states that any comparison against a NULL value does not evaluate to TRUE or FALSE, it evaluates to UNKNOWN. This introduces three-value logic to comparison operators, which can be difficult to manage correctly.

System Tables

SQL Server stores the data defining the configuration of the server and all its tables in a special set of tables known as system tables. Users should not query or update the system tables directly unless there is no other way to get the data required by the application. Only SQL Server should reference the system tables in response to administration commands issued by users. The system tables can change from version to version; applications referencing system tables directly may have to be rewritten before they can be upgraded to a newer version of SQL Server with a different version of the system tables. SQL Server exposes most of the information from the system tables through other means. For more information, see System Tables.

Temporary Tables

SQL Server supports temporary tables. These tables have names that start with a number sign (#). If a temporary table is not dropped when a user disconnects, SQL Server automatically drops the temporary table. Temporary tables are not stored in the current database; they are stored in the tempdb system database.

There are two types of temporary tables:

• Local temporary tables

  The names of these tables begin with one number sign (#). These tables are visible only to the connection that created them.

• Global temporary tables

  The names of these tables begin with two number signs (##). These tables are visible to all connections. If the tables are not dropped explicitly before the connection that created them disconnects, they are dropped as soon as all other tasks stop referencing them. No new tasks can reference a global temporary table after the connection that created it disconnects. The association between a task and a table is always dropped when the current statement completes executing; therefore, global temporary tables are usually dropped soon after the connection that created them disconnects.

Many traditional uses of temporary tables can now be replaced with variables that have the table data type.

Working with Tables

Users work with the data in tables using data manipulation language (DML) SQL statements:

-- Get a list of all employees named Smith:
SELECT emp_first_name, emp_last_name
FROM employee
WHERE emp_last_name = 'Smith'

-- Delete an employee who quit:
DELETE employee
WHERE emp_id = 'OP123'

-- Add a new employee:
INSERT INTO employee
VALUES ( 'OP456', 'Dean', 'Straight', '01/01/1960')

-- Change an employee name:
UPDATE employee
SET emp_last_name = 'Smith'
WHERE emp_id = 'OP456'

See Also

Specifying a Column Data Type

Tables