SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


odd situation


odd situation

Author
Message
Snargables
Snargables
Right there with Babe
Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)

Group: General Forum Members
Points: 741 Visits: 805
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28213 Visits: 39955
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
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86111 Visits: 41096
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39892 Visits: 32650
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
aaron.reese
aaron.reese
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1041 Visits: 902
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
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86111 Visits: 41096
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
aaron.reese
aaron.reese
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1041 Visits: 902
Oops!

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

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 Smile
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86111 Visits: 41096
aaron.reese (12/11/2013)
Oops!

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

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 Smile


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87343 Visits: 45272
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 Smile


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
aaron.reese
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1041 Visits: 902
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search