SQL query basics – How to improve readability by formatting capitalization and object naming

Many development teams spend an inordinate amount of time arguing over styling and formatting preferences. Although these preferences are often subjective, at the end the code should be consistent. Since styling comes up frequently during code reviews, it is a good idea to have a strategy in place for dealing with it. This article series will address several SQL readability strategies as well as provide examples that demonstrate different ways you can format T-SQL in ApexSQL Refactor. Let’s begin with capitalization and object naming.

By utilizing formatting features in ApexSQL Refactor, you can set the rules for:

• Reserved words – keywords (CREATE, ALTER, SELECT, DECLARE, and so on)

• Data types (int, varchar, nvarchar, etc.)

• Object names – identifiers (name of tables, views, stored procedures, and so on)

• System and built-in functions (SUBSTRING, ABS, LEFT, etc.)

• Variables

Some developers prefer to make all object names lowercase, whereas others prefer Proper Case (Camel Case). Some prefer the keywords in UPPERCASE while others just mix and match. ApexSQL Refactor can handle all those different preferences. Let’s look at a few examples that demonstrate different types of capitalization and object naming.

In the first example, keywords and data types are uppercase but user-defined object names are proper case:

IF OBJECT_ID('ApexDocs', 'U') IS NOT NULL

	DROP TABLE ApexDocs

GO

CREATE TABLE ApexDocs (

	DocID INT NOT NULL IDENTITY

	,DocTitle NVARCHAR(50) NOT NULL

	,DocFileName NVARCHAR(400) NOT NULL

	,CONSTRAINT PK_ApexDocs_DocID PRIMARY KEY CLUSTERED (DocID ASC)

	)

GO 

In the next example, all the keywords and data types are lowercase; the user-defined object names are proper case:

if object_id('ApexDocs', 'U') is not null

	drop table ApexDocs

go

create table ApexDocs (

	DocID int not null identity

	,DocTitle nvarchar(50) not null

	,DocFileName nvarcar(400) not null

	,constraint PK_ApexDocs_DocID primary key clustered (DocID asc)

	)

go

In the final example, the reserved words are uppercase, the object names are proper case, but the data types are lowercase:

IF OBJECT_ID('ApexDocs', 'U') IS NOT NULL

	DROP TABLE ApexDocs

GO

CREATE TABLE ApexDocs (

	DocID int NOT NULL IDENTITY

	,DocTitle nvarchar(50) NOT NULL

	,DocFileName nvarchar(400) NOT NULL

	,CONSTRAINT PK_ApexDocs_DocID PRIMARY KEY CLUSTERED (DocID ASC)

	)

GO

The key to a capitalization strategy should be readability. As long as the standards are applied consistently and the consistency follows the collation of the identifiers used in the target SQL Server databases, there is no right or wrong way.

Going further, when you name an object such as a table or column, you must decide on whether to qualify the object name, whether the asterisk (*) wildcard is permitted in SELECT lists rather than specifying the column names, whether the use of column numbers in place of names is permitted in your SQL statements, and so on. In the first example, a wildcard is used in the SELECT list, in place of the column names:

SELECT * FROM DimCustomer

Compare that code to the following one where the Expand wildcards option is used in ApexSQL Refactor:

SELECT DimCustomer . CustomerKey , DimCustomer . GeographyKey FROM DimCustomers 

As you can see, the SELECT list now includes the column names rather than a wildcard. Notice that in this example the table name is also qualified with the column names. If this wasn’t the case, you could use the Object name qualifying refactor to add an object name (a table, view name, etc.) to column names, a schema to each object listed in the code, or an alias name to column names.

Clear T-SQL formatting guidelines and automatic implementation of formatting rules will ensure that all team members and anyone who inherits the code can easily read it. Stay tuned for the next article, which will cover the popular formatting standards for commas, spacing, and aligning.

Useful resources:

Manage code formatting
How to capitalize one or more words
Convert any uppercase sentence as normal sentence