T-SQL Coding Standard - CodeProject®

Download T-SQL Coding Standard Document

Introduction

I am currently working on a T-SQL coding standard to formalize our work. Having been inspired by the IDesign C# Coding Standard, I made this T-SQL Coding Standard Document. Maybe this will have some value for the community. It is work in progress and I intend to update it as my experience with its implementation continues.

Unfortunately, I wrote the document in Word using loads of formatting and CodeProject's editor cannot display the document directly. Instead, I leave the document here in a zip for download.

When the document has matured some more, I will reformat it in HTML and modify this article.

Casper Leon Nielsen - 21. Sep 2006. (modified 1^st October 2008)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Casper Leon Nielsen

Architect
HelloGroup

Denmark

Member

Casper Leon Nielsen is a MCP/MCAD and is currently employed by HelloGroup a/s - a technical savy (yes that whats they are calling it these days) company.

Article Top

Poor

Excellent

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

You must Sign In to use this message board. (secure sign-in)

Search this forum
	Profile popups Noise Layout Per page

Nice

ramakrishnankt

21:22 22 Nov '11

Superb standard
Sign In·View Thread·Permalink

Any Update?

jhigaki

7:06 9 Jul '08

I've noticed that the thread hasn't been moving for a while. I'm on a DB2 -> MSSQL migration project and I would like to provide my team an appropriate coding standard before all developers start working on it. Any update on this or link to another coding standard document will be much appreciated. Thanks, Joseph
Sign In·View Thread·Permalink

Re: Any Update?

Casper Leon Nielsen

0:21 1 Oct '08

I hope I will find time soon to rework this manual to represent more experience and the Sql Server 2008 as a target platform. How did the migration go? Was it a succes and did you make your own standard for coding on the Sql Server. If so please share your experiences with the community. Best regards, Casper Leon Nielsen MCP/MCAD
Sign In·View Thread·Permalink

Table Naming - Statement Formatting

Ahmed_Barakat

7:16 31 Jan '08

Hi, First of all , thanks for your work. Tables represent instances of an intity. So why 'd I name a table User ? Does it hold one user or what? I think it makes more sense to name it Users. and what do you think about this ? `SELECT FirstName, LastName, WHERE UserID = 40 AND Age > 2 ORDER BY X, Y` Thanks.
Sign In·View Thread·Permalink

Re: Table Naming - Statement Formatting

Casper Leon Nielsen

0:19 1 Oct '08

Hi

Thank you for the input.

[table naming]
The reason we name tables in the singular form is complex but valid, as I am sure you will agree:

We need to make it simple. simple > complex

We want the naming to be consistent all over.

The simple form of the word user is... "user". The plural form of user is "users".

We now have an option of either naming all tables in the singular (simple) or in the plural form.

We choose the simple form.

Hope this makes as much sense to you as it does to me.

[formatting]
The formatting option you give is as valid as the one I propose.
My lazy eyes have gotten used to the one I propose, but the one you present here is as good in the general sense.

Best regards,

Casper Leon Nielsen
MCP/MCAD

NOLOCK?

Rhodesie

7:33 28 Sep '06

I seem to be the only person that uses (NOLOCK) statements in my SPs. I only use these when I need to improve performance and I can tell you it really does improve SPs for reports etc.,

I also find that using derived tables is a massive advantage in joins i.e.,

INNER JOIN
(
SELECT
ID ,
THINGO
FROM
dbo.Table1 (NOLOCK)
WHERE
THINGO = 2
) DER ON DER.ID = OTHER.TABLEID

especially when "Table1" above has a large amount of fields

Also, use the execution plan!!!!!!!!

Re: NOLOCK?

Casper Leon Nielsen

0:12 29 Sep '06

"It is possible to receive error messages if the read takes place on an uncommitted transaction or a set of pages being rolled back." qouted from http://www.sql-server-performance.com/rd_table_hints.asp[^].
This means we always have to be on our toes for special error conditions when using this hint. Furthermore its quite an advanced procedure to clearly see the implications of dirty reads - not something that can I can sum up in a few lines of "does and donts".

Using NOLOCK is risky business and should not be used in a coding standard as such IMO. Maybe I will write something in the optimization chapter, if I can find something precise to say about it.

