| Author |
 Topic  |
|
Eagle_f90
Constraint Violating Yak Guru
USA
377 Posts |
 Posted - 06/06/2005 : 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 |
|
|
Eagle_f90
Constraint Violating Yak Guru
USA
377 Posts |
Posted - 06/06/2005 : 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. |
 |
|
|
Bustaz Kool
Flowing Fount of Yak Knowledge
USA
1429 Posts |
Posted - 06/06/2005 : 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)
|
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 06/06/2005 : 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 |
Edited by - Michael Valentine Jones on 06/06/2005 15:19:45 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 06/06/2005 : 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
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
|
|
|
|
Eagle_f90
Constraint Violating Yak Guru
USA
377 Posts |
Posted - 06/06/2005 : 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
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. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 06/06/2005 : 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
|
Edited by - X002548 on 06/06/2005 15:50:59 |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
USA
377 Posts |
Posted - 06/06/2005 : 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. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 06/06/2005 : 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
|
Edited by - X002548 on 06/06/2005 16:14:59 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 06/06/2005 : 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
|
|
|
|
Eagle_f90
Constraint Violating Yak Guru
USA
377 Posts |
Posted - 06/06/2005 : 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. |
Edited by - Eagle_f90 on 06/06/2005 16:29:16 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Eagle_f90
Constraint Violating Yak Guru
USA
377 Posts |
|
|
elwoos
Flowing Fount of Yak Knowledge
United Kingdom
2039 Posts |
Posted - 06/06/2005 : 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. |
Edited by - elwoos on 06/06/2005 18:09:09 |
|
|
|
byrmol
Shed Building SQL Farmer
Australia
1591 Posts |
Posted - 06/06/2005 : 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. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 06/06/2005 : 19:05:55
|
David,
Are you serious?
The answer is to never use * except with EXISTS and NOT EXISTS.
Tara |
|
|
|
byrmol
Shed Building SQL Farmer
Australia
1591 Posts |
Posted - 06/06/2005 : 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. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 06/06/2005 : 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 |
Edited by - Michael Valentine Jones on 06/06/2005 19:51:27 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 06/07/2005 : 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 |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 06/07/2005 : 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 |
|
|
|
Topic |
|
Select * or declare all filds?
