Case (Capitalization) in SQL statement
When format SQL query, capitalization refers to the way in which SQL token should or should not be capitalized in your code. For example, some developers prefer to make all reserved keys uppercase, others prefer lowercase, and some mix and match. It’s all a matter of preference. When determining what strategies to implement with regard to case, we should take into account the following considerations:
1. SQL token should be categorized in terms of keywords, identifier, data types, variables and constants. Identifier includes schema name, table name, column name, alias name, function/stored procedure name, parameter and etc. Some SQL tokens may be treated as a specific category such identifier start with sp_ in SQL Server.
2. Case rules can be upppercase, lowercase, InitCapEachWord(camelCase), InitCap(capitalize first letter only) and no change.
Once you have those considerations in mind, then you can define your own capitalization strategies.
Let’s look at some examples that demonstrate different capitalization strategies.
In the first statement, all SQL reserved words are uppercase, all identifier such as tables and columns are lowercase, but the first letter of function name was capitalized(InitCap).
SELECT p1.productmodelid
FROM production.product AS p1
GROUP BY p1.productmodelid
HAVING Max(p1.listprice) >= ALL (SELECT Avg(p2.listprice)
FROM production.product AS p2
WHERE p1.productmodelid = p2.productmodelid);
In the next example, all the reserved words and identifer are lowercase, but the table name and alias name are uppercase.
select P1.productmodelid
from production.PRODUCT as P1
group by P1.productmodelid
having Max(P1.listprice) >= all (select Avg(P2.listprice)
from production.PRODUCT as P2
where P1.productmodelid = P2.productmodelid);
Here’s one other example. This time, the keywords are uppercase, the data types lowercase, identifier(such as dbo) lowercase, the table and column are camel case:
CREATE TABLE dbo.EmployeePhoto
(
EmployeeId int NOT NULL PRIMARY KEY,
Photo varbinary(Max) NULL,
MyRowGuidColumn uniqueidentifier NOT NULL ROWGUIDCOL UNIQUE DEFAULT Newid()
);
The key to a capitalization strategy should be readability. There’s no real right or wrong, as long as the standards are applied consistently throughout the organization.
SQL formatting tool can help you do this job precisely and quickly
Some conventional sql formatting tools use ad hoc string processing methods to implement the formatting process, so it can’t distinguish table and column name from other identifiers.
SQL Pretty Printer parse the SQL query according to the syntax of various database SQL dialect. So it can easily turn table name(product) into uppercase while make column name(productmodelid) camelCase like this.
select ProductModelId
from production.PRODUCT
Please note that schema name(production) was keep unchanged.
SQL Pretty Printer also has the ability to apply case rule to a specific identifier category, identifier start with sp_ was set to uppercase in next SQL statement:
execute SP_EXECUTESQL
@QUERY,
N’@Age int’,
@AGE = 25
Here is a screenshot of SQL Pretty Printer’s capitalization format options.
