Strange T-SQL Behavior

Question

Strange T-SQL Behavior

Viewing 15 posts - 16 through 30 (of 43 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Yep. I also just figured out. Works also in combination with TOP. It is the parser tolerance to allow missing spaces between numbers and aliases:

SELECT TOP 100N FROM Tally

That's also the reason why this works:

SELECT TOP(10) N FROM Tally t1

... And this doesn't:

SELECT TOP(10) N FROM Tally 1t

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Examples:

select 1.AColumn :

AColumn

---------

1

select 1.0AColumn :

AColumn

---------

1.0

Now do those with a space before AColumn, you will get the same results.

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 3

Wild. I've never seen that before. Cool. Good job figuring it out. I can sleep tonight now. Thanks.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 4

By the way, with a little more experimentation, this also works:

SELECT 'dude'dudette

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 5

Be careful - you might be surprised when you use something like:

SELECT 1.Email, 2.Everything, 3.Else

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 6

Ooooh. Cool. I like that one. You could really mess with people's heads with this.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Too Cool!!

I agree, great way to mess with someones head.

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 8

Yeah - it is kinda cool 😎

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

John Rowan SSC Guru Points: 56440 More actions · Answer 9

Wow....this one should make it to the 'evil' interview questions list.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

This has all the earmarks of an ancient SQL parsing rule. I bet this one goes waaay back. Can someone try it on SQL 2000?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

john.arnott SSChampion Points: 11882 More actions · Answer 11

I don't have the old QM anymore, but in my SQL2005 SSMS connected to a SQL2000 engine gives the same behavior, including the interpretation of "E"xponent notation. Interesting stuff -- it depends on some of those "why'd they do that?" rules, like column names cannot start with a digit.

Now, who's going to be first to post something based on this as a QOD? Select 1.4TheMoney,3*8TheShow,3.2'GetReady',2+2ToGo,'Five''Six'PickupStix

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 12

Yeah. It works on 2000 too. I wish I had a 7.0 or, better still, a 6.5 system to try it out on.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Now that's taking white space insensitive just a little too far.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

john.arnott (5/21/2009)
I don't have the old QM anymore, but in my SQL2005 SSMS connected to a SQL2000 engine gives the same behavior, including the interpretation of "E"xponent notation. Interesting stuff -- it depends on some of those "why'd they do that?" rules, like column names cannot start with a digit.
Now, who's going to be first to post something based on this as a QOD? Select 1.4TheMoney,3*8TheShow,3.2'GetReady',2+2ToGo,'Five''Six'PickupStix

Go for it. Looks like you already have the question.

Mike Mullen Hall of Fame Points: 3254 More actions · Answer 15

I tried the following on SQL 7.0, without a space before the 'from', so this does go back aways.

set nocount on

create table #cdarea (

cd int not null,

descr varchar(30) not null)

insert #cdarea values (0,'zero')

insert #cdarea values (2,'Area 2')

select cd,2from #cdarea

drop table #cdarea

-----------------------

cd

----------- -----------

0 2

2 2