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
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
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
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.
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')
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
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.