{"id":83589,"date":"2019-03-12T18:50:34","date_gmt":"2019-03-12T18:50:34","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=83589"},"modified":"2021-06-03T16:48:19","modified_gmt":"2021-06-03T16:48:19","slug":"why-you-shouldnt-hardcode-the-current-database-name-in-your-views-functions-and-stored-procedures","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/opinion\/editorials\/why-you-shouldnt-hardcode-the-current-database-name-in-your-views-functions-and-stored-procedures\/","title":{"rendered":"Why You Shouldn\u2019t Hardcode the Current Database Name in Your Views, Functions, and Stored Procedures"},"content":{"rendered":"<p>\u201c<em>There are only two hard things in Computer Science: cache invalidation and naming things<\/em>\u201d<br \/>\n<a href=\"https:\/\/www.karlton.org\/2017\/12\/naming-things-hard\/\" target=\"_blank\" rel=\"noopener\"><em>Phil Karlton<\/em><\/a><\/p>\n<p>I\u2019m 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\u2019d named my objects dbo.Foo, dbo.FooFoo, and dbo.Wat.<\/p>\n<p>But I feel strongly about a few important principles about\u00a0referring to\u00a0objects that are already named in a relational database, specifically SQL Server and Azure SQL Database.<\/p>\n<p>Most of the time, you should use a two part-name for objects in the current database. It\u2019s important to know your database context. When referencing an object in the current database, you should\u00a0not\u00a0specify the database name in the reference.<\/p>\n<p>For example, if you\u2019re creating a view, function, or stored procedure\u2026<\/p>\n<ul>\n<li>\u2018SELECT Col1 from dbo.Foo\u2019 is good<\/li>\n<li>\u2018SELECT Col1 from\u00a0<strong>CurrentDatabaseName<\/strong>.dbo.Foo\u2019 is\u00a0not\u00a0good<\/li>\n<\/ul>\n<p>This might seem like quibbling, but there\u2019s 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\u2019re putting a dependency on that database name.<\/p>\n<h2>When might a database need to have multiple names?<\/h2>\n<p>It\u2019s quite common to need a database to be able to operate under a different name. Here are a few use cases:<\/p>\n<p><strong>In development, test, and pre-production (staging) environments<\/strong>, it\u2019s 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\u2019re connected to. That makes it less likely to have those moments of \u201coops, I didn\u2019t mean to run that script against production!\u201d<\/p>\n<p><strong>When branching database code in source control<\/strong>, you may wish to have a different database for the branch you are working on, with specific sample data for that branch. Typically, it\u2019s convenient to keep these databases on the same instance of SQL Server, so they need to have different names.<\/p>\n<p><strong>When building database code\u00a0<\/strong>to validate that your database objects compile from source, it\u2019s 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.<\/p>\n<h2>What about \u2018deferred name resolution\u2019?<\/h2>\n<p>If you\u2019ve been working with SQL Server for a while, you might wonder about my comment about building database code, because of a feature called \u2018deferred name resolution.&#8217;<\/p>\n<p>Deferred name resolution has been around in SQL Server for a long time, and it\u2019s available in all editions, from LocalDB to Enterprise. There\u2019s no setting to enable this; it\u2019s on all the time. This feature allows you to reference objects which don\u2019t exist when you create stored procedures and some functions. SQL Server gives you a line of credit that those objects\u00a0will\u00a0exist at the time of execution.<\/p>\n<p>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\u2019t exist on the build server \u2014 at least when it comes to stored procedures and some functions.<\/p>\n<p>But there are some important gotchas:<\/p>\n<ul>\n<li>Deferred name resolution doesn\u2019t work in views and in inline-able functions<\/li>\n<li>It\u2019s better for our code to\u00a0not\u00a0be dependent upon the database having a specific name, so deferred name resolution isn\u2019t necessarily a great feature for builds, anyway<\/li>\n<\/ul>\n<h2>Rule of thumb: don\u2019t use the database name unless you absolutely have to<\/h2>\n<p>If you\u2019re writing a cross-database query, yes, database names may need to come into play in your objects.<\/p>\n<p>However, even with cross-database references, often folks find the dependency issue problematic:\u00a0<a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/synonyms\/synonyms-database-engine?view=sql-server-2017\">synonyms are a common tool to be able to dynamically set cross database references and limit the naming dependency<\/a>.<\/p>\n<p>As long as you\u2019re querying inside the current database, however, keep it simple: don\u2019t specify the database name.<\/p>\n<div class=\"spaced-bottom padded--tight scheme--lightest-grey\">\n<h4>Commentary Competition<\/h4>\n<p>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.<\/p>\n<\/div>\n\n","protected":false},"excerpt":{"rendered":"<p>\u201cThere are only two hard things in Computer Science: cache invalidation and naming things\u201d Phil Karlton I\u2019m 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\u2019d named my objects dbo.Foo, dbo.FooFoo, and dbo.Wat. But I feel strongly about&#8230;&hellip;<\/p>\n","protected":false},"author":321187,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[47125,53,143531],"tags":[],"coauthors":[61458],"class_list":["post-83589","post","type-post","status-publish","format-standard","hentry","category-editorials","category-featured","category-t-sql-programming-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83589","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/321187"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=83589"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83589\/revisions"}],"predecessor-version":[{"id":83596,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83589\/revisions\/83596"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=83589"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=83589"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=83589"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=83589"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}