Journey to SQL Authority with Pinal Dave

Posted in DBA, Database, Main, Pinal Dave, SQL, SQL Add-On, SQL Authority, SQL Backup and Restore, SQL Coding Standards, SQL Constraint and Keys, SQL Cursor, SQL DateTime, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Index, SQL Interview Questions and Answers, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, SQL Utility, Software Development, T SQL, Technology | 59 Comments

59 Responses to “SQL SERVER Database Coding Standards and Guidelines Complete List Download”

1. on June 11, 2007 at 8:40 am Rose

   Useful Notes

   
   
2. on June 13, 2007 at 11:11 am Deepan S.

   Hi Pinal,

   That is a Very Very Good Compiled Document. Appreciate it

   However, just one things i noticed. Please correct me if i am wrong.

   Stored Procedure Naming Convention
   - Is it feasible to start the User Created Stored procedure Name with “sp” ,since SQL Server looks for the Stored Procedure in the Master Database and assumes it to be as a System defined Stored Procedure?

   I know you have mentioned not to start with “sp_”, but just a thought.

   Thanks
   Deepan S.

   
   
3. on June 13, 2007 at 11:15 am pinaldave

   Thanks Deepan,

   As far as I know ‘SPName’ is no problem but ’sp_’. When I reviewed this document and that particular issue with other SQL Expert, we all agreed that it is fine.

   If anybody has other opinion or documentation. I am willing to adopt, but at this moment I think ’spNAME’ (no underscore) is fine.

   Regards,
   Pinal Dave (SQLAuthority.com)

   
   
4. on June 13, 2007 at 10:26 pm jalpesh vadgama

   very good articles keep doing things more and more

   
   
5. on June 24, 2007 at 10:02 pm Mayank

   Its Really Good Article…
   Best Of Luck for Bright Future…..

   
   
6. on June 28, 2007 at 8:37 am harshad b.

   really great articals to be referred by any .net programmer.

   thanks to pinal dave……..

   
   
7. on July 1, 2007 at 11:24 pm Dinesh Kumar Mandal

   Really fantastic…!

   
   
8. on July 12, 2007 at 7:07 am Vinod Dhone

   hi pinal,

   This site is very much usefull
   thanks to u

   Thanks,
   Vinod Dhone

   
   
9. on July 15, 2007 at 8:51 pm Harsh

   Hello Mr. Dave,

   Your articles are amazing and really very useful. I am a beginner in SQL BI and looking for resources, interview questions. It would be a great help if you can point me to those resources.

   Thanks

   
   
10. on July 20, 2007 at 10:59 am David Carr

    Hi. Nice articles but I am interested in why you would want to prefix all stored procedures with ’sp’. In the case of stored procedures I understand there is no impact but it seems superfluous and makes the natural reading of code less easy.

    It reminds me somewhat of the polish notation used by many VB programmers that in the end leads to problems. Luckily ones that don’t currently afflict SQL.

    
    
11. on July 28, 2007 at 9:09 pm Sachin

    Hello Mr. Dave,

    Your articles are amazing and really very useful. This should
    be done by each professional to encourage the begginners.
    your work is really appreciable.

    Go ahead

    Thanks
    sachin

    
    
12. on July 29, 2007 at 11:31 pm Aftab Satti

    Coding standards are very usefull. i like it very much. keep it up boyz.

    
    
13. on August 5, 2007 at 2:09 am Mayank J

    Hi,
    The articals are really very useful,keep uploading these type of DOC

    
    
14. on August 13, 2007 at 8:45 am Jaysam Thanki

    Hi,
    I think you should add the (NOLOCK) attribute as well.

    eg:

    SELECT UserID FROM [User] (NOLOCK)
    or
    SELECT u.UserID,s.SessionID FROM [User] u (NOLOCK) INNER JOIN [Session] s (NOLOCK) ON u.UserID=s.UserID

    Jaysam

    
    
15. on August 20, 2007 at 1:16 am Mohd. Gyashuddin

    Its very good work done by you people.

    
    
16. on August 31, 2007 at 4:24 am KICHU TVPM

    I really thankful to u……..

    
    
17. on September 12, 2007 at 1:40 am Abhas J

    Keep Uploading such good technical stuff. Thanks

    
    
18. on September 17, 2007 at 10:36 pm Paramswar

    this is very important .and very easy way to understanding

    its nice

    
    
19. on October 12, 2007 at 7:00 am SQL SERVER - Pre-Code Review Tips - Tips For Enforcing Coding Standards Journey to SQL Authority with Pinal Dave

    [...] Read SQL SERVER Database Coding Standards and Guidelines Complete List Download [...]

    
    
20. on October 22, 2007 at 10:00 pm Rickey Haynes

    hi Pinal Dave, I just graduated with a degree in network communication and management i would like to know of any more info to help me learn the sql server to further my carrer. i am studying now for my ccna and want all the expeirence i can get to futher myself if u can help i would greatly appreciate the info to learn maybe some books you could recommend. i am egar to learn from someone who has such a vast knowledge of this field

    thanks R.Haynes

    
    
