EasyCFM.COM ColdFusion Forums / Good Coding Tips! / Select * ?

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Select * ?

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

Webmaster
04-26-2004 @ 10:43 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

Technically yes.

Why? Because 9 out of 10 is still less data to bring back... Also by actually defining them, you can later on reference them easier and know where varaible came from etc..

Specially if at a later time (or even now) you have multiple people in the same code.

As far as what I suggest... Stay away from "*" as much as you can Smile

Thanks,
Pablo Varando
Team Macromedia Member
=====================================================

CJ
04-27-2004 @ 11:00 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4262
Joined: Oct 2002

agreed.  get into the habit of specifying your column names.

Pablo nailed both reasons.

1) only retrieve the data you want
2) know by looking at the code what columns you're selecting



-CJ-
@ #coldfusion/DALNet
http://charlie.griefer.com

epipko
05-25-2004 @ 1:57 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 278
Joined: Feb 2004

I thought of something else.
By saying select *.... you are not going to be able to user an index (helpful in case of a larger table)



Thanks,
Eugene

Scarecrow
10-07-2004 @ 1:37 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 109
Joined: Oct 2004

The MAJOR reason for not using Select * is that the RDBMS will do the following
Let's assume you have a table
username (varchar 1000)
userid tinyint

When you do Select *

A "memory" space is allocated for each column, but the catch is that because the varchar 1000 is first, the next column (tinyint) will also get the same amount of space

Ken

rajas
10-31-2004 @ 11:05 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 144
Joined: Oct 2004

Good thoughts Guys..

A doubt. If I have 8 fields in the table and if need to use ALL columns, still I shud not use "*" ??
Shud I name all the field names in the query? If so, what is the advantage?

Rajas...
<As long as you live, keep learn how to live>

This message was edited by rajas on 10-31-04 @ 11:06 PM

Scarecrow
11-01-2004 @ 12:39 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 109
Joined: Oct 2004

Yes, you should always use the column names instead of the "*", even if you are going to call all the columns.

As discussed in the previous replys.  The query will be more effiecent, you can give columns an alias if required, using index's and being able to follow the code later on when you need to review it.

Ken

SteelValor
05-10-2006 @ 12:40 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 8
Joined: May 2006

Using * hits the db twice. Once to get the collumnames and second to get the data (at least that's what I was told)

Anyways, I wrote this to help with those HUGE field lists. This will output the fieldlist from your database and you can then just cut and paste it into your real query.

<!--- Get Columns Development Script --->
<cfset variables.qname="members">
<cfquery datasource="#application.DSN#" name="getColumns">
     SELECT          *
     FROM          #variables.qname#
</cfquery>
<cfoutput>
     <cfset lowercase="#Lcase(getColumns.columnlist)#">
     <cfset lowercase="#replace(variables.lowercase, ",",", ", 'all')#">
     #variables.lowercase#<br />
</cfoutput>



Website Designed and Developed by Pablo Varando.