All Caps for SQL Syntax?

[N4g4rok]

I'm curious, do you prefer tot type your SQL in all caps? Lately i've been correcting myself and doing so.

[KLin]

I usually capitalize t-sql keywords (SELECT, FROM, WHERE, GROUP BY, HAVING) etc. etc. and use pascal casing for field names and table names.

[Nothinman]

I'm with KLin, keywords get capitalized while the tables, columns, etc get case according to how they were created/inserted.

[Markbnj]

Originally Posted by KLin

I usually capitalize t-sql keywords (SELECT, FROM, WHERE, GROUP BY, HAVING) etc. etc. and use pascal casing for field names and table names.

This.

[Leros]

Agree with the above. Not that it really matters, but it is nice to be consistent throughout the codebase.

[Dratickon]

Originally Posted by KLin

I usually capitalize t-sql keywords (SELECT, FROM, WHERE, GROUP BY, HAVING) etc. etc. and use pascal casing for field names and table names.

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.

[clamum]

Originally Posted by KLin

I usually capitalize t-sql keywords (SELECT, FROM, WHERE, GROUP BY, HAVING) etc. etc. and use pascal casing for field names and table names.

++

[KentState]

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.

[N4g4rok]

Originally Posted by KentState

the biggest pet peeve is indents for me.

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:

CREATE TABLE Persons
 (
   P_Id int primary key,
   LastName varchar(255),
   FirstName varchar(255),
   Address varchar(255),
   City varchar(255)
 );

[KentState]

I hate seeing something like:

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:

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.

[AyashiKaibutsu]

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.

[ringtail]

there's a formatting tool here that some may find useful
Instant SQL Formatter

[Snapshot1]

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

[Markbnj]

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.

[MajorMullet]

Originally Posted by KLin

I usually capitalize t-sql keywords (SELECT, FROM, WHERE, GROUP BY, HAVING) etc. etc. and use pascal casing for field names and table names.

Same here

[Evadman]

Originally Posted by KLin

I usually capitalize t-sql keywords (SELECT, FROM, WHERE, GROUP BY, HAVING) etc. etc. and use pascal casing for field names and table names.

This, and it is what I teach everyone else that I work with.

[pauldun170]

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.

[Evadman]

Originally Posted by pauldun170

SELECT BITCHES FROM UP_IN_HERE WHERE UPPER(BOOTY) = 'BOUNCING'
1. All my SQL queries are shouted (caps lock rocked)

That is hard as hell to read. I wonder how much time is spent debugging vs using mixed case.

[Markbnj]

Originally Posted by pauldun170

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'

I thought SQL was case insensitive. You're saying the engine treats those as different queries?

[beginner99]

Originally Posted by Markbnj

I thought SQL was case insensitive. You're saying the engine treats those as different queries?

maybe MySQL does but it also does a lot of other weird things. /bashing

I also have never heard of this...

[Evadman]

Originally Posted by beginner99

maybe MySQL does but it also does a lot of other weird things. /bashing

I also have never heard of this...

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.

[Obsoleet]

Usually a mix, but if I use one or the other, I use all lowercase.