SQL Server Optimization Tips for Designing Tables

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

»


See All Articles by Columnist Alexander Chigrik