SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Select * or declare all filds?
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

Eagle_f90
Constraint Violating Yak Guru

USA
422 Posts

Posted - 06/06/2005 :  14:42:59  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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 - 06/06/2005 :  14:46:55  Show Profile  Reply with Quote
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

Edited by - X002548 on 06/06/2005 15:50:33
Go to Top of Page

Eagle_f90
Constraint Violating Yak Guru

USA
422 Posts

Posted - 06/06/2005 :  14:55:31  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1645 Posts

Posted - 06/06/2005 :  15:16:56  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 06/06/2005 :  15:18:43  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 06/06/2005 15:19:45
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/06/2005 :  15:23:18  Show Profile  Reply with Quote
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


USE Northwind
GO

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



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

USA
422 Posts

Posted - 06/06/2005 :  15:42:01  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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


USE Northwind
GO

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



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 - 06/06/2005 :  15:49:40  Show Profile  Reply with Quote
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

Edited by - X002548 on 06/06/2005 15:50:59
Go to Top of Page

Eagle_f90
Constraint Violating Yak Guru

USA
422 Posts

Posted - 06/06/2005 :  16:06:11  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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 - 06/06/2005 :  16:14:07  Show Profile  Reply with Quote
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

Edited by - X002548 on 06/06/2005 16:14:59
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/06/2005 :  16:21:11  Show Profile  Reply with Quote
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

USA
422 Posts

Posted - 06/06/2005 :  16:22:05  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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.

Edited by - Eagle_f90 on 06/06/2005 16:29:16
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/06/2005 :  16:35:26  Show Profile  Reply with Quote
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

USA
422 Posts

Posted - 06/06/2005 :  16:58:23  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 06/06/2005 :  18:08:12  Show Profile  Reply with Quote
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.

Edited by - elwoos on 06/06/2005 18:09:09
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 06/06/2005 :  18:57:32  Show Profile  Reply with Quote
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

USA
36648 Posts

Posted - 06/06/2005 :  19:05:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Australia
1591 Posts

Posted - 06/06/2005 :  19:18:48  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 06/06/2005 :  19:46:09  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 06/06/2005 19:51:27
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36648 Posts

Posted - 06/07/2005 :  12:36:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Sweden
3279 Posts

Posted - 06/07/2005 :  12:56:24  Show Profile  Reply with Quote
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
Page: of 3 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.28 seconds. Powered By: Snitz Forums 2000