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.

The Myth

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:

1. 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 ("#")
2. Subsequent characters can be
   • Letters
   • Decimal numbers
   • Dollar sign ("$"), Number sign ("#") or underscore ("@")
3. The identifier must not be a Transact-SQL reserved word. SQL Server reserves both the uppercase and lowercase versions of reserved words
4. Embedded spaces or special characters are not allowed
5. 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.