Press "Enter" to skip to content

Curated SQL Posts

Building A Tally Table With U-SQL

Published 2017-08-23 by Kevin Feasel

Saveen Reddy shows how to create a tally (or numbers) table in U-SQL:

Many common scenarios for U-SQL developers require constructing a RowSet made up of a simple range of numbers or dates, for example the integers from 1 to 10. In this blog post we’ll take a look at options for doing this in U-SQL. In the process, we’ll get a chance to learn how to use some common U-SQL features:

  • Creating RowSets from constant values

  • Using CROSS JOIN

  • Using SELECT to map integers to DateTimes

  • Using CREATE TABLE to create a table directly from a RowSet. This is sometimes called “CREATE TABLE AS SELECT” and often abbreviated as “CTAS“.

Click through to learn more.

Comments closed

Handling Sensitive Data With Powershell

Published 2017-08-23 by Kevin Feasel

Adam Bertram shows off the data protection API in Powershell:

We need to automatically read this information without our intervention. This is where you sometimes see people get lazy and add passwords in plain text into the script. That’s a big no-no, and there’s a better way.

 PowerShell has native support for something called the data protection API (DPAPI). DPAPI is a built-in way Windows users can use certificates to encrypt and decrypt information on the fly which is perfect for PowerShell scripting. No username and password required. We simply need a certificate installed which can be self-signed.

This is about much more than storing and decoding passwords, so check it out.

Comments closed

Enabling SQL Server SSL Encryption With Powershell

Published 2017-08-23 by Kevin Feasel

Tracy Boggiano shows how to create and put into place SSL encryption certificates on SQL Server instances using Powershell:

Next we take the exported pfx file and copy it locally to the temp folder of each machine and import into the local certificate store.  Then we edit the registry with the thumbprint of the certificate.  After that you will have to restart SQL Server to get the changes to take effect.  We also clean up after ourselves and delete the pfx from the temp folder.

Note: To make this safe for production I commented out the restart of SQL Server.  Also, Get-CmsHosts cmdlet can found here.

If you’re dealing with sensitive information, enabling (and forcing!) SSL encryption is one of the easiest effective ways of securing an instance; in this case, it’s securing data in transit from SQL Server to and from the client.

Comments closed

SQL Server Features Forgotten But Not Gone

Published 2017-08-23 by Kevin Feasel

Robert Sheldon looks at five cobweb-ravaged parts of SQL Server:

Policy-Based Management (PBM) has a PR problem, even worse that DQS and MDS. A lot of people don’t know what it is, or if they do, are not sure if it’s still supported. Microsoft doesn’t even mention the feature in its list of Editions and supported features of SQL Server 2016. The PR problem is so bad, in fact, that I’ve come across community forum postings asking whether PBM still exists.

Despite its silent role, PBM is still a supported feature and appears to be available to all SQL Server editions, although I suspect it caters primarily to organizations with a large number of similar SQL Server implementations.

Microsoft introduced PBM in SQL Server 2008 to provide a policy-based system for managing one or more SQL Server instances. You can, for example, use policies to grant permissions on user databases, disable lightweight pooling, or choose an authentication mode. You can also target policies at specific SQL Server instances, databases, tables, or indexes.

Quick hits:  Data Quality Services and Master Data Services have been lapped by the competition and aren’t really competitive products anymore.  Policy-Based Management is still useful, and I wish it’d get some internal love to extend roles & features.  Management Data Warehouse is a tire fire that you should avoid at all costs.  Service Broker is useful in the right circumstances, but it needs a few big changes to become a great broker.  Unfortunately, I don’t see big changes happening and so there are superior alternatives for data flow (like Apache Kafka).  But read Robert’s take on these features.

Comments closed

Synchronizing Logins And Jobs

Published 2017-08-23 by Kevin Feasel

Ryan Adams shows five methods for synchronizing SQL logins and a couple ways of synchronizing SQL Agent jobs between instances of SQL Server:

Robert Davis wrote a great script back when he published his Mirroring book.  I started to write my own and was almost done when I contacted Robert and asked if he had dealt with SQL logins since the script only handled Windows logins.  His reply was something along the lines of, “What are you talking about? Of course it handles SQL logins”.  It turns out that the publisher didn’t get the right script version published with the book.  That’s when this post from Robert with the full script was born…

Transferring Logins

