Post reply

Referencing Schema for Tables

  • January 18, 2008 at 11:36 am

    #378090

    Hi Guys,

    I just read the post on using schema when calling stored procedures. I'll be doing this in the future.

    I work quite a bit with a particular Point of Sale package that has its own replication system. The tables used for replication purposes all have a different schema than the regular transaction tables. The regular transactions tables are plain old dbo.

    Would there be any difference in performance by explicitly prefacing the transaction tables with dbo in stored procedures and view?

    Todd Fifield

  • January 18, 2008 at 11:48 am

    #769819

    Yes. There's another post active at the moment on exactly this issue.

    If you don't prefix the table you risk double lookups for SQL to find the table and you also risk poor cached plan reuse.

    Edit: Found it. http://www.sqlservercentral.com/Forums/Topic443249-360-1.aspx

    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

  • January 18, 2008 at 12:37 pm

    #769850

    Thanks Gail. I'll do this in the future.

    Todd Fifield

  • January 21, 2008 at 3:14 am

    #770074

    There is another very insidious and often overlooked issue related to this. You can actually reference the WRONG object if you don't use schema qualifying. If my default schema is myschema and it has a table foo and there is a dbo.foo, if I am logged in as me and select * from foo, it will reference the one in my default schema, which may not be what the code intended.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 4 posts - 1 through 3 (of 3 total)

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