Tag: T-SQL

I saw an article recently about implicit transactions and coincidentally, I had a friend get caught by this. A quick post to show the impact of this setting.

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

The Scenario

You run this code:

Everything looks good. I ran an insert and I see the data in the table. I’m busy, so I click “close” on the tab and see this.

I’ve gotten so used to these messages, and annoyed by them in SSMS, I click “No” to get rid of it and close the window.

The Problem

A short while later I open a query window to do something related and check my data. I don’t see it.

What happened? I had implicit transactions set. This might happen if you mis-click this dialog. Ths option is close to the ANSI_NULL_DFLT_ON option.

You could also, or someone could in your terminal (as a poor joke) run this:

SET IMPLICIT_TRANSACTIONS ON

In either case, this means that instead of that insert running as expected, it really behaves like this:

BEGIN TRANSACTION

INSERT dbo.CityName
(
    CityName
)
VALUES
(‘Parker’)

If I don’t explicit commit (or click “Yes”) then this isn’t committed.

Be wary of implicit transactions. It’s a setting that goes against the way many of us work and can cause lots of unexpected problems. This is a code smell I would never want in my codebase.

SQL New Blogger

When I ran into this twice in a week, I decided to spend 10 minutes writing this post. It’s a chance to explain something and give a recommendation. Something every employer wants.

As part of a demo recently I was adding a default value to a new column with a simple DEFAULT and a value. Under the covers this creates a constraint, however, I want to ensure this is named explicitly and not auto generated. This post shows how to do this.

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

Setup

Let’s create a simple table like this one:

CREATE TABLE dbo.OrderHeader (
OrderHeaderID INT NOT NULL CONSTRAINT OrderHeaderPK PRIMARY KEY,
OrderDate DATETIME,
CustomerID INT
)
GO

Now I want to add a Created column to the table, with a default value of the current date and time. I decide to do this with an ALTER TABLE statement. In the past, I’ve done this with this code:

ALTER TABLE dbo.OrderHeader 
ADD Created DATETIME DEFAULT GETDATE()

The problem is this creates a constraint with a system generated name. If I deploy this code to different systems, I get different names. If I need to change the constraint or drop it, I have to query to find the name as it isn’t explicit. You can see this below.

What I’d rather do is have a named constraint that makes sense to me. Let’s drop this column and do a better job. However, I cant’ just drop the column because I need to drop the constraint and that means I need to get the name.

That’s the problem I’m trying to solve. Here is what I need to do.

Now that I’ve dropped this, let’s add it back with an explicit name. This is simple SQL, and easy to add, just like I can do for Primary Keys. We’ll add a CONSTRAINT keyword and name before the default.

ALTER TABLE dbo.OrderHeader 
ADD Created DATETIME CONSTRAINT df_OrderHEader_Created_Getdate DEFAULT GETDATE()
GO

When I run this, now I see a named constraint.

Note that I’ve named this for the column as if I need similar constraints in this table, they need to be uniquely named. This is in the database, not the table, as all constraints are stored in sys.default_constraints.

Do this and your database deployments go easier, especially across multiple systems.

SQL New Blogger

This is a simple thing, but it’s a good coding practice and better software engineering than allowing the system to name things. I explained how to do this and related this to a real issue in database development: deployments.

This post took me about 10 minutes, and it would likely take you about the same to start showcasing your knowledge. In today’s world, maybe you use AI to help you solve this problem and showcase that skill.

I had someone ask me about using triggers to detect changes in their tables. This is the third post in the series. The first one

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

The Setup

We’re using the same table from the first post.  This is the dbo.Customer table with a PK and 5 other fields. Here is the data in the table:

I had this trigger in the last post, and showed how it captured updates to the ContactEmail field.

CREATE OR ALTER TRIGGER Customer_tru ON dbo.Customer FOR UPDATE

AS

