A Fine Slice Of SQL Server
If you google “generating permutations using SQL”, you get thousands of hits. It’s an interesting problem if not very useful.
I wrote a solution recently and thought I’d share it. If you’re keen, try tackling it yourself before moving on.
Click through for the script. It’s an interesting approach and might be worth playing a round of code golf.
Comments closedMatan Yungman and Guy Glantser take a hack at DATEDIFF versus DATEADD for date calculations. First up is Matan:
Pretty simple right?
Well, it is, and since this problem is pretty common, I used this solution in many performance tuning sessions I performed over the years.
There’s a slight problem though: This solution isn’t 100% accurate.
When carefully looking at the results, I find out that for the first query, I get 5859 rows, and for the second query, I get 5988 rows. Where does this difference come from?
Then, Guy gives his take on the problem:
I tested both queries on a sample table, which has millions of rows, and only around 500 rows in the last 90 days. The first query produced a table scan, while the second query produced an index seek. Of course, the execution time of the second query was much lower than the first query.
Both queries were supposed to return the orders in the last 90 days, but the first query returned 523 rows, and the second query returned 497 rows. So what’s going on?
The answer has to do with the way DATEDIFF works. This function returns the number of date parts (days, years, seconds, etc.) between two date & time values. It does that by first rounding down each one of the date & time values to the nearest date part value, and then counting the number of date parts between them.
They both start from the same base problem, but end up with slightly different formulations of a solution.
Comments closedKendra Little shows how to search the plan cache and query store for a particular query:
If I’m looking in SQL Server’s Execution Plan Cache, I like to use the sys.dm_exec_text_query_plan dynamic management view. This stores those XML query plans as text.
I learned about using this DMV from Grant Fritchey in his post, “Querying the Plan Cache, Simplified.” Grant points out that while doing wildcard searches in the text version of a query plan isn’t fast, querying it as XML is often even slower.
This is definitely worth a read.
Comments closedNeil Gelder gives an introduction to JSON in SQL Server 2016:
A new feature in SQL Server 2016 (also available in Azure SQL database) is the ability to create and query JSON (Javascript object notation) documents, which have now become a common alternative to XML.
Lets look at some examples, I’ll be using tables from the new sample database for SQL Server 2016 WorldWideImporters which you can download from this link
I’m of two minds with JSON support: I think it’s very useful for building output sets for service calls and might be fine for inputs when you can’t use a table-valued parameter for some reason, but if you’re doing a lot of JSON splitting of data in a table, that’s a violation of first normal form.
Comments closedMadhivanan JR shows two methods for extracting the decimal value of a number:
One of the questions asked in a forum. “What are the different methods to extract decimal part from a number?”.
There can be many methods. Two simple methods are as shown below
Click through for the two methods. I knew method #1 but didn’t think about method #2 because I’ve always thought of the modulo with respect to integers, but it makes sense.
Comments closedManoj Pandey points out a new string function in SQL Server vNext:
Here in this post I’ll discuss about one more new function i.e. CONCAT_WS(), here “_WS” means “With Separator”.
This is very similar to the existing CONCAT() function introduced back in SQL Server 2012, which concatenates a variable number of arguments or string values.
The difference is the new function CONCAT_WS() accepts a delimiter specified as the 1st argument, and thus there is no need to repeat the delimiter after very String value like in CONCAT() function.
It’s a small change, but I think a rather useful one. Do think about how you’d want to interpret NULL values, though, as CONCAT_WS() does not include separators for NULL values.
Comments closedLouis Davidson points out a potential query writing time-saver:
But then I saw Mike use the following syntax (sans PROFILE and XML):
SET STATISTICS IO, TIME, PROFILE, XML ON;
GO
SELECT *
FROM dbo.Table1;
GO
SET STATISTICS IO, TIME, PROFILE, XML OFF;Wow, that is a lot easier! (And yeah, using SQL Prompt I can set a snippet to take away typing, but this saves space in any case.)
Read on for more examples.
Comments closedTom Martens explains a class of generic data processing problems:
Subset and Apply means that I have a dataset of some rows where due to some conditions all the rows have to be put into a bucket and then a function has to be applied to each bucket.
The simple problem can be solved by a GROUP BY using T-SQL, the not so simple problem requires that all columns and rows of the dataset have to be retained for further processing, even if these columns are not used to subset or bucket the rows in your dataset.
One quick example of this is running totals of orders for each customer, which Tom answers using T-SQL, R, and Power BI. Click through for those three solutions.
Comments closedTomaz Kastrun shows how to feed a JSON data set into R and turn that into a proper data frame:
JSON has very powerful statements for converting to and from JSON for storing into / from SQL Server engine (FOR JSON and JSON VALUE, etc). And since it is gaining popularity for data exchange, I was curious to give it a try with R combination.
I will simply convert a system table into array using for json clause.
There’s an R library. There’s always an R library.
Comments closedRob Sewell shows how to run SQL Server queries using Visual Studio Code:
Reading this blog post by Shawn Melton Introduction of Visual Studio Code for DBAs reminded me that whilst I use Visual Studio Code (which I shall refer to as Code form here on) for writing PowerShell and Markdown and love how easily it interacts with Githuib I hadn’t tried T-SQL. If you are new to Code (or if you are not) go and read Shawns blog post but here are the steps I took to running T-SQL code using Code
I played around with an early version of this and my thought was that there were some nice improvements over Management Studio (like being able to filter and sort the result set grid without going back to the server), but that there are still too many nice things Management Studio does for me to take a serious look at it. Still, I’m hopeful that Microsoft moves in the direction of having a fully-featured querying tool for Linux so I can finally join the perpetual Year of the Linux Desktop.
Comments closed