In the database world, the word 'Schema' is associated with the description of the tables and the relations in the database. However when Microsoft launched SQL Server 2005, they introduced a securable called Schema which created another meaning for the word 'Schema' in the database world. A Schema is a container of database objects (e.g. tables, views, stored procedures etc) and it is used to define the namespace for the objects. It is used to make management of objects easier for a database users. Schemas and users are separate entities and a schema can be owned by any user or database role.
In previous versions of SQL Server prior to SQL Server 2005, the four-part name for database objects included the owner name where as SQL Server 2005 and 2008 has schema name replacing owner name in the naming scheme.
Scenario 1 - SQL Server 2000
A user named Fred created objects in the 'Product' database. Each object has the following four-part name :
[Server Name]. Product.Fred.[Object Name]
Scenario 2 - SQL Server 2005
Objects need to be part of a schema and since Fred does not want to use the 'dbo' schema he created a schema called 'work'. Each object has the following four-part name :
[Server Name]. Product.work.[Object Name]
This is beneficial since changing user names or removing users does not require manually transferring each object the user owned to another user. In Scenario 1,if the Fred user is deleted then no one will be able to access any of the objects that he created unless each object is transferred to another user. However in scenario 2, the user Fred can be deleted once the ownership of the schema is transferred to a next user.
A default schema is assigned to each database user for each database that the user can access. This allows users to access objects in the default schema without having to specify the schema name. For example, Fred who has a default schema of 'work' can select data from the table work.Company by specifying Customer in his query instead of work.Company. If you need to access another schema, a two-part identifier (schema_name.object_name) will be required.
Syntax to create a schema:
CREATE SCHEMA [schema_name] AUTHORIZATION [owner_name]
e.g. CREATE SCHEMA work AUTHORIZATION Fred
The ALTER SCHEMA is used to transfer a securable between schemas. Syntax:
ALTER SCHEMA [schema_name] TRANSFER [securable_name]
e.g. ALTER SCHEMA work TRANSFER dbo.customers
Note: the securable_name can be one-part or two-part named
Syntax to remove a schema:
DROP SCHEMA [schema_name]
Azure B2B Big Data Business Intelligence Conferences CTP Database Design Developer Fun facts GUI How-To Hyper-V Interview MCSA MCSE Mobile Reporting NoSQL O365 PASS PD Presentation Programming Publication RLS SAGO SBCS Security SoftwareONE SolidQ SolidQ Journal SP1 SQL Azure SQL Database SQL Database V12 SQL in the City SQL MVP SQL Server 2008 R2 SQL Server 2012 SQL Server 2014 SQL Server 2016 SQLSaturday SSMS SSRS St Lucia T-SQL T-SQL Tuesday TechEd 2013 Training TTSAC TTSSUG Upgrade VDI VHD Virtual Machines Win10 Windows 2012 Windows 2012 R2 XML
- ► 2013 (24)
- ► 2012 (42)
- ► 2011 (33)
- ► 2010 (21)