BEGIN
     IF UPDATE(CustomerName)
         INSERT dbo.logger (logdate, logmsg) VALUES (DEFAULT, 'dbo.Customer.CustomerName changed')
     IF UPDATE(AddressKey)
         INSERT dbo.logger (logdate, logmsg) VALUES (DEFAULT, 'dbo.Customer.AddressKey changed')
     IF UPDATE(CustomerStatus)
         INSERT dbo.logger (logdate, logmsg) VALUES (DEFAULT, 'dbo.Customer.CustomerStatus changed')
     IF UPDATE(CustomerContact)
         INSERT dbo.logger (logdate, logmsg) VALUES (DEFAULT, 'dbo.Customer.CustomerContact changed')
     IF UPDATE(ContactEmail)
     BEGIN
         INSERT dbo.logger (logdate, logmsg) 
         SELECT GETDATE(), 'ContactEmail updated from ' + d.ContactEmail + ' to ' + i.ContactEmail 
          FROM inserted i
          INNER JOIN Deleted d ON i.CustomerID = d.CustomerID
     END

END

This seemed to work, but did it really?

The Problem

Let’s illustrate the big problem with this change. I’ll run this code:

UPDATE dbo.Customer
  SET ContactEmail = ‘andy@sqlservercentral.com’
  WHERE CustomerID = 2;

If I do this, here are the results:

I get a NULL? Why, the original value is null and when I concatenate null with other values, I get NULL. Not ideal.

Let’s fix this problem. I’ll use a function to handle null values. Note, I need to do this for both the inserted and deleted tables. Here’s the new trigger.

CREATE OR ALTER TRIGGER Customer_tru ON dbo.Customer FOR UPDATE

AS

BEGIN
     IF UPDATE(CustomerName)
         INSERT dbo.logger (logdate, logmsg) VALUES (DEFAULT, 'dbo.Customer.CustomerName changed')
     IF UPDATE(AddressKey)
         INSERT dbo.logger (logdate, logmsg) VALUES (DEFAULT, 'dbo.Customer.AddressKey changed')
     IF UPDATE(CustomerStatus)
         INSERT dbo.logger (logdate, logmsg) VALUES (DEFAULT, 'dbo.Customer.CustomerStatus changed')
     IF UPDATE(CustomerContact)
         INSERT dbo.logger (logdate, logmsg) VALUES (DEFAULT, 'dbo.Customer.CustomerContact changed')
     IF UPDATE(ContactEmail)
     BEGIN
         INSERT dbo.logger (logdate, logmsg) 
         SELECT GETDATE(), 'ContactEmail updated from ' + COALESCE(d.ContactEmail, 'null') + ' to ' + COALESCE(i.ContactEmail, 'null')
          FROM inserted i
          INNER JOIN Deleted d ON i.CustomerID = d.CustomerID
     END

END

We can see this handles the null appropriately.

In this case I’ve chosen to replace a NULL value with the word ‘null’. This means something to me, but I could have just as well replaced this with “blank” or any other word. In many applications a developer might display a null value as a blank, so choose what works for you.

I’m also including an example to show this works for multiple rows. Here I’ll update multiple rows and we can see each is inserted into my log.

This is a good reason to audit certain activities, as people will sometimes make these mistakes and updates lots of data.

This trigger is slightly more useful, and handles the NULL cases, but it still isn’t perfect. Imagine I need to parse out changes, or generate the reverse transactions, or even search for certain changes. Stuffing a lot of data into a single field is overloading it, and making it less useful over time. What we’d really want to do is separate pertinent data into different fields. In a NoSQL world, we might do this by using a JSON schema to track the before and after.

We could do that here, just stuff JSON into the log message and read it back out and de-serialize it.

SQL New Blogger

This post modified our trigger to address a previous design problem: not handling nulls. We also showed how to test the trigger with multiple rows. This shows I’ve added knowledge to my skillset and can test what I’m trying to do.

Write your own blogs that might examine what you’ve done poorly in the past and how to fix the problems you’ve identified, even in simple code. Many of us do this a lot.

This was a 20-30 minute post for me. You could likely do it in a similar amount of time.