A Fine Slice Of SQL Server
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.
Comments closedBert 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.
Comments closedItzik 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.
Comments closedDefault schemas in SQL Server can be a blessing, since they reduce the need to specify the schema when creating DDL statements in T-SQL. However, relying on the default schema when creating DML statements can be problematic. A recent question on dba.stackexchange.com asked “Does T-SQL have a Schema search path?”, similar to PostgreSQL implements the
search_pathparameter. This post shows how schemas are implemented in SQL Server. We’ll also see why it’s important to always specify the schema when using SQL Server.
A lot of this behavior goes back to the pre-2005 era. 2005 introduced schemas as logical separators, whereas pre-2005 they were more of a security measure (and dbo was the database owner’s schema). I completely agree that you should specify two-part names in-database. It’s a tiny bit faster (which adds up when you’re doing thousands of transactions per second) and reduces ambiguity.
Erik Darling explains patiently that if you use sp_executesql wrong, you don’t get the benefits of using it right:
The gripes I hear about fully fixing dynamic SQL are:
– The syntax is hard to remember (setting up and calling parameters)
– It might lead to parameter sniffing issuesI can sympathize with both. Trading one problem for another problem generally isn’t something people get excited about.
But there are good reasons fully to fix it, so read on.
Comments closedLori Brown shows how you can use DDL triggers to track database or instance-level changes:
I have been working on some improvements to some of the regular ways we monitor for important changes. We always have to be on the lookout for unexpected changes being made in the SQL instances that we monitor since often times we are not the only team who has sysadmin access to the instance. We are always the best trained to take care of and configure things but we sometimes find that someone makes a change either to the SQL or database configuration without telling us. We want to know when things like this happen!
I’m a big fan of these. Of course you need to get the code right, as a bad trigger can be devastating but you can get a lot of useful information out of it and figure out who’s hand was in the cookie jar.
Comments closedBrent Ozar has some advice on when to use common table expressions versus temporary tables:
I’d suggest starting with CTEs because they’re easy to write and to read. If you hit a performance wall, try ripping out a CTE and writing it to a temp table, then joining to the temp table.
This is my advice, too. Start with the thing which is easiest for you to develop and maintain. If it suffices for performance, stick with it; otherwise, move to the next-lowest level of complication. Stop when you have good enough performance. This optimizes for one of the most precious resources people rarely think about: developer maintenance time. Developers are pretty expensive, so the more time they spend trying to understand complex code, the less time they’re doing stuff which pushes the business forward.
Comments closedDaniel Hutmacher points out a side effect of using CAST() and CONVERT():
Suppose we want to set up a view in the new solution that mirrors the names and definitions of the old table, so the legacy integration can use that view going forward:
CREATE OR ALTER VIEW new.the_table_like_before
AS
SELECT CAST(id AS varchar(32)) AS id,
CAST([row] AS int) AS [row],
CAST(date_loaded AS datetime) AS dt
FROM new.the_table;Now, if you check out the resulting datatypes of the view, you’ll notice that all the columns are marked nullable, even though they’re all based on non-nullable columns, so the values in the view could never be null.
Read on for a couple possible solutions.
Comments closedBert Wagner explains the CHOOSE() function:
While I know I don’t utilize most of the features available in SQL Server, I like to think I’m at least aware that those features exist.
This week I found a blind-spot in my assumption however. Even though it shipped in SQL Server 2012, the SQL Server CHOOSE function is a feature that I think I’m seeing for the first time this past week.
CHOOSE() and IIF() were functions ported over to make it easier for Access and Excel users to write code. I tend to avoid them because there are typically better idiomatic constructs (like CASE) in SQL Server.
Paul White takes us through another way to perform minimally logged bulk loads with SQL Server:
This post provides new information about the preconditions for minimally logged bulk load when using
INSERT...SELECTinto indexed tables.The internal facility that enables these cases is called
FastLoadContext. It can be activated from SQL Server 2008 to 2014 inclusive using documented trace flag 610. From SQL Server 2016 onward,FastLoadContextis enabled by default; the trace flag is not required.Without
FastLoadContext, the only index inserts that can be minimally logged are those into an empty clustered index without secondary indexes, as covered in part two of this series. The minimal logging conditions for unindexed heap tables were covered in part one.
Click thorugh for a highly informative article.
Comments closed