Having fun with square brackets

This article is just for fun. From time to time T-SQL dialect may look like little bit scary. In the example below, we will create such a monster using square brackets. We will create a sample table and remove one square bracket in the column name. Let’s see what will happen.

First, create the tmp sample table standard way:

DROP TABLE IF EXISTS tmp 

CREATE TABLE [tmp] (
	[Id] INT NOT NULL PRIMARY KEY, 
	[Col1] VARCHAR(10) NOT NULL,
	[Col2] VARCHAR(20) NOT NULL,
	[Col3] VARCHAR(30) NOT NULL
	) 

SELECT * FROM tmp

SELECT c.[name], tp.[name], c.[max_length]
FROM sys.[tables] t
	INNER JOIN sys.columns c ON [c].[object_id] = [t].[object_id]
	INNER JOIN sys.types tp ON [tp].[system_type_id] = [c].[system_type_id] AND [tp].[user_type_id] = [c].[user_type_id]
WHERE t.[name] = 'tmp'
GO

We will remove one of the square brackets now: the one right to [Col1]. What do you expect to happen? Will the statement crash?

DROP TABLE IF EXISTS tmp 

CREATE TABLE [tmp] (
	[Id] INT NOT NULL PRIMARY KEY, 
	[Col1 VARCHAR(10) NOT NULL,
	[Col2] VARCHAR(20) NOT NULL,
	[Col3] VARCHAR(30) NOT NULL
	) 

SELECT * FROM tmp

SELECT c.[name], tp.[name], c.[max_length]
FROM sys.[tables] t
	INNER JOIN sys.columns c ON [c].[object_id] = [t].[object_id]
	INNER JOIN sys.types tp ON [tp].[system_type_id] = [c].[system_type_id] AND [tp].[user_type_id] = [c].[user_type_id]
WHERE t.[name] = 'tmp'
GO

Are you surprised by the result? It’s easy to explain. Everything between green marked square brackets is the new column name:

Square brackets don’t need to be paired in T-SQL like {} brackets in other programming languages. To be honest, I will prefer T-SQL to be more rigid there but this will require major changes to the syntax to allow escaping square brackets with other characters, etc., and makes things more complicated.