Create a database schema - SQL Server Tutorial

- [Narrator] SQL Server uses the concept of schemas to help organize and group database objects. This provides a number of benefits to the system. But to take full advantage, a plan must be developed that translates these object groups to the goals of the database's design. Let's start with what a schema is. Schemas are simply containers or buckets that simplify the management of a number of objects by allowing the database administrators and SQL Server itself to apply permissions and other operations to the entire group without having to single out each and every member of the group individually. Every database begins with a single DBO, or database owner schema, and unless otherwise specified, all objects will get created in this schema. To create additional schemas, you simply use T-SQL to create the bucket and then add or move objects into it to create a collection. Schemas are created with a CREATE SCHEMA command and can be named however you'd like. Typically, schema names align with the departments in an organization such as warehouse, sales, or human resources. Or they can be named to correlate with the various clients that connect to the database such as developer or remote user. To create a new object within a specific schema, simply provide the schema name when naming the object. So instead of a CREATE TABLE Employees, you'd type CREATE TABLE HumanResources.Employees. By default, the first command will place the Employees table in the DBO schema, and the second one that we just saw here will put it into the HumanResources schema. To move an existing object to a different schema, you'll alter the schema to include the object, as in ALTER SCHEMA HumanResources TRANSFER dbo.Employees. This'll move the Employees table out of the dbo schema and bring it into HumanResources. So what are the benefits of using schemas? First, schemas help to visually group related tables together. This is initially the most visible when looking at the Object Explorer in SQL Server Management Studio. Because it sorts objects alphabetically, all of the objects within the same schema will appear together. Beyond just the visual appearance though, this logical grouping of objects makes it easier to create multiple systems within a single database. Where you might otherwise have separate databases for different components of a business's operations, you can use schemas to bring all of those into a single physical database. This also makes management of the entire system more efficient since effort doesn't need to be duplicated to manage backup schedules, users in all of the other facets that maintain a working server. The other benefit of implementing a clear schema design is in the application of security. This is probably the most important benefit. Since many database objects can be assigned to a schema, granting user access permissions to the schema rather than the individual object means far fewer connections need to be made. A simple action such as granting select permissions to the sales schema to the salesperson user role covers a wide range of user object interactions. The sales schema might encompass dozens of tables, and you might have hundreds of users within the salesperson role. With one command, you can allow all of the salespeople access to the appropriate records across the database but deny them the ability to alter the design of the tables. Each object can only belong to a single schema at a time. Schemas can be owned by an individual user, or as we just saw, several users through user roles or groups. Each individual user of the database can own several schemas and have different permissions assigned to each schema which makes creating hierarchies of permissions easier to manage. This is where designing the application of schemas needs to follow your business requirements and logic. You'll need to identify where responsibilities are segmented within the organization and where users have overlapping areas of responsibility. As a simple example, members of the sales associate user role might only be able to select information from the sales schema in order to assist customers with their purchasing activities. But sales managers have access to the sales and inventory schemas in order to be able to communicate with the warehouse to ensure stock levels remain adequate as dictated by their elevated areas of responsibility within the organization. Database users can be assigned a default schema. Objects that they create will automatically get created within the default schema unless otherwise specified. Setting a default schema also has benefits with the users accessing objects within the database. When using schemas, it's considered a best practice to always use the schema name when referring to the database object. You do this by separating the schema from the object name with a period, as in Sales.Transactions which indicates the Sales schema, and within that, the table Transactions. You can also refer to objects outside of the current database by using the fully qualified name which concludes the server and the database. And it might look something like BackupServer.SalesDB.Purchasing.Invoices. The benefit to using the schema name at the very least is that without it, SQL Server has to figure out where to exactly look. This can cause the server to run several checks as it interprets your command. It'll check the user's default schema for the object. Then it'll check the DBO schema. And if it doesn't find the object under either schema, it'll return an error. You can prevent the extra processing time running through these multiple checks by assigning a default schema and using the two-part object names consistently throughout the database.