Creating table names that are reserved words/keywords in MS SQL Server
SqlSql ServerSql Problem Overview
Is it ok to name my database tables that are already keywords? For my case, I am trying to name the table that will hold my users. I've named it User but it is showing up as pink in SQL Server Management Studio so I am assuming its an existing System Table or Keyword. Thanks for your advice.
Official list of reserved keywords: Reserved Keywords (Transact-SQL)
Sql Solutions
Solution 1 - Sql
repeat this three times:
DO NOT DO IT, I WILL NOT USE RESERVED WORDS!
you'll thank me!
Solution 2 - Sql
You can create tables with the same name as keywords. If you "quote" the table name it should work. The default quotes in SQL server are square brackets: []
CREATE TABLE [dbo].[user](
[id] [bigint] NOT NULL,
[name] [varchar](20) NOT NULL
) ON [PRIMARY]
Solution 3 - Sql
Yes, it is ok. In your queries, you can put [ and ] around your table name so that SQL Server knows you are referring to a table - i.e.
CREATE TABLE [User] ...
SELECT * FROM [User]
Solution 4 - Sql
You can use [User] to do this. If at all possible use a table name that doesn't conflict with a keyword, to avoid confusion and bugs.
Solution 5 - Sql
For MS Query, I have found the double quotes works perfectly in the Query.
select T1."Reference"
from MyTable T1
Solution 6 - Sql
Use the 'single quote' for Strings, and "double quote" for column names.
Example:
INSERT INTO AccountMovement
("Date", Info)
VALUES
('2012/03/17', 'aa'),
('2012/03/17', 'bb'),
('2012/03/17', 'cc'),
('2012/03/17', 'dd')
Solution 7 - Sql
I sit in the camp that says that table names should be plural, so in your case that would be Users.
I like this convention as it makes sense to me. You have a collection of users so call your table that. Further down stream if you pull out an indvidual row that could then populate an object named User.
If your convention dictates use of singular for table names use something different e.g.: Member, Client etc.
Also see RacerX's answer!
As previously mentioned it is tecnically OK if you [Braket] the name.
Solution 8 - Sql
As mentioned, you can do it by quoting the name. Of course, you'll also have to quote the name anytime you reference it - trust me, it gets old real quick.
As an aside, just because SSMS syntax colors the word doesn't necessarily mean it's a reserved word. Sql can be annoying like that. ;)
Solution 9 - Sql
Basic rule for table and column names (or better object names in general):
Don't use anything the same as, or even similar to, a reserved word. Only use A-Za-z0-9 and underscore. Especially don't use spaces. Only use names that don't require escaping, and then don't use escaping as a perpetual test.
You, and everyone who works with you, or will ever work on your code, don't need the aggravation.
Solution 10 - Sql
As everyone else has said don't do this; however, I was in the same boat. I have a rule that says all my tables are stored in singular form. Organization not Organizations, Asset not Assets a PartsCatalog has many Part etc.
Well I have a User table so what do I call it? I ended up escaping it [User]. Now I regret that decision because I am always forgetting to escape the table; however, I've not come up with a better name yet: Member is the leading candidate.
Solution 11 - Sql
Not a good idea - for various good reasons
MORE REASONS WHY NOT
- The obvious possible conflict with reserved names
- If in two years you want to do a global replace in your code of say "user" in a form field or anywhere you are screwed when using generic names
- If you need to search for occasions that use "user" in your code - you know where that goes (we have over a million lines of code, it would kill us).
WHAT WE DID
- each table name has a unique start like O_nnn for objects F_nnn for finance data... we applied the same to fields like opp_created for opportunity was created at date, SUSR_RID for referencing to a user ID within a sales function versus OPUSR_RID an operational reference to a user...
- Other than the prefix we use as obvious as possible names such as O_FlightArrivalTime and not O_FltAT. Today's databases show no performance degradation with longer names.
- Now, when using OF_FlightArrivalTime as a Formfield name you find the association easily but a global search for O_F... would only find either the DB field, a search for OF_F... the form field and _F.... both.