All Caps for SQL Syntax?

Discussion in 'Programming' started by N4g4rok, Dec 1, 2012.

?

All Caps for SQL Syntax?

  1. Yes

  2. No

  3. A mix of both

Multiple votes are allowed.
Results are only viewable after voting.
  1. N4g4rok

    N4g4rok Senior member

    Joined:
    Sep 21, 2011
    Messages:
    285
    Likes Received:
    0
    I'm curious, do you prefer tot type your SQL in all caps? Lately i've been correcting myself and doing so.
     
  2. KLin

    KLin Lifer

    Joined:
    Feb 29, 2000
    Messages:
    29,330
    Likes Received:
    3
    I usually capitalize t-sql keywords (SELECT, FROM, WHERE, GROUP BY, HAVING) etc. etc. and use pascal casing for field names and table names.
     
  3. Nothinman

    Nothinman Elite Member

    Joined:
    Sep 14, 2001
    Messages:
    30,672
    Likes Received:
    0
    I'm with KLin, keywords get capitalized while the tables, columns, etc get case according to how they were created/inserted.
     
  4. Markbnj

    Markbnj Elite Member <br>Moderator Emeritus
    Moderator

    Joined:
    Sep 16, 2005
    Messages:
    15,687
    Likes Received:
    8
    This.
     
  5. Leros

    Leros Lifer

    Joined:
    Jul 11, 2004
    Messages:
    21,881
    Likes Received:
    5
    Agree with the above. Not that it really matters, but it is nice to be consistent throughout the codebase.
     
  6. Dratickon

    Dratickon Junior Member

    Joined:
    May 13, 2012
    Messages:
    21
    Likes Received:
    0
    This is also what I tend to do. I work with a lot of legacy code/queries that were written without any standardized coding style, so I've learned that the little things like this make a difference and add up over time.
     
  7. clamum

    clamum Lifer

    Joined:
    Feb 13, 2003
    Messages:
    23,702
    Likes Received:
    6
    ++
     
  8. KentState

    KentState Diamond Member

    Joined:
    Oct 19, 2001
    Messages:
    6,860
    Likes Received:
    3
    Capitalization is fine, but the biggest pet peeve is indents for me. A block of SQL code that I have to reformat to read is not pleasant. However, reserved T-SQL syntax is always in caps for me and objects match how the meta data.
     
  9. N4g4rok

    N4g4rok Senior member

    Joined:
    Sep 21, 2011
    Messages:
    285
    Likes Received:
    0
    What kind of indentation would you like to see in SQL code? I usually try to fit everything into C/C++ like blocks if i can. For example:

    Code:
    CREATE TABLE Persons
     (
       P_Id int primary key,
       LastName varchar(255),
       FirstName varchar(255),
       Address varchar(255),
       City varchar(255)
     );
    
     
  10. KentState

    KentState Diamond Member

    Joined:
    Oct 19, 2001
    Messages:
    6,860
    Likes Received:
    3
    I hate seeing something like:
    Code:
    WITH cte (column1, column2)
    AS (SELECT COLUMN1, COLUMN2 FROM TABLE)
    SELECT
    COLUMN1, COLUMN2,
    COLUMN3, COLUMN4....
    FROM A INNER JOIN B ON A. = B.
    INNER JOIN C ON B. = C. INNER JOIN D ON 
    A. = D. INNER JOIN cte ON ....
    WHERE A.VALUE > B.VALUE AND (D.VALUE = '' OR C.VALUE = '')
    AND C.VALUE = ''
    GROUP BY COLUMN1, COLUMN2,
    COLUMN3, COLUMN4....
    
    I much prefer:
    Code:
    WITH cte (column1, column2)
    AS
    (
      SELECT COLUMN1, COLUMN2
      FROM TABLE
    )
    SELECT
       COLUMN1, 
       COLUMN2,
       COLUMN3, 
       COLUMN4....
    FROM A 
      INNER JOIN B ON 
         A. = B.
      INNER JOIN C ON 
         B. = C. 
      INNER JOIN D ON 
         A. = D.
      INNER JOIN cte ON.....
    WHERE A.VALUE > B.VALUE 
       AND (D.VALUE = '' OR C.VALUE = '')
       AND C.VALUE = ''
    GROUP BY 
       COLUMN1, 
       COLUMN2,
       COLUMN3, 
       COLUMN4....
    
    Just break it apart enough so I can read it. I've seen various indentation methods and as long as it's used consistently, I'm happy. There are some SQL beautifier tools that do a great job right in Management Studio.
     
  11. AyashiKaibutsu

    AyashiKaibutsu Diamond Member

    Joined:
    Jan 24, 2004
    Messages:
    9,308
    Likes Received:
    1
    Last office capped sql key words. This office we cap variables... At the end of the day, I don't really care I just write it to fit in with the code that's there, but I do like key words a little better.
     
  12. ringtail

    ringtail Senior member

    Joined:
    Mar 10, 2012
    Messages:
    899
    Likes Received:
    0
  13. Snapshot1

    Snapshot1 Member

    Joined:
    Dec 26, 2011
    Messages:
    42
    Likes Received:
    0
    If maintaining existing code I will try to conform to whatever convention is established.

    In new code I tend to avoid capitalization but I am generally using SQL Server Management Studio which color-codes keywords, comments, etc. so they are easy to distinguish WITHOUT CAPITALIZATION which many of us have come to associate with shouting.

    But careful / consistent indentation and suitable comments are both mandatory, and I apply these to both new and existing work without exception.

    Snapshot1
     
  14. Markbnj

    Markbnj Elite Member <br>Moderator Emeritus
    Moderator

    Joined:
    Sep 16, 2005
    Messages:
    15,687
    Likes Received:
    8
    I think the point is that, in case-insensitive syntaxes, if you don't adhere to a convention you end up with a mix of styles. When I started learning SQL using dblib the convention that MS inherited from Sybase was uppercase for reserved words. So I've stuck with that over the years.
     
  15. MajorMullet

    MajorMullet Senior member

    Joined:
    Jul 29, 2004
    Messages:
    814
    Likes Received:
    4
    Same here
     
  16. Evadman

    Evadman Administrator Emeritus<br>Elite Member

    Joined:
    Feb 18, 2001
    Messages:
    30,990
    Likes Received:
    0
    This, and it is what I teach everyone else that I work with.
     
  17. pauldun170

    pauldun170 Diamond Member

    Joined:
    Sep 26, 2011
    Messages:
    3,346
    Likes Received:
    33
    We are an all caps shop here.
    With rent-a-devs floating in and out the easiest and quickest way to enforce a convention is just make everything all caps when it comes to queries.
    It is more than just readability\making the resident coder nazi's happy.

    The performance boost from shared statements directly impacts application performance at least for repeated simple queries.


    SELECT BITCHES FROM UP_IN_HERE WHERE UPPER(BOOTY) = 'BOUNCING'
    is not the same as
    SELECT Bitches FROM Up_in_here WHERE upper(BOOTY) = 'BOUNCING'
    is not the same as
    select bitches from up_in_here where upper(booty) = 'BOUNCING'

    If there are lot of queries looking for bouncing booty...we don't want the RDBMS to have to go through every little song and dance routine to fetch the data again and again.

    After years and years of having mixed skill sets\experience floating through the shop, we've found that one of the ways to ensure consistency and reduce headaches at least when it comes to sql is to hand them the naming convention cheat sheet and to rock the caps lock.

    There are always times where folks will check in a script\code with the convention they are used to but since its easy to spot we are pretty quick to identify the perp (who obviously doesn't listen or read the mandatory toilet reading when joining the department), implement a public shaming and place the code under public scrutiny.

    HOWEVER, this doesn't apply to all of our scripts (procedures\functions etc etc where typical coding conventions are used).

    In summary
    1. All my SQL queries are shouted (caps lock rocked)
    2. Strict naming convention policies.
    3. Rules of the shop trump whatever standard your used to or comfortable with.
     
  18. Evadman

    Evadman Administrator Emeritus<br>Elite Member

    Joined:
    Feb 18, 2001
    Messages:
    30,990
    Likes Received:
    0
    That is hard as hell to read. I wonder how much time is spent debugging vs using mixed case.
     
  19. Markbnj

    Markbnj Elite Member <br>Moderator Emeritus
    Moderator

    Joined:
    Sep 16, 2005
    Messages:
    15,687
    Likes Received:
    8
    I thought SQL was case insensitive. You're saying the engine treats those as different queries?
     
  20. beginner99

    beginner99 Platinum Member

    Joined:
    Jun 2, 2009
    Messages:
    2,832
    Likes Received:
    4
    maybe MySQL does but it also does a lot of other weird things. /bashing

    I also have never heard of this...
     
  21. Evadman

    Evadman Administrator Emeritus<br>Elite Member

    Joined:
    Feb 18, 2001
    Messages:
    30,990
    Likes Received:
    0
    If installed on a windows system, MySQL table names and databases are not case sensitive. If installed on some flavor of *ix, it IS case sensitive, and goes by the underlying OS rules. A database corresponds to a folder in the OS, and a table corresponds to a file. *ix OS's are case sensitive for folders and files, so that means MySQL is too on those OS's. Same thing with table aliases, and other things.

    I can think of nothing to call this behavior besides absolutely retarded.
     
  22. Obsoleet

    Obsoleet Platinum Member

    Joined:
    Oct 2, 2007
    Messages:
    2,184
    Likes Received:
    0
    Usually a mix, but if I use one or the other, I use all lowercase.
     
Loading...