Microsoft SQL Server is home to many myths and legends, and I have developed an interest in knowing about them. It is really a very interesting exercise to know about these myths, and prove them incorrect if the situation permits.
One such myth is that a table name in a Microsoft SQL Server database cannot be the same as the database name.
The application that I work on does not have such a scenario. I had never ever heard about anything remotely similar, and therefore, I had to investigate this one. So, let’s see if this one is true or not via a simple DIY ("Do It Yourself") test.
The DIY Test
I have always believed that a simple test can go a long way in clearing out the concepts. So, let’s begin by connecting to a SQL Server 2008 R2 instance, and checking if SQL Server allows us to create a table of the same name as a database.
/* WARNING - THIS CODE IS PRESENTED AS-IS AND WITHOUT WARRANTY. PLEASE DO NOT USE IT IN PRODUCTION/QA ENVIRONMENTS */ USE AdventureWorks2008R2 GO CREATE TABLE AdventureWorks2008R2 (MyKey INT) GO
Next, let’s insert a few values:
/* WARNING - THIS CODE IS PRESENTED AS-IS AND WITHOUT WARRANTY. PLEASE DO NOT USE IT IN PRODUCTION/QA ENVIRONMENTS */ USE AdventureWorks2008R2 GO INSERT INTO AdventureWorks2008R2 VALUES (1), (2), (3) GO
Finally let’s attempt to select from this new table followed by necessary cleanup:
/* WARNING - THIS CODE IS PRESENTED AS-IS AND WITHOUT WARRANTY. PLEASE DO NOT USE IT IN PRODUCTION/QA ENVIRONMENTS */ USE AdventureWorks2008R2 GO SELECT * FROM AdventureWorks2008R2 GO --Cleanup DROP TABLE AdventureWorks2008R2 GO
Surprised? Don’t be. It’s perfectly legal to have a table name same as the database name.
So, what’s going on here?
Great question! That’s what one should be after.
A table is ultimately a database object. The database object name is referred to as its identifier. If we look into MSDN for the rules of naming identifiers (http://msdn.microsoft.com/en-us/library/ms175874.aspx), we see that the rules for naming database objects can be summarized as under:
- First character must be one of:
- A Unicode letter (Latin characters from a-z, A-Z and characters from other languages)
- The Underscore ("_"), at sign ("@") or the number sign ("#")
- Subsequent characters can be
- Decimal numbers
- Dollar sign ("$"), Number sign ("#") or underscore ("@")
- The identifier must not be a Transact-SQL reserved word. SQL Server reserves both the uppercase and lowercase versions of reserved words
- Embedded spaces or special characters are not allowed
- Supplementary characters are not allowed
Nowhere is it mentioned that identifiers cannot be the same as the database name.
NOTE: When identifier names conflict with reserved words, they must be enclosed in square braces. So, a table name like "TABLE" is illegal, while "[TABLE]" is perfectly legal.
Special Thanks to…
This post has been inspired from Pinal Dave’s (blog) series – SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Efficient Query Writing Strategy – Day 4 of 35
Until we meet next time,
Be courteous. Drive responsibly.