Post reply

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

  • December 14, 2010 at 11:51 pm

    #1262641

    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.

  • December 14, 2010 at 11:52 pm

    #1262642

    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)
    Intro to Tally Tables and Functions

  • December 15, 2010 at 1:48 am

    #1262675

    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)

  • December 15, 2010 at 2:07 am

    #1262687

    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 😉

  • December 15, 2010 at 11:23 am

    #1263040

    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.

  • December 15, 2010 at 11:28 am

    #1263045

    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.

  • December 15, 2010 at 11:34 am

    #1263050

    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.

  • December 15, 2010 at 11:40 am

    #1263052

    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

  • December 15, 2010 at 12:14 pm

    #1263070

    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

  • December 15, 2010 at 1:01 pm

    #1263105

    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

  • December 15, 2010 at 1:11 pm

    #1263115

    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

  • December 15, 2010 at 8:51 pm

    #1263272

    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

  • December 15, 2010 at 9:20 pm

    #1263281

    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

  • December 15, 2010 at 9:49 pm

    #1263292

    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)
    Intro to Tally Tables and Functions

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

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