Best Practice SQL Server

Please understand the logical query processing of a query, so that you can write better queries:

SELECT h.*,hp.HomePhotosID,hp.HasColorPhoto
FROM Home h
LEFT join HomePhotos hp
ON h.HomeID = hp.HomeID
AND h.Taxes = -555

logical query processing :

FROM (incl joins)
ON
OUTER
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
TOP

CLR Integration

The benefits of integrating the CLR with SQL Server include:

1. The T-SQL statements that you execute actually run on the server end. But at times when you want to distribute the load between the client and server, you could go with the managed code. So using managed code you could perform critical logic operations in client side so that the server could be busy only with data intensive operations.

2. The fact that SQL Server provides you extended stored procedures to avail certain system related functions from your T-SQL code, but at the same time you may have to compromise with the integrity of the server. When it comes to managed code, it provides type safety, effective memory management and better synchronization of services which is integrated tightly with the CLR and, hence, the SQL Server 2005. So this means that integrating CLR with SQL Server provides a scalable and safer means for accomplishing tasks which are tougher or almost impossible using T-SQL.

3. .NET Framework provides a rich support for handling XML based operations from managed code; although realizing the fact the SQL Server supports XML based operations, you could perform such operations using .NET with little effort when compared to using T-SQL scripts.

4. Nested transactions in T-SQL have limitations when dealing with look back connections, whereas this could be better achieved using managed code by setting the attribute "enlist=false" in the connection string.

5. When working T-SQL you may not be able to fetch rows which form the middle of the operation from a result set until the execution gets finished. This is termed as pipelining of results which could be achieved with CLR integration.

If you could check your database configuration you could notice that the CLR Integration is turned off by default. Enabling or disabling of CLR integration could be done by setting the "clr enabled" option to 1 or 0. Once the CLE integration is disabled, all the executing CLR procedures are unloaded across all application domains. To turn it on you need to use the following.

Use Row versioning-based isolation levels

Row Level Versioning help in situations where an applications lot of insert and update operations on the data and at the same time a bunch of reports are accessing in parallel. It could also prove beneficial if your server is experiencing relatively high deadlocks. Also for systems performing mathematical computation, they require accurate precision and RLV gives a greater amount of accuracy for such kind of operations.

Error handling

use Try...Catch

BEGIN
    TRY
    BEGIN TRANSACTION
    …..
    --perform insert update and delete statements
    …..
    …..
    COMMIT TRANSACTION
END TRY
BEGIN
--Start
    CATCH
    ROLLBACK TRANSACTION
    PRINT ERROR_NUMBER()
    PRINT ERROR_SEVERITY()
    PRINT ERROR_STATE()
    PRINT ERROR_PROCEDURE()
    PRINT ERROR_LINE()
    PRINT ERROR_MESSAGE()
--End
END CATCH

Best Practices to handle Queries

Stored procedures help improve the performance since they are precompiled.
Avoid using like and notlike operatiors
Avoid using function in where clause.
Insead of CURSOR'S use temporary table with While.
Select only those rows or columns that are needed in the result set

Index

Create indexes on columns which are used in WHERE condition and ORDER BY, GROUP BY and DISTINCT clauses.
Indexes which are not used may cause extra overhead, so remove them if not in use.

Best practices in using Stored Procedures
Do not use prefix in your stored procedures, i.e., do not prefix them with sp_. Microsoft ships system procedures which are prefixed with sp_. So if you prefix your procedures with sp_, SQL Server will first search in the master database for the procedure and then in your application database.
If you do not want your client application to check the rows affected for an operation, then it is advisable to use SET NOCOUNT ON in your stored procedures.