you're reading...
Technical Topics

Using the Enhanced VALUES Clause in SQL 2008

For those of us who spend time querying Microsoft SQL Server, each new version of the product gives us new functionality that can be as helpful as a new belt full of tools to a carpenter.  One tool (first available in SQL 2008) that has proved useful in our ad hoc queries is the expanded VALUES clause.  I use it in three primary ways:

  1. A more intuitive way to insert data into a table.
  2. A great technique for quick and dirty derived tables.
  3. A way to aggregate data over columns rather than rows.

Some SQL statements that spell out the details of those three techniques follow.  The first two are fairly easy to understand by simply running the SQL code.  The third technique is a bit more involved:  for more explanation and code samples, refer to this article in SQL Server Magazine by Itzik Ben-Gan.

-- declare a table variable for use below
declare @TableVar table
      TermId int,
      LicenseId int,
      MaleCount int,
      FemaleCount int,
      [Description] varchar(500)

-- first use of the values clause:  one insert statement inserting multiple rows.
insert into @TableVar
      values (20083, 1, 12, 15, 'Sample data - bogus'),
      (20093, 4, 31, 47, 'Sample data - bogus'),
      (20103, 3, 50, 22, 'Sample data - bogus'),
      (20113, 2, 67, 83, 'Sample data - bogus'),
      (20123, 4, 39, 58, 'Sample data - bogus')

-- second use of values clause:  inline, quick & dirty derived table
select t.TermId, t.LicenseId, t.MaleCount, t.FemaleCount, tmp.*
            values -- use values to create derived table
                  (1, '130301-N','Science Chemistry 9-12 + 5-8 General Science'),
                  (2, '130302-N', 'Science Physics 9-12 + 5-8 General Science'),
                  (3, '130400-N', 'Science Earth 9-12 + 5-8 General Science'),
                  (4, '130600', 'Science General 5-8')
            ) as tmp (LicenseId, LicenseCode, LicenseDescription) -- name columns
      join @TableVar t
      on tmp.LicenseId = t.LicenseId

-- third use of values clause:  aggregate function over columns
-- rather than rows.  Better performance than unpivot with large sets.
select TermId,
      select max(mycol)
            (values ('FemaleCount', FemaleCount), ('MaleCount', MaleCount)) as mytable(mylabel, mycol)
      ) maximum_value
    from @TableVar 

-- compare syntax above to case statement
select TermId, 
		when FemaleCount > MaleCount then FemaleCount
		else MaleCount
	end maximum_value
	from @TableVar

-- now compare to unpivot 
select TermId, max(col) as maximum_value
		select TermId, col
			from @TableVar
			unpivot (col for cols in (FemaleCount, MaleCount)) as unpivott
		) p
    group by TermId


No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

IPAR Posts

November 2011
« Oct   Dec »


Enter your email address to follow this blog and receive notifications of new posts by email.

Join 6 other followers

%d bloggers like this: