Exceeding the my varchar data more than 8000..

Question

Exceeding the my varchar data more than 8000..

Mahesh-237345

SSC Enthusiast

Points: 177
More actions
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.

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

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

Chris Chilvers SSCrazy Points: 2260 More actions · Answer 1

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

Mahesh-237345 SSC Enthusiast Points: 177 More actions · Answer 2

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.

DCPeterson SSCoach Points: 15266 More actions · Answer 3

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

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

debrucer1 SSCertifiable Points: 5366 More actions · Answer 4

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

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

BeerBeerBeer SSCarpal Tunnel Points: 4021 More actions · Answer 5

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

Andy B. SSC Eights! Points: 890 More actions · Answer 6

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:

-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).
-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.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 7

hi

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

Thanks

"Keep Trying"

Chris Chilvers SSCrazy Points: 2260 More actions · Answer 8

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.