Tag: SQLNewBlogger

I wrote a piece on the new SUBSTRING in SQL Server 2025 and got asked a question. How do we get the last last name, such as only getting “Paolino” from “Miguel Angel Paolino”. This post will show how you can easily 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

I have a set of names, like those in the Northwind.dbo.Customers table. I want to find the last names only, perhaps for a mailing, or maybe for a search box. I have names like these:

Notice line 80 above. There are three names here. In the US, we might consider this as a first, middle, and last names. In Spain, however, this might be a first name and two surnames. If I only wanted the last last name (Paolino), how can I get that?

One of the cool things about working with strings is that we can look at them a few ways, and we have a great T-SQL function that can help: REVERSE(). The last last name is really the first name in a reversed string.

Backwards, but we can fix that.

Let me build up a query. First, I’ll get the ContactName and then the First Name. I’ll use the Charindex to find a space and then assume everything before the space is the first name. That gives me this code:

SELECT
        ContactName,
        SUBSTRING(ContactName, 1, CHARINDEX(' ', ContactName)) AS ContactFirstName
FROM dbo.Customers;

And these results. Notice I have the first names. This isn’t perfect, but it’s often works.

Now, let’s add the string reversed.

SELECT
        ContactName,
        SUBSTRING(ContactName, 1, CHARINDEX(' ', ContactName)) AS ContactFirstName,
        REVERSE(ContactName) AS ReversedName
FROM dbo.Customers;

The results are interesting. Look at lines 79 and 80. The first name is the first word before a space. For the last name, it’s the first word before a space, but reversed. The first part of 79 is shpesoJ and the first part of 80 is oniloaP.

So let’s repeat our substring on the reversed string. Here’s new code:

SELECT
        ContactName,
        SUBSTRING(ContactName, 1, CHARINDEX(' ', ContactName)) AS ContactFirstName,
        SUBSTRING(REVERSE(ContactName), 1, CHARINDEX(' ', REVERSE(ContactName))) AS ReversedLastName
FROM dbo.Customers;

And look at the results. now my third column is the last name, just backwards.

Now we can wrap that last column in another REVERSE() and we get the results we want.

SQL New Blogger

This is a common type of task, and one that you might be asked in an interview, or as a part of a spec. This post only took about 10 minutes to write, with code, and if this were on your blog, I bet an interviewer would ask you how to do this.

Try to influence the interview and write your own post. Do some testing on performance as well, explore how to work with T-SQL to become better at it and showcase this to your next hiring manager.

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.