Using .. (two dots) when specifying the table being queried FROM

Question

What is the purpose of the two dots in the following SQL statement?

select * from msdb..backupset

I understand that one would mean select from the table called 'backupset' with the 'msdb' database. But two dots is doing something different, which I do not understand.

Answer 1

The login would have default "schema" on the database that is connected to (in your case msdb). Specifying the object (in your case "backupset") with 3-part name like [DB name]..[Object Name] is omitting of the schema name (in your case probably "dbo") in the 3-part name.

There could be MyDatabase.SchemaA, and MyDatabase.SchemaB on a database MyDatabase database, and each schema could have 2 separate tables with same object name -- like MyDatabase.SchemaA.MyTable, and MyDatabase.SchemaB.MyTable.

If your login to MyDatabase defaults to SchemaA, and and run SELECT * FROM MyDatabase..MyTable, then you would be selecting from MyDatabase.SchemaA.MyTable.

If you wanted to select from the second table, you'd have to SELECT * FROM MyDatabase.SchemaB.MyTable or SELECT * FROM SchemaB.MyTable

Typically it is said that specifying the schema name explicitly is good practice (instead of backupset or MyTable, write dbo.backupset or SchemaA.MyTable

Answer 2

Using the two dots in this three-part naming convention (i.e Database.schema.table) indicates that the schema is the default schema(dbo)