Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Optional T-SQL coding practices

Answered Optional T-SQL coding practices

  • Saturday, December 29, 2012 5:26 PM
     
     
    Sign In to Vote
    1

    I have noticed the following optional coding practices widely. Like to know what they are and when these are really needed:

    1. object name: enclosed in bracket. [ProductName] vs ProductName.

    2. Use of 'N' in front of literals: N'Baseball Bat' vs 'Baseball Bat'.

    3. Ending the statement with a semicolon.

    Thanks in advance!

    .... Sushanta

     

All Replies

  • Saturday, December 29, 2012 6:06 PM
     
     Answered Has Code
    Sign In to Vote
    1

    1. You only need to enclose object names, when they contain special characters like spaces or hypens. I prefer names without special characters, thus I normally don't need them.

    2. The N is the Unicode marker for a unicode string. E.g. using a Braille character: 

    DECLARE @Unicode NVARCHAR(255) = N'⣿';
DECLARE @WrongUnicode NVARCHAR(255) = '⣿';
DECLARE @NonUnicode VARCHAR(255) = '⣿';
SELECT @Unicode, @WrongUnicode, @NonUnicode;

    3. Well this is basically an ANSI standard, but makes reading T-SQL easier.

    • Proposed As Answer by Stan210 Saturday, December 29, 2012 6:08 PM
    • Marked As Answer by Sushanta Saha Saturday, December 29, 2012 7:29 PM
    •  
     
  • Saturday, December 29, 2012 6:13 PM
     
     Answered Has Code
    Sign In to Vote
    1

    Hi Sushant,

    Regading your questions:

    1.) The brackets are required if you use reserved keywords or special chars in the column names. You could name a column [First Name] (with a space)--but then you'd need to use brackets every time you referred to that column.

    For example :

       SELECT 'Test' As First Name -- In Correct Syntax.This will throw error

   SELECT 'Test' As [First Name]   -- Correct Syntax

    2.) N basically denotes that the subsequent string is in Unicode (the N actually stands for National language character set). Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT.

    Unicode is typically used in database applications which are designed to facilitate code pages which extend beyond the English and Western Europe code pages.  Unicode is designed so that extended character sets can still "fit" into database columns. What this means is that Unicode character data types are limited to half the space, because each byte actually takes two bytes (Unicode is sometimes referred to as "double-wide").

    Orignal Post - > http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html

    3.) The Semicolon

    The semicolon character is a statement terminator. It is a part of the ANSI SQL-92 standard, but was never used within Transact-SQL. Indeed, it was possible to code T-SQL for years without ever encountering a semicolon.<o:p></o:p>

    Usage<o:p></o:p>

    There are two situations in which you must use the semicolon. The first situation is where you use a Common Table Expression (CTE), and the CTE is not the first statement in the batch. The second is where you issue a Service Broker statement and the Service Broker statement is not the first statement in the batch.<o:p></o:p>

    Regards Harsh

    • Marked As Answer by Sushanta Saha Saturday, December 29, 2012 7:29 PM
    •  
     
  • Saturday, December 29, 2012 6:54 PM
     
     Answered
    Sign In to Vote
    1

    To add to Harsh's comment, a semicolon is also required at the end of a MERGE statement. 

    Also, it is likely that as new features are added to T-SQL in future releases, some of them are likely to require semicolons at the end of statements.  So, IMO, it is a good habit to put semicolon's at the end of every SQL statement when you are writing new code.  However, legacy code will continue to work with or without semicolons at the end of each statement, so there is no need to update older T-SQL code.

    Tom

    • Marked As Answer by Sushanta Saha Saturday, December 29, 2012 7:29 PM
    •  
     
  • Saturday, December 29, 2012 7:32 PM
     
     
    Sign In to Vote
    0

    Stephan, Harsh, and Tom:

    Thank you so much for your detailed responses. Excellent support!!

    .... Sushanta

     
  • Saturday, December 29, 2012 7:47 PM
    Moderator
     
     
    Sign In to Vote
    0

    I do not completely agree with the comments about square brackets.  Square brackets is the method I see used most commonly in Transact SQL and it is the method that I tend to use for transact sql; however, there are two alternatives: either square brackets can be used or double quotes.

    If your code might be otherwise used in either Oracle or DB2 you probably should use double quotes for delimiting rather than square brackets because square brackets are peculiar to transact sql.

    As was mentioned, square brackets can be used to delimit a column name such as [First Name].  Another alternative is to use delimit with double quotes -- "First Name".  The rub with using double quotes is that this gets impact from the QUOTED_IDENTIFIER setting.  Some applications " require" that QUOTED_IDENTIFIER be set to OFF so that double quotes can be used as delimiters for string constants.  I suggest avoiding this practice.

    Delmiting methods in transact SQL:

    • Square Brackets
    • Double quotes with QUOTED_IDENTIFIERS set ON

     
  • Saturday, December 29, 2012 8:47 PM
    Moderator
     
     
    Sign In to Vote
    1

    If your code might be otherwise used in either Oracle or DB2 you probably should use double quotes for delimiting rather than square brackets because square brackets are peculiar to transact sql.

    Yes quoted identifiers are ANSI.

    Nonetheless, many coders (including me) feel insecure about coding with double quotes. The reason, if an object is scripted, it comes with "tons of" square brackets. In other words, we are used to [] for object delimiters.

    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

     
  • Saturday, December 29, 2012 9:52 PM
     
     
    Sign In to Vote
    1

    Nonetheless, many coders (including me) feel insecure about coding with double quotes. The reason, if an object is scripted, it comes with "tons of" square brackets. In other words, we are used to [] for object delimiters.

    Yup. And some people are so used with it, and they use them in their own code. Which does future-protect them against future reserved words - but makes the code oh so hard to read.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Saturday, December 29, 2012 10:08 PM
     
     
    Sign In to Vote
    1

    I dislike square brackets in code too as they make it so bulky.  I often use this 'square-bracket stripper' expression in SSMS:

    The Find expression is:  "\[|\]"  In English this translates to: "Find all opening square brackets OR closing square brackets and replace them with nothing."