In a previous post we discussed the four part name of a SQL Server object that included the SCHEMA as part of its name in 2005 and later. In this post, we will take a closer look at working with Schemas.

There are many advantages to using schemas, but they are primarily used to allow better and more flexible organization of objects, as well as, allowing for an additional security boundary.

CREATE SCHEMA Sales AUTHORIZATION Bjorn
	GRANT SELECT ON SCHEMA:: Sales to Jane
	DENY SELECT ON SCHEMA:: Sales to Sara

In this example, we are creating a new schema named Sales and allowing the Bjorn identity to be the owner of the Sales schema.  Next, we GRANT the SELECT permission to Jane and then DENY the SELECT permission from Sara. This will Grant or Deny the SELECT permission to ALL the tables and views that are being organized under the Sales schema.

Another way of creating a schema in SQL is to streamline the creation while also creating a table that will be stored underneath that schema. In this following example, we create the Production schema while also creating a simple Products table.

CREATE SCHEMA Production AUTHORIZATION Bjorn
CREATE TABLE Products
(ProductID int,
 ProductName varchar(15))

Another issue we may run into is if we want to move a table from one schema to another. Normally, this is not necessary if we started with good database design. But in case a table needs to be moved, it needs to be done as part of an ALTER SCHEMA statement. In this example, we will alter the HR schema and then TRANSFER the TestTable from the dbo schema into the HR schema.

 ALTER SCHEMA HR TRANSFER dbo.TestTable

Finally, if you no longer want to use a schema you can use the DROP SCHEMA statement. However, you need to make sure you transfer all the objects to another schema first.

DROP SCHEMA Production;