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,…
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.
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.
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…
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-6a79f6e541423545914374/] 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.…
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:
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.
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.
There are two ways you can write comments in T-SQL: [crayon-6a79f6e543db7074626110/] 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-6a79f6e543dbd593848271/] Becomes this: [crayon-6a79f6e543dbf123063731/] And you can't tell where the query really ends. This REALLY causes problems with…
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.
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.
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
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-6a79f6e546a68556795685/]
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:
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.
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?"
I'll demonstrate.
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:
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-6a79f6e54b027196493800/]
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.
Do table variables and variables respect transactions? If I set the value of a variable during a transaction, and I roll it back, what happens?
[crayon-6a79f6e54b8bf890282929/]
"I hate views," the DBA said. "They kill performance. And nested views are even worse."
Wrong. I'll prove it.
A perfect storm of unusual problems caused a client to ask, "What if we just used NVARCHAR(4000) as the default datatype parameter for any query, regardless of what datatype the table has in it - like numbers or dates?"
It actually works pretty well in most situations, believe it or not.
