SQL Server Best Practices

Recently was discussing with a colleague of mine as to the best practices in SQL. So this post is dedicated to that chat. The first thing we discussed about is the usage of NOLOCK's for tables in non highly transactional based databases with a wide user audience. Now the question of dirty reads does come to mind but at the end of the day the Read Uncommited transaction allows multiple end users to query the underlying table. NOLOCK is nothing more than a table hint that allows the query to be performed in a read uncommited fashion but is still a great practice to follow even while creating views. I have been using NOLOCK's for a large period of time (and sometimes implement it out of habit for my views i.e. SELECT C1,C2 FROM V1 (NOLOCK) as well which does not make any sense but is more of a force of habit except for my last post where I removed it on purpose) but I recently came across another table hint called READPAST but I am strictly avoiding this topic as this prevents the end user from reading locked rows as well. Another thing we discussed about was the usage of transactions within stored procedures. It is a good practice for SQL developers to always wrap SP's within transactions. Creation of an SP should have a BEGIN TRAN statement. The reason is quite simple Dr. Watson, you would want all the transactions that take place within the SP to be commited at one point in time. But there are exceptions to this practice and let us say that you are pulling information from table X to table Y and then going to update X or Y based on some logic. We would want the inserts to go ahead as planned and would not want to roll back the inserts unless it is a very small data load (roughly a couple of 100 records). The transaction wrapping allows us to follow the "ACI" of the ACID properties of an RDBMS. The next aspect to follow is the usage of ANSI standard SQL while writing basic standard SQL statements. I totally disagreed with this aspect, first and foremost using the standard t-sql everywhere is not going to downgrade the performance of your SQL. Example:
ANSI standard: SELECT T1.C1,T1.C2 from T1 INNER JOIN T2 on T1.C3=T2.C1 T-SQL: SELECT T1.C1,T1.C2 FROM T1,T2 WHERE T1.C3=T2.C1
The performance in SQL server is going to be the same.... I do agree from a readability perspective that the ANSI standard is better but I am not going to support a standard just because the readability is a bit better. I do however use t-sql on a regular basis for majority of my queries but in case if the queries is to be made available to productionand the users are more familiar with a specific standard then I would definitely go ahead with the standard that is followed. Usage of cursors is not good..... I agree but it cannot be avoided everywhere and besides I think CURSORS even though non performant does have a lot of benefits.
Benefits:

• Iterative looping (start from i and go on to n-1 once)
• Ensuring that every row is taken into consideration
• More hands on with the row cell than set based operations
• Primary Keys are not required in the underlying data set or for that matter even generating a ROW_NUMBER() for the dataset

Usage of index hints should be made a common practice while writing SQL. Figuring out the number of indexes on the base tables and which indexes will improve the performance of the query and targeting that index should be made a practice. Usage of filtered indexes is also a good practice and roughly one that I would recommend. More information on filtered indexes can be gathered @ http://msdn.microsoft.com/en-us/library/cc280372.aspx My colleague did suggest knowing the DBCC commands as well as underlying base sp's to the T. Well it does help to have mastery over the DBCC and internal SP's but what is BING or Google for. I do not want to memorize these commands as they are not pertinent for my day to day work but everybody has their own opinion. Using unicode related datatypes like NVARCHAR,NCHAR etc.... sparingly. Setting the NOCOUNT on will not decrease the performance of your SQL but does add an additional routine to the query results. Having the NOCOUNT on is also a good practice. This is where the conversation changed to a different track but still was quite interesting while this track lasted. Also on a quick note usage of indexed views can certainly improve performance of the underlying queries to a certain extent. Let me give an example here:
I have a table TableCountry on which I have no primary keys on. I created a view on top of this called dbo.temp_view and I have another view called Test.temp_view with the same underlying SQL with a slight difference while creating the views as follows:
CREATE VIEW [dbo].Temp_View AS
SELECT Country,Margin FROM [dbo].[TableCountry]
CREATE VIEW [Test].Temp_View WITH SCHEMABINDING AS
SELECT Country,Margin FROM [dbo].[TableCountry]
The next step was for me to create a clustered index on top of the view [Test].Temp_View as follows:
CREATE UNIQUE CLUSTERED INDEX idx_temp_view ON [Test].Temp_View (Country)
Now let me execute the following queries in the order defined below:
SET SHOWPLAN_ALL ON
SELECT Country FROM [dbo].[temp_view]
SELECT Country FROM [Test].Temp_View WITH (NOEXPAND)
Now if you check out the result plan you will notice that the second query utilizes a CLUSTERED INDEX SCAN VS the TABLE SCAN used for the first query. Also the underlying CPU cost for the second is much lesser (as it is targeting a fixed segment on the page) rather than the page itself. Indexed views are really beneficial when you are targeting large tables using views and dont want the underlying MACRO EXPANSION to happen. But there is a cost of maintaining the index for the view (Speed VS Cost) which is something where one has to carefully consider while working with large OLTP systems.