Data Type Performance Tuning Tips for Microsoft SQL Server

Generally, using computed columns in a table is not recommended because it does not follow the standard rules of normalization. But, it is sometimes more efficient overall to use computed columns in a table rather than re-computing the same data repeatedly in queries. This is especially true if you are running the same query over and over against your data that performs the same calculations over and over. By performing the calculations in the table, it can reduce the amount of work performed by a query each time it is run. You have to determine for yourself where the bottleneck in performance is, and act accordingly. If the bottleneck is in INSERTS and UPDATES, then using calculated columns may not be a good idea. But if your SELECT statements are the bottleneck, then using calculated columns may pay off. [6.5, 7.0, 2000] Updated 5-15-2006

*****

Avoid using the new bigint data type unless you really need its additional storage capacity. The bigint data type uses 8 bytes of memory verses 4 bytes for the int data type. [2000, 2005] Updated 12-6-2005

*****

Avoid using the SQL Server 2000 sql_variant datatype. Besides being a performance hog, it significantly affects what you can do with the data stored as a sql_variant. For example, sql_variant columns cannot be a part of primary or foreign keys, can be used in indexes and unique keys if they are shorter than 900 bytes, cannot have an identity property, cannot be part of a computed column, must convert the data to another datatype when moving data to objects with other datatypes, are automatically converted to nvarchar(4000) when accessed by client applications using the SQL Server 7.0 OLE DB or ODBC providers, are not supported by the LIKE predicate in the WHERE clause, cannot be concatenated, and don't work with some functions. [2000, 2005] Updated 12-6-2005

*****

Don't use the DATETIME data type as a primary key. From a performance perspective, it is more efficient to use a data type that uses less space. For example, the DATETIME datatype uses 8 bytes of space, while the INT datatype only takes up 4 bytes. The less space used, the smaller the table and index, and the less I/O overhead that is required to access the primary key. Updated 12-6-2005

*****

If you are creating a column that you know will be subject to many sorts, consider making the column integer-based and not character-based. This is because SQL Server can sort integer data much faster than character data. [6.5, 7.0, 2000, 2005] Updated 10-16-2005

*****

Take care when using Unicode data in your queries, as it can affect query performance. A classic problem is related to an application passing in Unicode literals, while the column searched in the database table is non-Unicode. This, of course, may be visa-versa depending on your scenario.

Here is an example. The DB column "orgname_name" has been indexed, and is of type varchar. The code below performs OK (so we think) performing an index scan operation:

declare @myvar nvarchar(200)
set @myvar = N'Central West College of TAFE'
select * from Organisation_Name where orgname_name = @myvar

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([corpsys].[dbo].[Organisation_Name]))
|--Index Scan(OBJECT:([corpsys].[dbo].[Organisation_Name].[Organisation_Name]),
WHERE:(Convert([Organisation_Name].[orgname_name])=[@myvar]))

Table 'Organisation_Name'.
Scan count 1,
logical reads 1145,
physical reads 0,
read-ahead reads 0.

If we change this around slightly, using a varchar variable instead (no explicit Unicode conversion) we see this:

declare @myvar varchar(200)
set @myvar = 'Central West College of TAFE'
select * from Organisation_Name where orgname_name = @myvarM

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([corpsys].[dbo].[Organisation_Name]))
|--Index Seek(OBJECT:([corpsys].[dbo].[Organisation_Name].[Organisation_Name_nameix]),
SEEK:([Organisation_Name].[orgname_name]=[@myvar]) ORDERED FORWARD)

Here we see an INDEX SEEK lookup with a massive performance improvement:

Table 'Organisation_Name'.
Scan count 1,
logical reads 9,
physical reads 0,
read-ahead reads 0.

Instead of 1,145 logical reads, there is only 9, a significant improvement. [7.0, 2000, 2005] Updated 10-16-2005. Contributed by www.chriskempster.com.