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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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