Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Yes, Virginia, you can have too much white space.

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.name

Here 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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...