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 ....
Ashish Kumar and Jorge Villamariona take us through data lakes and data catalogs:
Any data lake design should incorporate a metadata storage strategy to enable business users to search, locate and learn about the datasets that are available in the lake. While traditional data warehousing stores a fixed and static set of meaningful data definitions and characteristics within the relational storage layer, data lake storage is intended to support the application of schema at read time with flexibility. However, this means that a separate storage layer is required to house cataloging metadata that represents technical and business meaning. While organizations sometimes simply accumulate content in a data lake without a metadata layer, this is a recipe for an unmanageable data swamp instead of a useful data lake. There are a wide range of approaches and solutions to ensure that appropriate metadata is created and maintained. Here are some important principles and patterns to keep in mind. Single data set can have multiple metadata layers dependent on use cases. e.g. Hive Metastore, Apache Glue etc. Same data can be exported to some NoSQL database which would have different schema.
Having a bunch of data isn’t helpful if you don’t know where it is, how it’s formatted, or anything else about the data.
Comments closedJohn Mount has some advice if you’re doing cross-validation in Python:
Here is a quick, simple, and important tip for doing machine learning, data science, or statistics in Python: don’t use the default cross validation settings. The default can default to a deterministic, and even ordered split, which is not in general what one wants or expects from a statistical point of view. From a software engineering point of view the defaults may be sensible as since they don’t touch the pseudo-random number generator they are repeatable, deterministic, and side-effect free.
This issue falls under “read the manual”, but it is always frustrating when the defaults are not sufficiently generous.
Click through to see the problem and how you can fix it.
Comments closedJason Brimhall takes us through some of the internals of Query Store as exposed by Extended Events:
One of my favorite questions to ask during some of presentations on XE is “What was the first version of SQL Server to have Query Store?” You can imagine the wide array of answers but what is interesting is how often the correct answer is always missed. I hear lots of answers for 2012, some answers for 2017 and somewhere in between for 2016. But never does the correct answer pop up.
Right now, I hope you are scratching your head at that last statement. You see, the question is somewhat of a trick question. The first version of SQL Server that has QDS is SQL Server 2014. However, the first version where you can actually use it is SQL Server 2016. This fun fact is visible when we start exploring SQL Server from the realm of XE. Let’s take a look.
Read the whole thing.
Comments closed