TSQL Rambling

Wanted to share my thoughts on three interesting things stumbled across in TSQL

1. SQL Unary Operator

Lets create a simple table as follows

declare @TestPlus table

(

Type varchar(2),

TypeName varchar(10),

code int

)

insert into @TestPlus values ('P', 'Procedure', 1)

insert into @TestPlus values ('T', 'Table', 2)

insert into @TestPlus values ('V', 'View', 3)

insert into @TestPlus values ('F', 'Function', 4)

What is the output for the following select query?

select Type from @TestPlus where Code in ( +1, +++3, +4)

select +++Type from @TestPlus where Type in (+'P', +++'T')

When I saw this select query for a second I thought its trick question on how to use ++ operator, however it turns out that the result has nothing to do with the “+++” operator. Quickly browsed through MSDN and found that it has no effect on the evaluated expression

“Although a unary plus can appear before any numeric expression, it performs no operation on the value returned from the expression.”

http://technet.microsoft.com/en-us/library/ms174362.aspx

SQL Row Constructor

I was surprised to see a query like this

;with cte as

(

select dddid, payorid

from (values  ('100100', 1), ('100101', 2), ('100102',3 ) ) f(ClientID, SeqNo)

)

select * from cte where SeqNo = 3

turns out that this type of query is called row constructor a new feature introduced in SQL 2008.

3.  SQL Strict Names

Check out the following queries, spot the odd man out

select top 10  * from [dbo].[TestUpSurdTable]

select top 10  * from [dbo].[TestUpSurdTable]_Final

I was expecting two different result sets however the same result set was repeating twice, turns out that the “[ ]” ignored what ever is after the underscore