THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

T-SQL joke

I was attending an ORM class for developers where I heard it said "Don't select star from a table". I've heard this many times and I'm sure you have, too. It makes for brittle code in case a column is added to the table. I started wondering if I could create an example that is an exception to the rule and came up with this:

create table aTable (

    star int

);

 

select star from aTable;

 

Published Saturday, October 09, 2010 12:01 AM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Wes W. said:

Better yet, you can name the column "*" so you can really do "SELECT * FROM test;" and get just the one "*" column!

October 9, 2010 8:57 AM
 

ALZDBA said:

No Wes,

create table Star (idnr int not  null identity(1,1), "*" varchar(100), tsinsert datetime default getdate() )

go

insert into Star (  "*" ) values ('a') ;

go

Select * from star ;

gives:

idnr * tsinsert

1 a 2010-10-11 13:02:46.683

If you only want the column * in the result, you should code "*" or [*] .

Which supports the guideline to only select the columns you want.

Darn ... I just messed up a joke ;-(

October 11, 2010 7:06 AM
 

Paul White said:

SELECT  Data.string,

       iTVF.xml_encoded

FROM    (

       VALUES

           ('<D>'),

           ('E&F')

       ) Data (string)

CROSS

APPLY   (

       SELECT  [*] = Data.string FOR XML PATH ('')

       ) iTVF (xml_encoded);

October 11, 2010 11:27 PM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solution Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement