Concatenation and Aliases
Try reloading this page, or reviewing your browser settings
This video segment explains the naming structure used to identify tables in a SQL Server environment, including all four parts of a canonical name—server, database, schema, and the name of the table itself.
About this video
- John Deardurff
- First online
- 23 November 2018
- Online ISBN
- Copyright information
- © John Deardurff 2019
In this segment, we’re going to talk about the four-part name of a table. To start off with, the four-part name of a table is actually the four-part name of all of our objects within SQL server, that is, broken down into server.database.schema.table. But this could be for a view, a store procedure, basically most of our objects that are stored within SQL server. This is also how our security levels are assigned or permissions assigned at these levels as well. So not only is it a naming scheme, this is also our security levels. So it’s an important thing to know.
So how it’s broken down, server.database.schema.table, our server is where our database resides. A lot of times we don’t need to actually include the server name as part of our object or our table when we’re selecting from it. If we’re connected to the current table, that’s good enough, and we can typically leave that off. But we do need to include the database portion of the name. There’s actually three different places where you can include this. We’ll see that in the demonstration coming up very shortly.
The third part of our name is the schema, and this is a part that might be a little bit confusing. The schema was actually introduced in SQL server 2005, and it’s basically just to organize my tables, views and procedures so that people that work with similar objects, similar tables, similar views, they can access those as well as an administrator can simplify assigning permissions to those objects by just placing permissions at the schema level.
And then finally is our object, in this case a table. Table, view, procedure. So that’s the four part name of our table or a table.
Now let’s go look at our demonstration. Now as you can see, I have already written some code in our demonstration to select data from our SQL fundamentals server dot AdventureWorks database dot Person schema dot Person table. Now in our object explorer, we can also see how our objects are organized at the server, the database and the schema.table level. So I can execute this code and you can see, I can still retrieve all 19,972 records for my Person.Person table. Now if I’m currently connected to the SQL server, in this case SLQFundamentals is the name of my server, I really don’t need to include that as part of the name to retrieve data. Normally, the only time I need to include the server name is if I’m writing a query against another server in my network.
Now for databases, I can actually specify which database I want to use in one of three ways. Either by using the available databases dropdown list, you can see I’m currently selecting AdventureWorks. I can also in my query window write the statement, Use AdventureWorks to specify the database, or I can include it as part of my SELECT statement. Now again, if I’m using either the available databases or the Use statement to specify the database, I do not need to use that database name within my SELECT statement either.
So that kind of leads me down to the Schema.table. Technically, I do not need to include the schema portion of the table name if the user account that I’m using within the database has been assigned that schema as my default schema. But it is good practice to use the two-part name, because not everybody has the same default schema assigned to them. So if someone else wants to run your code, it might cause them problems. So it’s a good idea to use the schema and then the table name. You always have to include the table or view or whatever object you would want to select.
So in this video segment, we’ve discussed the four-part name of a table: server.database. schema.table.