Press "Enter" to skip to content

Curated SQL Posts

Gaps in Identity Columns

Published 2019-07-30 by Kevin Feasel

Josh Simar doesn’t like gaps in identity columns brought about by rollbacks:

At the end of this you can see that those records were in the table before the rollback as evidenced by the now 2000 records in the table. However we ran our identity checker after the rollback and we’ll see some interesting results because of that.

Checking identity information: current identity value '2000', current column value '1000'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The rollback did absolutely nothing for our identity and left it as is.

The short answer is that this is expected and reasonable behavior. Don’t expect identity integers to be sequential; the best you’ll get (assuming no resets or overflows) is a guarantee that they’ll be monotonically increasing. I left the long answer as a comment, currently awaiting moderation.

Comments closed

Using the Cosmos DB Change Feed

Published 2019-07-29 by Kevin Feasel

Hasan Savran shows us how we can use Cosmos DB’s change feed to track changes to documents in a container:

This is great but I want to do more than that. How am I going to access to changed data? What should I do if there is more than one change or insert? In my case, I need to access to SensorCode attribute so I can do something about this alert. To answer these questions, you need to know more about the Azure Functions. If you can see the number of modified documents by this code, that means you are done with Change Feed functionality. First, we need some kind of loop so if the code can process multiple changes. To do that, I will use a simple foreach loop.

The thing which comes to mind when I hear about Cosmos DB’s change feed is Kafka, with that immutable log of actions you can read through.

Comments closed

Spark Access Control in Qubole

Published 2019-07-29 by Kevin Feasel

Achuth Rajagopal and Shridhar Ramachandran show off the Spark Data Access Control Framework on Qubole’s platform:

With these requirements in mind, we decided to implement Hive Authorization as our first Policy Manager. Hive Authorization policies are stored in the Qubole Metastore which acts as a shared central component and stores metadata related to Hive Resources like Hive Tables. We enhanced Spark to honor the policies stored in the Qubole Metastore while accessing Hive Tables or for adding and modifying those policies.

In summary, we implemented a SQL standard access control layer identical to what is present in Apache Hive or Presto today. The following sections detail the architecture and provide an example that illustrates how it works.

Click through to learn more.

Comments closed

Data Virtualization and Power BI

Published 2019-07-29 by Kevin Feasel

Gerhard Brueckl explains how Power BI can act as a data virtualization engine:

But lets examine what we currently have in Power BI:
– a semantic layer and data modelling capabilities
– access to various data sources via Direct Query (remember, we do not want to load any data!)
– ability to combine data from those sources

The last part is the most important one here and you may wonder what I am talking about. And you are right, by default a DQ model is only linked to one data source at a time but you can add other data sources manually in the Power Query editor! 

This is where it get’s interesting and what this blog post is about.

Read the whole thing.

Comments closed

Filtering in DAX

Published 2019-07-29 by Kevin Feasel

Matt Allington takes us through the FILTER() function in DAX:

FILTER() is most often used as a filter parameter within the calculate function. OK, so now it is getting confusing. FILTER() is a function. There is another use of the word filter in DAX, and that is as a parameter in CALCULATE. Let’s look at the syntax

FILTER Syntax: FILTER(<table>, <true/false test>) CALCULATE Syntax: CALCULATE (<measure expression>, <filter parameter>, <filter parameter>,…)

This is one of the most useful functions in DAX and one you really want to know well.

Comments closed

Data Aggregation with Powershell

Published 2019-07-29 by Kevin Feasel

Jess Pomfret shows us how we can aggregate data using Powershell:

As a SQL Server DBA, aggregating data is first nature.  I can easily throw together some T-SQL if I need to get the average sales per product group, or perhaps the number of employees hired per month. Yesterday I was writing a little PowerShell when I came across a problem. I needed to get the size of my database data and log files for several databases on a server, when I realized I didn’t know how to group and sum this data in PowerShell.

Click through to learn how.

Comments closed

Undocumented Commands Can Change

Published 2019-07-29 by Kevin Feasel

Thomas Rushton warns us that undocumented commands in SQL Server are liable to change without notice:

I don’t have every version available to test, but it does appear that the record indicating Containment State is mis-spelled in SQL Server 2012 as “dbi_ContianmentState”, and then corrected in SQL 2014 and later. It’s a good job I’m not relying on it for anything.

So, yes, don’t rely on undocumented functions – as they may change without notice.

If there’s a documented method, use that one. If the only method available is undocumented, you can still use it, but be sure to test it with each release—that is, major release, service pack, or cumulative update.

Comments closed

Using the Power BI Visual Header Tooltip

Published 2019-07-29 by Kevin Feasel

Prathy Kamasani gives us several good uses of the Power BI visual header tooltip:

When we look at data journalism posts, most of the times they have annotations, explaining what visual showing or talking about measures. Again most of these data storeys are used for paper. But in the digital world, we do see these annotations more interactively. It is nice to have this kind of lil annotations for everyday reporting as well, and Tooltip Icon can be used for that purpose. Another thing is using canvas space wisely, it is important, and having this kind of hint helps us on saving the canvas space.

Click through for instructions on how to enable this as well as smart ways to use them.

Comments closed

Automated Alert Emails

Published 2019-07-29 by Kevin Feasel

Max Vernon shows how you can use the SQL Server Agent to send automated e-mails on alerts:

SQL Server Agent provides a great mechanism for sending alerts via email in response to various events. Once you’ve setup Database Mail, and configured a SQL Server Agent Operator, you should add alerts for severe errors that affect the health of your SQL Server. Creating Alerts can be tedious, but automating Alerts is simple, with the easy code below that automates creating alerts in response to critical events. Automating alerts is important because it provides a standardized Alert configuration that can be used by all the SQL Servers in your organization.

Read on for the script.

Comments closed

SSMS 18.2 Available

Published 2019-07-26 by Kevin Feasel

Dinakar Nethi announces SQL Server Management Studio version 18.2:

We are excited to announce the release of SQL Server Management Studio (SSMS) 18.2. For this update, while we added some features, our focus was dedicated to fundamentals such as stability, reliability, performance, etc.

You can download SQL Server Management Studio 18.2 today.
Some of the new features in SQL Server Management Studio include:

– Intellisense/editor: Added support for data classification
– Query execution: Added a completion time in the messages to track when a given query completed its execution.
– ShowPlan: Added new attribute in query plan when the inline scalar UDF feature is enabled.

There are several bugfixes in there as well.

Comments closed