Tuesday, September 14, 2010

SQL Schema

A schema is a distinct namespace to facilitate the separation, management, and ownership of database objects

The use of schemas can help to combine entities from separate applications or functional areas, or logical groups, into a single physical database.

One example of relevance is the protection of database objects in application development environments where developers and testers share the same set of database objects

Table 'Crawler_Status' in the database CMS can be used by two different users as follows
Table1, CMS.Testing.Crawler_Status is used by testing team
Table2 , CMS.Production.Crawler_Status is used by production team


Note : Usually we use two different databases

Performance

Always refer to the database objects(Table names , Views) using a fully qualified name. At the very least, use the schema name followed by the object name, separated by a period (.)

Example
SELECT COL1 , COL2 FROM DBO.TABLE_NAME

It will take less processing time for the Database Engine to resolve object names
Otherwise database engine checks in master database,if it’s not there in master database it checks in the current session database depends on schema

Security

Use schemas to protect the base database object from being altered or removed from the database schema by users without sysadmin permissions.
User permissions should be managed at the schema level

1 comment:

  1. Win Exciting and Cool Prizes Everyday @ www.2vin.com, Everyone can win by answering simple questions. Earn points for referring your friends and exchange your points for cool gifts.

    ReplyDelete