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

 

4 thoughts on “Do you find T-SQL scripts hard to read with all the square brackets?”

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

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

  3. 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?

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

Leave a Reply

Your email address will not be published. Required fields are marked *