SQL Server Myth – Table Name cannot be same as Database Name


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.

Advertisements

7 thoughts on “SQL Server Myth – Table Name cannot be same as Database Name

  1. Madhivanan

    There is no constraint on having the same name as long as object type differs. So the following code is perfectly valid.

    select test from test.test.test.test where test=’test’

    where the four part name specifies the server,db,owner and table

    Like

    Reply
  2. marc_jellinek@hotmail.com

    > There is no constraint on having the
    > same name as long as object type
    > differs

    This is not true.

    You cannot have two objects with the same name within the same scope.

    For example, you cannot have a table with the same name as a view, function or stored proc within the same schema.

    CREATE SCHEMA [x]
    GO

    CREATE TABLE [x].[foo] (id int IDENTITY)
    GO

    CREATE PROCEDURES [x].[foo] AS SELECT * FROM sys.tables
    GO — FAILS

    Msg 2714, Level 16, State 6, Line 2
    There is already an object named ‘foo’ in the database.

    You can have two objects with the same name (even of the same type) as long as they are in different schemas:

    CREATE SCHEMA [x]
    GO

    CREATE TABLE [x].[foo] (id int IDENTITY)
    GO

    CREATE TABLE [y].[foo] (id int IDENTITY)
    GO

    Like

    Reply
  3. marc_jellinek@hotmail.com

    @Madhivanan, I’m reasonably sure you shouldn’t name a linked server with the same name as the local server. That would lead to a duplicate in master.sys.servers and make four-part naming ([server].[database].[schema].[object]) unreliable.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s