doubledot

Think Using .. In Code Is A Time Saver? Think Again!

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.

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 use 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.

Leave a Reply

Your email address will not be published. Required fields are marked *