The 4 part naming convention
Ok, so what is this 4 part naming convention? It is the way the SQL Server (and other DBs out there) identify the exact location of the objects in the environment. The 4 part name follows the convention of <server>.<database>.<schema>.<object>. In most cases, you are only going to use the schema and object names. If you need to grab something from another DB on your instance you can get to it, if you have permissions, by adding the database name too.
Now, in this post, I don’t want to go into all the nitty-gritty details of the naming convention, but I do want to touch one something I have seen more and more.
The Double Dot Syntax ( .. )
I am finding more and more cases where SQL code is being created using the double dot or period for the 2 part naming convention.
For example, instead of using dbo.table1 I am seeing ..table1.
I don’t know who suggested this in the first place, but it is not a good idea. Sure it works and does what you expect, but there is a HUGE risk with doing this. When you use the .. syntax, you are telling the code to use whatever the default schema is for the user that is running the query. By default that is the dbo schema, but there is no guarantee that all systems are going to be that way.
Why Make This A Big Deal?
Simple. I’m a DBA by nature and prefer to have things a little more organized and properly accessed and secured. I am a proponent of using schemas to define objects and access. What does this mean? It means you default schema may not be dbo in my world or any other system’s world. Why does that matter? Simply put, I can have objects of the same name unique from each other in the same database just by changing the schema name. SQL Server doesn’t work with just the object name. It will also put the default schema in front to create a 2 part name, even if you don’t tell it.
Prove it!
Ok, here is a snippet of code you can use to have fun with this. You have to run this as DBA, r as an elevated login with the permissions to create Logins, objects and impersonate others, but you can also tweak this to play with the concept.
-- make a new login to use to test with and add to tempdb as a datareader
-- we will not set the default schema so it will be dbo (like most logins created)
USE [master];
GO
CREATE LOGIN [tester] WITH PASSWORD=N'test123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
USE [tempdb];
GO
CREATE USER [tester] FOR LOGIN [tester];
GO
USE [tempdb];
GO
ALTER ROLE [db_datareader] ADD MEMBER [tester];
GO
-- switch to tempdb
USE tempdb;
GO
-- make a new schema in tempdb for this demo
CREATE SCHEMA [doh] AUTHORIZATION [dbo];
GO
-- and make a table in te dbo schema of tables and populate it with a few records
CREATE TABLE dbo.table1
(
IDValue INT IDENTITY(1, 1)
,NameValue VARCHAR(25)
);
GO
INSERT dbo.table1
( NameValue )
VALUES ( 'Chris' ),
( 'Gigi' ),
( 'Digby' );
-- and the same done in the other schema we created, populated with different values
-- Yes! You can have same named objects in different schemas.
-- Starting to see how this can get dangerous?
CREATE TABLE doh.table1
(
IDValue INT IDENTITY(1, 1)
,NameValue VARCHAR(25)
);
GO
INSERT doh.table1
( NameValue )
VALUES ( 'ChrisDOH' ),
( 'GigiDOH' ),
( 'DigbyDOH' );
GO
-- Now for some fun. Since we are doing this as admins (hopefully) let's impersonate the login we created earlier and run a simple query
EXECUTE AS LOGIN = 'tester';
GO
SELECT *
FROM ..table1;
-- and back to us
REVERT;
GO
-- now let's change the default schema of our test account to be the other schema we created.
ALTER USER [tester] WITH DEFAULT_SCHEMA=[doh];
GO
-- let's impersonate again and see what happens
EXECUTE AS LOGIN = 'tester';
GO
SELECT *
FROM ..table1;
REVERT;
GO
-- DOH!
-- Let's try by using the proper schema prefix
EXECUTE AS LOGIN = 'tester';
GO
SELECT *
FROM dbo.table1;
SELECT *
FROM doh.table1;
REVERT;
GO
-- cleanup to put things back how they were.
DROP TABLE dbo.table1;
DROP TABLE doh.table1;
DROP SCHEMA doh;
DROP USER tester;
USE master;
GO
DROP LOGIN tester;
GO
Start by using the first part of the code to create the objects. Then run the EXECUTE AS statements individually to see just what happens.
Want to have some more fun? Remove the .. and just use the object name and see what happens.
Yeah.
That code where you only have the .., or nothing but the object name, changes the data referenced.
Not only that, but it is controlled at the user level, not in the code itself!
The only way you can control which object is referenced is to correctly specify the full schema name along with the object name.
Use the naming conventions correctly. Those of us that bring these things up don’t do it just to hear our voices; we do it because there is a risk with coding this way. Sure, it works fine now, but just wait. You’ll wind up using schemas eventually, or a 3rd party app that uses them will come along. Then you will have to get with the program and make sure your code is cleaned up and working not how you expect it to, but how you know it should.