A Fine Slice Of SQL Server
Microsoft has just released SQL Server 2019 CU3:
This article describes Cumulative Update package 3 (CU3) for SQL Server 2019. This update contains fixes that were released after the initial release of SQL Server 2019 and updates the SQL Server and Analysis services components to the following builds.
It does fix the SQL Agent problem from CU2 and it looks like the ML Services team had several patches thrown in.
Comments closedLaura Ellis walks us through the Patchwork package in R:
As is the focus of this post, when this package was shared on twitter, people were very excited about it. The patchwork package was created by Thomas Lin Pedersen. Not only is it incredibly easy to use, it comes with great documentation
Once you have your plots built, aligning them is rather easy.
Comments closedRyan Wade shows us how to use a pre-built sentiment analysis model with Power BI:
As of this writing, there are two pre-trained models available: one for sentiment analysis and another for image classification. This example focuses on sentiment analysis.
Both of these installations are freely available to the on-prem version of SQL Server 2017 and later. For more information on how to install these on your instance, reference this article for SQL Server Machine Learning Services and this article for pre-trained models.
Click through for step-by-step instructions.
Comments closedItzik Ben-Gan walks us through a workaround in T-SQL:
Suppose that you have a database in a platform that supports the standard unique constraint and you need to migrate that database to SQL Server. You may face issues with the enforcement of unique constraints in SQL Server if the unique columns support NULLs. Data that was considered valid in the source system may be considered invalid in SQL Server. In the following sections I’ll explore a number of possible workarounds in SQL Server.
I use a simplified version of this as an interview question, so it’s nice to see an entire article from Itzik on the topic, including a couple solutions way outside the box.
Comments closedBrent Ozar has some tips if you use RECOMPILE hints frequently:
The first query’s plan stuck around in memory, so it now shows 2 executions, and 2 total rows returned. Its row metrics are correct through the life of the stored procedure’s time in cache.
However, the second query – the one with the recompile hint – has a brand new plan in the cache, but also new metrics. You’re not just recompiling the execution plan, but you’re also not getting query plan metrics here. (That’s fine, and that part I was also kinda aware of.)
But the part that I keep forgetting is that when I’m looking at the stored procedure’s totals in sp_BlitzCache, the total, min, and max values are useless:
If the plan cache isn’t going to help, what will? Brent tells you exactly what.
Comments closedMarco Russo and Alberto Ferrari take us through the COALESCE() function in DAX:
COALESCE accepts multiple arguments and returns the first argument that is not blank. If the first argument is blank, COALESCE returns the value provided by the expression in the second argument, and so on. If all the arguments are blank, COALESCE returns blank as well.
Good for SQL users, who should already be familiar with the function.
Comments closedMadhivanan shows us how to query the results of a stored procedure:
One of my friends asked me “Is it possible to query Stored Procedure resultset like a table. ie select * from (EXEC Stored_procedure)?”
Well. Querying the resultset from the Stored Procedure like Table can be done using OPENROWSET function
This is a fairly novel approach to the problem. In the past, I’ve inserted the results of a stored procedure into a temp table, but you can only do that if the procedure itself doesn’t call INSERT INTO ... EXEC ....