Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2012-01-19 : 00:52:09
|
| I repeatedly see documentation that says that you should fully qualify your objects...select * from Sales.SalesDetailsInstead of just...select * from SalesDetailsI have three questions surrounding this. First - I don't seem to be able to reference my objects that way. I have to include the user. Is that because I don't have a default user specified for the database? I have to use this syntax...select * from Sales.dbo.SalesDetailsSecond - how do you manage changing your code as you promote it from local to dev to staging to production if your databases don't have the same name? A simple search and replace? Third - will this help increase plan reuse since our multiple environments (test, staging, and production) are on the same SQL instance (I know it is bad but it is what it is for now).Thanks. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-19 : 07:15:44
|
| SQL Server uses the four-part naming convention. See here for an explanation: http://msdn.microsoft.com/en-us/library/ms177563.aspxIn your example - Sales.dbo.SalesDetails, Sales is the database name, dbo is the SCHEMANAME and SalesDetails is the table name.If your current database is Sales then you can omit the database name - Select * from dbo.SalesDetails will be sufficient. You can omit the dbo as well if that is the default schema for the user.-- Regarding promoting from Dev to Staging to Production with a different database name: I would try my very best to have the same database names to avoid all kinds of pitfalls. But, if you had to, I guess you will need to replace the names as appropriate. I don't know of a better way. SQL 2008 has SYNONYMS, which may be useful in a limited way. http://msdn.microsoft.com/en-us/library/ms190626.aspx |
 |
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2012-01-20 : 15:06:21
|
quote:
Originally posted by sunitabeckIf your current database is Sales then you can omit the database name - Select * from dbo.SalesDetails will be sufficient.
So if the top of the stored proc has...USE DBNameThen You don't need to include it when tables are referenced. But from .NET code if you are executing a query and not an SP or function then I presume you should fully qualify the name? |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-20 : 15:19:22
|
| From your .Net code, you have the option to specify a database name in the connection string. (see here for examples: http://connectionstrings.com/ ). If you don't specify the database, when you are connected, your current database will be the default database of the login. If the schema and table you are querying is in your current database, you don't need to specify the database name.So it comes down to "what is your current database when you are executing the query". |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2012-01-20 : 16:43:07
|
| Of course. I feel slightly dumb right about now. Thanks for your answers. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-21 : 07:18:54
|
quote:
Of course. I feel slightly dumb right about now. Thanks for your answers.
You are very welcome, but you are being hard on yourself :) In my humble opinion, not knowing the obscure details of a software product does not make a person dumb. And,carrying all that information in one's head does not make one any smarter than the rest of the human population. |
|
|
|
|
|
|
|
|
name resolution