THE SQL Server Blog Spot on the Web
Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search
Home Blogs Downloads   Opml View as RSS news feed in XML

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 both a Registered Nurse and a Microsoft SQL Server MVP 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. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence to satisfy healthcare meaningful use requirements and improve patient outcomes. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication
©2006-2012 SQLblog.comTM
Brought to you by Adam Machanic & Peter DeBetta
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement