Press "Enter" to skip to content

Curated SQL Posts

Set Comparisons with EXCEPT

Published 2020-04-21 by Kevin Feasel

John Sterrett walks through one of the most important set operators:

Selecting the two tables is easy if we know what change occurred and there aren’t many changes. This can get complicated quickly. Therefore, if we just want to quickly know if we have differences lets take a look at my goto method using EXCEPT. To make this example easier to read instead of using “SELECT *” I will just focus on columns that are changing. In a real example, I would want to know if any columns changed.

One of the nicest things about set comparison operators is that they innately understand the concept of NULL.

Comments closed

Auditing Logons Using Extended Events

Published 2020-04-21 by Kevin Feasel

Jason Brimhall dumps a server-side trace in favor of extended events:

Some time ago, I wrote an article for SQL Server 2008 to help determine the use of the server since SQL Server 2008 was reaching End Of Life. In that article, I shared a reasonable use of server side trace to capture all of the logon events to the server. Afterall, you have to find out the source of connections and who is using the server if you need to migrate it to a newer SQL Server version. You can read that article here.

Soon after, from various sources, I received requests on how to perform a logon audit using the more preferred, robust, venerable, awesome tool called Extended Events (XEvents). In response, I would share a login audit session to each person. In this article, I will share my login audit solution and give a brief explanation. I use a solution like this on more than 90% of my client servers and I find it highly useful.

Click through to see how.

Comments closed

Building FAQs on a Power BI Dashboard

Published 2020-04-21 by Kevin Feasel

Evan Rhodes takes us through building out a FAQ for a dashboard:

I suppose you could add a bunch of text boxes with questions and answers. But, what if you have several questions and there isn’t enough space? I’m reminded of something a fantastic boss once told me, “Never pass up an opportunity to wow someone and grab their attention with your work.” So, let’s add some wow effect to this by leveraging the bookmarks and buttons functionality.

Bookmarks and buttons allow us to create a user experience that is intuitive to the user and that allows them to navigate around the page easily by just clicking. In this case, click on a FAQ and the answer appears. Click on the FAQ again or a different FAQ… I think you get the point.

If you need this on the dashboard itself, this is probably the right way to do it—there for the one time you need it and hidden away the rest of the time.

Comments closed

The Best of Extended Events

Published 2020-04-21 by Kevin Feasel

Grant Fritchey shares plenty of links for people to learn about extended events:

However, it’s really important to me that you understand just how powerful and amazing a tool Extended Events is. Further, that you have some resources to get going on this. So, I reached out to a bunch of friends and acquaintances and just some rando’s on the internet to compile a list of the very best of their Extended Events posts.

I’ll be sharing and promoting these links in all my upcoming sessions. They’re in the published slide decks. However, I also want to share them here.

Read on for the list.

Comments closed

Operational Database Management Tools in Cloudera Data Platform

Published 2020-04-20 by Kevin Feasel

Gokul Kamaraj, et al, describe tools available to DBAs in the Cloudera Data Platform:

Cloudera provides multiple mechanisms to allow backup and recovery, including:

– Snapshots
– Replication
– Export
– CopyTable
– HTable API
– Offline backup of HDFS data

These can be run manually or scheduled using Replication Manager. Backups can also be moved to other instances of the OpDB or alternate storage targets such as AWS S3 or Azure ADLS gen 2.

Even in the Platform-as-a-Service world, there’s still plenty of scope for database administration.

Comments closed

Understanding Key Lookups

Published 2020-04-20 by Kevin Feasel

Hugo Kornelis continues a series on SQL Server plan operators:

The Key Lookup operator provides a subset of the functionality of the Clustered Index Seek operator, but within a specific context. It is used when another operator (usually an Index Seek, sometimes an Index Scan, rarely a combination of two or more of these or other operators) is used to find rows that need to be processed, but the index used does not include all columns needed for the query. The Key Lookup operator is then used to fetch the remaining columns from the clustered index.

A Key Lookup operator will always be found on the inner input of a Nested Loops operator. It will be executed once for each row found. Since the key values passed in always come from another index, the requested row will always exist (except in rare race scenarios when read uncommitted isolation level is used).

Click through for a great deal of information on key lookups.

Comments closed

Decoding Statistics Names

Published 2020-04-20 by Kevin Feasel

Jason Brimhall explains how SQL Server comes up with names for auto-created statistics:

Every now and again I am asked about the meaning behind the automatically generated names for statistics in SQL Server. The quick answer is short, sweet and really easy. I give them a quick explanation and then often refer them to the blog post by Paul Randal on the topic.

The better answer is to show them what the auto-generated names really mean, alongside the great explanation from Paul. Finally, after years of the topic being on my backlog, I am sharing a script that will help decode those names and help to prove out fully what’s in a statistic name.

The proof is in the SQL; click through to see it.

Comments closed

Adding Time Zone-Adjusted Report Execution Times

Published 2020-04-20 by Kevin Feasel

Brett Powell shows how you can display a report’s execution time in a particular time zone:

For reports being viewed by users around the world, simply modifying the footer text box expression to note that this time is UTC may be a sufficient. However, for many paginated reports the users are all in one time zone and some of these users may ask to have the time zone conversion handled within the BI solution. The example in this post targets this scenario.

Even if the report serves users in multiple time zones, it’s technically feasible to leverage the UserID global field and a simple user to time zone mapping table to provide a local report execution time to all users. However, I tend to think most projects would not want to commit the time/resources for this logic – UTC date/time is what the users would get.

If you do need local report execution time, Brett has you covered.

Comments closed

Generating Entity Framework Core Classes from a Database Project

Published 2020-04-20 by Kevin Feasel

Erik Ejlskov Jensen walks us through generating Entity Framework classes from a Visual Studio database project and from a .dacpac file:

EF Core Power Tools adds the ability to generate code directly from a Database project, without having to publish to a live database first, and having a SQL Server database engine running locally. It can also generate code from live SQL Server, Azure SQL DB, MySQL, Postgres and SQLite database. It has a large number of customization options – pluralization, renaming, file and name space choices and more, which is not available via the EF Core commands. And you do not have to install any design time libraries in your own project.

Read on for a demo of that as well as a dacpac reverse engineering tool.

Comments closed