No difference in performance. However, you are using schemas right now (even if you don't know it).
The use of references to schema objects such as tables, stored procedures, UDFs, etc. that are not schema-qualified does have a performance impact. References should always be qualified by schema. Such unqualified references have to be resolved, and that happens like this:
- First, look for an object of the same name and type under the default schema of the user under whose credentials the session was established (e.g.
jsmith). If found, that instance is used.
- Otherwise, look for an object of the same name and type under the schema
dbo.
This has several effects:
- Most of the time, two lookups are required to resolve the reference rather than the single lookup required if the reference is schema-qualified.
- The execution plan obtained when the query/stored procedure/user-defined function is bound can't cached and reused.
The final effect that you'll only find—painfully—when something breaks is that different users may get different results from a given query or stored procedure. Something like select * from foo join bar may work fine for me as the db owner; it may be broken for user jsmith who, inadvertantly or not, created a table named foo under his own schema (jsmith.foo) in the same database.
For this reason, too, create and drop statements should schema-qualify the name of the object being created or dropped.
