SQL Server – Simple Best Practices
SET NOCOUNT ON
SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement.This setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.
USE JOINS WHEN POSSIBLE
This provides SQL Server opportunity to make best choices on indexes . Use JOIN directly rather than sub-queries.
USE UNION ALL RATHER THAN UNION IF POSSIBLE
UNION
The UNION command is used to select related information from two tables. With UNION, only distinct values are selected.
UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.
A UNION statement effectively does a DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.
DONT USE TRANSACTIONS IF NOT NEEDED
Operate on Small Result Sets
Limit the Number of Columns in SELECT List
Use Highly SELECTIVE Indexes
Avoid NonSargable Search Conditions
Use BETWEEN rather than IN / OR Operator
Avoid Arithmetic Operators on WHERE Clause
Avoid FUNCTIONS on WHERE Clause
Avoid OPTIMIZER Hints unless you absolutely know your data ( JOIN Hint , INDEX Hint , FORCEPLAN Hint )
Columns – Force NON NULL if possible
Force DRI if possible ( Declarative Referential Integrity )
Avoid Data Type Conversion
Use EXISTS rather than COUNT(*) queries
Use Indexes for Aggregate and Sort Conditions
Use SET NOCOUNT ON
Keep the transaction short if needed.
Reduce LOCK Overhead
Reduce the Logging Overhead
FILES AND FILEGROUPS:
We all are guilty of creating database with one file group and one data file and log file because that’s the default option for SQL Server.
It creates numerous problems in terms of scaling, copying and creates maintenance nightmare for scalability.
Create at least two file groups and make atleast 2 additional data files. That way if we want to scale, we can place the files groups in different spindle, RAID ,etc.
It lends itself nicely and we can take filegroup backups and restore it without doing the entire database backup and restore.
Its common sense instead of one big bang file, lets split into multiple files and file-groups which provides nimbleness in maintenance and scaling.