In my last post in this series,
I talked about defining varchar columns, parameters, or variables without length. Next I want to talk about using "bad" characters, like spaces or dashes, in entity names.
Every once in a while, I see people who have issues with T-SQL code, or the Management Studio UI, or 3rd party applications - because their server / instance / database / object / column name has a dash, or a space, or starts with a number. Why do we keep doing this to ourselves?
Of course, the easy workaround is to use a square bracket around the offending name. Double quotes are also acceptable, depending on your QUOTED_IDENTIFIER setting, but I prefer square brackets - both because the double quotes look like string delimiters to me, and because you can't always control settings like QUOTED_IDENTIFIER in everyone else's environments.
Using square brackets might be fine for your own T-SQL - though your co-workers will likely curse you every time they have to code around it. But what if it's someone else's code that you can't control? There have been several bugs in the SSMS UI where functionality was broken when it came across a non-standard name. Most of these have been fixed (let me know if you want me to dig a few of these up for you), but who's to say we won't come across another one tomorrow?
I typically add [square brackets] around any questionable names I end up using anyway, even when they are not necessary. For example, the word "Domains" lights up green in SSMS, and "Description" lights up blue, but these are both names that I prefer to use over others that don't cause this visual issue. I also add square brackets around column names that have special meaning, for example [object_id] is present in a lot of DMVs, and lights up pink if you don't use square brackets; I don't want it to be visually confused with the OBJECT_ID() function.
So try to avoid object names with dashes or spaces (including trailing spaces, see #483553), and in general follow the rules for identifiers. Even though this Books Online topic states that you can use them in delimited identifiers, I would strongly recommend staying away from hyphen, tilde, percent, etc. The benefits of using these characters in entity names are dwarfed by the associated costs.
I am working on a series of "Bad habits to kick" articles, in an
effort to motivate people to drop some of the things that I hate to see
when I inherit code. Up next: using SELECT or RETURN instead of OUTPUT.