Post reply

Exceeding the my varchar data more than 8000..

  • November 1, 2006 at 2:53 am

    #170979

    Hi folks,

         I have a table which has a varchar column holding comma seperated values of integer data. I have declared the length of the column as 8000. But my data in that column is exceeding more than 8000 chars. Do I have any option to increase the size of the column other than declaring the column as TEXT type. or Is there any solution for this?

     

    Thanks.

  • November 1, 2006 at 6:34 am

    #669189

    Instead of storing integer data comma seperated in a single column, could you move this data into a seperate, related table?

  • November 1, 2006 at 8:45 am

    #669228

    Actually most of my queries are dynamic queries. So If I store my data as comma seperated values, then performance of the queries are good. That's way I am proceeding with this. If I do not have any other best solution than the said one, then I wil follow by seperating into some other table.

    Thanks.

  • November 1, 2006 at 10:31 am

    #669264

    Assuming you are using SQL 2k5, just define the column as a varchar(max).

    I have mixed thoughts on providing this advice, because storing a bunch of comma delimeted values in a large varchar column is almost never a good thing to do, but you asked, and I answered...

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • November 1, 2006 at 11:49 am

    #669287

    yeah, it sort of violates "the rules", doesn't it? <rhetorical question>

    Thank-you,
    David Russell
    Any Cloud, Any Database, Oracle since 1982

  • November 1, 2006 at 3:07 pm

    #669358

    Perhaps by moving the integer data into a separate table you will have the opportunity to turn the query performance from good to exceptional - given the right structure and indexing of course.

    Whats the point of having a relational DB and not using the power of the engine?

    Of course, if you never need to directly access the individual bits of data stored in the varchar column, then by all means continue as you are.

    My question for you is do you have to package the data into this varchar, then unpackage it again when you query it?

    The answer should help you decide which is the best path to follow.

    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

  • November 1, 2006 at 11:46 pm

    #669394

    So, you have a table with at least one other column aside from the column that has all of these integers, right?  If so, PLEASE at least do one of the following:

    1. -Create a new table using the following from the first table: A unique identifier, and a column for the INT values. e.g. IdentifyingColumnFromPreviousTable INT NOT NULL, IntValueColumn INT

      -Load one int value per row.

      -Place a clustered index on IdentifyingColumnFromPreviousTable (and the IntValueColumn if you normally query it in some order like ascending or descending).

    2. -If you insist on storing the data in the same table, change the column to a datatype of TEXT and enable the full text searching.

      Then read this: http://www.sql-server-performance.com/tb_search_optimization.asp

     

     


    Enjoy!

    A Brown

    Manage all of your backups in one simple job using this script.

  • November 7, 2006 at 2:18 am

    #670240

    hi

    could you please explain why using varchar(max) is not such a good method in this case

    Thanks

    "Keep Trying"

  • November 7, 2006 at 2:51 am

    #670243

    The simple problem of when some one tries to ask you to find duplicate numbers in such a field, the seperate table with appropriate keys would have prevented this, finding duplicates in a text column is much more difficult.

    Also someone might ask for a list of those that have the same ids, this would be hard with the text column if the ids are not always store in numerical order with an identical amount of whitespace as now you can't simply compare the columns.

    The fact is you start out assuming it will only be used by the application but sooner or later someone will ask you to do something that requires the db to be able to work with the data.

Viewing 9 posts - 1 through 8 (of 8 total)

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