{"id":1960,"date":"2019-05-04T08:54:35","date_gmt":"2019-05-04T01:54:35","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1960"},"modified":"2020-04-11T20:12:08","modified_gmt":"2020-04-11T13:12:08","slug":"sql-server-create-schema","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-create-schema\/","title":{"rendered":"SQL Server CREATE SCHEMA"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server CREATE SCHEMA<\/code> to create a new schema in the current database.<\/p>\n\n\n\n

What is a schema in SQL Server #<\/a><\/h2>\n\n\n\n

A schema is a collection of database objects including tables, views<\/a>, triggers<\/a>, stored procedures<\/a>, indexes<\/a>, etc. A schema is associated with a username which is known as the schema owner, who is the owner of the logically related database objects.<\/p>\n\n\n\n

A schema always belongs to one database. On the other hand, a database may have one or multiple schemas. For example, in our BikeStores<\/code> sample database<\/a>, we have two schemas: sales<\/code> and production<\/code>. An object within a schema is qualified using the schema_name.object_name<\/code> format like sales.orders<\/code>. Two tables in two schemas can share the same name so you may have hr.employees<\/code> and sales.employees<\/code>.<\/p>\n\n\n\n

Built-in schemas in SQL Server #<\/a><\/h3>\n\n\n\n

SQL Server provides us with some pre-defined schemas which have the same names as the built-in database users and roles, for example: dbo<\/code>, guest<\/code>, sys<\/code>, and INFORMATION_SCHEMA<\/code>.<\/p>\n\n\n\n

Note that SQL Server reserves the sys<\/code> and INFORMATION_SCHEMA<\/code> schemas for system objects, therefore, you cannot create<\/a> or drop<\/a> any objects in these schemas.<\/p>\n\n\n\n

The default schema for a newly created database is dbo<\/code>, which is owned by the dbo<\/code> user account. By default, when you create a new user with the CREATE USER<\/code> command, the user will take dbo<\/code> as its default schema.<\/p>\n\n\n\n

SQL Server CREATE SCHEMA statement overview #<\/a><\/h2>\n\n\n\n

The CREATE SCHEMA<\/code> statement allows you to create a new schema in the current database.<\/p>\n\n\n\n

The following illustrates the simplified version of the CREATE SCHEMA<\/code> statement:<\/p>\n\n\n

CREATE<\/span> SCHEMA<\/span> schema_name\n    [AUTHORIZATION owner_name]\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax,<\/p>\n\n\n\n