A Fine Slice Of SQL Server
Jared Poche continues a series on join types:
Merge joins traverse both inputs once, advancing a row at a time and comparing the values from each input. Since they are in the same order, this is very efficient. We don’t have to pay the cost to create a hash table, and we don’t have the much larger number of index seeks nested loops would encounter.
Read the whole thing. Remember: merge joins are also the best strategy for when two lanes of the road come together.
Comments closedRobert Sheldon takes us through data insertion in MySQL:
In most cases, adding a single row of data to table is a reasonably straightforward process. You define the
INSERTclause andVALUESclause and usually specify the column list in between. The column list should include only those columns for which you provide values. The list can include the primary key column, generated columns, or columns defined with default constraints, but you must be careful how you handle them, as you’ll see later in the article.
This is definitely aimed at people new to MySQL and SQL in general.
Comments closedTom Zika continues the 12-step program for scalar function abusers:
In the previous posts, we have learned why Scalar Functions (UDFs) are bad for parallelism and performance and what the options are for their removal.
The only remaining question is where to start. You’ve probably guessed there isn’t one true way to approach this. So instead, I offer several strategies that you can combine.
Plus Tom has an offer for you at the bottom of the post: conversion from Scalar UDFs to Inline Table-Valued Functions, which tend to perform much better.
Comments closedEmily Tehrani notes an addition to Azure Synapse Analytics:
We are thrilled to announce that the MERGE T-SQL command for Azure Synapse Dedicated SQL pools is now Generally Available! MERGE has been a highly requested addition to the Synapse T-SQL library that encapsulates INSERTs/UPDATEs/DELETEs into a single statement, drastically easing migrations and making table synchronization a breeze.
If you do decide to use this, I’d expect it to have the same bugs which make its use on-premises a mess. As always, MERGE responsibly.
Comments closedDavid Fowler understand the order of things:
This was an interesting question that I was asked yesterday and something that I’d never really thought of before. Can you delete the top x number of rows based on an ORDER BY?
Why would you want to do that? Well let’s just assume that we have a ‘people’ table and some strange bug in the application has cause the need to delete the top 10 oldest females for whatever reason (I know, it’s very contrived example and not the situation that my colleague was facing but it’ll do to illustrate the point).
Click through for one way which doesn’t work and two ways which do.
Comments closedLouis Davidson likes a new operator in SQL Server 2022:
The MOST exciting change from a T-SQL standpoint is:
IS NOT DISTINCT FROM. This feature solves an age-old issue for T-SQL programmers and is worth its weight in gold. It is basically an equals comparison operator like =, but treatsNULLas an individual value. Unlike =, this new operator returns onlyTRUEorFALSE, but notUNKNOWN. Writing queries that compare to values that can containNULLis tedious, mostly because of code like the following:
Louis is quite happy here. I like the fact that the syntax is here, though I’d be concerned about performance—the syntax is nicer but you can run into the same performance issues as you’d have with “NULL or match” type queries.
Comments closedRob Farley like a syntax change:
This was fine, but it did start to become a little cumbersome.
Enter SQL Server 2022. Not only do we get the ability to ignore nulls now, making it easy to get the last non-null value from a list, but we also get a WINDOW clause – part of the SELECT query itself, dropping in between the HAVING clause and the ORDER BY clause, allowing us to predefine those OVER clause segments.
Read on for the full scope of Rob’s thoughts.
Comments closedPaul Randal takes us through one of my favorite operators:
Introduced by Microsoft in SQL Server 2005, SQL CROSS APPLY allows values to be passed from a table or view into a user-defined function or subquery. This tutorial will cover the incredibly useful and flexible APPLY operator, such as how the CROSS APPLY and OUTER APPLY operators work, how they’re like the INNER and LEFT OUTER JOIN, and give you some examples of both. All the examples use the AdventureWorks example database.
Later in the article, I’ll also discuss a highly pervasive SQL Server performance problem—one I still encounter with customers on a weekly basis. This problem is related to using the APPLY operator against a specific type of user-defined function. It’s so problematic and can overwhelm tempdb, bringing your SQL Server instance to a crawl!
If you’re not too familiar with APPLY in its two forms, read the whole thing.
Michael J. Swart has a function:
Just like PIVOT syntax, UNPIVOT syntax is hard to remember.
When I can, I prefer to pivot and unpivot in the application, but here’s a function I use sometimes when I want don’t want to scroll horizontally in SSMS.
Click through for the function. This is an area where I wish there was built-in * logic for PIVOT and UNPIVOT. Or at least a “Select all columns but the following” as that would make things easier.
Comments closedI went looking for other docs examples from vendors who have had the syntax around for 10+ years, and there wasn’t anything all that much more interesting.
Mostly case expressions and whatnot.
Big deal.
Check out the comments for more.
Comments closed