Press "Enter" to skip to content

Author: Kevin Feasel

SELECT INTO With UNION

Published 2016-03-14 by Kevin Feasel

Jason Strate shows us that you can use a UNION (or UNION ALL) to insert into a temp table:

What makes this interesting is when I am using UNION to join the results. How do you place a final resultset from a UNION, EXCEPT, or INTERSECT into a temporary table using SELECT INTO? Where does the INTO portion of the query go?

This is actually a pretty simple thing to do. The INTO for the SELECT INTO goes into the first query of the set. An example of UNIONing the results from sys.dm_exec_query_stats and sys.dm_exec_query_stats into a temporary table is provided in listing 1.

No subqueries are necessary here.

Comments closed

Sloan Digital Sky Survey

Published 2016-03-11 by Kevin Feasel

Joseph Sirosh and Rimma Nehme show a SQL Server use case, walking us through the Sloan Digital Sky Survey:

Astronomers wanted a tool that would be able to quickly answer questions like: “find asteroid candidates” or “find other objects like this one”, which originally gave the motive to build the SQL-based backend. Indeed, right from the beginning Jim Gray asked Alex Szalay to define 20 typical queries astronomers might want to ask and then together they designed the SkyServer database to answer those queries. The anecdote is that the conversation went as follows:

Jim: What are the 20 questions you want to ask?
Alex: Astronomers want to ask anything! Not just 20 queries.
Jim: Ok, start with 5 queries.
[it took Alex 30 minutes to write them all down]
Jim: Ok, add another 5 queries.
[it took Alex 1 hour to write them all down]
Jim: Ok, now add another 5 queries.
[Alex gave up and went home to think about them]
…
Alex (said later): In 1.5 hours, Jim taught me a lot of humility!
…
Alex (said later): It also taught us the importance of long-tail distribution and how to prioritize.

This is my favorite part of the article.

Comments closed

Forms Of Text

Published 2016-03-11 by Kevin Feasel

Kenneth Fisher explains his blog header, which shows different ways to manipulate text in SQL Server:

When I started this blog a friend of mine suggested I write a really complicated query as a header. Now I’m not sure how complicated it really is, but I find it fairly amusing, and the whole point of it is to manipulate some text to generate a different set of text. So this seems like a good time to go through it and explain what’s going on.

There’s a bit to unpack, but it’s a fun experiment.

Comments closed

Check Your Clocks

Published 2016-03-11 by Kevin Feasel

Thomas Rushton reminds us to check our clocks and our SQL Agent jobs:

At 1am the time jumps straight to 2am. Got any jobs scheduled to run at 01:30? They ain’t going to happen. I hope they weren’t important.

He also has the start of a script which helps fix timing issues, either from losing an hour in spring or gaining an hour in fall.  This is a timely reminder (no pun intended) that Daylight Savings Time begins on Sunday, March 13th this year in the US and March 27th in most of Europe.

Comments closed

Availability Group Changes

Published 2016-03-11 by Kevin Feasel

Allan Hirt digs into SQL Server 2016 Availability Group changes:

What is a distributed availability group? Distributed availability groups allows you to create two different AGs on different Windows Server failover clusters (WSFCs) but join them together – if you will, an AG of AGs. This is great for disaster recovery scenarios where you do not want to worry (more than you should) about things like voters and quorum in a  single WSFC which could make the configuration more complex. This also allows for different versions of Windows Server (one WSFC is Windows Server 2012 R2 and another Windows Server 2016). Heck, it could even facilitate migrations to new hardware/the public cloud/virtualization assuming the same major version of SQL Server. It’s a cool feature. You can only manually fail over the AG between the WSFCs, but that’s OK, and you do need a listener for each AG. If you do not plan on using a listener, you cannot create a distributed AG.

Side note:  when I read DAG, I think directed acyclical graph.  Maybe I’m just weird that way…

Main note:  the idea of “seeding” an Availability Group sounds wonderful.

Comments closed

Restoring A Striped Backup

Published 2016-03-11 by Kevin Feasel

Steve Jones shows us how to restore a backup striped across multiple files:

Each of these is part of a striped backup, a piece of a backup file. To restore the backup, I need all the files to reassemble the backup. This is fairly simple, but you should be aware of how this works and how to perform a restore.

In my case, you can see I have 7 files for each database. They are the same name with an _0x at the end, with x being the number of the file.

Take Steve’s advice and script out the restore process.  That way you know how to do it next time, can start building automation scripts, and can make your life easier.

Comments closed

Mockaroo

Published 2016-03-11 by Kevin Feasel

Steph Locke tells us about a way to mock data for R:

Mockaroo is a really impressive service with a wide spread of different data types. They also have simple ways of adding things like within group differences to data so that you can mock realistic class differences. They use the freemium model so you can get a thousand rows per download, which is pretty sweet. The big BUT you can feel coming on is this – it’s a GUI! I don’t want to have spend time hand cranking a data extract.

Thankfully, they have a GUI for getting data too and it’s pretty simply to use so I’ve started making a package for it.

Steph is working on an R package, so this is pretty exciting.

Comments closed

DATETIME2 Partition Elimination

Published 2016-03-11 by Kevin Feasel

Kendra Little shows that DATETIME2 implicit conversion can prevent partition elimination:

SQL Server is implicitly converting my date value to DATETIME2(7). That is a larger, more precise value than the data type I have in the table–FakeBirthDateStamp is DATETIME2(0).

That data type mismatch is preventing partition elimination!

This is a nasty issue to catch in production, especially after you spend a bunch of time arguing with devs that DATETIME2 is the way of the future, that it’s better because of the variable precision, etc.

Comments closed

R Tools For Visual Studio Launched

Published 2016-03-10 by Kevin Feasel

R now integrates into Visual Studio:

RTVS is an IDE and as such you can use it with any recent version of R such as 3.2.x. If you install the free Microsoft R Open, you automatically get some turbo options such as threading support on multi-processor machines, providing significant speedup for a variety of analytical functions, as well as package collections check-pointed to a particular date/version. Microsoft R Server provides Big Data support and additional advanced features that can be used with SQL Server.

This is an early release, so expect a few bugs and some missing functionality.  It also isn’t RStudio—it’s RStudio several years ago.  But what it does nicely is integrate with the rest of your stack:  you can tie together the R code, the C#/F# code which helps clean data, the SQL Server project which holds your data, etc. etc.

Comments closed

SSMS February Preview

Published 2016-03-10 by Kevin Feasel

Aaron Bertrand notes that there’s a new SSMS preview available:

Gianluca Sartori (@spaghettidba) blogged about the issues you may have noticed with older versions of Management Studio on HiDPI, UQHD, or 4K/5K screens in his post, “SSMS in High-DPI Displays: How to Stop the Madness.” Essentially, Windows tries to scale things for your DPI settings and, depending on the technology used to render your fonts and dialogs, this can end up with ugly and sometimes unusable screens. Gianluca’s fix is to make a registry change and add a manifest file to the SSMS directory so that SSMS no longer tries to render Windows’ scaling changes. In the latest builds of SQL Server 2016, SSMS now acts as if the manifest file were in place. Take a look at the following image:

That’s music to my ears; I use a high-resolution laptop and before Gianluca’s solution, it was impossible to use SSMS.  I’m looking forward to SSMS 2016, but probably won’t move until the add-ons I use are supported; I’ve grown to like them too much to make the jump, even on a trial basis.

Comments closed