• BinaryDigit (3/12/2013)


    Hi

    A random question, if you write queries and put the schema before objects will it affect performance in any way and if so what type of metrics are we looking at? even if they are tiny 😀

    Regards

    Gordon Beeming

    Cumulatively, yes, there can be a performance gain by schema-qualifying all your objects. It avoids the overhead of the engine having to find the object you meant to refernece either in the default schema (depends on context as shown above) or in the dbo schema. I like to see all objects schema-qualified. In my opinion it makes the code more readable and avoids potential for latent bugs becoming actual bugs.

    Consider the code example above. If someone were to drop the table testB.Cities for some reason the stored procedure would suddenly start returning Chicago...probably not an acceptable side-effect of dropping a table. Similarly, if when the proc had gone live at a time when there was no such table as testB.Cities and later someone added that table to the database the proc would suddenly stop returning Chicago and would start returning Atlanta. These are not things I would like to see happening when a table is added or removed from a database.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato