The default seems to be upper case, but is there really any reason to use upper case for keywords?
I started using upper case, because I was just trying to match what SQL Server gives me whenever I tried to create something, like a new stored procedure. But then, I felt terrible for my baby (5th) finger, that always needs to hold down the Shift button, so I stopped using upper case. Is there a reason why I should go back to upper case?
PERSONALLY, I DON'T LIKE MY SQL YELLING AT ME. IT REMINDS ME OF BASIC OR COBOL.
So I prefer my T-SQL lowercase with database object names MixedCase.
It is much easier to read, and literals and comments stand out.
It's just a matter of style, probably originating in the days when editors didn't do code colouring.
I used to prefer all upper case, but I'm now leaning towards all lower.
Either way, be consistent.
SQL IS OLD. UPPER CASE IS SHOUTING. IT LOOKS STRANGE AND PERHAPS UGLY.
While arguably true, none of those address the reasons special to the SQL language why upper-case keywords are a good convention.
Unlike many newer languages, SQL has a large number of keywords and relies on the reader's ability to distinguish keywords versus identifiers in order to mentally parse the syntax.
The direct answer to your question, then, is more an answer to “why does the reader of SQL code benefit so much from uppercase keywords, when that's not as true for most modern languages?”:
To rely on keeping the keywords in one's head is reasonable for many modern languages, but unreasonable for SQL; it has too many keywords, and too many variants.
To rely on punctuation cues is reasonable for most modern languages, but unreasonable for SQL; it has too few, instead depending on the precise order of keywords to indicate syntax.
To rely on automatic highlighters for distinguishing keywords is reasonable for modern languages in usual cases, but ignores the reality of what highlighters can achieve for SQL. Most don't cover all keywords of all variants of SQL, and regardless, SQL is frequently and routinely read in contexts where a highlighter won't help.
These are some of the reasons, specific to SQL, that the reader of SQL code is best served by standardising on upper case for keywords, and only using not-upper (i.e. lower, or mixed) case for identifiers.
Highlighting can sometimes help. But only if the highlighter knows you've got SQL; and we very often have SQL in a context where the editor/formatter can't reasonably know it's dealing with SQL. Examples include in-line queries, programmer documentation, and text strings within the code of another language. The same is not true anywhere near as often for languages like Python or C++; yes, their code does sometimes appear in those places, but it's not routinely done the way it is with SQL code.
Also, the reader will commonly be using a highlighter that only knows a subset of the keywords your specific SQL implementation uses. Many of the less-common keywords won't be highlighted except by one that knows your SQL variant intimately. So the reader, even if they're using a highlighter, still needs some more direct way of distinguishing keywords in any moderately-complex SQL statement.
Thus the reader will frequently – and the writer can't know ahead of time when that will be – need assistance from the content of the SQL statement itself, to know what's intended by the writer as a keyword and what's intended as an identifier. So the SQL content itself needs to distinguish keywords for the reader, and using uppercase keywords is the conventional and useful way to do that.
THERE WAS A TIME WHEN MOST PEOPLE DID NOT HAVE THE POSSIBILITY OF ENCODING ANYTHING BEYOND UPPER CASE LETTERS BECAUSE THE RELEVANT ENCODING (ASCII) WAS NOT YET INVENTED. ONLY SIX BITS WERE AVAILABLE. WHILE SQL IS MORE RECENT, LOWER CASE LETTERS WERE NOT COMMON PRACTICE IN PROGRAMMING YET.
NOTE THAT SOME PEOPLE CLAIM THAT THE DATABASE WILL GET A SENSE OF URGENCY AND RUN YOUR QUERIES FASTER.
Gordon Bell's examples are not exactly correct; generally, only the keywords are highlighted, not the entire query. His second example would look like:
SELECT name, id, xtype, uid, info, status,
base_schema_ver, replinfo, parent_obj, crdate,
ftcatid, schema_ver, stats_schema_ver, type,
userstat, sysstat, indexdel, refdate, version,
deltrig, instrig, updtrig, seltrig, category, cache
FROM sysobjects
WHERE category = 0
AND xtype IN ('U', 'P', 'FN', 'IF', 'TF')
ORDER BY 1
I find this far easier to read, since the keywords stand out more. Even with syntax highlighting, I find the uncapitalized example much harder to read.
At my company, we go a little bit farther with our SQL formatting.
SELECT name, id, xtype, uid, info, status,
base_schema_ver, replinfo, parent_obj, crdate,
ftcatid, schema_ver, stats_schema_ver, type,
userstat, sysstat, indexdel, refdate, version,
deltrig, instrig, updtrig, seltrig, category, cache
FROM sysobjects
LEFT JOIN systhingies ON
sysobjects.col1=systhingies.col2
WHERE category = 0
AND xtype IN ('U', 'P', 'FN', 'IF', 'TF')
ORDER BY 1
Less than 10% of the letters in the text we read are upper case. Hence our brains are more keen at recognizing lower case letters than upper case ones. Studies have shown it takes longer to read upper case text. Here is just one example:
http://www.guardian.co.uk/media/mind-your-language/2010/oct/04/new-york-street-signs-capitals
The above example I think emphasizes that even when you're talking about just one or two words, it makes a difference.
It's because SQL is such an old language (1974) that when it was conceived, most keyboards didn't have lowercase letters! The language documentation simply reflected the technology of the time.
Scientific research has proven ALL CAPS is harder to read, so much so that the USA Federal Highway Administration has mandated the use of mixed-case signs in their Manual on Uniform Traffic Control Devices, which states:
The lettering for names of places, streets, and highways on conventional road guide signs shall be a combination of lowercase letters with initial uppercase letters.
The New York Post also published:
Studies have shown that it is harder to read all-caps signs, and those extra milliseconds spent staring away from the road have been shown to increase the likelihood of accidents, particularly among older drivers.
There is no good reason to use uppercase letters, and good reasons not to.
I personally loath using uppercase for SQL keywords. I find it harder to read and absurd in this day and age.
The SQL language is defined as being case-insensitive. Take your finger off that shift key!
Upper case can provide a gain in keyword visibility, but you can compensate with code highlight and indentation.
We use lower case because query editor and other tools do wonders in editing t-sql code, and we see no need to torture the little finger.
Monkey see, monkey do for me. Pattern matching - if I do it the way I've seen it done, the structure of the clauses lines up mentally more easily.
Uppercase is less readable. The outline of all words are shaped like boxes; there are no descenders or ascenders. Lowercase FTW!
I find it more readable. Same for having a newline for the beginning of each clause and indenting between clauses.
Try a formatting product (I use SQL Prompt/SQL Refactor from Red Gate). You can set how you want the capitalization to work, and your code will always be consistently formatted. Rest your pinky and let the computer do the work for you.
One of the reasons for continuing to use capitalization is when you(or someone else) are viewing code in something like notepad, it makes it easier to read. i.e. you can differentiate easily between the "keywords" and the tablenames, SP's, udf's etc
Other than conformity for conformitys sake, no. Although it's a very subjective topic, I prefer using mixed case for all SQL. The SQL is much easier to read, and nothing is lost in modern IDEs where keywords are all color-coded anyway.
The intellisense/autocompletion in Microsoft SQL Server Management Studio allows either upper or lower case for reserved words, but upper cases function calls like MAX(), SUM().
Even so, the parser still allows lower case versions of max() and sum() to be processed.
This implies an ambivalence with regard to the nature of execution, and therefore is simply a matter of personal preference.
I dislike anything written in all caps (and hate typing all caps even more), but couldn't convince myself to go against the community. As usual Vim and its associated packages are the solution to so many problems:
http://www.vim.org/scripts/script.php?script_id=305
Simply type as normal and it will auto-capitalize the keywords as you type. I haven't used all obscure SQL incantations but it hasn't failed me yet.
I call most of my mySQL code from within PHP, and I do all of my PHP editing within vim (or I suppose in this case, VIM ;-). Now I am sure there are plugins out there to highlight the mySQL code within PHP, but I have not found it, and I don't have to the time to go looking for it. Therefore, I prefer to have everything in allcaps. I find this:
if ( !$bla )
{
echo "select something from something where something";
}
if ( !$beepboop )
{
echo "create table if not exists loremIpsum;
}
$query = "
CREATE TABLE IF NOT EXISTS HISTORY
(
ID INT NOT NULL AUTO_INCREMENT,
INSERTDATE TIMESTAMP DEFAULT NOW(),
ALTERDATE TIMESTAMP(8) DEFAULT NOW(),
DELETEDATE TIMESTAMP(8),
ALTERCOUNT INT DEFAULT 0,
SELECTCOUNT INT DEFAULT 0,
PRIMARY KEY(ID),
)ENGINE=InnoDB
";
mysqlQuery( $query, $con );
Helps me distinguish between PHP versus SQL a lot better than this:
if ( !$bla )
{
echo "select something from something where something";
}
if ( !$beepboop )
{
echo "create table if not exists loremIpsum;
}
$query = "
create table if not exists history
(
id int not null auto_increment,
insertdate timestamp default now(),
alterdate timestamp(8) default now(),
deletedate timestamp(8),
altercount int default 0,
selectcount int default 0,
primary key(id),
)engine=InnoDB
";
mysqlQuery( $query, $con );
Also, for some reason, I hate mixing allcaps with camel case, like so:
CREATE TABLE IF NOT EXISTS history
(
ID INT NOT NULL AUTO_INCREMENT,
insertDate TIMESTAMP DEFAULT NOW(),
alterDate TIMESTAMP(8) DEFAULT NOW(),
deleteDate TIMESTAMP(8),
alterCount INT DEFAULT 0,
selectCount INT DEFAULT 0,
PRIMARY KEY(ID),
)ENGINE=InnoDB
That ID irks me. Should it instead be id? or iD?
