SELECT All Columns from a table - without re-engineering if table stucture changes

Question

SELECT All Columns from a table - without re-engineering if table stucture changes

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Toby Harman SSCarpal Tunnel Points: 4168 More actions · Answer 1

If all you are doing is writing that data out to another file and you *always* want *every* column, then SELECT * would be fine.

If you can't categorically say that you always want every column, regardless of structural changes (and note, that may result in fewer columns too) then code it with explicit columns.

Basically I see this as one of those occasions where we break the rules to keep it simple....

1st Rule of Programming - Keep it simple.

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 2

ASDL (12/14/2010)
I understand that but in my case I have to get all columns.

Please understand that that's usually a bad thing to do because it makes it real tough on index usage and performance. That's why people keep bugging you about it.

I agree with Gus' original comment, though. What does the table hold and why are you frequently adding columns to it? Is it a reporting table with something like a column for every month? If so, then a horrible design error has been made because of the very thing you're fighting right now.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Resender SSCertifiable Points: 7443 More actions · Answer 3

I've been taught to avoid the use of select all, so method 2 is indeed one way you could do it.

However a table that changes a lot getting new columns doesn't sound like a good thing to me,

(well in development and test ok)

grahamc SSCertifiable Points: 6762 More actions · Answer 4

If you plan on use Dynamic SQL, how are the new columns created? If the users are able to create them and name them however they want, use the SELECT *. Im pretty sure you could create an "interesting" column name that would get executed with the compile of the dynamic SQL 😉

ASDL Old Hand Points: 388 More actions · Answer 5

Jeff Moden (12/14/2010)
ASDL (12/14/2010)
I understand that but in my case I have to get all columns.
Please understand that that's usually a bad thing to do because it makes it real tough on index usage and performance. That's why people keep bugging you about it.
I agree with Gus' original comment, though. What does the table hold and why are you frequently adding columns to it? Is it a reporting table with something like a column for every month? If so, then a horrible design error has been made because of the very thing you're fighting right now.

Jeff, I agree with your comments but I want to find out the best possible way I can do this within the limited options I have here.

This is not a reporting table or has any columns added dynamically, like I said before I am being asked to write "future" proof (I know there is no such thing but I am trying to catch most cases) code so that any changes to table columns does not affect the consumer of this SP. The consumer code is a SQLCLR SP that writes the output to a file, which is actually dumping the whole table's data to a file for recovery in future, etc.

I cannot use BCP, DTS, SSIS.

Please see my replies to Wayne, GSquared and CirquedeSQLeil it has more information.

ASDL Old Hand Points: 388 More actions · Answer 6

Resender (12/15/2010)
I've been taught to avoid the use of select all, so method 2 is indeed one way you could do it.
However a table that changes a lot getting new columns doesn't sound like a good thing to me,
(well in development and test ok)

I prefer to use Method #1 by specifying columns I need and change SP if the table changes but in this case I think following Method #3 makes sense since Method #2 is an overkill. 🙂

I am not able to understand any benefit in Method 2 over Method 3. That is my biggest questions!?

Resender, please see my replies to Wayne, GSquared and CirquedeSQLeil above that has more information.

ASDL Old Hand Points: 388 More actions · Answer 7

grahamc (12/15/2010)
If you plan on use Dynamic SQL, how are the new columns created? If the users are able to create them and name them however they want, use the SELECT *. Im pretty sure you could create an "interesting" column name that would get executed with the compile of the dynamic SQL 😉

grahamc, the new columns may be created by developers in the future. Plus this code has to run on all versions of our product so say an old version may have just 2 columns in a table but in latest version same table may have 5 columns. So, this code should be versatile enough to accommodate all those changes.

I hope I will not have to go through "interesting" problems as developers will manually be creating columns and this code runs in the background not many people will have access to it. 🙂

Please see my replies to Wayne, GSquared and CirquedeSQLeil it has more information.

WayneS SSC Guru Points: 95460 More actions · Answer 8

Toby Harman (12/14/2010)
If all you are doing is writing that data out to another file and you *always* want *every* column, then SELECT * would be fine.
If you can't categorically say that you always want every column, regardless of structural changes (and note, that may result in fewer columns too) then code it with explicit columns.
Basically I see this as one of those occasions where we break the rules to keep it simple....
1st Rule of Programming - Keep it simple.

Toby,

I have to agree. In this case (the only consumer of the data can handle a variable number of columns), I would say that the OP needs to push the DBA to allow the select * for this one case. Dynamic sql is needlessly complex in this situation. Every rule has exceptions, and this case is one of those.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

