SQL Tips - Optimization tips for designing tables

*****

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.

*****

Consider the denormalization of your tables from the forth or fifth normal forms to the third normal form.
Normalization to the forth and fifth normal forms can result in some performance degradation, especially when you need to perform many joins against several tables. It may be necessary to denormalize your tables to prevent performance degradation.

*****

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.

*****

Try to use constraints instead of triggers, rules, and defaults whenever possible.
Constraints are much more efficient than triggers and can boost performance. Constraints are more consistent and reliable in comparison to triggers, rules and defaults, because you can make errors when you write your own code to perform the same actions as the constraints.

*****

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.

*****

If you need to store integer data from -2,147,483,648 through 2,147,483,647, use int data type.
The columns with int data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with bigint data types. For example, to design tables for a library with more than 32,767 books, create a books table with a BookID int column to store the unique number of each book.

*****

Use smallmoney data type instead of money data type, if you need to store monetary data values from 214,748.3648 through 214,748.3647.
The columns with smallmoney data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with money data types. For example, if you need to store the monthly employee payments, it might be possible to use a column with the smallmoney data type instead of money data type.

*****

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 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.

*****

Use char/varchar columns instead of nchar/nvarchar if you do not need to store unicode data.
The char/varchar value uses only one byte to store one character, the nchar/nvarchar value uses two bytes to store one character, so the char/varchar columns use two times less space to store data in comparison with nchar/nvarchar columns.

*****

Consider setting the 'text in row' SQL Server 2000 table's option.
The text, ntext, and image values are stored on the Text/Image pages, by default. This option specifies that small text, ntext, and image values will be placed on the Data pages with other data values in a data row. This can increase the speed of read and write operations and reduce the amount of space used to store small text, ntext, and image data values. You can set the 'text in row' table option by using the sp_tableoption stored procedure.

*****

If you work with SQL Server 2000, 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.