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.”
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
