T-SQL Archives - Page 4 of 7 - Brent Ozar Unlimited®

T-SQL & Development

[Video] Fundamentals of Stored Procedures at SQLBits

July 27, 2022

Anybody can write a stored procedure with a little help from Google. This session is about how to write stored procedures that have a high likelihood of performing well and are easy to troubleshoot.

This fast-paced, all-demo session from SQLBits will NOT cover how to write a query, syntax, or performance tuning. This is about good best practices after you've written the first one - things like how to catch errors, how to pass in multiple values, how to debug without the debugger, and more.

T-SQL & Development

The 6-Year-Old T-SQL Book That Never Goes Out of Style

May 12, 2022

You've been working with Microsoft SQL Server for yeeears, and you're pretty confident that you know how to write a query.

So when I tell you about a book called T-SQL Fundamentals, you're all, "nah, man, I got this covered."

T-SQL & Development

Don’t Treat SQL Like a Programming or Scripting Language.

May 10, 2022

In a programming language like C# or Java, you tell the computer what to do, in order. Get the customers from California, then get their invoices, then sum the total. SQL, on the other hand, is a declarative language where you declare the shape of your result set: Get the total sales from Californian customers.…

T-SQL & Development

Error Handling Quiz Week: Where Are You Handling Errors?

January 14, 2022

This week's series on error handling has been an eye opener for many of us. We've laughed. We've cried. We've screamed in horror. We've read the documentation. I don't blame you. This is a really confusing topic, and for many of us, it's the first time we've actually looked at the mechanics of how transactions,…

T-SQL & Development

Error Handling Quiz Week: Making a Turkey Sandwich with XACT_ABORT

January 13, 2022

CAN YOU BELIEVE THAT HOT MESS IN YESTERDAY'S POST?!?

I know, right? You thought that by combining try/catch with a transaction, you'd get robust error handling. Instead, you ended up with half the tables populated, and a leftover open transaction. You'd already forgotten Tuesday's post in which I pointed out that TRY/CATCH doesn't catch low severity or high severity errors.

T-SQL & Development

Error Handling Quiz Week: Combining Transactions And TRY/CATCH

January 12, 2022

In yesterday's epic cliffhanger, you might have been shocked to discover that a plain ol' transaction does not solve our problem - and in fact, it makes things worse. With yesterday's code, we still got rows inserted into the Parent table, no rows in the Child table, and a nasty surprise that our session will have to deal with down the road.

Performance Tuning

Error Handling Quiz Week: Will a Transaction Help?

January 11, 2022

In yesterday's post, we tried a TRY/CATCH, and those of us who actually ran the demo scripts were disappointed. We found that a TRY/CATCH alone doesn't fix it: we end up getting rows in the Parent table, but no rows in the Child table. It turns out there are some gotchas in Books Online's summary of…

T-SQL & Development

Error Handling Quiz Week: Tryin’ TRY/CATCH

January 10, 2022

Let's say we have two tables, Parent and Child, and we need to guarantee that they both get populated at once. We'll write a single stored procedure to do both inserts: [crayon-6af2ba20788d2290383936/] I put a WAITFOR in there, but that isn't the problem - I'm just using that to demonstrate why the code isn't production-ready.…

T-SQL & Development

Most T-SQL Queries Don’t Even Try to Handle Errors.

August 20, 2021

David Tovee asked a great question in yesterday's Mastering Query Tuning class. He asked his fellow students, "How many of you actually use TRY/CATCH?"

I turned it into a Twitter poll because I wanted to check a wider audience:

T-SQL & Development

What does SET NOCOUNT ON do?

June 15, 2021

When you're working with T-SQL, you'll often see SET NOCOUNT ON at the beginning of stored procedures and triggers.

What SET NCOUNT ON does is prevent the "1 row affected" messages from being returned for every operation.

T-SQL & Development

9 Signs Your T-SQL Might Fail a Code Review

May 19, 2021

It's hard to set absolute rules about, "Feature X should absolutely never be used."

