T-SQL Tuesday #065 – Teach Something New – APPLY

This is my second contribution to T-SQL Tuesday. This month’s topic is hosted by Mike Donnelly (blog | twitter) with the subject of learning/teaching something new.

The topic this month is straight forward, but very open ended. You must learn something new and then write a blog post explaining it. One of the reasons I present and blog is because it forces me to really learn a subject if I am going to explain it to someone else. I am now giving all of you that same opportunity. You’re welcome.

I didn’t see this topic until Friday afternoon, so luckily it is a very open-ended topic. I have to find a better way to track T-SQL Tuesday. Right now the plan is to hope that I see something appear about it in my Twitter Feed. Anyways, I had to scramble together an idea while finding something I could learn and write about in one day. I recently scheduled another article that used the APPLY operator in one of the examples, but it’s something I have yet to use much. This seemed like a great time to explore it.

APPLY has two basic forms, CROSS APPLY or OUTER APPLY. CROSS APPLY works essentially like an INNER JOIN, whereas OUTER APPLY is like a LEFT OUTER JOIN. The primary purpose of the APPLY operator is to evaluate a Table-Valued Function against every row returned by a query. The expression acts as the left input while the function acts as the right input. OUTER applies will return NULL values on the column evaluated by the function and CROSS applies will exclude NULL results – again, just like joins.

The following example is fairly simple, but it displays the SQL script used for currently running processes. The CROSS APPLY example will return fewer results as it filters spids without a sql_handle, basically system processes. The OUTER APPLY will still return the processes, just with a NULL Text column. The APPLY operator allows you to avoid performing subqueries to evaluate Table-Valued Functions.

1) APPLY for Table-Valued Functions

CROSS APPLY

SELECT
 session_id,[status],blocking_session_id,wait_type,
 wait_time,wait_resource,transaction_id,t.[text]
FROM sys.dm_exec_requests r
 CROSS APPLY sys.dm_exec_sql_text(sql_handle) t

OUTER APPLY

SELECT
 session_id,[status],blocking_session_id,wait_type,
 wait_time,wait_resource,transaction_id,t.[text]
FROM sys.dm_exec_requests r
 OUTER APPLY sys.dm_exec_sql_text(sql_handle) t

2) Reuse Column Aliases

First we will create a temp table with some numbers for sample data.

CREATE TABLE #Numbers (Num1 INT NOT NULL,Num2 INT NOT NULL)
INSERT INTO #Numbers VALUES (1,3),(3,5),(5,7),(10,12)

Traditional Script (No Aliasing)

SELECT Num1,Num2,
[SumNums] = Num1+Num2,
[SumNumsTimes3] = (Num1+Num2)*3,
[SumNumsTimes3Plus2] = (Num1+Num2)*3+2
FROM #Numbers

Window Function to Reuse Column Aliases

WITH Alias AS
(
SELECT Num1,Num2,
[SumNums] = Num1+Num2
FROM #Numbers
)
SELECT Num1,Num2,
SumNums,
[SumNumsTimes3] = SumNums*3,
[SumNumsTimes3Plus2] = SumNums*3+2
FROM Alias

APPLY to Reuse Column Aliases

SELECT Num1,Num2,
SumNums,
SumNumsTimes3,
[SumNumsTimes3Plus2] = SumNumsTimes3+2
FROM #Numbers
CROSS APPLY (SELECT Num1 + Num2) a (SumNums) 
CROSS APPLY (SELECT SumNums * 3) b (SumNumsTimes3)

Obviously if you have to calculate anything complex, not being able to use a column alias would require a lot of copying and pasting and room for error. Using a CTE to alias requires nested CTEs for multiple aliases and much more typing. Using an APPLY operator provides the most flexibility and could provide a lot of code reuse.

3. Unpivot

Again, first thing is to create some sample, denormalized data.

CREATE TABLE #ContactInfo
(
 ContactInfoID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 Phone1 INT, Phone2 INT, Phone3 INT,
 Email1 VARCHAR(50), Email2 VARCHAR(50), Email3 VARCHAR(50)
)
INSERT INTO #ContactInfo VALUES
(5555510,5555511,5555512,'ASmith@Test.com','ASmith1@Test.com','ASmith2@Test.com'),
(5555520,5555521,5555522,'BJones@Test.com','BJones1@Test.com','BJones2@Test.com'),
(5555530,5555531,5555532,'CDoe@Test.com','CDoe1@Test.com','CDoe2@Test.com')

Traditional Unpivot

SELECT
ContactInfoId,ContactPriority,PhoneNumber,EmailAddress
FROM (SELECT * FROM #ContactInfo) p
UNPIVOT ( PhoneNumber FOR ContactPriority IN (Phone1,Phone2,Phone3) ) up1
UNPIVOT ( EmailAddress FOR EPriority IN (Email1,Email2,Email3) ) up2
WHERE RIGHT(EPriority,1) = RIGHT(ContactPriority,1)

A basic unpivot won’t work here, as we need to unpivot multiple columns. Luckily you can unpivot twice, but the syntax is a bit weird and you’ll probably have to look it up every time you try to unpivot like this. The WHERE clause at the end filters the results so the result doesn’t become a cartesian join. The RIGHT operator has the results match on just the numerical values of the Email/Phone Priority.

Unpivot Using Apply

SELECT
ContactInfoId,ContactPriority,PhoneNumber,EmailAddress
FROM #ContactInfo
CROSS APPLY (VALUES(1,Phone1,Email1),
(2,Phone2,Email2),
(3,Phone3,Email3)) p (ContactPriority,PhoneNumber,EmailAddress)

Unpivoting this way might take a bit of practice, but the syntax seems a bit more intuitive to me for occasional use. Just remember that you have to define the new column names at the end of the statement. This scales a lot smoother than using unpivot.

4. Select TOP N Results

Using Window Functions

WITH Columns AS (
SELECT
TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,
[RN] = ROW_NUMBER() OVER(PARTITION BY TABLE_SCHEMA,TABLE_NAME ORDER BY ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
)
SELECT
t.TABLE_SCHEMA,t.TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.tables t
INNER JOIN Columns c ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
WHERE c.RN <=3

Using APPLY

SELECT
t.TABLE_SCHEMA,t.TABLE_NAME,c.COLUMN_NAME
FROM INFORMATION_SCHEMA.tables t
OUTER APPLY (SELECT TOP 3 *
FROM INFORMATION_SCHEMA.columns c
WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
) c

Comparing syntax alone, APPLY is a bit easier to type out, but performance-wise, it depends on your use case, SQL version, etc. I’m used to writing CTEs, so I’ll probably stick with that syntax.

Summary These were all the uses I’ve been able to find so far for the APPLY operator, but please feel free to identify any others you know of. Sometimes the syntax using APPLY is more intuitive than the alternative, but the main use is definitely the originally intended purpose, evaluating Table-Valued Functions.