SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Difference in Querying Data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cstokes91
Yak Posting Veteran

USA
60 Posts

Posted - 05/02/2013 :  12:30:50  Show Profile  Reply with Quote
Hey,

I have a random question...

When you write a query... say SELECT* FROM [DatabaseName].[dbo].[TableName]

what is the difference in that and
[DatabaseName]..[TableName] ? I have seen it written like that before but I just don't want to run into errors later down the road for not fully qualifying or something.

I have even ran into weird errors from not putting the brackets around the database and table names.

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 05/02/2013 :  12:36:54  Show Profile  Reply with Quote
The 3-part naming convention is [Databasename].[SchemaName].[TableName]. When you omit the [Schemaname], it assumes the schema is the default schema (in your example dbo).

So most of the time, you could omit the schema name, but I always specify the actual schema name.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
799 Posts

Posted - 05/02/2013 :  13:01:56  Show Profile  Reply with Quote
If you omit the schema name - it could cause performance issues. When you omit the schema - and you have multiple users using that query to access the system and each user has a different default schema, SQL Server will create a separate execution plan for each user.

Not only could you have multiple plans defined and saved in memory, but one user could end up with an optimal plan and a different user with a non-optimal plan and troubleshooting will become much harder.

I recommend that you always schema-qualify all objects and only use the database when accessing objects in another database.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000