Sql server® 2012 t-sql fundamentals

 

CHAPTER 1

 

Background to T-SQL Querying and Programming

 

 19

You can control permissions at the schema level. For example, you can grant a user SELECT permis-

sions on a schema, allowing the user to query data from all objects in that schema. So security is one 

of the considerations for determining how to arrange objects in schemas.

The schema is also a namespace—it is used as a prefix to the object name. For example, suppose 

you have a table named Orders in a schema named Sales. The schema-qualified object name (also 

known as the two-part object name) is Sales.Orders. If you omit the schema name when referring to 

an object, SQL Server will apply a process to resolve the schema name, such as checking whether 

the object exists in the user’s default schema, and if it doesn’t, checking whether it exists in the dbo 

schema. Microsoft recommends that when you refer to objects in your code you always use the two-

part object names. There are some relatively insignificant extra costs involved in resolving the object 

name when you don’t specify it explicitly. But as insignificant as this extra cost might be, why pay it? 

Also, if multiple objects with the same name exist in different schemas, you might end up getting a 

different object than the one you wanted.