odd situation

  • 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

  • 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!

  • 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)

  • 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

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

  • 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)

  • 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 🙂

  • 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)

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

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

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