Press "Enter" to skip to content

Curated SQL Posts

3D Maps In Power BI

Published 2015-11-30 by Kevin Feasel

Reza Rad gives us an introduction to 3D visualization using Power BI Desktop:

Power BI team did a great step forward with adding 3D map visual in Power BI Desktop. Thank you Microsoft Power BI team because of that! It is really useful for some scenarios that users need to see visualization on 3D map. However this feature is far behind Power Map features for story telling, creating tours, play axis, and many other features. I believe that soon many of these features will be added into Power BI. So I say Power BI said hi to 3D maps, but please be quick on that Microsoft team because Power Map raised expectations of our clients to a very high level! They will be looking for same features (at least) in Power BI desktop.

Looks like there’s still some work yet to be done.

Comments closed

Build Your Own Statistics

Published 2015-11-27 by Kevin Feasel

Dan Holmes shows how to build custom statistics:

There is a performance benefit to imported stats. The cost to compute the stats are on an “offline” table. The only downtime for the production table is the duration of the stream import.

This process does use undocumented features and it looks like it could be dangerous, but remember there is an easy undo: the update statistics statement. If something goes wrong, the statistics can always be updated using standard T-SQL.

Scheduling this code to run regularly can greatly help the optimizer produce better plans given a data set that changes over the tipping point but not enough to trigger a statistics update.

This feels like the time of thing you want to know because it’ll come in handy once, but if you feel the need to use it frequently, that may not be the best choice.

Comments closed

Use The Alt Key

Published 2015-11-27 by Kevin Feasel

Andy Mallon talks Alt key:

Everyone knows you can use SHIFT + [Left/Right Arrow] to highlight text. But you can also use ALT + SHIFT + [Up/Down/Left/Right Arrow] to select a block of text or even make a vertical selection to insert a block of text on multiple lines. Or you can use ALT + [Mouse Drag] to make a block selection with your cursor.

Notepad++ works the same way.  Every once in a while, I’ll run into a scenario in some tool which doesn’t implement Alt key functionality—especially certain non-Microsoft platform database products—and it will hurt a little bit inside.

Comments closed

Error Handling

Published 2015-11-27 by Kevin Feasel

Continuing my “classics” series, Erland Sommarskog has a three-part series (with three appendices) on error handling that will take up your entire weekend:

Why do we have error handling in our code? There are many reasons. In a forms application we validate the user input and inform the users of their mistakes. These user mistakes are anticipated errors. But we also need to handle unanticipated errors. That is, errors that occur because we overlooked something when we wrote our code. A simple strategy is to abort execution or at least revert to a point where we know that we have full control. Whatever we do, simply ignoring an unanticipated error is something we should never permit us. This can have grave consequences and cause the application to present incorrect information to the user or even worse to persist incorrect data in the database. It is also important to communicate that an error has occurred, lest that the user thinks that the operation went fine, when your code in fact performed nothing at all.

Error handling is a crucial part of development.  And given that SQL Server has…peculiarities…when it comes to error handling, I highly recommend reading this series.

Comments closed

Videography Gear

Published 2015-11-27 by Kevin Feasel

Doug Lane has an outstanding post on the types of equipment you should look at if you’re getting into technical presentations:

Your choice(s) here will drive what equipment you need to buy in order to produce a high-quality video on a reasonable budget. You don’t want to overspend on lighting and camera equipment if you’re never going to be shown bigger than a 240×180 pocket in the corner. Likewise, you may not need a USB microphone if you’re going to shoot mostly studio video.

As someone thinking about getting into podcasting and webcasting, this is a top-notch set of advice.

Comments closed

Calculating Feast Days

Published 2015-11-26 by Kevin Feasel

Phil Factor generates feast days:

I’ve been determined to produce a SQL Expression that was able to tell you when all the feasts and saints days are. In the following example, I’ve only put the major feast days that were generally celebrated in Britain before the reformation, but it is very easy to add or take away what I’ve given you to taste.

A fitting topic for America’s premier feast day.

Comments closed

More On Temporal Tables

Published 2015-11-26 by Kevin Feasel

Newly-minted Canadian citizen Randolph West’s latest article is on modifying temporal tables:

Last week I demonstrated how temporal tables in SQL Server 2016 work. If you have implemented a history table—populating it with triggers or stored procedures—it works the same way.

This week, we are going to look at how to modify a temporal table that already has data in the history table.

If you’re using temporal tables for auditing, it’s important to know that yes, data can be modified.

Reza Rad also has a recent blog post on temporal tables:

Temporal tables are new type of database tables introduced in SQL Server 2016, these tables are system-versioned and keep history of changes (insert, delete, update) of everything happened on data rows. Retrieving change log from these tables are easy. These tables can simply tell you what was the data at specific point of the time in the table. These tables works with datetime2 columns to keep FROM DATE and TO DATE information of each change. This means these tables can be used for implementing changes in dimensions, yes you know what it called; Slowly Changing Dimension!

Slowly-changing dimensions in an OLTP scenario was the first use I could think of for temporal tables, so I’m glad Reza put this article together.

Comments closed

Connecting To SQL Server From Linux

Published 2015-11-26 by Kevin Feasel

Erland Sommarskog has an article on ways in which you can connect to SQL Server from a Linux (or UNIX) box:

TDS — Tabular Data Stream — is the protocol that SQL Server talks with its clients. This is a proprietary protocol, owned by Microsoft (and Sybase, who have their version). Nevertheless there is exists FreeTDS which originally was a reverse-engineering effort of TDS. Now when Microsoft has published the TDS specification, they should be able to repair any cracks they may have. Check out the FreeTDS home page for further details. There appears to be a DBD::FreeTDS that goes along with it.

I used FreeTDS to connect to SQL Server from RStudio, so I endorse that method.

Comments closed

Happy Thanksgiving

Published 2015-11-26 by Kevin Feasel

Curated SQL will not be taking Thanksgiving off.  To compensate for the pace of blog posting activity usually drops around Thanksgiving, I’ll link to a few classic articles.  That way, even if you’re in the office, you’ll have something to occupy that extra time.

Comments closed

Calculating Partition Sizes

Published 2015-11-25 by Kevin Feasel

Rolf Tesmer has a nice series on partitioning going. His latest entry involves calculating partition sizes in advance:

Sometimes (just sometimes) you need to calculate the size your table partitions upfrontbefore you actually go to the pain and effort of partitioning (or repartition) a table.  Doing this helps with pre-sizing the database files in advance instead of having them auto-grow many many times over in small increments as you cut data over into the partitions.

Check out the entire series.

Comments closed