SQL Server Performance

Coding Standard

Discussion in 'SQL Server 2005 General Developer Questions' started by jkbeaumont1, Feb 16, 2007.

  1. jkbeaumont1 New Member

    I am trying to understand why the following is considered a "standard":

    Quote all database objects/fields using square brackets
    Reference all objects by owner/schema
    Eg: [dbo].[authors]

    I can understand referencing all objects by owner/schema, but why waste additional keystrokes by using the square brackets?

    JimB
  2. madhuottapalam New Member

    If you have system reserved keyword (User,Index,Role....) or Multi word (MY Table) as object name
    then you have to use square brackets...

    Madhu
  3. jkbeaumont1 New Member

    Yes, but I am working with a "certified" developer who insists this is standard SQL coding practice for ALL SQL code...

    For example:

    SELECT [First_Name], [MI], [Last_Name], [Suffix], [DOB]
    FROM [dbo].[Authors]
    WHERE [Last_Name] = 'Smith'

    Now, WHY do I have to type all those brackets? Couldn't I just write:

    SELECT First_Name, MI, Last_Name, Suffix, DOB
    FROM dbo.Authors
    WHERE Last_Name = 'Smith'

    What the heck is wrong with that?

    JimB
  4. madhuottapalam New Member

    in this particular case there is no need to user [] but... this may not be the case for the whole database objects and columns.... so he may just want to standardise process and don't want to take any chance...

    google Coding Standards in SQL Server , there are many article available...


    Madhu
  5. MohammedU New Member

  6. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by jkbeaumont1</i><br /><br />Yes, but I am working with a "certified" developer who insists this is standard SQL coding practice for ALL SQL code...<br /><br />For example:<br /><br />SELECT [First_Name], [MI], [Last_Name], [Suffix], [DOB] <br />FROM [dbo].[Authors] <br />WHERE [Last_Name] = 'Smith'<br /><br />Now, WHY do I have to type all those brackets? Couldn't I just write:<br /><br />SELECT First_Name, MI, Last_Name, Suffix, DOB <br />FROM dbo.Authors <br />WHERE Last_Name = 'Smith'<br /><br />What the heck is wrong with that?<br /><br />JimB<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />May I ask what certification that developer has?<br />May I further ask you to let him show you the corresponding text passage from the ANSI SQL Standard? I might be wrong (since I am really bad in finding my way through that standard), but I haven't found any rule or guideline in the standard to back this claim up.<br /><br />On the other hand, your developer certainly has a point here. You need to use square brackets for object names with spaces in it. The fact that SQL Server scripts things that way does not make it a standard. But then again, I might be wrong. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  7. jkbeaumont1 New Member

    Frank, Microsoft Certified in pretty much everything. And "he" is a "she". My main argument here is this was indicated in a code review, and it was not in the coding standards document that my code reviewer provided me. I have an issue with being critiqued on a point which was not communicated in our standards document. Being told that square brackets is an "industry standard", doesn't cut it with me either if I cannot easily locate the "industry standard". I have found a couple of articles that state you should always use square brackets, but they don't say why. I don't like it when someone says "do it this way, because" and doesn't back it up with a reasoned explanation. I can understand using square brackets for objects whose names have spaces in them, but one of our standards is to "not" place spaces in object names, be they a table name, column name, etc. So, I don't see the reason for wasting the keystrokes. It seems to me, the more SQL there is, square brackets just clutter it up.

    JimB
  8. jkbeaumont1 New Member

    quote:Originally posted by MohammedU

    SQL Server Database Coding Conventions,
    Best Practices, and Programming Guidelines
    http://www.sql-server-performance.com/vk_sql_best_practices.asp


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    I don't see anything in that article that speaks to square brackets as a standard. Am I missing something?

    JimB
  9. MohammedU New Member

    You don't see any where about square brackets in any coding standards...
    It is just coding standards written by one of the SQL MVPs....


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  10. ekb18c New Member

    I wouldn't say it's a standard but I'm thinking the DBA or whoever is setting up the tables is trying to set it up so that you don't get any errors while trying to join those tables. Example he/she maybe creating a table called [employee id] or something of that nature, he/she is just saying it's best practice to use brackets so you don't get those errors. I personally don't like including spaces in my table names, I would try to use a underscore '_' to replace the spaces or just concatenate it so it say EmployeeId or something to that nature. It's just the preference of the person who creates the tables.
  11. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by jkbeaumont1</i><br /><br />Frank, Microsoft Certified in pretty much everything. And "he" is a "she". My main argument here is this was indicated in a code review, and it was not in the coding standards document that my code reviewer provided me. I have an issue with being critiqued on a point which was not communicated in our standards document. Being told that square brackets is an "industry standard", doesn't cut it with me either if I cannot easily locate the "industry standard". I have found a couple of articles that state you should always use square brackets, but they don't say why. I don't like it when someone says "do it this way, because" and doesn't back it up with a reasoned explanation. I can understand using square brackets for objects whose names have spaces in them, but one of our standards is to "not" place spaces in object names, be they a table name, column name, etc. So, I don't see the reason for wasting the keystrokes. It seems to me, the more SQL there is, square brackets just clutter it up.<br /><br />JimB<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Sorry, as a non native speaker, I always have a hard time figuring out that you use the same job title for males and females. Here in my place it is handled differently and you can mostly tell from the correct job titles, if the person in question is a male or a female. A female "developer" would probably be called a "developeress" (or something like that. Don't take that for certain).<br /><br />I fully agree with you and understand you pretty well. Just because someone claims something, that doesn't make it a standard in and of itself. <br /><br />If your internal guidelines do mention the use of square brackets, well, that's okay. But that is just and only your internal convention. Nothing more and nothing less. <br />If they, however, do not mention square brackets, claiming that their use is some sort of "industry standard" does sound way too simple for me. Especially when this claim is not backed up with some substantial arguments. As you say, it seems as if she hasn't provided any reasonable argument for her claim yet. <br /><br />Again, to repeat myself, I haven't found a corresponding section in the ANSI SQL 92 standard. To the best of my knowledge, they are not part of that standard, but rather MS convention. ANSI SQL uses double quotes to delimit object names. If you follow reasonable naming conventions for object names, like you obviously do, and do adhere to these conventions, the only reason why to use square brackets is ruled out. That is, you would run into trouble with object names that contain a space or some other special characters without using square brackets. Though I can understand your mentioning of "keystrokes" (being a lazy coder myself), it is not about keystrokes or that they might make an SQL statement look ugly. If square brackets would be mandatory, you wouldn't complain about that anyway. But they aren't mandatory (or necessary) and so I think you should get your discussion on a technical level again. The whole topic itself is not really worth fighting over it as it is actually very clear. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Share This Page