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.
»
See All Articles by Columnist Alexander Chigrik
|