Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

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

Sep 5 2011 12:00AM by Nakul Vachhrajani   

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.

Tags: 


Nakul Vachhrajani
4 · 36% · 11618
5
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

7  Comments  

  • 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

    commented on Sep 14 2011 9:45AM
    Madhivanan
    3 · 40% · 12909
  • yeah,u r right! it's interesting!

    commented on Sep 15 2011 5:37AM
    goodasong
    1077 · 0% · 24
  • 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
    
    commented on Sep 16 2011 5:50AM
    Marc Jellinek
    95 · 2% · 586
  • marc_jellinek@hotmail.com , by object type I meant the different categories (database, users, tables and linkedserver). See my example code I have posted

    commented on Sep 16 2011 9:36AM
    Madhivanan
    3 · 40% · 12909
  • Nice post Nakul. Thanks for sharing

    commented on Sep 20 2011 1:11AM
    Hardik Doshi
    20 · 9% · 2853
  • @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.

    commented on Sep 20 2011 6:01AM
    Marc Jellinek
    95 · 2% · 586
  • marc_jellinek@hotmail.com , that is a good point. But I have just pointed out that it is possible to create like that.

    commented on Sep 20 2011 6:23AM
    Madhivanan
    3 · 40% · 12909

Your Comment


Sign Up or Login to post a comment.

"SQL Server Myth - Table Name cannot be same as Database Name" rated 5 out of 5 by 5 readers
SQL Server Myth - Table Name cannot be same as Database Name , 5.0 out of 5 based on 5 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]