Press "Enter" to skip to content

Category: T-SQL

Defining Deferred Complation

Gail Shaw explains the concept of deferred compilation:

When I talked about row estimations for table variables, I mentioned ‘deferred compile’, but didn’t give a whole lot of details. What, then, is a deferred compilation? Let’s start with how batches work normally.

T-SQL is an interpreted language. While we talk about compiles, they’re not compilations in the sense of what happens to C++. There’s no conversion of the script to a machine language or intermediate language which is used from that point onwards. Every time a batch executes, it has to be parsed, bound and have an execution plan generated or fetched from cache.

Click through to learn what it does mean.

Comments closed

One Reason to Avoid SELECT *

Andy Levy has a new reason for us:

I got a merge a while back that included a change I wasn’t expecting from one of my developers. All they were doing was renaming a column on a table. We had names settled a while ago. What’s up with that?

Turns out, when I created the table, I named one of the fields BrithYear. This merge request corrected it to BirthYear. The misspelling slipped past me, past everyone else, it was only discovered because when this developer was building the a new query into their code, they spelled the field as one would expect, and the query threw an error.

There’s many a reason not to use SELECT * in application code; this is one I don’t think I’d heard before.

Comments closed

The Concatenation Operator

Hugo Kornelis explains what the Concatenation operator does:

The Concatenation operator reads and returns all rows from all its inputs, in order, and without modification.

This operator is most commonly used to execute queries that use UNION or UNION ALL. In the former case, other operators are required to remove the duplicates, because Concatenation doesn’t provide that functionality. You may also find the Concatenation operator in queries on partitioned views.

Read on to see the algorithm and lots of details about the operator.

Comments closed

Build Your Own Number Generator

Itzik Ben-Gan has part one of solutions to a challenge:

Last month I posted a challenge to create an efficient number series generator. The responses were overwhelming. There were many brilliant ideas and suggestions, with lots of applications well beyond this particular challenge. It made me realize how great it is to be part of a community, and that amazing things can be achieved when a group of smart people joins forces. Thanks Alan Burstein, Joe Obbish, Adam Machanic, Christopher Ford, Jeff Moden, Charlie, NoamGr, Kamil Kosno, Dave Mason and John Number2 for sharing your ideas and comments.

Initially I thought of writing just one article to summarize the ideas people submitted, but there were too many. So I’ll split the coverage to several articles. This month I’ll focus primarily on Charlie’s and Alan Burstein’s suggested improvements to the two original solutions that I posted last month in the form of the inline TVFs called dbo.GetNumsItzikBatch and dbo.GetNumsItzik. I’ll name the improved versions dbo.GetNumsAlanCharlieItzikBatch and dbo.GetNumsAlanCharlieItzik, respectively.

One of the best parts about this happening in public is that there is a real benefit to having multiple people look over a problem, and then waves of people refining those solutions over time.

Comments closed

Archival on Delete in SQL Server

Erik Darling shows off a pattern:

Well, friends, I have good news for you. This is an easy one to implement.

Let’s say that in Stack Overflow land, when a user deletes their account we also delete all their votes. That’s not how it works, but it’s how I’m going to show you how to condense what can normally be a difficult process to isolate into a single operation.

The one gripe I have with this post is that my annoyingly loud keyboard is buckling spring, not Cherry MX Blue, thank-you-very-much.

Comments closed

String Modification in T-SQL

Steve Jones answers a question:

Recently I ran across a question posted by a beginner on the Internet and thought this would be a good, basic topic to cover. The question was: how can I replace a value in a comma separated string in a table?

This post covers the basics of this task.

Incidentally, this is where I say hey, that sounds like a failure in normalization. If you need to care about individual values in a collection, your value is not atomic. But that’s a bit of a tangent.

Comments closed

Performance Impact of Foreign Keys with Non-Default ON UPDATE or ON DELETE

Hugo Kornelis continues a dive into foreign keys:

Welcome to part fifteen of the plansplaining series. In the three previous parts I looked at the operators and properties in an execution plan that check a modification doesn’t violate foreign key constraints. That part is done. But I’m not done with foreign keys yet.

We normally expect foreign keys to throw an error on violations. But that’s actually only the default option: they can also be set to be self-correcting. This is done using the ON UPDATE and ON DELETE clauses, which provide the user with several choices on how to handle child data that would become orphaned, and hence violate the constraint, as a result of a change in the parent table.

Read on to see how these operate in SQL Server.

Comments closed

Correlated Subqueries which Don’t

Daniel Hutmacher gives us an eye test:

The developer wrote this pretty little query to show us which accounts are up for review (which in our case means they have a “30” flag).

SELECT account, balance, 'For review' AS [status]
FROM #accounts WHERE account IN (SELECT account FROM #accountFlags WHERE flag=30) ORDER BY account;

Did you spot it?

I did, but in fairness, I’ve been burned enough times by this that I check for it.

Comments closed