T-SQL coding

Stored Procedures – Best Practices

This post is (hopefully) just a review for everyone; what should I be doing in my SQL Server stored procedure coding, and why? Here is a “skeleton” stored procedure with some best practices shown. Following are notes on each best practice.

CREATE PROCEDURE dbo.USP_MYPROC
AS
/*********************************************************************
* #1 - comment box including a maintenance log
*********************************************************************/
--#2 remove the rowcount responses
SET NOCOUNT ON;
--#3 do you need to change the isolation level?
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--#4 use one declare
DECLARE @Var1 INT, -- #5 datatypes for variables and parameters
     @Var2 VARCHAR(50);

-- main body of stored procedure code goes here
--#6 - use 2-part naming for all objects
--#7 - follow your project's formatting guidelines

--#2
SET NOCOUNT OFF;
GO

#1 – Use a comment box

The comment box should contain basic information about your procedure…every project has its own standards, so follow them and keep the box up-to-date! Your fellow developers and support staff will thank you. Believe me, there is nothing worse than having someone ask you to check on the “version” of a procedure in production and having no maintenance log updates at the top to indicate that the procedure has been changed. What is a maintenance log? It’s just a simple list of changes; date, developer, and a short change summary. It’s easy, just do it…

#2 – SET NOCOUNT ON

By default, almost every statement executed generates a “number of rows affected” response; this is network traffic between the database server and the requestor. It may not seem like much to you (and it’s really not), but multiply that by the number of statements executed in each request and by the number of user connections to the database server and…well, you get the idea. It’s unnecessary network traffic for your application, so just remove it by issuing these statements. Why not just turn it off for the whole server? That is a possibility, but there are times when this information is useful (ever run a data fix script and NOT check on the number of rows affected?).

#3 – Do you need to change the isolation level?

By default, the isolation level in SQL Server is READ COMMITTED. That means your SELECT statement will wait until it can acquire the shared locks that it needs to fulfill your request. I’ve never been a big fan of wholesale usage of the READ UNCOMMITTED isolation level but there are times when you need it. On our project there are general “search” procedures that seem to need this for consistent performance. If you need to change the isolation level, I suggest that you use this statement instead of the older “WITH NOLOCK” hint. For more information on this please see other articles such as:

Microsoft help on Isolation Level

Paul White’s thoughts on the subject

Bottom line; don’t just add this to your procedure because “everyone is doing it”; only use it when needed.

#4 – Use one DECLARE

This goes back to my old COBOL days, but I like it when all of my variables are declared at the beginning. Call it a personal preference if you want, but it helps you find the declarations later when you are maintaining the code, and there is a small benefit to be gained in the way that memory is allocated for those variables. Enough said.

#5 – Watch those datatypes

Please do your due diligence and lookup the datatypes for all of your variables and parameters and make sure they are correct. What is correct? I find that (most of the time) variables and parameters are storing data based on a database column, so your datatype should MATCH the column datatype (type and length/precision). I can’t tell you how many times I have had to troubleshoot issues and found datatype mismatches to be the culprit. Have you ever seen a character column with truncated data? A numeric column with incorrect values due to imprecise or missing precision? Datatypes can also affect the overall performance of your statements; if datatypes do not match then SQL Server has to implicitly change the datatypes to match before performing its work, slowing down your query. With SQL 2014’s new query optimizer I have also seen datatype mismatches affect the query plan as well.

#6 – Use 2-part naming

For this I will just refer to my older posting:

https://sghdba.wordpress.com/2016/02/26/why-should-i-preface-objects-with-an-owner/

#7 – Format your procedure

There is NOTHING worse than trying to debug a poorly-formatted stored procedure. Perhaps you can read it (while you are developing the code), but no one else can. If it’s easy to read, then it’s easy to debug or modify later. Again, most projects have formatting guidelines; follow them, for everyone’s benefit!

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s