Heard over the cube walls


  • Discourse touched me in a no-no place

    A: "How do you make a column wider in SQL?"

    B: "Create a new table with the same columns, except the chance you need, copy over the data, drop the original table and rename the new one."

    A: "This is production. There are millions of rows. I can't lock the table that long."

    B: "Try it anyway."

     

    God I hope that was some brand of sarcasm. (Of course, TRWTF is that all our data eventually ends up in Foxpro files. Maybe I'll talk about that one once I've been around the place a little longer)



  • ūüėģ



  • It is a solution. It's not the optimal solution, but it is a solution.



  • ¬†Always create table columns with the maximum allowed width. Problem forever solved.



  • ¬†Drag the little groove between column headers. This will let you control the column width.



  • @dhromed said:

     Drag the little groove between column headers. This will let you control the column width.

     

    +1

     



  • @dhromed said:

    Drag the little groove between column headers. This will let you control the column width.
    My thoughts exactly.

    You may even try double-clicking to auto-size it.

     



  • @Mole said:

    It is a solution. It's not the optimal solution, but it is a solution.

     

    This is why math is better than programming.  You only have to show that a solution exists; you don't actually have to go out and find one.

     



  • Well, unless you claimed that you were going to find that solution to begin with.



  • I wouldn't create a new table with all the same columns but I would create a new table with 2 columns: system ID and extra-long data.¬† I'd migrate all the data from the production table into the lookup table then repurpose the old column and populate it with the foreign keys.

    To ensure this new structure works properly going forward (without the need to change the application) I'd set up a trigger to do it automatically.  You'd probably need a couple of nested cursors but it's doable.



  • @Weng said:

    A: "How do you make a column wider in SQL?"

    B: "Create a new table with the same columns, except the chance you need, copy over the data, drop the original table and rename the new one."

    A: "This is production. There are millions of rows. I can't lock the table that long."

    B: "Try it anyway."

     

    God I hope that was some brand of sarcasm. (Of course, TRWTF is that all our data eventually ends up in Foxpro files. Maybe I'll talk about that one once I've been around the place a little longer)

    Heaven forbid that you only backup + drop the COLUMN that you need to change, or run the change outside production hours!



  • @da Doctah said:

    This is why math is better than programming.  You only have to show that a solution exists; you don't actually have to go out and find one.
     

    Yeah, but math has that whole "the answer is in the question" thing down to, erm, a science.



  • @RTapeLoadingError said:

    You'd probably need a couple of nested cursors but it's doable.

    You forgot XML. Lots and lots of XML. Possibily base64 encrypted. But XML.



  • @da Doctah said:

    This is why math is better than programming.  You only have to show that a solution exists;

     

    For a long time I thought that that's why we have virtualisation.

     


  • ‚ôŅ

    @renewest said:

    Always create table columns with the maximum allowed width. Problem forever solved.

    This is 2011! Why do we even have maximum widths?!



  • ¬†SQL Server Management Studio (2005 at least) does exactly that when you modify a column in table design, rather than generate the appropriate alter table alter column command. Though it does use alter table for just adding or removing columns.



  • I love the poetic justice of giving a stupid answer for a stupid question.



  • @Zemm said:

    Possibily base64 encrypted.
    Queue Semantic War CXVII on why base64 is not encryption.



  • @The_Assimilator said:

    @Weng said:

    A: "How do you make a column wider in SQL?"

    B: "Create a new table with the same columns, except the chance you need, copy over the data, drop the original table and rename the new one."

    A: "This is production. There are millions of rows. I can't lock the table that long."

    B: "Try it anyway."

     

    God I hope that was some brand of sarcasm. (Of course, TRWTF is that all our data eventually ends up in Foxpro files. Maybe I'll talk about that one once I've been around the place a little longer)

    Heaven forbid that you only backup + drop the COLUMN that you need to change, or run the change outside production hours!

    ...or just use ALTER TABLE ALTER COLUMN and don't drop anything?

     



  • @RTapeLoadingError said:

    I wouldn't create a new table with all the same columns but I would create a new table with 2 columns: system ID and extra-long data.  I'd migrate all the data from the production table into the lookup table then repurpose the old column and populate it with the foreign keys.

    To ensure this new structure works properly going forward (without the need to change the application) I'd set up a trigger to do it automatically.  You'd probably need a couple of nested cursors but it's doable.

    You should also make sure to sprinkle hints all over the place. Preferably making sure all joins are hash joins.  Also make sure you put nolock everywhere.  For even more fun cut the servers memory in half.


  • @boomzilla said:

    @renewest said:
    Always create table columns with the maximum allowed width. Problem forever solved.

    This is 2011! Why do we even have maximum widths?!

    NVARCHAR(MAX) is like 4 GB.



  • @blakeyrat said:

    @boomzilla said:
    @renewest said:
    Always create table columns with the maximum allowed width. Problem forever solved.

    This is 2011! Why do we even have maximum widths?!

    NVARCHAR(MAX) is like 4 GB.

    "See class, this disk wafer holds the first field, and the next one hold the second field.  We have one drive per record."



  • @hoodaticus said:

    @blakeyrat said:
    @boomzilla said:
    @renewest said:
    Always create table columns with the maximum allowed width. Problem forever solved.

    This is 2011! Why do we even have maximum widths?!

    NVARCHAR(MAX) is like 4 GB.

    "See class, this disk wafer holds the first field, and the next one hold the second field. We have one drive per record."

    The post brought to you from 1997!



  • Still, I never once thought about the possibility of actually SEEING a database¬†field on disk with my naked eye, and it being a sizeable fraction of a square inch.

    I would love to see a non-clustered index applied to a field like that that actually used all the available field width.  Also, can you imagine how long collision resolution would take if the hash values didn't avalanche well?



  • @Weng said:

    B: "Create a new table with the same columns, except the chance you need, copy over the data, drop the original table and rename the new one."


    This is the chance you need. It isn't the chance you want, but it is the chance you need.



  • @blakeyrat said:

    @boomzilla said:
    @renewest said:
    Always create table columns with the maximum allowed width. Problem forever solved.
    This is 2011! Why do we even have maximum widths?!
    NVARCHAR(MAX) is like 4 GB.

     

    Why you could store entire Access databases in individual fields!!



  • @Medezark said:

    @blakeyrat said:

    @boomzilla said:
    @renewest said:
    Always create table columns with the maximum allowed width. Problem forever solved.
    This is 2011! Why do we even have maximum widths?!
    NVARCHAR(MAX) is like 4 GB.

     

    Why you could store entire Access databases in individual fields!!

    You'd want VARBINARY for that.



  • @Medezark said:

    Why you could store entire Access databases in individual fields!!

    Hey, I was going to post the obligatory meme but decided against it



  • @blakeyrat said:

    @Medezark said:

    @blakeyrat said:

    @boomzilla said:
    @renewest said:
    Always create table columns with the maximum allowed width. Problem forever solved.
    This is 2011! Why do we even have maximum widths?!
    NVARCHAR(MAX) is like 4 GB.

     

    Why you could store entire Access databases in individual fields!!

    You'd want VARBINARY for that.
    Somewhere someone will see this thread an immediatly create some sort of antipattern of dependency injection where the stored procedure acts like a factory class and returns an access database based on parameters passed in.... 


  • Oh that's just evil.¬† But probably patentable.

    Say, there's an idea!  What if we went out and got software patents on all the anti-patterns, then sued habitual bad coders into oblivion!



  • @hoodaticus said:

    Oh that's just evil.  But probably patentable.

    Say, there's an idea!  What if we went out and got software patents on all the anti-patterns, then sued habitual bad coders Adobe into oblivion!

    FTFY.



  • @hoodaticus said:

    Oh that's just evil.  But probably patentable.

    Say, there's an idea!  What if we went out and got software patents on all the anti-patterns, then sued habitual bad coders into oblivion!

    Thats actually a patent troll I would love to see.


  • @hoodaticus said:

    Oh that's just evil.  But probably patentable.

    Say, there's an idea!  What if we went out and got software patents on all the anti-patterns, then sued habitual bad coders into oblivion!

    That's the greatest thing I've heard in a while!  Lets do it!



  • @hoodaticus said:

    @Zemm said:
    Possibily base64 encrypted.
    Queue Semantic War CXVII on why base64 is not encryption.
    Well, Zemm did use AIR QUOTES.



  • @hoodaticus said:

    Oh that's just evil.  But probably patentable.

    Say, there's an idea!  What if we went out and got software patents on all the anti-patterns, then sued habitual bad coders into oblivion!

    Beautiful idea, but it would fail due to abundant prior art claims.

     



  • @C-Octothorpe said:

    @hoodaticus said:

    Oh that's just evil.  But probably patentable.

    Say, there's an idea!  What if we went out and got software patents on all the anti-patterns, then sued habitual bad coders into oblivion!

    That's the greatest thing I've heard in a while!  Lets do it!

    But what would happen to us, the brave, the few, the dailywtfer?  We would lose our raison d'être and live happy lives, without bitching, lost forever to oblivion.  Let us come to our sense before it is too late.



  • @Mason Wheeler said:

    @hoodaticus said:

    Oh that's just evil.  But probably patentable.

    Say, there's an idea!  What if we went out and got software patents on all the anti-patterns, then sued habitual bad coders into oblivion!

    Beautiful idea, but it would fail due to abundant prior art claims.

     

    It would never make it to court.  The larger company would settle based that the costs of going to court for this would be higher than the settlement.  throw in even if they did win and the patent was invalidated they troll would simply go under and if set up right there would be nothing for the larger company to reclaim in terms of legal fees. In other words for a large company its a lose/lose.


  • Can't we just

    fieldName NVARCHAR(MAX) PRIMARY KEY
    fieldContents NVARCHAR(MAX)

    No need to ever change the scheme again! ūüôā



  • I like how one snarky and obviously imfeasible suggestion leads to posts explaining how or why it wouldn't work.



  • Just for the record: In Orrible, for creating an index over more than one text column, the sum of the maximum lengths of all those columns may not exceed a certain size (4KB if I recall correctly). That is usually the reason why you limit your varchar columns. If you don't need an index, you can use CLOB with (almost) unlimited size.



  • @mott555 said:

    I like how one snarky and obviously imfeasible suggestion leads to posts explaining how or why it wouldn't work.

     

     That isn't something you should like. All what it does is leads to people thinking those posts are helpful, or that they contribute to a healthy forum. It only encourages people to be pedantic dickweeds, and drives away both new members, and existing members with legitimate topics of conversation. 



  • @mott555 said:

    @hoodaticus said:

    Oh that's just evil.  But probably patentable.

    Say, there's an idea!  What if we went out and got software patents on all the anti-patterns, then sued habitual bad coders Adobe into oblivion!

    FTFY.

    This.



  • @Lorne Kates said:

    @mott555 said:

    I like how one snarky and obviously imfeasible suggestion leads to posts explaining how or why it wouldn't work.

     

     That isn't something you should like. All what it does is leads to people thinking those posts are helpful, or that they contribute to a healthy forum. It only encourages people to be pedantic dickweeds, and drives away both new members, and existing members with legitimate topics of conversation. 

    I like how one snarky and obviously sarcastic comment leads to posts explaining why it's the opposite of what he said.


  • @galgorah said:

    It would never make it to court.  The larger company would settle based that the costs of going to court for this would be higher than the settlement.  throw in even if they did win and the patent was invalidated they troll would simply go under and if set up right there would be nothing for the larger company to reclaim in terms of legal fees. In other words for a large company its a lose/lose.
    We could fund it with Alex and Steve's Bad Code Offsets.  I could represent us.



  • @Lorne Kates said:

    @mott555 said:

    I like how one snarky and obviously imfeasible suggestion leads to posts explaining how or why it wouldn't work.

     

     That
    isn't something you should like. All what it does is leads to people
    thinking those posts are helpful, or that they contribute to a healthy
    forum. It only encourages people to be pedantic dickweeds, and drives
    away both new members, and existing members with legitimate topics of
    conversation.

    Isn't that the sole purpose of this site? I seem to remember the registration form looking something like this:

     


    Please fill out this form to register for TDWTF.

    Username*: <input type="text">

    Password*: <input type="password">

    Confirm Password*: <input type="password">

    <input type="checkbox"> I agree to be a pedantic dickweed, drive away new members, and derail all legitimate topics of conversation.*:

    <input value="Submit" type="submit">

    • denotes a required field.

    Yes I was bored.



  • @Zemm said:

    @RTapeLoadingError said:
    You'd probably need a couple of nested cursors but it's doable.

    You forgot XML. Lots and lots of XML. Possibily base64 encrypted. But XML.

    No thanks. I'll stick to what I know.



  • @hoodaticus said:

    We could fund it with Alex and Steve's Bad Code Offsets.  I could represent us.

    A toad who's a lawyer. Have I wandered into the Chalk by mistake?


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.