Data Types and Table Structures
New Information - SQL Server 2000 SP3.
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:
Each column represents some attribute of the object modeled by the table, such as a parts table having columns for ID, color, and weight.
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 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.
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.
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. SQL Server does not support direct updates to the system tables by users or applications. Only SQL Server should update the system tables in response to administration commands issued by users. Additionally, users should not query the system tables directly unless there is no other way to get the data required by the application. 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.
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'