Why You Shouldn’t Hardcode the Current Database Name in Your Views, Functions, and Stored Procedures

“There are only two hard things in Computer Science: cache invalidation and naming things”
Phil Karlton

I’m terrible at naming things. I recently wrote some quick code to reproduce a design problem and demonstrate several options for solutions, and later realized that I’d named my objects dbo.Foo, dbo.FooFoo, and dbo.Wat.

But I feel strongly about a few important principles about referring to objects that are already named in a relational database, specifically SQL Server and Azure SQL Database.

Most of the time, you should use a two part-name for objects in the current database. It’s important to know your database context. When referencing an object in the current database, you should not specify the database name in the reference.

For example, if you’re creating a view, function, or stored procedure…

• ‘SELECT Col1 from dbo.Foo’ is good
• ‘SELECT Col1 from CurrentDatabaseName.dbo.Foo’ is not good

This might seem like quibbling, but there’s an important difference here: hardcoding the database name in the second command means that you are restricting the code in this object to only work when the database has that exact name. You’re putting a dependency on that database name.

When might a database need to have multiple names?

It’s quite common to need a database to be able to operate under a different name. Here are a few use cases:

In development, test, and pre-production (staging) environments, it’s a common practice to use different database names than production. This not only allows for multiple iterations of a database to be on the same instance of SQL Server, but the database name can make it more obvious which environment you’re connected to. That makes it less likely to have those moments of “oops, I didn’t mean to run that script against production!”

When branching database code in source control, you may wish to have a different database for the branch you are working on, with specific sample data for that branch. Typically, it’s convenient to keep these databases on the same instance of SQL Server, so they need to have different names.

When building database code to validate that your database objects compile from source, it’s better to not have to hard-code the database name in the build. If you do need to hard-code the name, you need to make sure that only one build server at a time can run a build for that database on that instance, otherwise, multiple builds will code on that database.

What about ‘deferred name resolution’?

If you’ve been working with SQL Server for a while, you might wonder about my comment about building database code, because of a feature called ‘deferred name resolution.’

Deferred name resolution has been around in SQL Server for a long time, and it’s available in all editions, from LocalDB to Enterprise. There’s no setting to enable this; it’s on all the time. This feature allows you to reference objects which don’t exist when you create stored procedures and some functions. SQL Server gives you a line of credit that those objects will exist at the time of execution.

This allows a build operation (which validates that your database code will create properly from source control) to succeed, even if you have hardcoded references to a specific database name which doesn’t exist on the build server — at least when it comes to stored procedures and some functions.

But there are some important gotchas:

• Deferred name resolution doesn’t work in views and in inline-able functions
• It’s better for our code to not be dependent upon the database having a specific name, so deferred name resolution isn’t necessarily a great feature for builds, anyway

Rule of thumb: don’t use the database name unless you absolutely have to

If you’re writing a cross-database query, yes, database names may need to come into play in your objects.

However, even with cross-database references, often folks find the dependency issue problematic: synonyms are a common tool to be able to dynamically set cross database references and limit the naming dependency.

As long as you’re querying inside the current database, however, keep it simple: don’t specify the database name.

Commentary Competition

Enjoyed the topic? Have a relevant anecdote? Disagree with the author? Leave your two cents on this post in the comments below, and our favourite response will win a $50 Amazon gift card. The competition closes two weeks from the date of publication, and the winner will be announced in the next Simple Talk newsletter.

The post Why You Shouldn’t Hardcode the Current Database Name in Your Views, Functions, and Stored Procedures appeared first on Simple Talk.

from Simple Talk https://ift.tt/2F1WaAS
via