Tips for designing SQL Server 2005 tables (Part 1)
Avoid using CLR user-defined data types.
SQL Server 2005 introduces the CLR user-defined data types. These data types allow implement
database data types in any of the languages supported by the .NET Framework CLR. Because using
these data types usually provides worse performance than internal SQL Server data types, and you
may have problems with migration these data types to other RDBMS, use CLR user-defined data types
very carefully.
Consider horizontal partitioning of the very large tables into the current and
the archives versions.
The less space used, the smaller the table, the less work SQL Server has to perform to evaluate
your queries. For example, if you need to query only data for the current year in your daily
work, and you need all the data only once per month for the monthly report, you can create two
tables: one with the current year's data and one with the old data.
Create the table's columns as narrow as possible.
This can reduce the table's size and improve performance of your queries as well as some
maintenance tasks (such as backup, restore and so on).
Try to reduce the number of columns in a table.
The fewer the number of columns in a table, the less space the table will use, since more rows
will fit on a single data page, and less I/O overhead will be required to access the table's data.
If you need to store integer data from 0 through 255, use tinyint data type.
The columns with tinyint data type use only one byte to store their values, in comparison with
two bytes, four bytes and eight bytes used to store the columns with smallint, int and bigint
data types accordingly. For example, if you design tables for a small company with 5-7
departments, you can create the departments table with the DepartmentID tinyint column to
store the unique number of each department.
If you need to store integer data from -32,768 through 32,767, use smallint
data type.
The columns with smallint data type use only two bytes to store their values, in comparison with
four bytes and eight bytes used to store the columns with int and bigint data types respectively.
For example, if you design tables for a company with several hundred employees, you can create
an employee table with the EmployeeID smallint column to store the unique number of each employee.
Use smalldatetime data type instead of datetime data type, if you need to store
the date and time data from January 1, 1900 through June 6, 2079, with accuracy
to the minute.
The columns with smalldatetime data type use only four bytes to store their values, in comparison
with eight bytes used to store the columns with datetime data types. For example, if you need to
store the employee's hire date, you can use column with the smalldatetime data type instead of
datetime data type.
Use varchar/nvarchar columns instead of text/ntext columns whenever possible.
Because SQL Server 2005 stores text/ntext columns on the Text/Image pages separately from the other
data, stored on the Data pages, it can take more time to get the text/ntext values.
Normalize your tables to the third normal form.
A table is in third normal form (3NF) if it is in second normal form (2NF) and if it does not
contain transitive dependencies. In most cases, you should normalize your tables to the third
normal form. The normalization is used to reduce the total amount of redundant data in the
database. The less data there is, the less work SQL Server has to perform, speeding its
performance.
Use cascading referential integrity constraints instead of triggers whenever possible.
For example, if you need to make cascading deletes or updates, specify the ON DELETE or ON UPDATE
clause in the REFERENCES clause of the CREATE TABLE or ALTER TABLE statements. The cascading
referential integrity constraints are much more efficient than triggers and can boost performance.
Consider using the XML data type.
SQL Server 2005 introduces the XML data type. This data type is used to store XML documents in
table columns or Transact-SQL variables. The XML data type can be used in variables, columns, or
in stored procedure and function parameters.