However, there are some features that set off alarm bells when I see them. Usually, when I start asking more questions about when we're using those particular features, I get answers of, "Oh, I didn't know that was a problem." As we have a bigger discussion, it leads to the piece of code failing the code review, and going back to the drawing board for improvements.

T-SQL & Development

Never, Ever, Ever Start T-SQL Comments with Two Dashes

April 6, 2021

There are two ways you can write comments in T-SQL: [crayon-6af2ba2079eb7605081984/] Never, ever use two dashes. Never. The problem is that monitoring tools and diagnostic management views often dump out the T-SQL in one long line. So this query: [crayon-6af2ba2079ebe313338456/] Becomes this: [crayon-6af2ba2079ec1976093349/] And you can't tell where the query really ends. This REALLY causes problems with…

T-SQL & Development

#TSQL2sday 136 Invitation: Blog About Your Favorite Data Type (Or Least Favorite)

March 2, 2021

T-SQL Tuesday is a monthly blogothon where we get together and write about a different topic. I'm hosting this month's episode.

Your mission: write a blog post about your favorite data type, and schedule it for next Tuesday, March 9.

T-SQL & Development

How to Batch Updates A Few Thousand Rows at a Time

December 22, 2020

You've got a staging table with millions of rows, and you want to join that over to a production table and update the contents. However, when you try to do it all in one big statement, you end up with lock escalation, large transaction log usage, slow replication to Availability Groups, and angry users with pitchforks gathered in the Zoom lobby.

T-SQL & Development

How to Insert Rows and Get Their Identity Values with the OUTPUT Clause

November 24, 2020

Say you've got a two-step process where you're:

Inserting rows into a table that has an identity column, then
Querying that table to figure out what identities you got

T-SQL & Development

How Scalar User-Defined Functions Slow Down Queries

November 18, 2020

When your query has a scalar user-defined function in it, SQL Server may not parallelize it and may hide the work that it's doing in your execution plan.

To show it, I'll run a simple query against the Users table in the Stack Overflow database.
[crayon-6af2ba207bfaa316633784/]
I don't have an index on Reputation, so SQL Server has to sort all of the Users by their Reputation. That's a CPU-intensive operation, so SQL Server automatically parallelizes it across multiple CPU cores:

T-SQL & Development

Why Full Text’s CONTAINS Queries Are So Slow

November 7, 2020

SQL Server's full text search is amazing. Well, it amazes me at least - it has so many cool capabilities: looking for prefixes, words near each other, different verb tenses, and even thesaurus searches. However, that's not how I see most people using it: I've seen so many shops using it for matching specific strings, thinking it's going to be faster than LIKE '%mysearch%'. That works at small scale, but as your data grows, you run into a query plan performance problem.

T-SQL & Development

If Your Trigger Uses UPDATE(), It’s Probably Broken.

October 21, 2020

Your queries won’t tune themselves. My 2027 Live Class Season Pass is $995 (normally $4,395), with live Fundamentals and Mastering classes plus recordings. Register by October 31 to join Fundamentals Week, November 2–6. See the pass.

In yesterday's blog post about using triggers to replace computed columns, a lively debate ensued in the comments. A reader posted a trigger that I should use, and I pointed out that their trigger had a bug - and then lots of users replied in saying, "What bug?"

T-SQL & Development

Using Triggers to Replace Scalar UDFs on Computed Columns

October 20, 2020

Your database is riddled with computed columns whose definition includes a scalar user-defined function. Even up to & including SQL Server 2019, which boasts faster scalar function processing, any table that includes a scalar function cause all access to that table to go single-threaded. In that case, a trigger can actually be a great replacement.

No, wait, come back. Triggers have a pretty bad reputation amongst the database community because:

T-SQL & Development

How to Create a Table with a Partitioned Clustered Columnstore Index

September 16, 2020

If you need to create a table and you want it to be partitioned right from the start, AND you want it to have a clustered columnstore index, here's a creation T-SQL example:
[crayon-6af2ba207e76d596132315/]
The partition scheme name goes on the outside of the parenthesis, the index name goes on the inside, and the order of the whole thing is a little counter-intuitive.