Erik Darling wants you to simplify your life queries:
See, when a query is big and complicated to you, there’s a pretty good chance you’re gonna get a big and complicated query plan, because it’s big and complicated to the optimizer, too.
This isn’t to say the optimizer is dumb or bad or ugly; it’s just that there’s only so long it’s willing to spend coming up with a plan.
Remember, cheap plan fast. Not perfect, not great, maybe good enough.
It’s a good operating philosophy: if you have a query which has gone off the rails, one of the best things you can do is try to turn the query into several small steps. It’s possible to reduce complexity that way…though you may also gain complexity in the process if you do it wrong.
Pamela Mooney shows us how we can find broken code on our SQL Server instances:
Before we approached our last major SQL Server upgrade, I was curious about what might break. Yes, I had used the DEA to check our code against deprecated or discontinued code. But I am talking about code that might not have been used in some time and would break because objects no longer existed, or other things like that. So I wrote these scripts to refresh the sprocs, views and functions in our (non-production) environment.
Pamela’s motivation was to handle code which breaks during an update. You can also use this to see what you can probably deprecate—if the view doesn’t work, it can’t be in use. That means either someone should fix it or drop it.
So we proceed to execute an alter view over the first view:
ALTERVIEWdbo.[vi_invoices_received_by]ASSELECT
ConfirmedReceivedByas[Receivedby],
COUNT(InvoiceID)as[#ofInvoices],
CustomerIDFROMSales.InvoicesGROUPBYConfirmedReceivedBy, CustomerID;GOSo we should now be able to see the updated column names on the second view, right?
but if you execute the view again, you will obtain the same results as before:
Read on to see what’s gone wrong and how you can fix it.
Jason Brimhall shows how you can find gaps in your data:
This method is the much maligned recursive CTE method. In my testing it runs consistently faster with a lower memory grant but does cause a bit more IO to be performed. Some trade-off to be considered there. Both queries are returning the desired data-set which happens to be my missing question days. Only, I have added an extra output in the second query to let me know the day of the week that the missing question occurred on. Maybe I forgot to enter it because it was a weekend day or maybe I opted to not create one at all because the day lands on a Holiday. Let’s take a small peek at the results.
This is a good use for tally tables (or for a calendar table, which is basically a date dimension called something else so you can feel comfortable dropping in a non-warehouse system).
Bert Wagner digs into an interesting topic:
Success! But as I was celebrating my dynamic SQL victory, I realized I was making an assumption about SQL Server that I had never thought about before:
The above query only works because SQL Server is executing the variables in the SELECT list sequentially. I’m incrementing @CurrentRow only after processing my @RowQuery variable, and this logic only works correctly if SQL Server executes the variable expressions in the order they appear in the SELECT list. If SQL Server was executing items in the SELECT list in reverse or random order, @CurrentRow could potentially get set BEFORE @RowQuery was evaluated, causing the logic of adding “UNION ALL SELECT” in the right location to fail.
I have a vague recollection that you couldn’t always count on this, though I admit to never having seen a counter-example. I don’t believe expression execution order is defined in the ANSI SQL standard—the idea is that everything is typically resolved “at once.”
The table value constructor is basically like a virtual table not too different from a CTE or a subquery (in that they are all virtual tables of sorts). The table value constructor however can be combined with either of those other types and is a set of row expressions that get put into this virtual table in a single DML statement.
It’s one of the nicer things SQL Server 2008 gave us.
Bert Wagner shows that you can only insert with IDENTITY_INSERT = ON for one table at a time:
Ok, simple enough to fix: we just need to do what the error message says and SET IDENTITY_INSERT ON for both tables:
SET IDENTITY_INSERT dbo.User_DEV ON; SET IDENTITY_INSERT dbo.StupidQuestions_DEV ON;And… it still didn’t work:
IDENTITY_INSERT is already ON for table 'IdentityTest.dbo.User_DEV'. Cannot perform SET operation for table 'dbo.StupidQuestions_DEV'.
Click through for the ramifications and your alternative.
Erik Darling shows a clever use of the APPLY operator:
A while back, Jonathan Kehayias blogged about a way to speed up UDFs that might see NULL input.
Which is great, if your functions see NULL inputs.
But what if… What if they don’t?
And what if they’re in your WHERE clause?
And what if they’re in your WHERE clause multiple times?
Oh my.
But fear not—Erik’s got you covered.
Bert Wagner shows how SQL Server handles trailing spaces when comparing two strings:
The LEN() function shows the number of characters in our string, while the DATALENGTH() function shows us the number of bytes used by that string.
In this case, DATALENGTH is equal to 10. This result is due to the padded spaces occurring after the character “a” in order to fill the defined CHAR length of 10. We can confirm this by converting the value to hexadecimal. We see the value 61 (“a” in hex) followed by nine “20” values (spaces).
Click through to see what happens and why it works the way it does.
Itzik Ben-Gan takes us through four bugs or oddities around joins:
The order counts are now correct, but the total freight values are not. Can you spot the new bug?
The new bug is more elusive because it manifests itself only when the same customer has at least one case where multiple orders happen to have the exact same freight values. In such a case, you are now taking the freight into account only once per customer, and not once per order as you should.
Click through to avoid accidentally introducing bugs in your T-SQL code.
Kevin Feasel
2019-07-23
T-SQL
No Comment