SQL Derived Table from Values

Josh,

So you have a small set of data you want to use in a query.  The data is static and temporary in nature so you don't really want to store it in a table.  A derived table from values might do the trick!

The FROM clause allows for a table_source clause of type derived_table (Table Value Constructor):

The derived_table can be structured like so:

SELECT
   Holidays.HolidayDate,
   Holidays.HolidayName
FROM
   (
   VALUES
       (CAST('1/1/2014' AS date),'New Year''s Day'),
       (CAST('1/20/2014' AS date),'Martin Luther King, Jr. Day'),
       (CAST('5/26/2014' AS date),'Memorial Day'),
       (CAST('7/4/2014' AS date),'Independence Day'),
       (CAST('9/1/2014' AS date),'Labor Day'),
       (CAST('11/27/2014' AS date),'Thanksgiving Day'),
       (CAST('12/24/2014' AS date),'Christmas Eve'),
       (CAST('12/25/2014' AS date),'Christmas Day')
   ) AS Holidays (HolidayDate, HolidayName)  

The resulting query:

The resulting table and columns are strongly typed making it easy to fill out the select clause and joins.