Data Types and Table Structures

All the data in Microsoft® SQL Server™ 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 expect to 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.

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.

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 a reader and helps ensure that columns holding similar classes of data have the same base data type.

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.

Columns can either accept or reject NULL values. NULL is a special value in databases which 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 zero is a valid number, while NULL just 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 just has 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.

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. 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 supports temporary tables. These are tables whose names start with a number sign (#). If a temporary table is not dropped when the user disconnects, it is dropped automatically by SQL Server. Temporary tables are not stored in the current database, they are instead stored in the system database tempdb. There are two types of temporary tables:

• Local temporary tables have only one number sign (#) at the start of their name. They are visible only to the connection that created them.

• Global temporary tables have a double number sign (##) at the start of their name. They are visible to all connections. If they 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, so global temporary tables are generally dropped soon after the connection that created them disconnects.

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’