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

odd situation Expand / Collapse
Author
Message
Posted Wednesday, November 20, 2013 2:25 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 1:50 PM
Points: 145, Visits: 357
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
Post #1516213
Posted Wednesday, November 20, 2013 2:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 12,890, Visits: 31,851
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1516216
Posted Wednesday, November 20, 2013 4:03 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 10:10 AM
Points: 36,795, Visits: 31,254
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."

(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 #1516248
Posted Tuesday, December 3, 2013 10:59 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 15,561, Visits: 27,938
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1519329
Posted Monday, December 9, 2013 8:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:34 AM
Points: 386, Visits: 624
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.





Post #1521146
Posted Monday, December 9, 2013 4:01 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 10:10 AM
Points: 36,795, Visits: 31,254
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."

(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 #1521338
Posted Wednesday, December 11, 2013 9:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:34 AM
Points: 386, Visits: 624
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 :)
Post #1521961
Posted Friday, December 13, 2013 5:43 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 10:10 AM
Points: 36,795, Visits: 31,254
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."

(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 #1522904
Posted Saturday, December 14, 2013 6:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:09 AM
Points: 42,493, Visits: 35,563
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 2008, MVP
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

Post #1522943
Posted Saturday, December 14, 2013 3:11 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:34 AM
Points: 386, Visits: 624
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.
Post #1522977
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse