When I was in college and taking programming classes (back in the days when “a T-Rex ate my 5.25 inch floppy” was a reasonable excuse) we were taught that since the compiler didn’t really see white space we should use it to format our code. And that is absolutely correct. White space is a wonderful way to format your code. However it can, and frequently is, taken to extremes. I’ve heard “There is no such thing as too much white space” and I disagree. Strongly.
I’m going to show you an example using one of the queries that will output from my sp_dbpermissions stored procedure. It’s medium-long so it should make a good example.
SELECT 'master' AS DBName, DBPrincipals.name AS DBPrincipal, SrvPrincipals.name AS SrvPrincipal, DBPrincipals.sid,
DBPrincipals.type, DBPrincipals.type_desc, DBPrincipals.default_schema_name, DBPrincipals.create_date,
DBPrincipals.modify_date, DBPrincipals.is_fixed_role, Authorizations.name AS Role_Authorization,
CASE WHEN DBPrincipals.is_fixed_role = 0 THEN
'DROP ' + CASE DBPrincipals.[type] WHEN 'C' THEN NULL
WHEN 'K' THEN NULL
WHEN 'R' THEN 'ROLE'
WHEN 'A' THEN 'APPLICATION ROLE'
ELSE 'USER' END +
' '+QUOTENAME(DBPrincipals.name COLLATE SQL_Latin1_General_CP1_CI_AS) + ';' ELSE NULL END AS Drop_Script,
CASE WHEN DBPrincipals.is_fixed_role = 0 THEN
'CREATE ' + CASE DBPrincipals.[type] WHEN 'C' THEN NULL
WHEN 'K' THEN NULL
WHEN 'R' THEN 'ROLE'
WHEN 'A' THEN 'APPLICATION ROLE'
ELSE 'USER' END +
' '+QUOTENAME(DBPrincipals.name COLLATE SQL_Latin1_General_CP1_CI_AS) END +
CASE WHEN DBPrincipals.[type] = 'R' THEN
ISNULL(' AUTHORIZATION '+QUOTENAME(Authorizations.name COLLATE SQL_Latin1_General_CP1_CI_AS),'')
WHEN DBPrincipals.[type] = 'A' THEN ''
WHEN DBPrincipals.[type] NOT IN ('C','K') THEN
ISNULL(' FOR LOGIN ' +
QUOTENAME(SrvPrincipals.name COLLATE SQL_Latin1_General_CP1_CI_AS),' WITHOUT LOGIN') +
ISNULL(' WITH DEFAULT_SCHEMA = '+
QUOTENAME(DBPrincipals.default_schema_name COLLATE SQL_Latin1_General_CP1_CI_AS),'')
ELSE ''
END + ';' +
CASE WHEN DBPrincipals.[type] NOT IN ('C','K','R','A')
AND SrvPrincipals.name IS NULL
AND DBPrincipals.sid IS NOT NULL
AND DBPrincipals.sid NOT IN (0x00, 0x01)
THEN ' -- Possible missing server principal'
ELSE '' END
AS Create_Script
FROM sys.database_principals DBPrincipals
LEFT OUTER JOIN sys.database_principals Authorizations
ON DBPrincipals.owning_principal_id = Authorizations.principal_id
LEFT OUTER JOIN sys.server_principals SrvPrincipals
ON DBPrincipals.sid = SrvPrincipals.sid
AND DBPrincipals.sid NOT IN (0x00, 0x01)
WHERE 1=1
AND SrvPrincipals.name LIKE '%Kenneth%'
AND EXISTS (SELECT 1
FROM sys.all_objects [Objects]
INNER JOIN sys.database_permissions Permission
ON Permission.major_id = [Objects].object_id
WHERE Permission.major_id = [Objects].object_id
AND Permission.grantee_principal_id = DBPrincipals.principal_id
AND [Objects].name LIKE '%MyObject%')
ORDER BY DBPrincipals.nameHere it is formatted with a bit more white space. Note it’s still a fairly consistent format and just so you know this format is one I’ve seen before on more than one occasion.
SELECT
'master' AS DBName,
DBPrincipals.name AS DBPrincipal,
SrvPrincipals.name AS SrvPrincipal,
DBPrincipals.sid,
DBPrincipals.type,
DBPrincipals.type_desc,
DBPrincipals.default_schema_name,
DBPrincipals.create_date,
DBPrincipals.modify_date,
DBPrincipals.is_fixed_role,
Authorizations.name AS Role_Authorization,
CASE
WHEN DBPrincipals.is_fixed_role = 0
THEN
'DROP ' +
CASE DBPrincipals.[type]
WHEN 'C'
THEN NULL
WHEN 'K'
THEN NULL
WHEN 'R'
THEN 'ROLE'
WHEN 'A'
THEN 'APPLICATION ROLE'
ELSE 'USER'
END +
' ' +
QUOTENAME(DBPrincipals.name
COLLATE SQL_Latin1_General_CP1_CI_AS) +
';'
ELSE NULL
END AS Drop_Script,
CASE
WHEN DBPrincipals.is_fixed_role = 0
THEN
'CREATE ' +
CASE DBPrincipals.[type]
WHEN 'C'
THEN NULL
WHEN 'K'
THEN NULL
WHEN 'R'
THEN 'ROLE'
WHEN 'A'
THEN 'APPLICATION ROLE'
ELSE 'USER'
END +
' ' +
QUOTENAME(DBPrincipals.name
COLLATE SQL_Latin1_General_CP1_CI_AS)
END +
CASE
WHEN DBPrincipals.[type] = 'R'
THEN
ISNULL(' AUTHORIZATION '+
QUOTENAME(Authorizations.name
COLLATE SQL_Latin1_General_CP1_CI_AS),
'')
WHEN DBPrincipals.[type] = 'A'
THEN ''
WHEN DBPrincipals.[type] NOT IN ('C','K')
THEN
ISNULL(' FOR LOGIN ' +
QUOTENAME(SrvPrincipals.name
COLLATE SQL_Latin1_General_CP1_CI_AS),
' WITHOUT LOGIN') +
ISNULL(' WITH DEFAULT_SCHEMA = '+
QUOTENAME(DBPrincipals.default_schema_name
COLLATE SQL_Latin1_General_CP1_CI_AS),
'')
ELSE ''
END +
';' +
CASE WHEN DBPrincipals.[type] NOT IN ('C','K','R','A')
AND SrvPrincipals.name IS NULL
AND DBPrincipals.sid IS NOT NULL
AND DBPrincipals.sid NOT IN (0x00, 0x01)
THEN ' -- Possible missing server principal'
ELSE ''
END
AS Create_Script
FROM
sys.database_principals DBPrincipals
LEFT OUTER JOIN
sys.database_principals Authorizations
ON
DBPrincipals.owning_principal_id =
Authorizations.principal_id
LEFT OUTER JOIN
sys.server_principals SrvPrincipals
ON
DBPrincipals.sid =
SrvPrincipals.sid
AND DBPrincipals.sid NOT IN (0x00, 0x01)
WHERE
1=1
AND SrvPrincipals.name LIKE '%Kenneth%'
AND EXISTS
(SELECT
1
FROM
sys.all_objects [Objects]
INNER JOIN
sys.database_permissions Permission
ON
Permission.major_id =
[Objects].object_id
WHERE
Permission.major_id =
[Objects].object_id
AND Permission.grantee_principal_id =
DBPrincipals.principal_id
AND [Objects].name LIKE '%MyObject%')
ORDER BY
DBPrincipals.name
This format still looks nice and clean and it’s certainly readable. Unfortunately if I’m looking at it the first time I’m going to start getting lost about half way down. The whole point of white space is to make code easier to read not to spread it out so thin that it’s hard to keep track of.
This was an example using a moderate sized query of around 50 lines. The longer format is 125 lines. I’ve seen pieces of code like this that run into the multiple hundreds of lines and longer. Use white space, just use it responsibly.
Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, formatting, language sql, T-SQL 
