odd situation

Question

odd situation

Snargables

SSCrazy Eights

Points: 8855
More actions
November 20, 2013 at 2:25 pm

#282829

How can u have the same tablename in sysobjects w/ different id's. It's making one of my processes fail. The difference in the records is the uid. I didnt think this could happen. doesn't happen in sys.objects though
--this returns two tables and the count = 2 for each
select name,count(*)
from dbo.sysobjects
where xtype='u'
group by name
having count(*)>1

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Snargables (11/20/2013)
How can u have the same tablename in sysobjects w/ different id's. It's making one of my processes fail. The difference in the records is the uid. I didnt think this could happen. doesn't happen in sys.objects though
--this returns two tables and the count = 2 for each
select name,count(*)
from dbo.sysobjects
where xtype='u'
group by name
having count(*)>1

schemaname + tablename are unique, so you can have multiple tables with the same name, but under different schemas.

when you are running multiple schemas, your scripts must be a bit smarter and take that into consideration

select MIN(schema_name(object_id)),

MAX(schema_name(object_id)),

name

from sys.tables

group by name

having count(*)>1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 2

This is a very good example of why you should (and I don't use the word often) ALWAYS use the two part naming convention. 😉

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

Jeff Moden (11/20/2013)
This is a very good example of why you should (and I don't use the word often) ALWAYS use the two part naming convention. 😉

Absolutely.

On both points, using qualified names and the term ALWAYS.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

aaron.reese SSChampion Points: 13498 More actions · Answer 4

Or synonyms if you might need to switch schemas between development and production, or for different clients (e.g. data segregation is done by schema rather than by database - I've seen it done!) In theory you can get the same sprocs and views to run against different copies of the tables based on the login credentials and the schemes owned by that user.

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 5

aaron.reese (12/9/2013)
Or synonyms if you might need to switch schemas between development and production, or for different clients (e.g. data segregation is done by schema rather than by database - I've seen it done!) In theory you can get the same sprocs and views to run against different copies of the tables based on the login credentials and the schemes owned by that user.

My apologies. You're absolutely correct about synonyms. I neglected to state that they road to 2 part naming is largely dependentent on synonyms.

I've not tried to make spocs and views to run against different copies of tables without needing to use either a ton of duplicated code (well, not me) or dynamic SQL. Can you expand on the "theory" you're talking about? It would be a good thing for me to try.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

aaron.reese SSChampion Points: 13498 More actions · Answer 6

Oops!

I should know better than to make throwaway statements like that when Jeff or Gill are lurking 😀

I will have to find some time to set it up on my development system but I am pretty sure you can have

FOO.Customers

and

BAR.Customers

with the same table structure.

FOO and BAR are schemas that are owned by different user groups and they only have access to their own schema so when a Bob logs on and runs

SELECT * from Customers, this will run against FOO.Customers

and likewise when a Jim logs on it will run against BAR.Customers

so unless your stored procedures include the EXECUTE AS command they will run under the correct context for the login

Not sure what it will do to the cached execution plans though if the statistics for the different schemas are skewed 🙂

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 7

aaron.reese (12/11/2013)
Oops!
I should know better than to make throwaway statements like that when Jeff or Gill are lurking 😀
I will have to find some time to set it up on my development system but I am pretty sure you can have
FOO.Customers
and
BAR.Customers
with the same table structure.
FOO and BAR are schemas that are owned by different user groups and they only have access to their own schema so when a Bob logs on and runs
SELECT * from Customers, this will run against FOO.Customers
and likewise when a Jim logs on it will run against BAR.Customers
so unless your stored procedures include the EXECUTE AS command they will run under the correct context for the login
Not sure what it will do to the cached execution plans though if the statistics for the different schemas are skewed 🙂

Actually, that's a pretty darned good example of when single part naming works very well. I've not had to work in an environment where such naming was required but I can certainly see it happening in a good number of environments.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

aaron.reese (12/11/2013)
Not sure what it will do to the cached execution plans though if the statistics for the different schemas are skewed 🙂

Different execution plans for different users, because the optimiser can tell that the plan is not safe for reuse between users. So if you have 200 database users, you get 200 plans in cache.

This is why it is recommended to use 2-part naming in procedures, to clarify which tables are used so that the optimiser doesn't include the user as part of the cache lookup key.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aaron.reese SSChampion Points: 13498 More actions · Answer 9

Not wanting to hijack the thread because we are getting OT now.

By utilising schema separation and controlling access through users rights you can force segregation of data which for some systems (e.g. shared patient admin systems). Personally I wouldn't do it this way, I'd use separate databases or even separate instances, scripting for updates to non table objects is normally pretty rapid.