T-SQL Ordering Myth #2: ORDER BY in Views

• ,

Since a view is a saved query and queries can specify ordering, adding ORDER BY a view definition might seem a reasonable proposition. Try it and Microsoft SQL Server chokes. Then you learn the trick: include a TOP clause and SQL Server will be a-ok with ordering clauses in views. You want all rows returned so you add “TOP 100 PERCENT” to your definition and SQL Server is happy!

Yet you notice something strange. Sometimes the rows returned aren’t sorted according to the view’s ORDER BY clause. As you ponder this puzzlement, your mind wonders back to the roundabout syntax required to finagle ORDER BY into the view definition. Is something funny going on? But the answer eludes you. Continue reading →

Quick Tip: Decrementing IDENTITY Column

•

Did you know that IDENTITY columns can count downward?! According to MSDN, IDENTITY’s second argument, named increment, “is the incremental value that is added to the identity value of the previous row that was loaded.” No constraint is given that increment must be positive (remember from Algebra that both positive and negative numbers can be added). Set increment to a negative number and IDENTITY will generate a descending sequence of numbers. Continue reading →

T-SQL Ordering Myth #1: Clustered Indexes Determine Default Sort Order

• ,

“If I query a table that has a clustered index without specifying an ORDER BY clause, the resultset will be sorted according to the clustered index” may sound reasonable. After all, since “a clustered index determines the physical order of data in a table” (per TechNet), isn’t it logical to assume that a query with no ORDER BY clause returns data sorted this way? Continue reading →

Object IDs: Unique Instance-Wide or Database-Wide?

•

The versatility of T-SQL’s object_id() function, which accepts one-part (object_name), two-part (schema.object_name) and three-part (database.schema.object_name) object names as its first argument, could seem to imply that object identification numbers (object IDs) are global across a Microsoft SQL Server instance. After all, if object_id() accepts a multi-part object name which uniquely identifies the specified object relative to the server instance, wouldn’t the object ID returned by that function also uniquely identify the same object across the server instance? Continue reading →

Dynamic Unpivot Stored Procedure

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 →

SQL Tip: Windowing Functions

•

A simple 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?

T-SQL’s OVER clause allows several kinds of functions to be applied to non-GROUPed 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 →

Understanding MaximumErrorCount

• ,

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.

MaximumErrorCount

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 →

SSRS: Auditing Report Queries

•

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 →

SQL Tip: Double Duty for DECLARE

•

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;

Continue reading →

ETL Without the T: A Loop-Driven, Data about the Dataflow Approach Using SSIS

•

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 →