T-SQL or ISO

pootle flump · #1 (**permalink**) 02-17-11, 16:57

Hi chumps...I mean chaps

I saw something new today. Wondered if anyone else had seen it, and if it was T-SQL specific or ISO. BoL entry for SELECT does not suggest it is valid, but it is. Whaddaya reckon the result will be? (tested in SQL 2005 & 2008)

Code:

SELECT  name
      , (object_id)
      , (SELECT schema_id) 
      , (SELECT TOP 937 type_desc)
FROM    sys.objects

shammat · #2 (**permalink**) 02-17-11, 17:33

Having a Sub-SELECT in the column list of a SELECT statement is ISO standard. The sub-select must return exactly one row with one column (so I wonder how the TOP 937 works here)

A select without a FROM clause however is non-standard.

But for the nested (SELECT schema_id) I don't see any advantage (apart from making the statement harder to understand) as schema_id is probably a column from sys.objects.

pootle flump · #3 (**permalink**) 02-18-11, 02:28

Oh I'm not claiming an advantage. It looks like total nonsense. The columns are all from the source table. That isn't the exact query, just an example of the syntax

Wim · #4 (**permalink**) 02-18-11, 05:08

I simplified it by converting it into

Code:

SELECT  name
      , object_id
      , schema_id
      , type_desc
FROM    sys.objects

without changing the result. It returns 92 records on my test system.

Quote:

Oh I'm not claiming an advantage.

Think further! You must have heard of The Obfuscated C and Perl Contests. Now we can have our own Obfuscated MS-SQL Contests ® (OMSC) too! Just think of all the advantages it will bring to be able to write code only at most 2 other people on Earth are able to understand. And then imagine the benefits of one of your developers in your team sharpening his/her OMSC®-skills on production software on a daily base!

pootle flump · #5 (**permalink**) 02-18-11, 08:04

Oh I have some doozies. I'll start a new thread at the weekend

Teddy · #6 (**permalink**) 02-18-11, 10:21

Quote:

Originally Posted by shammat

The sub-select must return exactly one row with one column (so I wonder how the TOP 937 works here)
.

It works because there's only one row to return. It could be TOP {anyPositiveNumber}. That one threw me for a loop at first too.

pootle flump · #7 (**permalink**) 02-18-11, 11:23

Code:

SELECT  name
      , (object_id)
      , (SELECT schema_id) 
      , (SELECT TOP (937) type_desc)
      , (SELECT TOP (-0) type_desc)
FROM    sys.objects

Teddy · #8 (**permalink**) 02-18-11, 11:26

Now where did I put that "facepalm" image macro...

Internet Services

Web Development

Digital Marketing

Consumer Tech