A Fine Slice Of SQL Server
That SSIS change just won’t stick! There is a new business requirement, so now you have to change an existing SSIS package. You opened up a new project, imported the package, and made your changes.
But the changes just aren’t showing up. You back and look again and sure enough, the changes you made are there. You run your job (or whatever) and still the change aren’t taking effect. So what happened?
Read on for the cause and the solution.
Comments closedTibor Karaszi notes an important change to SQL Server Management Studio with the July 2016 release:
Now, exposing one more SET option can’t be bad, right? But Erland Sommarskog found out that this is checked by default. Now, using XACT_ABORT might now [sic] be a bad thing, au contraire. See this one of Erland’s series of error handle articles.
Making this change after all of these years is a little odd. Making it in a regular update is very odd. Using SET XACT_ABORT ON is a smart move in general, but there are times in which you don’t want to rollback immediately after an error; the problem is, are all of those places in your code well-documented?
Comments closedChrissy LeMaire talks about the 25 new cmdlets in SQL Server’s Powershell module:
One of the best things about these cmdlets is that it makes failed jobs a whole lot easier to find. Note that SQL Server Management Studio 2016 does not have to be installed on the server — only your workstation. That’s because the cmdlet is built on top of SMO which is available in all versions of SQL Server since 2000.
As an aside, you may be wondering what a cmdlet is, and what’s the difference between a cmdlet and what you and I write. A cmdlet is a formally written PowerShell command that’s written in C#. I’ve never written one in my life. Instead, I write Functions. They’re both essentially the same, but a cmdlet is fancier on the back end.
Powershell is the present (not the future) for administration, so it’s nice to see the SQL Server team pushing themselves a bit when it comes to cmdlets. They’ve a long way to go, but this is a nice step.
Comments closedKendra Little has a great diagram showing which indexes are disk-based and which are memory-resident:
I was looking through some terms in SQL Server documentation the other day, thinking about what it’s like to learn about SQL Server’s indexes when you’re new to the field. I jotted down a note: B-tree = Rowstore = Disk Based
And then I realized that’s not quite right.
Not all disk based indexes are traditional clustered and nonclustered indexes. Columnstore indexes are also disk based. Updatable Columnstore indexes use special rowstore B-trees behind the scenes. And Books Online says “rowstore” also refers to Memory-Optimized tables.
If you’re new to indexing, this picture will save you some learning time.
Comments closedDevin Knight continues his Power BI visualization course:
In this module you will learn how to use the Histogram, a Power BI Custom Visual. A Histogram is a column chart which shows the distribution of occurrences divided into categories, called bins. This type of chart is useful for estimating density and discovering outliers.
Another fine entry in a great series. Check it out.
Comments closedDevin Knight discusses query folding and the View Native Query feature inside Power Query:
The idea behind Query Folding is to push the logic that you built into a Power BI query back to the data source server and execute it there in it’s native language instead of doing a client side transform of the data. Why is this important? Let me give you an example. Say you have a 2 billion row SQL Server table you need to connect to in Power BI, but you want to filter to only return the last year of data. With Query Folding the filter of that data is done on the SQL Server side instead of on the client side. If Query folding did not take place than that would mean all 2 billion rows would be brought across the network only to then filtered out on the client workstation. So clearly the ideal situation is that all your queries get folded for the best possible performance, but Query Folding only works in certain scenarios.
I hadn’t heard the term “query folding” before, but the concept makes sense; in the PolyBase world, it’s “predicate pushdown.” Check out Devin’s post, as he shows how easy it is to see to what extent your query is running client-side versus server-side.
Comments closedRiddhiman shows how to use R and plotly to build charts of Federal Reserve data sets:
In this post we’ll try to replicate some of the charts created by the Federal Reserve which visualize some well known macroeconomic indicators. We’ll also showcase the new Plotly 4.0 syntax.
This is a very code-heavy blog post and is a good way to learn about plotly.
Comments closedDave Mason looks at DDL triggers:
DDL triggers and the events they handle are run within the same transaction, which can be rolled back. This is a powerful and convenient feature. It gives you the ability to programmatically “undo” undesirable events. Let’s look at a variation of the previous DROP_TABLE trigger we created. This script will create a temporary logging table, drop the previous trigger, and create it again. This time, the trigger will roll back the transaction, preventing the table from being dropped. Then it inserts a message to the log table.
There are a few gotchas here, to be sure, but DDL triggers are powerful tools.
Comments closedDustin Ryan talks about this month’s batch of Power BI updates:
Searchable slicers are also a new feature in the latest release of Power BI Desktop. A couple days ago I wrote about some of my favorite custom visuals, which included the Smart Filter by SQLBI. I think I still prefer the Smart Filter in many situations, but the search-ability of the native Slicer is definitely a nice feature to have right out of the box.
The headline is row-level security, but there are several interesting features here.
Comments closedTim Radney looks at Stretch Database functionality in RTM:
Prior to learning about this new billing method for DSU, I could make the argument that using Stretch Database would be a very cost effective method for storing cold data (unused data) into the cloud. By stretching this data into Azure, you could migrate a large portion of older data, which would decrease the size (and thus cost) of your local backups. In the event you had to restore a database, you would simply have to establish the connection to Azure for the stretched data, thus eliminating the need to restore it. However, with the minimal cost being nearly $1,000 per month for the low end DSU scale, many organizations will find that it is much cheaper to retain the data on a less expensive tier of storage within their data center and find other methods for HA such as mirroring, log shipping, or Availability Groups.
Read the whole thing. Maybe V2 of stretch databases will fix some of the biggest problems (the cost, needing to pull all of your data back down before you make any schema changes, etc.) and become a viable feature, but I can’t see it being one today.
Comments closed