Press "Enter" to skip to content

Curated SQL Posts

Failure To Connect With A SQL Login

Published 2018-01-09 by Kevin Feasel

Bert Wagner hits on the most common reason why you might fail to connect with a SQL authentication login:

I thought it would be best to start with a clean slate so I created a new SQL login and database user so that I could definitively figure out which permissions are needed.

Normally I use Windows Authentication for my logins, but this time I thought “since I’m getting crazy learning new things, let me try creating a SQL Login instead.”

After I created my login, I decided to test connecting to my server before digging into the permissions. Result?

After the fifth or sixth time it happens to you, you start making that the first thing you check.

Comments closed

Fun With Undocumented Trace Flags

Published 2018-01-09 by Kevin Feasel

Joe Obbish has a list of 45 undocumented trace flags:

Below is a list of trace flags which, as far as I can tell, have never been publicly documented. I did not fully investigate many of them and many of the descriptions are just guesses. I make no guarantees and none of these should be used in production. All tests were performed on SQL Server 2017 CU2 with trace flags enabled at the global level.

This is combining a bit of database archaeology and database anthropology.

Comments closed

Blue-Green Deployments

Published 2018-01-09 by Kevin Feasel

Michael J Swart has started a new series on online deployments and covers the blue-green deployment architecture:

When using the Blue-Green method, basically nothing gets changed. Instead everything gets replaced. We start by setting up a new environment – the green environment – and then cut over to it when we’re ready. Once we cut over to the new environment successfully, we’re free to remove the original blue environment. The technique is all about replacing components rather than altering components.

Check it out for a great explanation, not only of how true blue-green doesn’t jibe well with databases, but how to get it to work well enough.

Comments closed

Goal Tracking With Power BI

Published 2018-01-09 by Kevin Feasel

Stacia Varga uses New Year’s resolutions to motivate a Power BI tutorial:

As I was thinking about this relationship between goals and feedback, I thought Microsoft Power BI would be a great tracking tool. It’s free, so use it! In years past, I used spreadsheets or checklists in journals or OneNote, any of which is a fine way to accumulate a comprehensive list of all that a person wants to do. However, I never measured progress, thereby denying myself feedback. Consequently, I’d let myself get sidetracked during the year.

This year I promised myself I’d try a different approach and thought I’d share the process with you through a series of blog posts. Although I’m going to discuss goal-tracking from a personal point of view, you can also use the same techniques for your business-oriented goals. Either way, I hope you learn something about Power BI along the way and are inspired to do some goal-setting of your own.

It’s a good use of Power BI.

Comments closed

Columnstore Functionality Per Edition

Published 2018-01-09 by Kevin Feasel

Niko Neugebauer looks at how columnstore indexes differ between SQL Server Standard Edition, Express Edition, and Enterprise Edition:

One rather small (relatively other features, as I imagine), but an incredibly useful improvement was described in Columnstore Indexes – part 109 (“Trivial Plans in SQL Server 2017”) – is the ability to automatically produce Fully Optimised execution plans for the Database, which compatibility level is set to 140.

Running on both instances (Standard & Express), the following script, while altering the compatibility level between 140 (SQL Server 2017) & 130 (SQL Server 2016), will produce different execution plan for the SELECT COUNT_BIG(*) operation – the fast one (with FULL optimisation in 140 compatibility level) and slow one (with TRIVIAL optimisation in 130 compatibility level):

I am happy that this feature has got no Edition dependence, this is a needed improvement that simply increases the value of the offer and can actually be achieved in a lot of different ways, event without parallelism kicking in.

Niko has also helpfully provided a table at the end of the post to summarize his findings.

Comments closed

2018 Data Professional Survey Results

Published 2018-01-09 by Kevin Feasel

Brent Ozar has posted data for the 2018 Data Professionals Survey:

A few things to know about it:

  • The data is public domain. The license tab makes it clear that you can use this data for any purpose, and you don’t have to credit or mention anyone.

  • The spreadsheet includes both 2017 & 2018 results. For the new questions this year, the 2017 answers are populated with Not Asked.

  • The postal code field was totally optional, and may be wildly unreliable. Folks asked to be able to put in small portions of their zip code, like the leading numbers.

Looks like I’m going to add one more thing to the to-do list for this week…

Comments closed

Know Your Audience

Published 2018-01-08 by Kevin Feasel

I continue my series on dashboard visualization:

Before you build a dashboard, you have to know your audience.  If you don’t know who your viewers will be and where their interests lie, you run the risk of building a dashboard which fails to serve their needs.  When that happens, people stop looking at your dashboard.  In order to increase the likelihood that your dashboard will be useful, I have a few critical questions:

  1. Who is your intended audience?
  2. How will your intended audience use your dashboard?
  3. What actions do you want them to take as a result of what they see?
  4. Are you showing the right measures in the right way?
  5. What cultural differences might matter?

The rest of this post will drill into each of these concepts.

These are the types of questions which can make the difference between a dashboard people love and a dashboard people never use.

Comments closed

Voice-Driven Workflow With Apache MiNiFi

Published 2018-01-08 by Kevin Feasel

Tim Spann shows how to take off the shelf hardware and build a voice-activated workflow:

Using a Raspberry Pi 3 with Google AIY Voice Kit is an easy way to control data flows in an organization.

The AIY Voice Kit is a good way to prototype voice ingestion. It is not perfect, but for low-end, inexpensive hardware, it is a good solution for those who speak clearly and are willing to repeat a phrase a few times for activation.

I was able to easily add a word for it to react to. I also have it waiting for someone to press the button to activate it.

It seems like a fun DIY project.

Comments closed

More SSMS Tips

Published 2018-01-08 by Kevin Feasel

Wayne Sheffield continues his series on SSMS tips and has four new posts for us.

Check out his posts on pinned tabs, easily opening or copying query file paths, template variables, and map mode:

Have you ever had a long script that you are trying to scroll through? Do you wish that you could see a preview of the scroll area to easily see what section you are in? Well, starting in SSMS 2016, you can. Just right-click the vertical scroll bar, and select “Scroll Bar Options…”

Of the four, map mode is my favorite.  It’s extremely helpful when going through large files.

Comments closed

Inventive Uses Of Python In SQL Server 2017

Published 2018-01-08 by Kevin Feasel

Gerald Britton has a couple non-ML uses for Python in SQL Server 2017:

One of the new features announced with SQL Server 2017 is support for the Python language. This is big! In SQL Server 2016, Microsoft announced support for the R language – an open source language ideally suited for statistical analysis and machine learning (ML). Recognizing that many data scientists use Python with ML libraries, the easy-to-learn-hard-to-forget language has now been added to the SQL Server ML suite.

There’s a big difference between R and Python though: R is a domain-specific language while Python is general purpose. That means that the full power of Python is available within SQL Server. This article leaves ML aside for the moment and explores a few of the other possibilities.

Gerald has two good cases for using Python with SQL Server.  Funny enough, they’re both also easily supported in R, so you could do this in 2016 as well.

Comments closed