Working with “special” names

4

October 19, 2015 by Kenneth Fisher

What do you do when you need to use a special character in a name? Well, I guess I should define what I mean by special first. Special characters are any non-alphanumeric character, so &#!~\/ etc, including spaces. It’s really pretty easy. Put []s around the name. Now you can use “s if QUOTED_IDENTIFIER is set on but I wouldn’t recommend it. The []s are more standard and you aren’t relying on a setting. FYI this is also how you manage names that are reserved words. I’m sure no one reading this has ever used a reserved word for a table name (for example) but you might work with someone who has and have to deal with the results.

One nice thing we can do with this is to create formatted column names. Say we have a query and we are planning on giving the output to our manager. It’s not worth doing anything special (ssis, ssrs) but we still want it to look nice.

SELECT name AS [Database Name],
	create_date AS [Date & Time Created]
FROM sys.databases

4 thoughts on “Working with “special” names

  1. notarian says:

    The only naming sin you have not mentioned is trailing spaces on object names – which I have definitely seen [TableName ].

  2. Ray Herring says:

    I once had to work with a schema in which the developer used C# object notation for column names. so for example [dbo].[MyTable].[SomeObj.SomeMemeber]
    Of course there were also references that did not need the [] so the same table would probably have columns named SomeObj and SomeMember.
    The original developer used the same approach for UDFs and procedures also.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,655 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: