DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to increase performance by table design?

The simplest way to increase the performance of SQL Server is to design tables as thin (narrow) as possible. The more rows one database page of 8K contains, the better. It decreases disk IO / buffer activitiy, it decreases "reads".

Use smalldatetime (4 bytes) instead of datetime (8 bytes). DATE (3 bytes) is an option starting with SQL Server 2008.

If you don't need the range of int, use smallint or tinyint.

For flags and switches use the bit type.

Below 30 characters use char not nullable, varchar otherwise. nchar and nvarchar doubles the space usage. Don't use them unless you really need them, for example to store UNICODE Japanese or Chinese text.

Smallmoney takes only 4 bytes while money takes 8 bytes. If you don't need the range or accuracy of money, just apply smallmoney.

Thin design is especially helpful with performance of large tables with millions of rows.

Related article:

SQL Server performance difference with single or multi column primary key?

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE