Pass in a query and a column name (or a column list) and receive an unpivoted result set back. Specify either the columns to unpivot or the columns to leave unpivoted (i.e. unpivot all but the specified columns). Behind the scenes, this stored proc casts value columns to a common data type of your choosing, eliminating “the type of column ‘x’ conflicts with the type of other columns specified in the UNPIVOT list” errors. It can also be used as a SQL generator, outputting the dynamically-built query’s text instead of executing it. Continue reading
SELECT returns all relevant rows from the database.
GROUP BY applies aggregate functions, condensing
SELECTed data into one summary row per grouping. What if mixing the two approaches is desired—what if data rows need to be combined with aggregate totals in the same result set?
OVER clause allows several kinds of functions to be applied to non-
BY rows. Using
OVER, a simple
SELECT returning data rows can be expanded to include columns containing summary statistics. Traditionally, aggregate functions can only be used in conjunction with
GROUP BY or to produce a single row summary result set; when used with
OVER, these limitations are removed. Continue reading
Learning about how SSIS’s MaximumErrorCount property works can be challenging. There’s not much documentation describing this property and the behavior it controls. Here’s my attempt to help remedy this.
When the number of errors occurring inside a container during execution reaches its MaximumErrorCount, the container’s ExecutionResult is changed to Failure if it is not already set to that state. A value of zero sets the error count threshold to infinity, disabling this functionality. Continue reading
Want to view a list of queries used in reports deployed on a Microsoft SQL Server Reporting Services (SSRS) server?
While SSRS’s web service exposes the functionality necessary to assemble this information, using that service requires programming/scripting skills or third-party software. Thankfully, these requirements can be bypassed by fetching this information directly from the report server database. Continue reading
Just recently, I realized that T-SQL allows variables to be declared and set with a single statement:
DECLARE @UserID int = 1592;
Up until this discovery, I only knew about the more traditional, two-step declaration and assignment syntax:
DECLARE @UserID int; SET @UserID = 1592;
Microsoft SQL Server Integration Services (SSIS) is designed for ETL. However, sometimes the T in ETL isn’t necessary: data simply needs to be moved without manipulation. While defining a simple source-to-destination dataflow is easy, filling a package with the numerous copy operations required to move data between multiple source-destination pairs results in a verbose package that violates the Don’t Repeat Yourself (DRY) principle.
Using a loop to apply a single copy data dataflow to a list of source-destination pairs eliminate this negative. With this approach, the main dataflow in the SSIS package is not the data being moved; rather, it is data about that data—that is, data describing the sources and destinations to which the extract-and-load operation should be applied. Continue reading
Internet search results are typically broken into batches. The initial page displays results 1 through perhaps 10, page two shows matches 11–20 and so on. Splitting results up like this is known as (no surprise) paging.
To implement results paging with Microsoft SQL Server (version 2012 or later), try modifying your
ORDER BY clause with
OFFSET tells SQL Server to start returning x rows into a result set.
FETCH defines the number of rows to return. Continue reading
Alphabetical sorting is simple: A comes after B, next comes C, then D and so on all the way to Z…right?!
If only it were so easy! Consider the character Á—should it be sorted before A, after A or just like it was A (ignoring the accent)? The answer depends on language: Hungarian’s Á comes after A; in Spanish, the accent should be ignored. In many languages, Y comes between X and Z; however, Lithuanian orders Y before J. French has an interesting twist: sometimes strings are sorted by first comparing their letters from left to right then by comparing diacritics from right to left. Some languages even treat certain multi-character combinations as single letters, like CD in Welsh. If this doesn’t sound complex enough, alphabetization rules can change: for example, in 1994, the Congress of the Association of Spanish Language Academies decreed that Spanish’s CH would no longer be considered a single, muilt-character letter combination! Continue reading
Use exceptions for exceptional circumstances. Will your program still run if the exception handling code is removed? If so, you may be misusing exceptions. In effect, you may be using exceptions as a way to emulate goto statements!
(Found this today in a collection of programming thoughts I wrote back in 2005.)