Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Interesting enhancements to the VALUES Clause in SQL Server 2008

Aug 2 2010 3:10AM by Madhivanan   

As you know well, VALUES clause is used to insert values to the table

A simple example is

declare @t table (id int , col1 int, col2 int, col3 int) 
insert into @t values(1, 14, 12, 24)
select * from @t

But in version 2008, you can do more things using VALUES clause than the previous versions.The following examples will explain it's various usages in detail

1 Insert multiple rows using a single INSERT statement

declare @t table (id int , col1 int, col2 int, col3 int) 
insert into @t 
values
	(1, 14, 12, 24),
	(2, 55, 31, 122),
	(3, 121, 50, 28)
select * from @t

2 Simulate table variable

A table variable has a statement level scope ie you can run it for any number of times in a single session.Similarly you can use VALUES clause to simulate it.The following will explain you

select * from 
(
	values
		(-1) ,
		(1),
		(32),
		(523) 
) as t (no)

The result is

no
-----------
-1
1
32
523

As you see a table is created with the name t and column n and it's scope is only for duration of the execution.You can use any number of columns with different datatypes as you see in the following examples

select * from 
(
		values
			(-1,56) ,
			(1,23),
			(32,7123),
			(523,632) 
) as t (no1,no2)

The result is

no1         no2
----------- -----------
-1          56
1           23
32          7123
523         632
select * from 
(
	values
		(1,'India','Chennai'),
		(2,'India','Delhi'),
		(3,'India','Mumbai'),
		(4,'India','Bangalore')
) as t (id,country,city)

The result is

id          country city
----------- ------- ---------
1           India   Chennai
2           India   Delhi
3           India   Mumbai
4           India   Bangalore

3 Replace CASE expression and UNPIVOT operator

Suppose you have many columns of same datatype and want to find minimum or maximum values out of them.You can use CASE espression or UNPIVOT operator for this as shown below

Source Data

declare @t table (id int , col1 int, col2 int, col3 int) 

insert into @t select 1, 14, 12, 24
insert into @t select 2, 55, 31, 122
insert into @t select 3, 121, 50, 28
insert into @t select 4, 53, 67, 24
insert into @t select 5, 2, 39, 47

Solution 1 : CASE Expression

select id,
	case 
		when col1>col2 and col1>col3 then col1 
		when col2>col3 then col2
		else col3
	end  as maximum_value
from @t

Solution 2 : UNPIVOT Operator

select id, max(col)  as maximum_value from
( 
	select id, col from @t 
		unpivot (col for cols in (col1,col2,col3)) as unpivott
		) as p 
group by id 

Solution 3 : VALUES Caluse You can now use VALUES clause for the same task as shown below

select id,
	(
		select max(n) from 
		(
			values(col1),(col2),(col3)
		) as t(n)
	)  as maximum_value 
from @t 

As you see all the above produce the same following result

id          maximum_value
----------- -------------
1           24
2           122
3           121
4           67
5           47

Tags: t-sql, sql_server, 2008, tsql, BRH, #TSQL, VALUES, #SQLServer,


Madhivanan
3 · 40% · 12936
18 Readers Liked this
me Liked this on 1/22/2012 11:46:00 PM
Profile · Facebook
Dattatrey Sindol (Datta) Liked this on 2/7/2012 7:42:00 AM
Profile · Blog
Jacob Sebastian Liked this on 1/23/2012 9:32:00 AM
Profile · Blog · Facebook · Twitter
Guru Samy Liked this on 3/13/2012 3:46:00 AM
Profile · Blog
Madhivanan Liked this on 5/2/2012 5:56:00 AM
Profile · Blog · Facebook · Twitter
Phil Steffek Liked this on 5/24/2012 2:59:00 PM
Profile
raginigupta20 Liked this on 7/2/2012 7:53:00 AM
Profile
Sergejack Liked this on 7/3/2012 2:07:00 AM
Profile
John Liked this on 7/3/2012 10:35:00 AM
Profile · Facebook
Himani Liked this on 7/5/2012 5:10:00 AM
Profile
Ramesh Velayudhan Liked this on 8/22/2012 9:18:00 AM
Profile
redpants Liked this on 9/11/2014 10:46:00 PM
Profile
Samir Matkar Liked this on 5/24/2012 9:50:00 PM
Profile
Olga Medvedeva Liked this on 5/25/2012 6:43:00 AM
Profile · Blog · Twitter
Hardik Doshi Liked this on 5/28/2012 12:19:00 AM
Profile · Facebook
Nakul Vachhrajani Liked this on 5/29/2012 11:07:00 AM
Profile · Blog · Facebook · Twitter
Mitesh Modi Liked this on 6/29/2012 5:20:00 AM
Profile
sunil20000 Liked this on 6/30/2012 5:22:00 AM
Profile
18
Liked
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

