THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Bit Bucket (Greg Low): IDisposable

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

Do you find T-SQL scripts hard to read with all the square brackets?

The T-SQL scripting tools (such as SQL Server Management Studio) provide two options for scripting names: either to quote them or to omit quotes.

If you avoid things like spaces in object names, you can mostly get away without quoting i.e.

Sales.Customers is just fine and doesn't need to be [Sales].[Customers].

Even then, one problem that can arise is that a name you had used in your code could become a SQL reserved word. In that case, you either need to change every reference to it (painful), or quote it wherever it's used (also painful). So quoting by default is always a safe option. However, it makes the scripts much harder to read due to visual noise.

What I feel is really needed is some way to determine if a name needs quoting i.e. a function such as:

ISVALIDASOBJECTNAME()

So for example:

ISVALIDASOBJECTNAME('Sales') would return 1 but ISVALIDASOBJECTNAME('Sales Targets') would return 0.

This functionality could then be used to extend the QUOTENAME function with an optional parameter that says:

QUOTENAME('Sales Targets',0) for "always quote" or QUOTENAME('Sales Targets',1) for "quote only if needed). They could leave 0 as the default so it is option and the function still works unchanged when no parameter is supplied.

That would then make it easy for the team to change SSMS to have a single setting for how you'd like scripts generated.

If you agree with this, please vote here: https://connect.microsoft.com/SQLServer/feedback/details/796172/isvalidasobjectname-and-quotename-enhancement-to-clean-up-script-readability

 

Published Saturday, August 03, 2013 2:08 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

WayneS said:

Greg - Are you proposing a third parameter to the QUOTENAME function?

QUOTENAME already has an optional parameter to specify what the text should be quoted with. BOL says you can use a square bracket (left or right), or a single or double quote character. My testing shows other characters that works with this function - CHAR(34, 39, 40, 41, 60, 62, 91, 93, 96, 123, 125) all work.

I agree that a way to only quote the needed names would be desirable.

Wayne

August 8, 2013 12:43 AM
 

Greg Low said:

Great point Wayne, I was forgetting that it already had an optional quote character parameter. (I never use that parameter). So yes, I'd be suggesting a third parameter that's also optional.

August 8, 2013 1:09 AM
 

RichB said:

I'd like to go a stop further to see a server side setting that prohibited 'complex' or reserved object names.  

I mean, really, who's the genius that thought putting full stops in database names was a good idea?

August 12, 2013 8:47 AM
 

Greg Low said:

You can already do that to a fairly large degree. You can create a DDL trigger that rolls back any object creation with names you don't like. If we had this function, that would make it even easier but you might also like to have even stricter rules.

However, you can't roll back CREATE DATABASE. I can also see a trend towards more DDL statements that aren't transactional i.e. ones that also can't be rolled back. That's one (amongst many) of the reasons why I'd like to see INSTEAD OF triggers for DDL. Adam Machanic and I have been pushing for that for a long time to little avail. Here's Adam's latest Connect item for it: http://connect.microsoft.com/sql/feedback/details/243986

August 12, 2013 8:32 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement