SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Strange T-SQL Behavior
44 posts, Page 1 of 5
12345»»»

Strange T-SQL Behavior Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Grant Fritchey
Posted Wednesday, May 20, 2009 11:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 7,186, Visits: 14,701
One of the developers came up and asked me what happens if I run this query:

SELECT 1.ColumnName

I said that you'd probably get a syntax error. But when I ran it, I got this as a result:

ColumnName
1

That was strange, so I tried this:

SELECT 1.AString
     ,2.AnotherString
     ,3.SomethingElse

and I got this:

AString AnotherString SomethingElse
1 2 3

I then tried it with characters & functions, which all errored out. The only thing I got to work were integers. What the heck is this? Has anyone seen this behavior or have any idea what's happening?

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of SQL Server 2008 Query Performance Tuning Distilled

For better & quicker help read:
How to Post Performance Problems
Post #720769
Lynn Pettis
Posted Wednesday, May 20, 2009 11:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 11,363, Visits: 11,242
That is interesting. I have no explanation.


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #720771
Grant Fritchey
Posted Wednesday, May 20, 2009 11:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 7,186, Visits: 14,701
Weird isn't it? I've searched through the syntax in BOL and several books as well as a Google search. I can't find anything that references this type of syntax. I'm assuming that it's attempting to leverage... something, CLR, UDT... I'm not sure.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of SQL Server 2008 Query Performance Tuning Distilled

For better & quicker help read:
How to Post Performance Problems
Post #720773
Lynn Pettis
Posted Wednesday, May 20, 2009 11:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 11,363, Visits: 11,242
Try this and look at the table created:

SELECT
    30.AString
    ,50.AnotherString
    ,10.SomethingElse
into dbo.TempTable;
select * from dbo.TempTable;


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #720776
Florian Reischl
Posted Wednesday, May 20, 2009 11:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:27 AM
Points: 2,020, Visits: 3,549
rofl!!!

The more I learn, the more I know what I do not know

How to Post Data/Code to get the best Help"Numbers" or "Tally" Table
How to Post Performance Problems
Post #720780
Grant Fritchey
Posted Wednesday, May 20, 2009 11:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 7,186, Visits: 14,701
Yeah, based on the behavior so far, that's about what I would expect. It's like some kind of alias short hand, but it doesn't work for strings. If you try this:

SELECT 'myvalue'.MyColumn

You just get a syntax error, like I expected on the other query.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of SQL Server 2008 Query Performance Tuning Distilled

For better & quicker help read:
How to Post Performance Problems
Post #720781
Grant Fritchey
Posted Wednesday, May 20, 2009 11:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 7,186, Visits: 14,701
Florian Reischl (5/20/2009)
rofl!!!


Hey! Don't laugh at me. Just tell me what it is.

I'm starting to feel like Earl in Tremors:

"Hell Earl, everyone knows all about these things. We just never told you."

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of SQL Server 2008 Query Performance Tuning Distilled

For better & quicker help read:
How to Post Performance Problems
Post #720784
Carl Federl
Posted Wednesday, May 20, 2009 11:41 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:53 PM
Points: 1,958, Visits: 2,553
Possibly a SQL Parser problem along with an implicit datatype conversion
SELECT  1.1Number11

Which has results with a column name of "Number11" having a numeric value of "1.1"

Looks like the parser is "seeing" this string as number constant followed by a column alias even when there is no whitespace in the string.

SQL = Scarcely Qualifies as a Language
Post #720787
Florian Reischl
Posted Wednesday, May 20, 2009 11:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:27 AM
Points: 2,020, Visits: 3,549
This really seems to be an unwanted feature. A CONVERT also doesn't work:
SELECT
   CONVERT(INT, 1).Blah

The more I learn, the more I know what I do not know

How to Post Data/Code to get the best Help"Numbers" or "Tally" Table
How to Post Performance Problems
Post #720788
Florian Reischl
Posted Wednesday, May 20, 2009 11:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:27 AM
Points: 2,020, Visits: 3,549
Grant Fritchey (5/20/2009)
Florian Reischl (5/20/2009)
rofl!!!


Hey! Don't laugh at me. Just tell me what it is.

I'm starting to feel like Earl in Tremors:

"Hell Earl, everyone knows all about these things. We just never told you."


Sorry Grant, I really didn't laugh at you. I just laughed at the fact that this works. I have no idea why. Just tried on SSE2k5, works.

The more I learn, the more I know what I do not know

How to Post Data/Code to get the best Help"Numbers" or "Tally" Table
How to Post Performance Problems
Post #720790
« Prev Topic | Next Topic »

44 posts, Page 1 of 5
12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2010 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use