SSIS Talk

Teradata offers the ability to build a query, and then use a QUALIFY statement that filters the resulting resultset. This is especially handy when using a ROW_NUMBER function where you are partitioning on a natural key (or whatever it may be) and you want to return only the rows where the ROW_NUMBER value is 1, for example.

In SQL Server today, this has to be done with derived tables/CTEs like so:

USE AdventureWorksDW
GO
/* Ignoring the fact that this table
   has a "Status" and an EndDate column */
;WITH CTE AS (
SELECT EmployeeKey,
       FirstName,
       LastName, 
       MiddleName,
       Title,
       ROW_NUMBER() OVER (PARTITION BY EmployeeNationalIDAlternateKey
                              ORDER BY StartDate DESC) AS ROW_NUM
  FROM dbo.DimEmployee
)
SELECT EmployeeKey,
       FirstName,
       LastName,
       MiddleName,
       Title
  FROM CTE
 WHERE row_num = 1
 ORDER BY FirstName, LastName
GO   

That’s easy enough to understand, of course, but the problem lies in the fact that you have to write a bunch of extra lines of code to accomplish this task.

Teradata has the QUALIFY option which would be represented like this:

 SELECT EmployeeKey,
        FirstName,
        LastName, 
        MiddleName,
        Title
   FROM dbo.DimEmployee
QUALIFY ROW_NUMBER() OVER (PARTITION BY EmployeeNationalIDAlternateKey
                               ORDER BY StartDate DESC) = 1

Note how much cleaner that looks. QUALIFY has many advantages, but I’ll leave that as an exercise for you to review. In the mean time, if you would like to see such an option in SQL Server, please vote on Itzik Ben-Gan‘s feature request: https://connect.microsoft.com/SQLServer/feedback/details/532474/qualify-request-for-a-new-filtering-clause