WayneS (12/15/2010)
Toby Harman (12/14/2010)
If all you are doing is writing that data out to another file and you *always* want *every* column, then SELECT * would be fine.
If you can't categorically say that you always want every column, regardless of structural changes (and note, that may result in fewer columns too) then code it with explicit columns.
Basically I see this as one of those occasions where we break the rules to keep it simple....
1st Rule of Programming - Keep it simple.
Toby,
I have to agree. In this case (the only consumer of the data can handle a variable number of columns), I would say that the OP needs to push the DBA to allow the select * for this one case. Dynamic sql is needlessly complex in this situation. Every rule has exceptions, and this case is one of those.

I would take that statement a bit further. The rule imposed by this dba is needless. It looks like an effort to avoid future work.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

WayneS SSC Guru Points: 95460 More actions · Answer 10

CirquedeSQLeil (12/15/2010)
WayneS (12/15/2010)
Toby Harman (12/14/2010)
If all you are doing is writing that data out to another file and you *always* want *every* column, then SELECT * would be fine.
If you can't categorically say that you always want every column, regardless of structural changes (and note, that may result in fewer columns too) then code it with explicit columns.
Basically I see this as one of those occasions where we break the rules to keep it simple....
1st Rule of Programming - Keep it simple.
Toby,
I have to agree. In this case (the only consumer of the data can handle a variable number of columns), I would say that the OP needs to push the DBA to allow the select * for this one case. Dynamic sql is needlessly complex in this situation. Every rule has exceptions, and this case is one of those.
I would take that statement a bit further. The rule imposed by this dba is needless. It looks like an effort to avoid future work.

I'm not sure I'd agree: a rule to not allow a select * is IMO a pretty good one. However, all rules have exceptions. To avoid needless complexity, this case should be one of those.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

WayneS (12/15/2010)
CirquedeSQLeil (12/15/2010)
WayneS (12/15/2010)
Toby Harman (12/14/2010)
If all you are doing is writing that data out to another file and you *always* want *every* column, then SELECT * would be fine.
If you can't categorically say that you always want every column, regardless of structural changes (and note, that may result in fewer columns too) then code it with explicit columns.
Basically I see this as one of those occasions where we break the rules to keep it simple....
1st Rule of Programming - Keep it simple.
Toby,
I have to agree. In this case (the only consumer of the data can handle a variable number of columns), I would say that the OP needs to push the DBA to allow the select * for this one case. Dynamic sql is needlessly complex in this situation. Every rule has exceptions, and this case is one of those.
I would take that statement a bit further. The rule imposed by this dba is needless. It looks like an effort to avoid future work.
I'm not sure I'd agree: a rule to not allow a select * is IMO a pretty good one. However, all rules have exceptions. To avoid needless complexity, this case should be one of those.

The justification for the DBA making up this rule is to prevent code changes in the future when a schema change occurs. That for me is lacking. The OP wants to list out all of the columns which I agree with - it doesn't make the code any more complex. But in order to meet the requirement of the DBA to prevent future changes to code, then only option 2 or 3 will work.

I don't agree with using Select * just to prevent future code changes. I have come across many databases where procs were Select *. The problems we saw ran the gambit from poor performance to errors that didn't make sense in the application.

I agree that there are exceptions to rules. Those exceptions need to be backed by sound logic and requirements though.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ASDL Old Hand Points: 388 More actions · Answer 12

Thanks all! This was a good discussion.

My options are Method 2 and Method 3, I'll choose #3 (SELECT * query) because it is simple and AFAIK I would get similar performance in both plus I will be able to use sp_depends on it which will not be possible with dynamic query.

Only comment I have is that not everyone reads the whole post before suggesting/answering. 🙂

Now, time to go bug that DBA. 😛

Thanks,

ASDL

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

ASDL (12/15/2010)
Thanks all! This was a good discussion.
My options are Method 2 and Method 3, I'll choose #3 (SELECT * query) because it is simple and AFAIK I would get similar performance in both plus I will be able to use sp_depends on it which will not be possible with dynamic query.
Only comment I have is that not everyone reads the whole post before suggesting/answering. 🙂
Now, time to go bug that DBA. 😛
Thanks,
ASDL

You're welcome

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 14

ASDL (12/15/2010)
Thanks all! This was a good discussion.
My options are Method 2 and Method 3, I'll choose #3 (SELECT * query) because it is simple and AFAIK I would get similar performance in both plus I will be able to use sp_depends on it which will not be possible with dynamic query.
Only comment I have is that not everyone reads the whole post before suggesting/answering. 🙂
Now, time to go bug that DBA. 😛
Thanks,
ASDL

Only comment I have is that it took 15 posts on this thread for you to finally tell us what you were actually doing instead of including it on the first post. I don't blame a soul for not reading all the posts for such things. You shouldn't either. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)