Voice of the DBA

Tag: T-SQL

What is Deferred Name Resolution?
One interesting concept in SQL Server is Deferred Name Resolution. This is something many developers struggle with understanding how this works and where it works.

In the Microsoft docs, there is a specific section in the CREATE TRIGGER docs that covers Deferred Name Resolution. This is a short section, and I’ve reproduced it below:

SQL Server allows for Transact-SQL stored procedures, triggers, and batches to refer to tables that don’t exist at compile time. This ability is called deferred name resolution.

I don’t know how batches are compiled, but procs and triggers are compiled for sure. What this statement says is that I can reference a table in a proc or trigger that doesn’t exist. When I create the trigger or proc, the reference is deferred at compile time and resolved at runtime.

Let’s see how this works. I’ll run this code in SSMS, all at once. In this code, I create a database in one batch, switch to it in the next, and then create a proc in the third.
```
CREATE DATABASE DNRTest

GO

USE DNRTest

go

CREATE PROCEDURE dnrproc

AS

SELECT * FROM sdfsfdsfs

GO
```
If we look in SSMS, this works.

In my database, I have only one object, the stored procedure.

However, if I execute this, it fails.

The table doesn’t exist, so the proc fails. However, I can not create the table and re-run the proc, and it works.

This is handy as I might create procs that reference temp tables, which don’t exist until they’re created. Often this happens in the proc, but if we were to try and resolve the reference at compile time, it would fail.

This also works in triggers as I might often script a table and triggers that reference a second table. When I run that script, I don’t want to trigger creation to fail, so I defer the name resolution until the trigger fires. This way my scripts can be organized logically.

This also works with functions, as seen below.
March 3, 2025
Declaring a Complex PK in a CREATE TABLE: #SQLNewBlogger
Recently I was talking with someone who had not named any of the primary keys (PKs) in their database. They used system generated names and when they ran comparisons, they got all sorts of drops and creates they didn’t expect. This post shows how easy it is to declare PKs with names, even with complexity.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The Scenario

I’m going to use a schema that I have for baseball data. It illustrates the point well, I think.

For my friend, imagine a table like this one:
```
CREATE TABLE [dbo].[salaries](
     [yearID] [int] NOT NULL,
     [teamID] [varchar](3) NOT NULL,
     [lgID] [varchar](2) NOT NULL,
     [playerID] [varchar](9) NOT NULL,
     [salary] [int] NULL,
PRIMARY KEY CLUSTERED 
(
     [yearID] ASC,
     [teamID] ASC,
     [lgID] ASC,
     [playerID] ASC
)
)
```
If I create this table, the system will decide what the PK is. In fact, after I run this, I can see in sys.objects that the name is some random code.

What’s worse is that if this were an FK and I were dropping these in some script, I’d have issues in other systems. This name is different on each system.

The better solution is to make a simple change. Before the Primary Key keyword, I can add Constraint and a name, like this:
```
CREATE TABLE [dbo].[salaries](
     [yearID] [int] NOT NULL,
     [teamID] [varchar](3) NOT NULL,
     [lgID] [varchar](2) NOT NULL,
     [playerID] [varchar](9) NOT NULL,
     [salary] [int] NULL,
CONSTRAINT salariesPK PRIMARY KEY CLUSTERED 
(
     [yearID] ASC,
     [teamID] ASC,
     [lgID] ASC,
     [playerID] ASC
)
)
```
If I run this, then I have a better named PK.

Be explicit in your work. It makes for better code and easier, repeatable, reliable deployments.

SQL New Blogger

This is a simple thing, a code smell, but one that wastes DBA and developer time. This post shows a simple thing you can do to have better code. This took me about 10 minutes and you could do something similar.

Write this and maybe someone asks you how to do this in an interview.
February 3, 2025
The Lesser Used Functions
Recently, I reviewed an article that examined the bitwise functions that were added to T-SQL in SQL Server 2022. As I was looking over the article, I started to wonder if anyone was using these in production code. I used to do bitwise work early in my programming career, when memory and space were tight. However, it always felt like I was hiding some information that a subsequent developer (or my future self), might easily miss.

I looked through some other changes to the T-SQL language in the last few versions and made a list. This week I wonder if any of you use these functions in production code?
- CHOOSE
- IIF
- GET_BIT
- SET_BIT
- BIT_COUNT
- DATE_BUCKET
- APPROX_PERCENTILE_CONT
- APPROX_PERCENTILE_DISC
- LEFT_SHIFT
- RIGHT_SHIFT
- BIT_COUNT
- GET_BIT
- SET_BIT
- TRANSLATE
There are some interesting changes in here, and I can see the use for these functions, but I suspect these are specialized functions built for specific situations (or customers). I don’t expect many people to use them outside of those situations, but maybe I’m wrong. Perhaps some of you like doing bitwise operations, you like the logical CHOOSE/IIF, or maybe you can stomach approximate calculations.

I’m glad that the T-SQL language continues to grow. I would like to see more changes that ease the development of database code, though I would like Microsoft to ensure these new functions perform well. Some of the changes added in the past haven’t done so, especially when a dotNet function is wrapped with T-SQL. Performance matters and many of these functions will be called in queries that need to compete a result with thousands of rows.

Let us know today if you’ve found these functions useful in your work, or if there are changes made in the past that you would like to see improved.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.
January 10, 2025
Creating a “Real” Copy of a View: #SQLNewBlogger
I saw a post where a developer was trying to read the Information Schema views to create a copy of a view as a “real” table, a user table. This posts shows an easy way to do this.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The Scenario

Imagine you have a view, for example, I have this one:
```
CREATE   VIEW [dbo].[City] 

AS

SELECT TOP 10 
  cn.CityNameID, cn.CityName
  FROM dbo.CityName AS cn
  WITH CHECK OPTION

GO
```
The structure of the underlying table is:

I have data being returned from this view as well, as you can see here:

If I want a copy of this view, I can certainly look in the information_schema views and see some data. Below, I have the column information for this view, which can be used to structure a create table statement.

However, there’s a better way.

Quickly Copying a View

The INTO clause is very valuable and helpful here. Many of us use this to copy a table or part of a table, but it work with views. Here is how I create an empty copy of my view.
```
SELECT * 
  INTO dbo.MyCities 
  FROM dbo.city
  WHERE 1 = 0;
```
This will actually create a new table, as you can see in my Table list when I refresh after running the command.

The table looks like the structure of the view above. The PK isn’t set, but there isn’t necessarily a PK in a view as it can combine data from multiple tables. If I wanted data, I can run the same statement above without the WHERE clause. I’ve done that below and then selected data from the new table so show this.

If I needed to add some constraints or other items, I could easily add those with ALTER TABLE statements.

SQL New Blogger

This post required about 20 minutes for me to setup a demo, test, and then write with some screenshots. It wasn’t a hard post to write, but it shows a quick technique for doing something I’ve commonly seen from others.

This is the type of post you can write that might get an interviewer interested in you and perhaps ask you a question. You could add some context as to why you did this, or why you like (or don’t like) this technique.
December 11, 2024