21. on October 31, 2007 at 9:11 pm Akash

    Excellent Web Site.

    
    
22. on November 8, 2007 at 3:45 am Sami

    Thank you, I really appreciate your work. I hope one day I will be able to give back too.

    
    
23. on November 15, 2007 at 6:51 pm Saravanan nallamuthu

    I want to appreciate the author of this documents
    keep the gud work up.

    
    
24. on December 13, 2007 at 2:54 pm anilkumar

    hello pinal Dave

    i am really happy with u r articles

    
    
25. on January 11, 2008 at 9:09 pm Shashi Kant Chauhan

    Good Pinal Dave,
    Keep updating the things……….
    I want to send some new concept of performance tunning and new way of using sql server 2005 coding stratergy. I will appreciate you if you can provide me your mail ID so that i can send you the document which i Collect from my learnings………

    Thanks & Regards
    Shashi Kant Chauhan

    
    
26. on January 12, 2008 at 12:28 pm Deepshika

    how can i count number of column in a row with same data?
    i have a table with column empid,month, day1, day2 ………….day31 i want to count number of ‘p’ and ‘a’ of that employee id?

    
    
27. on January 16, 2008 at 3:59 pm Rana

    You are really good man. Your effort with no charge is really appreciable.

    God bless you.

    Rana

    
    
28. on January 30, 2008 at 3:10 pm vishal

    i am not written this for u .because i really dont no which word i say to u. no word is expressed to u.U R a very tallented person ,u have a great capabilities to done all u r effort.u also done a work that needed all jobseekers like me.
    i just searches SQL related questions with ansers ,and i get this wonderful and easy solution from u.

    thanks ,may have 2 requst u plz contact with me.

    best of luck u r next …………………………………………………………………..
    till end

    
    
29. on February 14, 2008 at 6:05 pm Ganesh Jamdurkar

    Hi!

    Its very nice and helpful link for me and all sql users.

    regards,
    Ganesh Jamdurkar

    
    
30. on February 14, 2008 at 6:07 pm Faisal Qureshi

    Hi!

    Its very nice and helpful link for me and all sql users.

    regards,
    Faisal Qureshi

    
    
31. on March 7, 2008 at 7:57 am Arvind R

    Wonderful website..appreciate the effort.

    Arvind

    
    
32. on March 9, 2008 at 9:07 pm ravi singaram

    hi

    it is very useful and necessary to all who are all using sql server. keep on adding tips

    regards,
    ravi

    
    
33. on March 17, 2008 at 9:44 pm Ramesh Chinnaappusamy

    Hi pinaldave,

    Very usefull article keep going.

    
    
34. on March 20, 2008 at 6:59 pm SQL SERVER - Add Column With Default Column Constraint to Table Journey to SQL Authority with Pinal Dave

    [...] It is very easy to add column and specify default constraint. I have seen many examples where constraint name is not specified, if constraint name is not specified SQL Server will generate unique name for itself. I prefer to specify my constraint name as per my coding standards. You can read my coding standard here : SQL SERVER Database Coding Standards and Guidelines Complete List Download [...]

    
    
35. on March 21, 2008 at 11:59 pm inder

    CONNECTION OF SQL SERVER 2005 TABLES WITH VISUAL STUDIO 2005

    
    
36. on March 30, 2008 at 7:21 am Prem

    Hi,
    wha is the command used to count foreign key in a table.

    
    
37. on April 6, 2008 at 10:54 am SQL SERVER - Popular Articles of SQLAuthority Blog Journey to SQL Authority with Pinal Dave

    [...] SQL SERVER Database Coding Standards and Guidelines Complete List Download [...]

    
    
38. on April 15, 2008 at 7:34 pm Cesar Rangel

    Hi Pinal,

    Exist standars in SQL for creation views ?

    Thanks

    Cesar

    
    
39. on April 22, 2008 at 6:24 am Jane

    thats very nice tips! lucky i found your article. that save a lot of my time to fix my problem.

    
    
40. on April 22, 2008 at 11:02 am hakeem

    it is nice to read ,very interesting.

    
    
41. on May 2, 2008 at 7:22 pm rupam

    hi everyone can you tell me the steps of how to disable procedure in sql 2000 and 2005

    
    
42. on May 6, 2008 at 8:51 pm Suresh

    Hi Pinal Dave,

    Surprising greatly. Keep good work. Now I am addicted this website.

    
    
43. on May 8, 2008 at 7:57 am SQL SERVER - How to Rename Database Objects to Comply With Naming Conventions Journey to SQL Authority with Pinal Dave

    [...] 2, 2008 by pinaldave Christopher Miller read article of SQL SERVER Database Coding Standards and Guidelines Complete List Download and came up with wonderful SQL Server Script to rename all their database constraint with more [...]

    
    
44. on May 20, 2008 at 2:50 pm Sajitha

    Hi,

    It is a very very good article. I never think these kind of questions. Thanks a lot for doing research on it and all the best for further research.

    Regards
    Sajitha

    
    
45. on May 23, 2008 at 5:57 pm Frank W Holt

    Hello,

    Great document!

    I noticed you recommend having your table names be ‘plural’. It seems there are those who are passionate about making them singular. I was wondering what your reasoning is for doing it your way.

    Thanks
    Frank

    
    
46. on June 3, 2008 at 4:08 am shirsendu

    Its a fantastic job,your work will help many developers and God is almighty with you

    
    
47. on June 11, 2008 at 1:45 am Zach

    Hi Pinal,

    Really appreciate the work you have done.

    Have a question for you in regards to SQL Server Database Coding Standards and Guidelines

    Other than keeping within ANSI92 or being Oracle compatible why do you say “Do not use the identitycol or rowguidcol”?

    Thanks keep up the good work!!

    Zach

    
    
48. on June 18, 2008 at 12:44 pm Basab

    Hi Pinal

    Really nice compilation. I am greately benifitted.

    I was going thru your article on Coding Standards. You have mentioned

    “Use BEGIN..END blocks only when multiple statements are present within a conditional code
    segment.”

    I think we should always use BEGIN…END blocks. Anyone else maintaining a code that I have written may put another line and lured by the indent think that he has put the statement inside the block. Later this may be a nightmare for the third person to debug.

    
    
49. on June 20, 2008 at 12:26 pm ashish

    hello sir,

    can u tell why u r not use more than one oprimary key on a table

    
    
50. on June 22, 2008 at 4:47 pm shilpi

    HI

    I am preparing for interviews.
    If you can send me latest interview questions on SQL and PL?SQL that would be great.

    Site is very useful

    
    
51. on June 22, 2008 at 10:24 pm SQL SERVER - 2008 - Introduction to Policy Management - Enforcing Rules on SQL Server Journey to SQL Authority with Pinal Dave

    [...] 13, 2008 by pinaldave I have previous written article about SQL SERVER Database Coding Standards and Guidelines Complete List Download. I just received question from one of the blog reader is there any way we can just prevent [...]

    
    
52. on June 27, 2008 at 5:58 pm Ashok

    Hi,

    Great document!

    I noticed you recommend having table names as ‘plural’ but table is an entity and i think an entity should always be in ’singular’. Correct me if i am wrong

    Thanks
    Ashok

    
    
53. on June 28, 2008 at 10:58 pm Rihanna

    Hi!

    very nice and helpful link.

    Regards,
    R.Wright

    
    
54. on July 9, 2008 at 12:27 am nico

    I agree with most of your guidelines. One that I don’t agree with, is using varchar instead of TEXT. TEXT results in a 16-byte only fixed column, while a varchar(max) could result in 8KB being accessed by the HDD and sent to the resultset. It just serves better to have a huge amount of data off-table and only accessed when needed. Even if a SELECT list is provided, excluding the varchar, the hard drive still has to read all of it into the page before producing the selective list, which is a waste.
    Ordinarily, I try to assess the maxlength of a varchar and its usability, and if it’s going to be used infrequently and could be very large, it goes into a TEXT column.

    One guideline I use that you didn’t mention, is to have keys at the front of the table, followed by fixed-length columns and variable length columns at the end of the table. Your assessment that varchars are faster to process than chars is only valid when the column length is large. Varchars require the application of a base address with an off-set to reach them. If one places fixed-length columns behind varchars, they, too, will be subject to an off-set. Setting relationships against columns behind varchars places a huge overhead burden on the server.

    Just some thoughts.

    
    
55. on July 30, 2008 at 5:41 pm Bhavik

    I want list of pratical question of sql server 2000.
    Where i will get?

    
    
56. on August 27, 2008 at 3:32 pm HONEY

    PLEASE GIVE ME THE TIPS ABOU SQL CONTRAINT WORK

    
    
57. on November 1, 2008 at 1:51 am Mukund

    Hi Pinal,

    Impressive stuff!

    I am currently looking for a tool which verifies whether SQL standards are met in the in the SQL script. For example if a input a stored procedure script then i would like see a report on what all predefined coding standards not met in the stored procedure.

    Do you know any tools available like that or let me know pointers to build that kind of a tool.

    
    
58. on November 1, 2008 at 11:15 am sRajesh

    HI

    I am preparing for interviews.
    If you can send me latest interview questions on SQL and PL?SQL that would be great.

    Site is very useful

    
    
59. on December 24, 2008 at 12:32 pm anitha

    hi
    i am fresher.i don’t know sql server.please give more tips for sql server 2005 and sql server 2008