A Fine Slice Of SQL Server
Ben Weissman shows how to write extension methods in Biml:
An AstTableNode requires a schema to be valid, which is the only information that we can’t get from the AstFlatFileFormatNode so we’re defining a variable called UseSchema and pass it to our ToAstTableNode extension method.
But… how does that extension method work? MUCH easier than you might think.
Writing an extension method in C# isn’t tough either.
Comments closedI have a post on using the APPLY operator to unpivot data:
This code works, but if you have dozens of years, it gets messy writing those case statements and you’re a bit more likely to make a mistake when refactoring code. Here’s a simpler version using CROSS APPLY:
SELECTs.Product,y.[Year],y.Quantity,y.[Value]FROM#Sales sCROSSAPPLY (VALUES(2013, [Qty2013], [Val2013]),(2014, [Qty2014], [Val2014]),(2015, [Qty2015], [Val2015])) y([Year], Quantity, [Value]);It’s a little easier to read than the other version, and adding additional years is pretty straightforward. That makes for a great tip when you’re trying to refactor poorly-thought-out tables or bring into your system potentially well-thought-out flat files.
APPLY is an elegant solution to so many different classes of problem.
Comments closedAndy Leonard shows us how to use Biml to remove spaces from flat file column names:
This Biml declares an AstFlatFileFormatNode named ffformat and sets it to the FileFormat named “FFCM Provider Data” found the RootNode’s FileFormats collection. I next loop through each column in the ffformat AstFlatFileFormatNode object. I use Biml to generate the <Column> object, replacing the spaces with an empty string for the Name attribute. The results in Mist appear as shown below
I loved the “I had 5 minutes, so I decided to fix it with Biml” line. Both funny and true.
Comments closedPatrick LeBlanc explains a confusing part of KPIs in Power BI:
Now, take a look at the KPI visual. What happened? First, you should notice a trend line on the KPI that depicts Sales Amount for each month. This is cool and a great feature of the visual, but wait. Why doesn’t the Indicator value and the goal match the values in the Card? Now I see the confusion.
I appreciate that Patrick put in several embedded reports to show us exactly what’s going on.
Comments closedGinger Grant explains several problems with nested views:
In addition to the performance reasons, there are other reasons not to use nested views. Supporting and maintaining a nested views can be a nightmare. If there is an issue with the accuracy of the data, finding the problem is just that much harder. And what about when you go to fix the problem? Then you need to test all of the places the view is called. I worked with some code recently where a view was created on a set of data just to create 3 new values based up 3 case statements. That view was nested many levels below where the field was actually called. I know people from a development background look at nested views as modular development. Don’t. TSQL is not the same as object oriented code.
Read the whole thing. She also has helpful links digging further into the topic.
Comments closedAlways Encrypted uses two types of cryptographic keys: column encryption keys (CEKs) and column master keys (CMKs). A column encryption key is used to encrypt data in an encrypted column. A column master key is a key-protecting key that encrypts one or more column encryption keys. A column master key is stored in a key store that is external the database and is accessible to a client application, for example Windows Certificate Store or Azure Key Vault. The database server does not store the keys of either type in plaintext – it only contains metadata about the keys in column master key and column encryption key metadata objects.
Given that they’re supporting database projects, I wonder if Integration Services is far behind.
Comments closedI have a blog post on some troubles I’ve had with the FORCE EXTERNALPUSHDOWN hint:
As soon as I kick this off, I get an error:
Msg 7320, Level 16, State 110, Line 1
Cannot execute the query “Remote Query” against OLE DB provider “SQLNCLI11” for linked server “(null)”. Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints.Well, that’s not good… Checking sys.dm_exec_compute_node_errors gives me four error rows with stack trace results, none of which seems very useful to me (as opposed to a Microsoft support tech).
I don’t have any good answers in this blog post, so I’m hoping to learn more and report back later.
Comments closedEwald Cress looks at the SOS_WaitableAddress class next in his series on internals:
All the synchronisation mechanisms I have discussed so far have one things in common: in order to be globally visible, they involve synchronisation objects embedded within the things they protect. So for instance, if we want to protect the global scheduler list with a spinlock, that spinlock lives within the global scheduler list, and allocating the spinlock’s storage (lightweight as it is) is the responsibility of whoever creates that list. But what if there were millions of things we might occasionally want to lock, and we don’t want to embed a lock within each such item due to the hassle and/or overhead involved? What if we just wanted a publicly visible corkboard upon which we can pin notes describing the items which are currently locked?
This is a rather different locking structure than we’ve seen so far. Read on for details, including magic within the Signal method.
Comments closedJos Dirksen uses Spark and D3 to count and graph swear words in movies:
So how do we do this? Well, the first thing to do is get the number of swearwords per minute. I mentioned that for the original article someone just counted every swearwords, in our case, we’re just going to parse a subtitle file, and extract the swear words from that.
Without going into too much detail, you can find the code I’ve experimtend with in this gist (it’s very ugly code, since I just hacked something together that worked).
Jos includes counts for four movies. This link does contain a few bad words, but if you get past that, it’s a good pattern for analyzing word counts in general.
Comments closedErik Darling notes that databases using Transparent Data Encryption now support backup compression:
First, the database without a Max Transfer Size at the bottom was a full backup I took with compression, before applying TDE. It took a little longer because I actually backed it up to disk. All of the looped backups I took after TDE was enabled, and Max Transfer Size was set, were backed up to NUL. This was going to take long enough to process without backing up to Hyper-V VM disks and blah blah blah.
The second backup up, just like the blog man said, no compression happens when you specify 65536 as the Max Transfer Size.
You can see pretty well that the difference between compressed backup sizes with and without TDE is negligible.
Check it out, including the table Erik put together. I’m glad that backup compression is now supported, although I’m kind of curious how they can do that while retaining encrypted backups—are they decrypting data, writing to backup (and compressing), and then encrypting the backup? That’d be worth checking out with a hex editor.
Comments closed