18  Comments  

  • Superb! I didn't know that VALUES can be used to simulate a table! Thanks a ton! This is going to be useful.

    commented on Aug 12 2010 1:31PM
    Nakul Vachhrajani
    4 · 36% · 11645
  • Another interesting thing - I ran the last bunch of queries (CASE/UNPIVOT/VALUES). The performance is as under: CASE (5%) = VALUES (5%) > UNPIVOT (21%)

    UNPIVOT is the worst performer - 4 times slower! I find this very interesting.

    commented on Aug 12 2010 1:36PM
    Nakul Vachhrajani
    4 · 36% · 11645
  • Nakul

    Thanks for the testing

    commented on Aug 13 2010 4:07AM
    Madhivanan
    3 · 40% · 12936
  • I had somehow not put together that you could put a VALUES clause in the SELECT statement as in Option 3. Thank you for pointing this out! Of course you can, because you can use outer references for values in the SELECT statement, and any query can be embedded inside parentheses anywhere you want, as long as it returns a single value (per row).

    If instead you want to be able to reference the values again in the query (like to join on the max value in another table in the FROM clause, you would use CROSS APPLY:

    SELECT T.id, X.MaxVal
    FROM
        @t T
        CROSS APPLY (
        	SELECT Max(Val) MaxVal
        	FROM (
        		SELECT T.col1
        		UNION ALL SELECT T.col2
        		UNION ALL SELECT T.col3
        	) V (Val)
        ) X
    

    Or using VALUES:

    SELECT T.id, X.MaxVal
    FROM
        @t T
        CROSS APPLY (
        	SELECT Max(Val) MaxVal
        	FROM (
        		VALUES (T.Col1), (T.Col2), (T.Col3)
        	) V (Val)
        ) X
    

    Note that doing it with CROSS APPLY lets you refer to V.MaxVal in the FROM and WHERE clauses, which can't be done otherwise.

    commented on Jun 30 2011 12:17PM
    ErikEckhardt
    65 · 3% · 898
  • Madhivana, Thanks for your article. Here I wanted to add one point that in below query we are constructing table T with column name ID.

    SELECT *  FROM(VALUES('A'), ('B'), ('C')) T(ID)
    

    Some time column name is very important like in below query-

    SELECT * INTO #t FROM (VALUES('A'),('B'),('C'))T(ID)
    EXCEPT
    SELECT 'A'
    

    Thanks. Jeetendra Kumar

    commented on Dec 4 2011 3:59PM
    Jeetendra
    145 · 1% · 342
  • Thanks for this post, I really want it.

    commented on Dec 16 2011 11:03PM
    mvbedarkar
    2765 · 0% · 4
  • Great post! I suspect most people are not aware of the new functionality in 2008. Keep up the good work! I incorporated this info into Compare a Row to the MAX of Multiple Columns and Multiple Rows: The Easy Way on sqldbpros.com and linked back to this article. Thanks again!

    commented on May 24 2012 2:57PM
    Phil Steffek
    1920 · 0% · 10
  • Nice post

    commented on May 24 2012 9:40PM
    Nirav Gandhi
    38 · 5% · 1567
  • Phil Steffek , Thanks. However the link is missing in your article

    commented on May 25 2012 2:38AM
    Madhivanan
    3 · 40% · 12936
  • Madhivanan,
    Thanks! Fixed the link!
    Phil

    commented on May 25 2012 11:01AM
    Phil Steffek
    1920 · 0% · 10
  • I had never seen the UNPIVOT trick before! Learn something every day...

    commented on Jun 30 2012 10:29PM
    jcelko
    452 · 0% · 87
  • Joe Celko, Thanks for your feedback.

    commented on Jul 1 2012 11:39PM
    Madhivanan
    3 · 40% · 12936
  • Very Cool!

    commented on Jul 2 2012 3:56PM
    benstaylor
    431 · 0% · 92
  • This method of using CROSS APPLY VALUES for UNPIVOT is described in both of the following links:

    Spotlight on UNPIVOT by Brad Schulz

    Unpivoting data by Itzik Ben-Gan

    In case anyone is interested in more information.

    commented on Jul 3 2012 12:43AM
    Dwain Camps
    1491 · 0% · 14
  • Thanks for the links Dwain Camps

    commented on Jul 3 2012 12:50AM
    Madhivanan
    3 · 40% · 12936
  • One more link. This article was pending publication when I made my prior post.

    http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

    There are some interesting performance characteristics of this method if you read the article and the discussion.

    commented on Aug 22 2012 6:52AM
    Dwain Camps
    1491 · 0% · 14
  • Thanks Dwain Camps for the article

    commented on Aug 22 2012 7:27AM
    Madhivanan
    3 · 40% · 12936
  • This comment is waiting for moderation.

    commented on Sep 21 2014 9:36AM
    desperadomar
    2381 · 0% · 5

Your Comment


Sign Up or Login to post a comment.

"Interesting enhancements to the VALUES Clause in SQL Server 2008" rated 5 out of 5 by 18 readers
Interesting enhancements to the VALUES Clause in SQL Server 2008 , 5.0 out of 5 based on 18 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]