I also wrote about it HERE.

This script creates a stored procedure to handle the move and also uses Linked Servers.  If you can’t have linked servers in your environment this is not a good choice for you.  However, you can create the linked server in a SQL Agent job step prior to the step for transfer and then remove it in a job step after the transfer.  It breaks the rule but it does it fast enough maybe no one will notice.

Read the whole thing.

Comments closed

Custom SQL Server Docker Images

Published 2017-08-23 by Kevin Feasel

James Anderson shows an easy way of creating a custom Docker image which relies on some other image:

The FROM statement declares that we want to lay some instructions on top of the microsoft/mssql-server-windows image. The beauty of this approach is that when I pull down a new version of the microsoft/mssql-server-windows image, my image will be updated too. The microsoft/mssql-server-windows Dockerfile does the same thing with the microsoft/windowsservercore image.

The rest of the Dockerfile sets some meta data, downloads the installer and adds the Advanced Analytics feature.

SSIS, SSAS, SSRS or any other SQL Server feature could be added to a containerised SQL Server deployment in the same way.

With this approach, you do run the risk that upstream changes will break your image, but for something like this, it’s a very useful approach.

Comments closed

Building Temp Tables From Queries

Published 2017-08-23 by Kevin Feasel

David Fowler shows how to use dm_exec_describe_first_result_set to generate a temp table schema:

Have you ever needed to store the results of a complex query in a temp table?  How did you go about working out what the definition for that temp table should be, the columns and their data types?

It can be a bit of a pain, not to mention time consuming to have to go figuring out what all datatypes of the base tables are.

I got fed up with all that hunting around as well so as a quick blog I thought I’d share a little script that will take your query in a variable and print out a temp table definition for its result set.

Click through for the script, as well as an important comment by frequent curatee Shane O’Neill.

Comments closed

The Pain Of Multi-Statement TVFs

Published 2017-08-23 by Kevin Feasel

Andy Mallon walks through a multi-statement table-valued function in Microsoft Dynamics CRM:

Look at all those table-valued function calls! Followed immediately by a really expensive hash match. My Spidey Sense started to tingle. What is fn_GetMaxPrivilegeDepthMask, and why is it being called 30 times? I bet this is a problem. When you see “Table-valued function” as an operator in a plan, that actually means it’s a multi-statement table-valued function. If it were an inline table-valued function, it would get incorporated into the larger plan, and not be a black box. Multi-statement table-valued functions are evil. Don’t use them. The Cardinality Estimator isn’t able to make accurate estimates. The Query Optimizer isn’t able to optimize them in the context of the larger query. From a performance perspective, they don’t scale.

Even though this TVF is an out-of-the-box piece of code from Dynamics CRM, my Spidey Sense tells me that it’s the problem.

That said, Joe Sack and team are working on making multi-statement TVFs faster in SQL Server 2017.  Whether it will move the needle from Andy’s excellent advice, we’ll have to wait and see.

Comments closed

Using NLP To Find Similar Facebook Posts

Published 2017-08-22 by Kevin Feasel

The folks at Knoyd put together a word embedding example by scraping a Python Facebook group:

We are going to represent the content of a Facebook post using word embeddings and comparing the transformed posts using word mover’s distance. The combination of both have shown lower k-nearest neighbor-document classification error rates compared to other state of the art techniques.

The advantage of word embeddings is that the words which have similar meanings but don’t have any letters in common will still have similar vectors (be close) in the embedded space (e.g. lion and tiger).

There’s a good high-level discussion of techniques in this post.

Comments closed

R Services Internal Communication Mechanisms

Published 2017-08-22 by Kevin Feasel

Niels Berglund continues his R Services internals series:

When browsing for the symbols, you can use this command: x /1 *!TCP*. By using the option /1 you’ll only see the names, and no addresses. On my machine that gives me quite a lot, but there are two entries that catch my eye: sqllang!Tcp::AcceptConnection and sqllang!Tcp::Close. So let us set breakpoints at those two symbols, and see what happens when we execute our code.

The result when executing the code is that we initially break at sqllang!Tcp::AcceptConnection. Followed somewhat later by breaking at sqllang!Tcp::Close. Cool, this seems to work – let us set some more breakpoints and try to figure out the flow of events.

The first half recapitulates his previous findings, and then he incorporates new information in the second half.

Comments closed