Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Select * or declare all filds?

Author  Topic 

Eagle_f90
Constraint Violating Yak Guru

424 Posts
Posted - 2005-06-06 : 14:42:59
I have a T-SQL script that will be pulling all the data from a table but I have heard arguments that doing select * is bad and I should declare all the column names in my select statment. Is this true? (I.e. is select colum1, coulum2, ... coulem12 better then select *)

--
For those with wings, fly to your dreams

Dearms are what are found at the end of reality, and your reality if what is found at the end of your dreams.

X002548
Not Just a Number

15586 Posts
Posted - 2005-06-06 : 14:46:55
What do you mean by pulling all the data from a table. What are you trying to do?

And yes SELECT * is usually not a good idea for production code...but for quick analysis it aint bad

http://weblogs.sqlteam.com/brettk/archive/2004/04/22/1272.aspx

Brett

8-)

EDIT: Fixed the type-o

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Eagle_f90
Constraint Violating Yak Guru

424 Posts
Posted - 2005-06-06 : 14:55:31
quote:
Originally posted by X002548

What do you mean by pulling all the data from a table. What are you trying to do?

And yes SELECT * is usually to a good idea for production code...but for quick analysis it aint bad

http://weblogs.sqlteam.com/brettk/archive/2004/04/22/1272.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


I mean I have a table with about 20 colums and I will be pulling all 20. So it is fine to use select * in instad of writting out all 20 colums?

--
For those with wings, fly to your dreams

Dearms are what are found at the end of reality, and your reality if what is found at the end of your dreams.
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts
Posted - 2005-06-06 : 15:16:56
It is better to list each column in your select statement. One reason is that if the table changes by adding additional columns you will only be reading the columns that you need (currently all of them). If the table gets altered to drop a column, you will receive feedback the first time you execute your code that the necessary data no longer exists.

HTH

=================================================================
All restraints upon man's natural liberty, not necessary for the simple maintenance of justice, are of the nature of slavery, and differ from each other only in degree. -Lysander Spooner, lawyer (1808-1887)
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2005-06-06 : 15:18:43
There are many good reasons for not doing SELECT * in production code. A simple example is that you are inserting the data into a temporary table with 20 columns. If you add one more column to this table, your code wiil break if you do SELECT *. If you use a select list, it will keep right on working.

Believe me that it is much less work to do this when you are coding the application, than to try to figure out what broke at 3:00 am when some report code breaks.



CODO ERGO SUM
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2005-06-06 : 15:23:18
Did you look at my link? I have a list of reasons.

And I like the fact that you want to be lazy. Sign of a good dba.

You can use code to build code as well

Do this

[CODE]
USE Northwind
GO

SELECT ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'Orders'
ORDER BY ORDINAL_POSITION
[/CODE]


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Eagle_f90
Constraint Violating Yak Guru

424 Posts
Posted - 2005-06-06 : 15:42:01
quote:
Originally posted by X002548

Did you look at my link? I have a list of reasons.

And I like the fact that you want to be lazy. Sign of a good dba.

You can use code to build code as well

Do this

[CODE]
USE Northwind
GO

SELECT ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'Orders'
ORDER BY ORDINAL_POSITION
[/CODE]


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx



1. Your link is not working, I keep getting a DNS error for it so no I did not look at your link.

2. What the hell makes you think I am lazy? I am spimply trying to cut down on errors, writting out all 20 colums has a biger chance of typeos then using the *. Speaking of types it is your typeo in your first post that confused me even more

"And yes SELECT * is usually to a good idea for production code...but for quick analysis it aint bad"

3. I am trying to learn to be a good DBA which is why I am asking questions like this, unfortunaly I am not an all knowing T-SQL god like you but instead have bearly scratched the surface of working with T-SQL and DBs.

--
For those with wings, fly to your dreams

Dearms are what are found at the end of reality, and your reality if what is found at the end of your dreams.
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2005-06-06 : 15:49:40
Chill....a lazy dba is a good dba...it means that you are looking for ways to do things other than the long way around the mountain...in other words you find ways to make your life easier...it's a compliment...not an insult...must be something in the water today....

And I meant to say It is not a good idea.

Did you run the sample code to generate your select list?

That way you can just cut and paste it.

I have no problem with the link...try and cut and paste the link to your browser...



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Eagle_f90
Constraint Violating Yak Guru

424 Posts
Posted - 2005-06-06 : 16:06:11
quote:
Originally posted by X002548

Chill....a lazy dba is a good dba...it means that you are looking for ways to do things other than the long way around the mountain...in other words you find ways to make your life easier...it's a compliment...not an insult...must be something in the water today....

And I meant to say It is not a good idea.

Did you run the sample code to generate your select list?

That way you can just cut and paste it.

I have no problem with the link...try and cut and paste the link to your browser...



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx



Sorry I have been out of work for a few weeks and everyone is calling me lazy because I am not looking for a part time job till I get back. As for sample code are you talking about this:

USE Northwind
GO

SELECT ', '+COLUMN_NAME 
  FROM INFORMATION_SCHEMA.Columns 
 WHERE TABLE_NAME = 'Orders'
ORDER BY ORDINAL_POSITION


If so no I did not since I have no idea what most of that does. What I really only know it does is ordering the output by the colom ORDINAL_POSISTION. If you are talking about my select statment, I have not enven built it yet since it will be joining 2 tables and that scares me

--
For those with wings, fly to your dreams

Dearms are what are found at the end of reality, and your reality if what is found at the end of your dreams.
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2005-06-06 : 16:14:07
Do you have query analyzer open?

Cut and paste the code and execute it.

It will create the column list you want very easily..so you can just cut and paste the code into your statement you're building.

