Sql-server – SQL table with same name as catalog view

best practicesnaming conventionsql serverssmst-sql

When naming tables in SQL I try to stay away from SQL reserved keywords, but today a colleague questioned the use of Events as a table name. They said, anything that turns green in SSMS shouldn't be used as a table name.

Are there any conflicts or gotchas that I should be concerned with using Events as a table name in MS SQL Server?

Best Answer

I also generally do my best to avoid using reserved words for user created objects, but sometimes it's really hard to avoid. Some very common terms are reserved words (e.g. User and Login, etc), and so sometimes being forced to avoid them at all cost means you need to choose a less meaningful, less intuitively obvious name for your project, and that really does not benefit your project or company.

So the only real "gotcha" is what you need to do in order to prevent the conflicts: always delimit object names that are reserved words (i.e. always specify the names surrounded by [ and ]).

Now, not all words that change color in SSMS will be a technical conflict, but that doesn't mean that you shouldn't delimit them. Those words could become a technical conflict in a future version, and that could complicate (i.e. increase the time, hence cost of) upgrading.

A simple example:

USE [tempdb];
CREATE TABLE dbo.Select ([Col1] INT); -- "Select" = blue
/*
Msg 156, Level 15, State 1, Line XXXXX
Incorrect syntax near the keyword 'Select'.
Msg 102, Level 15, State 1, Line XXXXX
Incorrect syntax near 'INT'.
*/


CREATE TABLE dbo.[Select] ([Col1] INT);
-- Success


CREATE TABLE dbo.Event ([Col1] INT); -- "Event" = blue
-- Success


CREATE TABLE dbo.Events ([Col1] INT); -- "Events" = green
-- Success

Again, even though "Events" isn't required to be delimited, if you do use it, I would highly recommend that you always specify it as [Events] to guarantee no conflicts.

Related Solutions

Should I use a Column naming convention or rely on column descriptions

Pick a standard, make sure it makes sense, and document it. There have been plenty of debates about this (e.g. whether the identity column in the contacts table should be called ID or ContactID, or whether the contacts table should be called dbo.Contacts or the unnecessarily verbose dbo.tblContacts), and you're never going to find agreement among your peers. Which standard you choose is not important; what is important is that you use it consistently, make it well-known, and get agreement from your colleagues that this is the way.
Your columns should be self-documenting (and not cryptic, abbreviated or obfuscated) where practical. I would NOT rely on the extended properties, but that's just me (google/bing for problems with extended properties and you will see plenty of complaints I would guess). External documentation / data dictionary is much more valuable to me - if your database goes south and so does your backup, it makes it that much easier to rebuild. It also makes it easier to describe your schema to junior developers etc. without giving them direct access to everything.

Sql-server – Renaming SQL Server Agent operator

In order to change the name (or any other property of an operator), you'd make a call to msdb.dbo.sp_update_operator. For instance:

exec msdb.dbo.sp_update_operator 
    @name = 'OldOperatorName', 
    @new_name = 'NewOperatorName';
go

As for SSMS: most things you do in SSMS you have to option to script out the changes you are making in the GUI. So, without hitting Ok, you can select the Script button at the top to see what T-SQL SSMS will be executing. It seems as though even with a changed name (i.e. altered text in the Name text box), the generated T-SQL doesn't contain a specified @new_name parameter for the stored procedure call.

Moral of the story? Don't rely on a UI. If you want to have full control over what's happening then you should be using T-SQL. There will be no question in your mind if you're writing out the code to do what you want. No assumptions, nothing.

Regarding changing the name, SQL Server stores the ID of the operator to create the correlation between notification -> operator, and not the operator name. So no, you are not bound to the operator name and I don't believe changing the name of the operator will break anything.

Best Answer

Related Solutions

Should I use a Column naming convention or rely on column descriptions

Sql-server – Renaming SQL Server Agent operator

Related Question