Casper Leon Nielsen
MCP/MCAD

Re: NOLOCK?

Casper Leon Nielsen

1:29 29 Sep '06

The summary of the link pasted above is:
"As you can see, table-level hints are available for use by Transact-SQL developers or SQL Server database administrators, but should only be used to fine-tune your code, not as a standard technique for writing queries. You should perform a strict review of the query plans procedure by the query optimizer before you decide that a table-level hint may be want you need to solve your problem. In addition, you should perform this strict review after the hint is in place. "

In short: It have no room in a standard.

Casper Leon Nielsen
MCP/MCAD

Re: NOLOCK?

Rhodesie

23:59 1 Oct '06

I can completely understand your point of view. However, we service a number of proprietary databases where we can not just create constraints and indexes when we need to, even if we want to.

The primary function of our department is to create our own extensions for these systems and reports, and this is why we use (NOLOCK) hints. We have found that NOLOCK improves performance on ten-fold when you dont need real-time data and still be considered for reports and so forth...in my own opinion...

derived tables

Casper Leon Nielsen

0:15 29 Sep '06

I am currently working on a "queries best practices" - and this will surely be part of that (without the nolock). Best regards, Casper Leon Nielsen MCP/MCAD
Sign In·View Thread·Permalink

Discussion on joelonsoftware

Casper Leon Nielsen

11:50 24 Sep '06

There is also a long list of suggestions to improve this standard on http://discuss.joelonsoftware.com/default.asp?design.4.393698.5[^]. Integrating these during the next few days. Casper Leon Nielsen MCP/MCAD
Sign In·View Thread·Permalink	1.00/5 (1 vote)

prefixing stored procedures with sp_ is bad practice

KinStephen

14:13 21 Sep '06

Your coding standard says to prefix all stored procedures with "sp_". There is numerous article recommding this is bad practice as SQL Server will scan for system stored procedures prefixed with sp and then look for your stored procedure (this is an optimization issue). It would be best if you prefixed the stored procedure with something such as "usp_".
Sign In·View Thread·Permalink

And the standard says you shouldnt

Casper Leon Nielsen

21:53 21 Sep '06

Hi The standard says to prefix stored procedures with "sp" not "sp_". It clearly states that UDO naming should match expression [a-zA-Z0-9]+ so in reality it states that using the underscore in naming should be avoided. But thanks for your input anyways. Regards, Casper
Sign In·View Thread·Permalink

Re: And the standard says you shouldnt

KinStephen

8:21 22 Sep '06

I was pointing out the standard is incorrect and should be changed. You shouldn't prefix stored procedurs with sp_ due to performance issues and you shouldn't prefix them with sp (stands for system procedure) as this can cause confusion between user stored procdures and system stored procedures. Why not be more descriptive and prefix them with usp (user stored procedure)?

Or another option is why not go with just p for procedure, since technically a function is also a stored function (or stored procedure with a return value).

Example:

uspMyProcedure
fMyFunction

or

pMyProcedure
fMyFunction

Conclusion [modified]

Casper Leon Nielsen

9:33 22 Sep '06

Hi Stephen

Thanks for the input!

Well...It all boils down to being a question of stylistic taste:
Do we abbreviate stored procedures as "sp", "usp" or "p". In this standard, as is the case with standards generally, I had to make some choices on what to name things and "sp" seemed, and still seems, a natural choice for stored procedures.

In your first message you incorrectly stated that it was a technical question and I argumented that it wasnt - then you progressed onto stating "the standard is incorrect", not by any technical argument, but rather a feeling that using the abbreviation "sp" to indicate a stored procedure would clog the other use of that abbreviation, "sp_", which is used for system stored procedures - this is not my view.

Regarding your observations on procedures and functions: It is a very common misunderstanding, even amongst some of the more experienced people out there, that functions are technically equivalent to stored procedures - this is not the case however:
Syntactically the programmer uses these constructs very differently:
Stored procedures must be EXECUTE'd while UDF's can be used in ex. joins directly. This gives UDF's a massive usability advantage over stored procedures in set operations: We dont have to store the result set in an intermediate table to use the result, rather we can use it directly.
Furthermore the Sql Server engine is aware that that UDF's does not create side effects and have a constrained result type - and can therefore execute them quite differently and more effectively from stored procedures.

So in the end it boils down to a choice of taste: is "usp" or "sp" better to cover the term "Stored Procedure" - I say no as I like the stylistic beauty of only having two-letter prefixes. Furthermore the standard states that the underscore character is not to be used in naming UDO's, making it very easy for the programmer to visually differentiate between "sp_" and "sp".

Hope this makes sense,

Postscript...
Too many people feels uncomfortable with prefixing stored procedures with "sp" so I decided to change it to "usp" for the sake of democracy.

Casper Leon Nielsen
MCP/MCAD

Re: Conclusion

KinStephen

12:28 22 Sep '06

Yes it is a matter of preference in styles... I was only trying to point out that clarity should be be considered more than it normally is. I get the feeling here that it isn't and sp was chosen simple because your used to it.

In addition... when I meant functions and stored procedures are the same, I should have clarified that and what I meant to say is reguardless how how they behave in SQL Server, they are still prodecures and functions 'stored' on SQL Server.

Why not...

Colin Angus Mackay

4:41 21 Sep '06

Rework your word document into a suitable format that Code Project will accept. Upcoming Scottish Developers events: * UK Security Evangelists On Tour (2nd November, Edinburgh) * Developer Day Scotland: are you interested in speaking or attending? My: Website \| Blog
Sign In·View Thread·Permalink

Re: Why not...

Casper Leon Nielsen

4:57 21 Sep '06

Heh... I anticipate some changes in the near future to the document. When I am satisfied that the document is mature, hopefully via some discussions here, I will reformat it and update this article. Until then I leave it in the Pdf. Regards, Casper Nielsen
Sign In·View Thread·Permalink

Avoid using the blob type - NOT

__alias

4:24 21 Sep '06

"Avoid using the blob type for storing files. Files are stored on a file-system, not in the database. Use releative paths to files instead. Remember to backup the filestore simultaneously with the database to counter synchronization issues." - Not according to Microsoft or any other SQL based file system.
Sign In·View Thread·Permalink	2.00/5 (1 vote)

Re: Avoid using the blob type - NOT

Casper Leon Nielsen

4:34 21 Sep '06

Hi Thanks for your input. I dont understand your message unfortunately, could you elaborate? Regards, Casper
Sign In·View Thread·Permalink

Re: Avoid using the blob type - NOT

__alias

11:14 21 Sep '06

The notion of non using of blobs in db is outdated. Check how Sharepoint or MS CMS stores files, inside SQL thats how
Sign In·View Thread·Permalink

Re: Avoid using the blob type - NOT

Casper Leon Nielsen

0:53 22 Sep '06

Well there are pros and cons to both strategies. It is correct that using the database as a filestore can solve some issues and have some tantalising aspects:
Uniform access: Access to files can be encapsulated in the same type of datalayer components as the rest of the application.
Backup: Files will be backed up with the rest of the datastore.

But unfortunately it also tends to clog the visibility alot:
Users cannot interact with files through their normal tools, but have to extract them from the database to view and manipulate them.
Most components expects files to be excatly that: files in a directory structure and cannot interact directly with files in a datastore. This creates the need to unpack the files to a cache before using them.

So you are partly right: If you are the architect of a massive product, such as the before mentioned Sharepoint and MS CMS, you are well of trading visibility for structure. If however you are designing are solution on a smaller scale the tradeof is not as good.

To state however that "The notion of non using of blobs in db is outdated" is as valid as stating that "Designing an application using a multitiered approach is outdated, as Microsoft have now introduced DataAdapters that exist in the presentation layer" Laugh | :laugh:

...

I think I will rewrite the standard to simply state the tradeoffs instead of warning against the usage of blobs as file-storage.

Regards,
Casper

Re: Avoid using the blob type - NOT

Casper Leon Nielsen

1:38 22 Sep '06

Changed the blob statement to:

"Be cautious when using the blob type for storing files. Weigh the structure benefit with the clarity cost before deciding which approach to use. Whichever approach you decide to use, be consistent and avoid using both in the same project. If you choose the filesystem as the datastore for files, remember to backup the filestore simultaneously with the database to counter synchronization issues."