Implement table schemas - SQL Server Tutorial

- [Narrator] Every object in a SQL Server database gets added to something called a schema. We can see that by creating a new database and then adding in a simple table. I'm going to run these four lines here. First I'm going to create a new database called SchemaDemo, and then we're going to turn that into the active database with the USE SchemaDemo line. Go and highlight all of those and press the execute button. That'll add in a new database that we can find by going into Object Explorer and we'll see it here called SchemaDemo. If you're not seeing it, right click on the databases folder and choose refresh. Now I'm going to create a new table called Authors and this'll be the same table that we created in the last movie. There'll be a table called Authors with the AuthorID, first name, and last name fields. Go ahead and highlight these lines and press execute. Then I'll go into the databases folder inside of SchemaDemo, inside of Tables, and we'll see our new table here called dbo.Authors. If no schema is specified then the objects will automatically go into the default schema for the user. In my case, I'm logged in as the administrator, so this table automatically got placed into the dbo, or Database Owner Schema. We can make this whatever we want though. Schemas are used to organize objects into logical groupings that are useful for a variety of reasons. Schemas generally follow functional classifications, such as all the tables that belong to the sales department will be part of the sales schema, and tables related to employees and personnel records might be part of a human resources schema. Once objects are grouped together into a schema, Simple Server makes it easy to assign permissions to every object within that schema. So that our sales managers have read and write access to the tables in the sales schema, and our HR generalist can be restricted to only working with the tables in the Human Resources schema. Further, database objects inherit permissions set at the schema level. So even if the object is created later, it'll automatically pick up the permissions assigned to the schema and only the appropriate users will have access to it. Let's see how to create a custom schema for our database. Let's go ahead and scroll down a little bit. And I'm going to first drop the table Authors. Go ahead highlight these lines and execute them. Then we'll scroll down a little further and we'll find this line here that starts with CREATE SCHEMA We're going to create a new schema called publications. To see all of the existing schemas in the database, we'll go into the security folder and then expand the schemas folder. You can see all the schemas that are already created in this database, including the dbo schema, guest, and systems schemas. For the authorization portion of the line, I'm going to assign permissions to a specific user. In my case I'm going to assign them to myself, the database owner or dbo. If you need to review a listing of all the different users for the database, we can scroll down a little bit further and inside of the security folder we'll find our users and we'll see the different users that are in this database. Let's go ahead and create a schema, I'll highlight these lines and execute them. And then we'll go back to our schemas folder, I'll right click on it and choose refresh and we'll see the new schema Publications has been added to the list. Now I can recreate the Authors table within the Publications schema. We'll run the exact same line here except this time we're going to specify the schema here before the table name, so it'll say publications.authors, instead of just authors. I'll execute these lines and then we'll scroll back up. We'll go into our SchemaDemo database, to the Tables, and I'll right click on it and choose refresh and we should now see our new authors table as part of the publications schema. Now as a best practice it's always beneficial to use the schema name when referencing database objects to aid in name resolution. You do this by specifying the schema and the table name in any select queries. For instance this one here, where I'm going to select the authorID, firstname and lastname fields from the publications.authors table. Notice that I'm using the publications schema along with the name of the table. I'll select those and press execute. Now I'm not seeing any data because I haven't added any data to the table, but we can see the three column names here. Always specifying the schema will speed up SQL Server queries, since if you don't specify the schema, SQL Server will check both the user default schema for the object, then the dbo schema, and if it doesn't find it under either of those, it'll return an error stating that the object cannot be found. You can prevent all this extra checking by simply providing the two-part schema plus object name every time you make a call to the database. You can also transfer tables between schemas. To do this, we need to alter the schema that we want to bring the table into. Let's go ahead and scroll down and take a look at the next few lines in our script. I'm going to create a new schema called people with the authorization of dbo again. I'll highlight those lines and execute it. Then, we need to alter the schema People to include the Authors table that's currently in the Publications schema. I'll select these lines and execute them. One common mistake here is that we're altering the schema name to include the table. Altering the table only allows you to modify the structure of the table, and doesn't allow you to modify the schema that the table belongs to. Now that I've changed the schema though I can go back to my tables, right click and refresh, and we'll see that the new table authors is now in the People schema instead of Publications. Schema names can be whatever you want them to be, and whatever makes sense for the organization of your business use cases. If we take a quick peak into the Wide World Importers database, you'll see that they make use of a large number of schemas to organize their content. To do that, we'll switch into the WideWorldImporters database by using the USE statement. That'll activate this database as the active database, and we can scroll down a little bit, and we're going to select information out of two system tables, sys.schemas and sys.tables. Let's go ahead and highlight these lines and press execute. And in the results window, we can see a listing of all the different schemas and tables that are present in the WideWorldImporters database. We can also make use of a where clause to filter out the results to see just the tables in a specific schema. I'll click at the beginning of this where line and I'll press backspace to erase the two dashes to uncomment this line. Then we'll just come to the end where it says insert_schema_name and we'll type in the name of the schema that we're interested in viewing the tables for. In this case I'll type in sales. Since we might be using unicode characters for the schema names, I want to prepen the text search with a capital letter N so that I get the proper text matches to unicode characters. Then we'll highlight these lines again and press execute. Now I'm seeing just the tables that all belong to the sales schema. When we're done reviewing the results I'll go ahead and scroll down just a little bit more to clean up my server instance. I'll highlight the USE tempdb line all the way down to this last GO to drop the database schema demo. Press execute and the database is removed from the server. So leveraging schemas is an important part of creating a well-organized database. As we'll see later, assigning user permissions to tables within the schema, is a much better security strategy than assigning permissions for each and every table that gets created.