Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

how to differentiate between two tables sharing the same name but belonging to different databases Expand / Collapse
Author
Message
Posted Monday, January 13, 2014 8:21 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 276, Visits: 1,000
liteswitch (1/13/2014)
I'd actually disagree with putting the database name in the 3 identifier, if the database gets renamed all the queries will fail, and that's a lot of effort to put right.



I guess from my stand point if someone renamed a database I would want things to fail.

If you're digging through thousands of lines of code and the connection was to 'databaseA' but somewhere in the code someone put a 'use databaseB' and you are looking for the table that needs 'fixing' you may look at the wrong table.

I've ran into many issues here where the wrong tables get deleted or cleared out because someone didn't put all 3 parts.

I can convert the way I code for a lot of different things, but this is one of those things I don't think I will budge on.
Post #1530311
Posted Monday, January 13, 2014 8:26 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:52 AM
Points: 980, Visits: 1,324
GilaMonster (1/13/2014)
The only time I'll use three-part naming is when I know that I need to be crossing databases. Otherwise 2-part naming's fine.


I agree with you Gail. Sprocs that span multiple databases are always an issue if/when they get moved around and/or renamed. There is not much that can be done other than making sure there is sufficient documentation indicating those objects that reference across to other databases.

There is no easy around that one.

Kurt


Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Post #1530313
Posted Monday, January 13, 2014 8:30 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 276, Visits: 1,000
Almost ever query we write will span more than one database, so that's why I prefer it.
Post #1530314
Posted Monday, January 13, 2014 2:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 36,728, Visits: 31,180
Kurt W. Zimmerman (1/13/2014)
GilaMonster (1/13/2014)
The only time I'll use three-part naming is when I know that I need to be crossing databases. Otherwise 2-part naming's fine.


I agree with you Gail. Sprocs that span multiple databases are always an issue if/when they get moved around and/or renamed. There is not much that can be done other than making sure there is sufficient documentation indicating those objects that reference across to other databases.

There is no easy around that one.

Kurt


Actually, there is. Use synonyms instead of hard coding the 3 or 4 part naming into your code.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530478
Posted Monday, January 13, 2014 2:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 36,728, Visits: 31,180
below86 (1/13/2014)
Jeff Moden (1/12/2014)
GilaMonster (1/12/2014)
Use three-part naming for your queries

SELECT <column list> FROM Sims.dbo.Students
SELECT <column list> FROM SimsCopy.dbo.Students


I agree that works great for ad hoc queries (and you already know this... just saying it for others that may read this) but I'd stick with the 2 part naming convention in any stored T-SQL and use differently named synonyms to point to other database just in case they rename or even move that other database. It keeps you from having to find all occurances of 3 part naming in all of the code and changing it. It's much easier to just change the synonym.


Sorry Jeff I have to agree with Gail here. ALWAYS use the three-part naming. It's going to make the query easier to read and you won't run into an issue of connecting/using the wrong table. I've seen it happen here time and time again.


Then I'll have to agree to disagree. I've never had that problem when using synonyms.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530479
Posted Tuesday, January 14, 2014 12:27 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 4,387, Visits: 9,503
below86 (1/13/2014)
Almost ever query we write will span more than one database, so that's why I prefer it.


If your system consists of multiple databases - and there is never the possibility that one or more of those databases grows to a point where it needs to be moved to another server, then 3-part naming makes sense.

However, if the possibility exists that at some point in time one or more of those databases can be moved to a different server - then using synonyms makes sense.

In either case, if someone changes the name of an object - drops an object - etc... then both will generate a failure.

Synonyms are ideal if you have linked servers in your organization. On a test system, you would have linked server 'OtherSystemTest' and on the production system your linked server would be 'OtherSystem'. If you are using 4-part naming in your code - then promoting that code to production *requires* a code change be made as it is being applied.

Using synonyms in this situation avoids any issues with making code changes between test and production and still allows for code comparisons between the environments.

Synonyms are also ideal in situations where the names of the databases on a test system are different than the names in production. For example, some sites like to put a post-fix of _test on the database names in their test environment. In that situation, using 3-part naming doesn't work because you cannot promote that code to production without code changes - however, using synonyms means no changes to the code need to be made.

There are good reasons to use either method - and both are valid.

Note: I use both methods, but will only use 3-part naming when referencing objects outside the scope of the database where the procedure will live. This allows me to move that procedure to another database if needed - without worrying about accessing data in the 'old' database.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1530833
Posted Wednesday, January 15, 2014 11:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:39 AM
Points: 101, Visits: 489
What would prevent a user from scripting all stored procs and views in a common file and then just use a text editor to find and re-place the old-new databasename and rerun the scripts after dropping all the stored procs and views (you will of course create a backup before starting any of it) ?

So why should it be a lot of work for someone who has the authority to rename or relocate a databse ?
Post #1531241
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse