SQL SERVER: 9 Most Forbidden Things

1. DBCC SHRINKDATABASE

Most of SQL Server developers and even some DBAs think that we can shrink size of a database by executing DBCC SHRINKDATABASE. You can just reacquire unused space of database and it’s never going to compress your database and change magically low size. Advantage is very very low as compared to performance loss. Why DBCC SHRINKDATABASE is so awful SQL Server Guru Pinal Dave has explained it here.

2. SET AUTO_CREATE STATISTICS OFF

SQL Sever by default SET this option to ON and create statistics for all the columns used in join and filters, for even those columns on which no index exists. SQL Server is made quite intelligent to create statistics on required columns only and even drop statistics which are no more in use. So always keep AUTO_CREATE STATISTICS ON.

3. RECOMPILE hint in Stored Procedures

You can provide RECOMPILE hint for stored procedure to recompile it every time it is executed. RECOMPILE hint for SPs is nothing but a performance overhead.

4. Query hint to force some index usage

Index of your own choice can be forced to use for a query BUT never ever do this on production servers. Query Optimizer is intelligent enough to select, which index is better to use for a query. Let optimizer work.

5. Heap table structure

Tables without any clustered index are called HEAP tables and records for these tables are not kept physically in order. Always create a clustered index on each of your database table because lot of hard work is required by SQL Server to perform any type of query on these heap tables.

6. Clustered index on UNIQUEIDENTIFIER column

To types of data types are used to create a surrogate key (artificial key) i.e. INT (as identity column) and UNIQUEIDENTIFIER. Problems occur when clustered indexes are created on UNIQUEIDENTIFIER data type column. Value for UNIQUEIDENTIFIER are never sequential (I am not talking about Sequential GUID in Sql Server 2008) and always play a giant role in index fragmentation not only for clustered index but due to this wide key, non-clustered indexes need more space and maintenance time. Read more about this in early post UNIQUEIDENTIFIER column as primary key a worst choice.

7. Index on BIT data type columns

Though, we are allowed to create index on columns with BIT data type. Yet creating an index on a BIT data type column by itself is not a big advantage since such a column can have only two unique values. Such indexes are just over head as we SQL server still need to maintain these indexes.

8. COUNT(*) to check records existence

Never use COUNT (*) to check existence of record, instead use

IF EXISTS (SELECT 1 FROM YourtTable WHERE YourFilterCondition)

(Update: http://connectsql.blogspot.com/2011/01/sql-server-why-we-should-avoid-count.html)

9. User Tables in System Databases

Always create user tables in your own user databases and never use system databases for this purpose.

Do you guys have more points which must be added in this list ? Share with us.