There are multiple ways of retrieving data from a table when you have a list of values. Sometimes, the method is straight-forward and sometimes we just can’t see the right solution.
Some of the methods to retrieve data from a table (based on a list of values) might include one or multiple of the following: join to another table, throwing an in clause at it, dumping the values into a temp table, sub-queries, and a table value constructor.
Let’s focus on the table value constructor. This one seems to trip a few people up from time to time. First off, what is it?
Table Value Constructor
The table value constructor is basically like a virtual table not too different from a CTE or a subquery (in that they are all virtual tables of sorts). The table value constructor however can be combined with either of those other types and is a set of row expressions that get put into this virtual table in a single DML statement.
So, this virtual table contains a set of values that we define. Let’s go ahead and take a look at an example set of data that we can cultivate into a table value constructor.
VALUES (14) , (15) , (18) , (20) , (102) , (103) , (104) , (105) , (106) , (107) , (108) , (109) , (110) , (111) , (112) , (113) , (115) , (116) , (117) , (118) , (128) , (129) , (130) , (131) , (132) , (133) , (134) , (135) , (152) , (153) , (170) , (171) , (172) , (173) , (175) , (176) , (177) , (178)
Now, let’s say that I want to get all of the trace events that might correlate to those specific values, all I need to do is add this list of values to a query in the appropriate place. First, let’s see what it looks like when I add it via a CTE.
WITH traceevents (EventID) AS (SELECT * FROM ( VALUES (14) , (15) , (18) , (20) , (102) , (103) , (104) , (105) , (106) , (107) , (108) , (109) , (110) , (111) , (112) , (113) , (115) , (116) , (117) , (118) , (128) , (129) , (130) , (131) , (132) , (133) , (134) , (135) , (152) , (153) , (170) , (171) , (172) , (173) , (175) , (176) , (177) , (178) ) AS it (EventID) ) SELECT te.EventID , t.name AS EventName , tc.name AS Category FROM traceeventste INNER JOIN sys.trace_eventst ON t.trace_event_id = te.EventID INNER JOIN sys.trace_categories tc ON t.category_id = tc.category_id;
In this case, I have thrown the table value constructor into a CTE. There could be a few good reasons to do it this way such as it being easier to read or maybe some corporate style policy. For me, I like the readability and the tight grouping of the join clauses without needing to scroll. Running the query in this case would produce results similar to the following.
Now, what if I really prefer to not use a CTE? Easy enough, let’s look at this next example. I will use the same list of values as I search for various wait_types.
SELECT mv.name , mv.map_key , mv.map_value FROM ( VALUES (14) , (15) , (18) , (20) , (102) , (103) , (104) , (105) , (106) , (107) , (108) , (109) , (110) , (111) , (112) , (113) , (115) , (116) , (117) , (118) , (128) , (129) , (130) , (131) , (132) , (133) , (134) , (135) , (152) , (153) , (170) , (171) , (172) , (173) , (175) , (176) , (177) , (178) )it (itemid) INNER JOIN sys.dm_xe_map_values mv ON mv.map_key = it.itemid WHERE mv.name = 'wait_types';
Notice here that I have a little bit of a different query. I only have one table to join to after the table value constructor. That said, it works just the same as the CTE example. Once the data is constructed, I alias it like a table, then I define the column names. This query will now return results similar to the following.
Both of these examples have been pretty easy in building a single attribute array for the table value constructor. What if we had the need for multiple columns in our list of data?
SELECT mv.name , mv.map_key , mv.map_value , it.WaitStatus FROM ( VALUES (14,'Active') , (15,'Active') , (18,'Active') , (20,'Active') , (102,'Active') , (103,'Active') , (104,'Active') , (105,'Active') , (106,'Active') , (107,'Active') , (108,'Benign') , (109,'Benign') , (110,'Active') , (111,'Active') , (112,'Active') , (113,'Active') , (115,'Active') , (116,'Active') , (117,'Active') , (118,'Active') , (128,'Active') , (129,'Benign') , (130,'Benign') , (131,'Benign') , (132,'Benign') , (133,'Active') , (134,'Active') , (135,'Active') , (152,'Active') , (153,'Active') , (170,'Active') , (171,'Active') , (172,'Active') , (173,'Active') , (175,'Active') , (176,'Active') , (177,'Active') , (178,'Active') )it (itemid,WaitStatus) INNER JOIN sys.dm_xe_map_values mv ON mv.map_key = it.itemid WHERE mv.name = 'wait_types';
Notice the addition of an extra value (separated by a comma) within each set of parenthesis and the additional column definition added after the alias for the table value constructor. This is a pretty simple modification. Upon execution, I can expect results such as the following.
The Wrap
Being able to take a list of data and construct it into a virtual table for further querying and data analysis is a pretty cool thing to do. The setup and syntax are fairly simple – it just takes a bit of practice and effort.
This article demonstrated three different examples of using a table value constructor to join a list of data to other tables. See what other kinds of examples you can come up with and share them.
This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.