I hope you get a new gig soon.

Where are you located?

Never mind...I love NH

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2005-06-06 : 16:21:11
Hey, how far is Boston by car from Derry? Doesn't look to far...

How close are you to Gunstock?

Do you know what DDL is? Does the link at the bottom of my sig work for you?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Eagle_f90
Constraint Violating Yak Guru

424 Posts
Posted - 2005-06-06 : 16:22:05
quote:
Originally posted by X002548

Do you have query analyzer open?

Cut and paste the code and execute it.

It will create the column list you want very easily..so you can just cut and paste the code into your statement you're building.

I hope you get a new gig soon.

Where are you located?

Never mind...I love NH

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx



You love NH well that makes one of us.

I tryed that code you gave it an it outputs nothing, do I need to modify anything but the use statment?

quote:
Originally posted by X002548

Hey, how far is Boston by car from Derry? Doesn't look to far...

How close are you to Gunstock?

Do you know what DDL is? Does the link at the bottom of my sig work for you?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


1. I think it is a bout 1.5 to 2 hours. Never drove it exept in trafic time, and that makes it like 4 hours.

2. Gunstock ski? About 2 hours. I used to go up there to do testing on the cell towers in that area all the time and ski on my lunch brake

3. That links works, I have no idea what a DDL is and the info on that page makes my head hurt. Now to get an asprin.

--
For those with wings, fly to your dreams

Dearms are what are found at the end of reality, and your reality if what is found at the end of your dreams.
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2005-06-06 : 16:35:26
Well you need to change the table_name to the table the you are going to use, Orders is from the sample Northwind database...

I didn't think beantown was that far.

Map quest say 44 minutes...which means I can make it 30

http://www.mapquest.com/directions/main.adp?go=1&do=nw&rmm=1&un=m&cl=EN&ct=NA&rsres=1&1ahXX=&1y=US&1a=&1c=Derry&1s=NH&1z=&2ahXX=&2y=US&2a=&2c=Boston&2s=MA&2z=



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Eagle_f90
Constraint Violating Yak Guru

424 Posts
Posted - 2005-06-06 : 16:58:23
quote:
Originally posted by X002548

Well you need to change the table_name to the table the you are going to use, Orders is from the sample Northwind database...

I didn't think beantown was that far.

Map quest say 44 minutes...which means I can make it 30

http://www.mapquest.com/directions/main.adp?go=1&do=nw&rmm=1&un=m&cl=EN&ct=NA&rsres=1&1ahXX=&1y=US&1a=&1c=Derry&1s=NH&1z=&2ahXX=&2y=US&2a=&2c=Boston&2s=MA&2z=



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


I have only done the drive in the end of day traffic so I am not sure how long it would take in non traffic.

I changed the table name and it printed all my colums with a "." in front of them.

--
For those with wings, fly to your dreams

Dearms are what are found at the end of reality, and your reality if what is found at the end of your dreams.
Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts
Posted - 2005-06-06 : 18:08:12
If I remember rightly if you want to select all the rows in a single table you can right click on the table in Query Analyser and use the Script option to get a SELECT statement, but it is now past 11pm and I'm at home and I've had a little to drink....
(hic!)


A sarcasm detector, what a great idea.
Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts
Posted - 2005-06-06 : 18:57:32
Should I use * in a intermediate set for calculations? Or can anyone think of a reason against it?

ie:

Select [columns from the * below], DATEDIFF(n,Starthour,Endhour) as TotalMinutes
FROM 
(
  Select *, DATEADD(dd,DATEDIFF(d,MinTime,GETDATE()),MinTime) as StartHour
, DATEADD(dd,DATEDIFF(d,MaxTime,GETDATE()),MaxTime) as EndHour FROM dbo.TimeRange
) AS X


DavidM

A front-end is something that tries to violate a back-end.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-06-06 : 19:05:55
David,

Are you serious?

The answer is to never use * except with EXISTS and NOT EXISTS.

Tara
Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts
Posted - 2005-06-06 : 19:18:48
I am serious! Damn you Tara! :-)

Lets consider if I have 6 of these intermediate steps, where I am building on calculations and then finally doing some JOIN work with it.

I promise I will list out the finally column list, but I really don't want to list out all intermediate columns..

Can I have this one?

DavidM

A front-end is something that tries to violate a back-end.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2005-06-06 : 19:46:09
The code breaks if someone later adds a column named Starthour or Endhour to dbo.TimeRange. It would actually be better if you used the column list in the derived table, and the SELECT * in the final select, since the number of columns would not change.

quote:
Originally posted by byrmol

Should I use * in a intermediate set for calculations? Or can anyone think of a reason against it?

ie:

Select [columns from the * below], DATEDIFF(n,Starthour,Endhour) as TotalMinutes
FROM 
(
  Select *, DATEADD(dd,DATEDIFF(d,MinTime,GETDATE()),MinTime) as StartHour
, DATEADD(dd,DATEDIFF(d,MaxTime,GETDATE()),MaxTime) as EndHour FROM dbo.TimeRange
) AS X


DavidM

A front-end is something that tries to violate a back-end.


CODO ERGO SUM
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-06-07 : 12:36:12
We don't allow developers to use * except with EXISTS. So David, since you're the DBA and the developer, you get to decide it. I personally wouldn't allow it. Even though the cost is minimal, we don't want to receive a performance hit for SQL Server to do the lookup when it could have been avoided in dev.

Tara
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-06-07 : 12:56:24
In Query Analyzer's object browser, (I usually have it to the left):
Expand the database
Expand "Tables"
Expand the table You want to select from
Grab the little folder that says "Columns" into the Query pane.
Columns listed (ok, horizontally.. but that's life).

rockmoose
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site