SQL Server 2016 and Azure SQL Database implement a new feature called Temporal Tables, not to be confused with Temporary Tables. This feature implements in a common sense fashion the needs of database applications to maintain a history of changes not just for auditing but for the purpose of analytics.
Temporal Tables allow a user to leverage the FOR SYSTEM_TIME clause when querying a table which was created with the SYSTEM_VERSIONING = ON. Users can also query the temporal history table directly, which is necessary for history values with a duration of zero (e.g. rows updated multiple times in a single transaction).
In honor of Super Bowl 50 I put together a quick visualization with Power Map in Excel 2016. We can see the scores of each team by the size of the dot on their cities. We can also see the number of people in attendance as the height of the bar on the hosting city.
Let me start off by thanking Manuel Quintana for his providing quick resolution to this mystery. I also want to thank the rest of the team at Pragmatic Works Consulting for welcoming me to the team as well as providing their input on this mystery.
“Manuel is like the Seal Team 6 of support people :)” -Mitchell Pearson
In addition to welcoming me to the team Pragmatic Works provides me with the opportunity to get to know Task Factory and Pragmatic Workbench. I was excited to get better acquainted with the latest edition of Task Factory.
The first thing I did was install Visual Studio 2015 so that I could install SSDT. According to MSDN this is a prerequisite. Also according to MSDN it is necessary to install SSDT as update through the Extension and Updates dialog of the Tools menu.
I found it odd that after installing SSDT and launching Visual Studio I wasn’t able to find Integration or Analysis services projects in the new project dialog. I did see SQL Server database projects but that wasn’t going to satisfy my urge to jump into to Task Factory.
I allowed myself to test a hypothesis about the order of installation (I installed Task Factory before SSDT). After reinstalling Visual Studio and SSDT I still didn’t find my templates.
I did some more reading and skimming through the annals of TechNet, MSDN, and Microsoft’s support pages. I found a footnote to a table that stated:
1 There are no BI templates in the product release version of SSDT for Visual Studio 2015. You can either choose the Preview or use a previously released version of SSDT. See Previous releases of SQL Server Data Tools (SSDT and SSDT-BI) for installation links.
Aha! I need to install a previous version! I proceeded to install Visual Studio 2012 and SSDT for Visual Studio 2012. But still no templates… another link, another link… another link… How many SSDTSetup.exe file downloads are there.
In the end Manuel pointed me to SSDT_VS2012_x86_ENU. As you may have guessed. SSDT is now two separate things SSDT and SSDT-BI. I installed SSDT-BI an now I can create BI Projects in VS 2012. An interesting post script to this mystery is that if I want to create BI projects in 2015 I can install the December 2015 Preview of SQL Server Data Tools.
Yesterday, working with a colleague the topic got steered towards the use of snippets in SQL Server 2012+. My reflex was to let my colleague know that my blog is wonderful source for all things SQL Server. Much to my chagrin, for all of the snippets I have, I have written a blog post about it.
Visual Studio has had code snippets for a decade now, but SQL Server only got them a version and a half ago. I might imagine that having templates was the excuse given for not pursuing them but I am glad we have them now. The tools menu will lead you to the Code Snippets Manager… from there you can find the path to built-in snippets. These provide good fodder for creating your own.
There are two types of snippets. Expansion snippets insert the code snippet where the cursor is. Surround snippets wrap code around highlighted SQL. Both have arguments that allow you to tab through variable portions and update multiple references in a single place. Let’s take a look at a simple expansion snippet.
<?xml version="1.0" encoding="utf-8" ?>
<_locDefault _loc="locNone" />
<Title>Create Temporary Table</Title>
<Description>Creates a temporary table.</Description>
<Author>Thomas W Marshall</Author>
<ToolTip>Name of the Table</ToolTip>
<ToolTip>Name of the Columneter</ToolTip>
<ToolTip>Data type of the Columneter</ToolTip>
<ToolTip>Name of the Column</ToolTip>
<ToolTip>Data type of the Column </ToolTip>
IF EXISTS(SELECT name FROM tempdb.sys.objects WHERE object_id=OBJECT_ID('tempdb..#$TableName$') AND type = N'U') DROP TABLE #$TableName$;
CREATE TABLE #$TableName$
You can copy the above XML into a file with the extension .snippet and save it in the Code Snippets\SQL\My Code Snippets directory. You should then see it in the Code Snippets manager. Alternatively, you can import a .snippet file using the Code Snippets manager. Once the snippet has been installed whenever you strike the expansion chord CTRL+K,X in SQL Server Management Studio you will see it in the My Code Snippets folder of the fly-out menu.
In the above XML document we can see a field in the declarations for each of the variable fields. Once you have chosen to insert the snippet those declare fields are linked by there name appearing between $’s. This allows you to change the value in one and have all instances of that name update. You can also tab to the next name.
In an earlier post we started generating prime candidates testing only numbers of the form 6k+1 or 6k-1 less than or equal to the square root of the next integer. The reason we were doing this is that all prime numbers greater than 3 are of the form 6k+1 or 6k+2 and we only have to modulo test primes. The reason we only have to test primes is that the Fundamental Theorem of Arithmetic indicates that all non-prime numbers greater than one is the product of primes.
What that means is that we only have to modulo test prime numbers. Of course in pursuing that particular optimization we actually neglected to test first whether the next integer we were testing was of the form 6k+1 or 6k-1. The point of this post is to highlight how in the course of developing SQL (or any other code for that matter), is how we can read our requirements in a number of ways and will need to step out of it and reconsider whether our first reading was complete or even correct.
Consider the following statements: “We only need to modulo test primes for a given integer.” and “We need to test whether or not their exists a prime factor for a given integer.” These two statement mean essentially the same thing. When combined with the form of primes being 6k+1 or 6k-1 led us to skip testing the integer itself for being of that form, but assume we have already filled a prime table to a certain number and are testing the prime candidates. Those two statements can lead us to two very different approaches to SQL.
The first statement: “We only need to modulo test primes for a given integer.” could lead us to write something like:
DECLARE @n INT
SELECT TOP 1 1 FROM prime_candidates pc INNER JOIN primes p ON pc.p % p.p = 0 WHERE pc.p=@n
Whereas the second statement: “We need to test whether or not their exists a prime factor for a given integer.” might lead us to write something like:
SELECT TOP 1 1 FROM primes p WHERE @n % p = 0
Since our requirement was to test for prime factors of a given integer and not to produce a set of prime factors performing we do not need the non-equijoin that produces a less efficient execution plan.
Seeks that lead to lookups are usually more efficient than scans. Of course unnecessary operations are inherently inefficient. In either case it is important to test that we do in fact get the results we expect.