A Fine Slice Of SQL Server
The problem was that the plan was doing a table scan on the underlying table, and not using the indexes I had carefully crafted.
I had even created a second non-clustered index to try and make sure it was a proper covering index for the query.
Read on to see how Randolph solved the problem.
Comments closedItzik Ben-Gan covers some underutilized functions and function overloads in T-SQL:
TRIM is more than LTRIM(RTRIM())
SQL Server 2017 introduced support for the function TRIM. Many people, myself included, initially just assume that it’s no more than a simple shortcut to LTRIM(RTRIM(input)). However, if you check the documentation, you realize that it’s actually more powerful than that.
This article is an excellent argument in favor of reading the documentation, as all of it is in there but it’s easy to miss.
Comments closedDaniel Hutmacher shows how you can aggregate date and time data types in SQL Server:
Because the “Duration” column is a “time(3)” datatype, it doesn’t really aggregate into an average that easily.
Msg 8117, Level 16, State 1, Line 20 Operand data type time is invalid for avg operator.This may seem a little odd, as time is really a continuum just like any integer or floating-point value, right?
Read on to see how to do this and a warning from Daniel about overflowing. This sort of aggregation is a lot easier to do in R, but you can still do it in T-SQL.
Comments closedLet’s say we are interested in each column of each table: the ordinal position, the name, the data type, and whether it is nullable. In all currently supported versions of SQL Server (2012 and up), there is a handy function that allows you to get all the columns for a T-SQL query, sys.dm_exec_describe_first_result_set. This function is superior to querying the information from sys.columns, mainly because you don’t have to join to sys.types, or embed complicated logic to deal with translation; for example, CASE expressions that change -1 to max, eliminating types that have synonyms, or cutting nvarchar lengths in half – but only if they aren’t max. (You can see the type of query you end up in an earlier tip, “SQL Server Data Type Consistency.”)
This use of sys.dm_exec_describe_first_result_set() is new to me.
Slava Murygin gives us unique file names:
That is pretty common task to generate new files with a timestamp in their names.
It gives you ability to easily identify them, sort them and make them pretty unique.
However, if you have a very busy process it is possible that duplicate name will be produced and you might loose some data.
To avoid that situation I’ve came up with following solution.
It is difficult to envision this solution going wrong.
Comments closedBert Wagner reminds us that integer math is a thing:
To determine how much lumber I would need for building the new walls, I decided to write a SQL query to help with my framing calculations. I was building a 6 foot wall and wanted to put a stud every 16 inches. Easy enough to do the math on this:
SELECT (6*12)/16The output of the query above was 4, indicating the number of studs I would need for one wall section.
What’s interesting is that if we do this same equation in a calculator, we get a slightly different answer: 4.5.
Click through for Bert’s thoughts on the issue. Other languages and platforms do this as well, so it’s not unique to SQL Server, but if you’re not used to it, you might be in for a surprise.
Comments closedDaniel Hutmacher has a painful but realistic problem to solve:
Here’s an example customer. You’ll notice right off the bat that we’re sending this customer an invoice every day on the 20th of the month. To add some complexity, the customer will arbitrarily pay parts of the invoiced amount over time, and to add insult to injury, the banking interface won’t tell us which invoice the customer is paying for, so we’ll just decide that each payment goes towards the oldest outstanding invoice.
Our task is to calculate how many days have elapsed, for each invoice, from invoice date to payment in full.
Daniel has an excellent solution to the problem, so check it out.
Comments closedBert Wagner is on a quest to find out who moved his cheese:
Have you ever wondered who was the last person (or process) to modify a piece of data in your database?
SQL Server offers plenty of system views and functions that provide insight into how your server is running and how your queries are performing. However, it doesn’t offer much information about who last modified your data.
There are a few workarounds, though they aren’t great.
Comments closedItzik Ben-Gan wraps up an outstanding series:
When people want to pivot data using T-SQL, they either use a standard solution with a grouped query and CASE expressions, or the proprietary PIVOT table operator. The main benefit of the PIVOT operator is that it tends to result in shorter code. However, this operator has a few shortcomings, among them an inherent design trap that can result in bugs in your code. Here I’ll describe the trap, the potential bug, and a best practice that prevents the bug. I’ll also describe a suggestion to enhance the PIVOT operator’s syntax in a way that helps avoid the bug.
If you use the PIVOT operator, you definitely want to read this article.
Erik Darling has a card trick for us:
I always try to impart on people that SQL injection isn’t necessarily about vandalizing or trashing data in some way.
Often it’s about getting data. One great way to figure out how difficult it might be to get that data is to figure out who you’re logged in as.
There’s a somewhat easy way to figure out if you’re logged in as sa.
Wanna see it?
Of course you do.
Comments closed