Names have been changed to protect the innocent.
In the above delete statement which table will have rows deleted from it?
C: Both Table1 and Table2
D: Neither Table1 and Table2
Got it in one. I like having this syntax available to me when I need it, even though it’s not ANSI standard.
CURRENT_TIMESTAMPis the ANSI-equivalent of
GETDATE(). ANSI is an acronym for the American National Standards Institute, and sometimes vendors will include ANSI functions in their products so they can say that they’re ANSI-compliant (which is not a bad thing, in most cases).
There are three main problems with
- No brackets. It goes against the rules about functions. So much for standards!
- It’s functionally equivalent to
GETDATE(), which uses
DATETIME, which we previously identified is old and bad.
- It’s too similar to the poorly-named
TIMESTAMPdata type, which has nothing to do with dates and times and should be called
Bottom line: don’t use
At one point I used
GETDATE() with the thought of portability in mind. Since then, my thoughts on code portability have changed and regardless, as Randolph mentions, it’s better to use
DATETIME2 functions to avoid precision issues with
Now, a new learner of SQL comes along with the requirement to find the last 2 rows per PartitionId.
They are diligent and enthusiastic and have just read about Windows Functions. They think to themselves
Wow! This is great! I can do this with Windows Functions!
They also work for a company that has invested in RedGate’s SQL Prompt so they know that they can rely on SQL Prompt to help iron out any inconsistencies in their script.
So they take the SELECT script above and type in
WHERE… and the auto complete pops up
And that popup isn’t quite accurate… Shane covers this in the guise of a SQL Prompt bug, but it’s a good thing to remember regardless of which tooling you use.
Recently we were doing a project that heavily focused on query tuning and many tables had various outer joins. My co-worker pointed out that many of these could be converted to an apply rather than a join.
Apply gives you both CROSS and OUTER. Think of CROSS APPLY like an INNER JOIN and OUTER APPLY like an OUTER JOIN.
Let’s compare some code to see how APPLY stacks up.
I like the APPLY operator so much that I created an entire presentation on it. It’s not a cure-all by any means, but if you understand the intent, you can find places where it improves your code significantly.
In the last post, I looked at a new T-SQL function for SQL Server 2017. Let’s continue down that path and look at CONCAT_WS(), which is also new for SQL Server 2017. Here’s the definition of the function from Microsoft Docs:
“Concatenates a variable number of arguments with a delimiter specified in the 1st argument. (CONCAT_WS indicates concatenate with separator.)”
CONCAT_WS. It’s one of those functions that I haven’t quite committed to memory, but every time I get reminded of it, I remember that I really need to remember it.
DECLARE @TeamId bigint = NULL, @SubTeamId bigint = NULL; SELECT TOP 1 TaskId FROM tasks WHERE assignedTeamId IS NOT DISTINCT FROM @TeamId AND assignedSubTeamId IS NOT DISTINCT FROM @SubTeamId
Talk about elegant! That’s what we wanted from the beginning. It’s part of ANSI’s SQL 1999 standard. Paul White tells us it’s implemented internally as part of the query processor, but it’s not part of T-SQL! There’s a connect item for it… err. Or whatever they’re calling it these days. Go read all the comments and then give it a vote. There are lots of examples of problems that this feature would solve.
CONS: Invalid syntax (vote to have it included).
This would be nice to have. In the meantime, Michael shows several options which are currently valid syntax.
Now I can tidy things up and remove both leading and trailing spaces with a single call to TRIM():SELECT TRIM(b.foo) FROM dbo.bar b;
On the surface, this may not seem like that big of a deal. And I would tend to agree. But, in this example, it saves a few key strokes and makes the code slightly more readable. And it is nice for T-SQL to finally have a function that has been around in other languages for far longer than I’ve been writing code for a living.
But Wait, There’s More!
Click through for that more. This makes TRIM a lot more useful, so go check it out.
What we want is a table with an extra column which will represent the average price of all products belonging to the same category as the one on the current line.
One approach to solve this problem is to calculate the average price per category using an aggregate function and then join the result with the initial table over the Product Type column in order to get a new table looking at which you can easily find out if a product is more expensive than the average of its category.
Although this would definitely do the job, the query would be quite complicated and lengthy and may lack readability. To avoid these, an alternative approach would be to make use of window function where there is no need to mess with subqueries and joins. When using a windowed function, you can retrieve both aggregated and non-aggregated values at the same time while when using GROUP BY you can get only the results grouped into a single output row.
I ask questions about window (or windowing) functions whenever I interview someone for a job. They are extremely useful things, and I highly recommend Itzik Ben-Gan’s windowing functions book for SQL Server 2012 if you want to learn a lot more.
Creating comma separated strings from a column, or delimited strings as I like to call it, is a very common problem in SQL. Beginning with SQL Server 2017 and Azure SQL Database, there is now another option to the existing set of solutions, STRING_AGG().
I would like to convince you to use STRING_AGG over the other methods. So, let us begin with the competing solutions.
I completely agree and have been switching code over to use STRING_AGG since upgrading to 2017. The code is so much clearer as a result compared to STUFF + FOR XML PATH concatenation.
This is a much more efficient result. In the first line of the script the id, name and gender columns are retrieved. These columns do not contain any aggregated results.
Next, for the columns that contain aggregated results, we simply specify the aggregated function, followed by the OVER clause and then within the parenthesis we specify the PARTITION BY clause followed by the name of the column that we want our results to be partitioned as shown below.
This post focuses on normal aggregates which accept windows. Once you understand that, there’s a wide world beyond it, and you’ll quickly see how useful window functions can be.