| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2005-03-30 : 10:39:33
|
| I'm trying to choose between VARCHAR 4000 or TEXT for a column.Correct me if I'm wrong but you cant put an index on TEXT but you can on VARCHAR. But would you want an index that includes a VARCHAR 4000? That could get massive.Which is better for performance? Are there other considerations? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-03-30 : 10:51:28
|
| You can't use a lot of functions on text fields.Depends on what you want to do with it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-30 : 10:59:47
|
| Yes, I am aware of that. But what about the indexing issue? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-03-30 : 11:02:09
|
| Yep you probably wouldn't want to index that sort of column unless there were a lot of duplicates - in which case you would probably reference the data from another table.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-30 : 11:29:32
|
| So VARCHAR 4000 is preferred but dont put an index on it.Would the same go for a comparison between VARCHAR 8000 and TEXT?VARCHAR 8000 seems very undesireable to me because it often causes problems to UPDATE statements because with just a few other columns in the table, an update of all the columns will cause an error because it effects more than 8016 (dont quote me) bytes. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-03-30 : 12:41:13
|
| 8060Again depends what you will use it for.Another option is to put the 8000 char column in another table linked via the PK.Usually though you don't want to limit things to 8000 chars when you have a requirement for something that size.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-30 : 12:50:42
|
| So if I dont need the functionality of the aforementioned functions:VARCHAR 4000 is preferred over text but dont put an index on itBUTTEXT is preferred over VARCHAR 8000 Wouldn't you say TEXT is preferred over VARCHAR 4000 as well then? |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-03-30 : 17:21:54
|
| VARCHAR is easier to work with than TEXT.Only use TEXT when the data You need to store is larger than the 8000 byte limit of VARCHAR.And don't break the 8060 byte limit;to overcome this limitation do what Nigel suggest and break out the varchar(8000) column to another table.Another example of "avoiding" TEXT can be seen in the syscomments table;if the text of an object definition is > 4000 char long, it is broken up into several rows.If there is an equality comparison or such that would make you want to compare large varchar fields,you could consider to add a computed BINARY_CHECKSUM(bigcolumn) column to your table.rockmoose |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-03-30 : 18:05:52
|
| I'd like to see someone attempt to place an index on a varchar(4000) column. Considering the limit (IIRC) is 900 bytes. That limit required me to add a surrogate to key to few tables (-:If you need intensive searching look at Full Text indexing...DavidMA front-end is something that tries to violate a back-end. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-03-30 : 18:17:23
|
| >> VARCHAR 4000 is preferred over text but dont put an index on it>> BUT>> TEXT is preferred over VARCHAR 8000 You state that as though you get it from my posts.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-30 : 20:22:52
|
quote:
TEXT is preferred over VARCHAR 8000
Well, that's kinda ass-backwards. Personally, I'd prefer multi-rowed varchar(8000) values over text, unless I absolutely NEVER needed to manipulate them. Text/image has a nasty habit of wasting a lot of disk space very quickly, in addition to all its other shortcomings already mentioned.One thing to keep in mind is to QUESTION the need for any large text/varchar column, especially over 1,000 characters. I never assume people really need to store that much data in a column, and I make them defend (and demonstrate) that they do need it. I've seen plenty of systems that allowed people to type entire books in a comment field, and every one of them was badly used. All of the relevant information in a comment was very short, under 500 characters. Naturally, all of the irrelevant crap was very long, and always copied from another comment or comment chain. The end result was that people just stopped reading the comments altogether. Setting limits on how much can be entered is usually a better decision: it's always easier to expand according to demonstrable need than to cut back from some excessive amount. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-31 : 03:18:30
|
| Thanks Robvolk. Brilliant as always.I dont quite get what byrmol said though:Considering the limit (IIRC) is 900 bytes. That limit required me to add a surrogate to key to few tables |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2005-03-31 : 03:25:30
|
| Funnily enough I was looking at this the other day for some reason. You can store a page of A4 text in the default character size in Word in a varchar(4000), or two pages in 8000. If you bear in mind that it would be rare for anyone to want to type that amount of stuff in a text input box, you'd have to wonder why anyone would need anything bigger than that in any one field, and, if they do, you should probably consider making them attach documents and storing them in the file system.-------Moo. :) |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-31 : 03:54:09
|
One of the situations I face is that for years these people have copied emails into text columns as part of there notes about their communication with a client. Let me see if I can go and stir the pot and suggest that they save it as attachments. I'll let you know what happens! |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-31 : 04:10:39
|
| Can I use the added argument that because TEXT columns can't be indexed, there is a significant performance hit?Like Robvolk said to me recently, if you have a covering index you can get al the data from the index without hitting the table. But you can't include the TEXT column in your index. Exactly how does SQL go about retrieving that TEXT column if you do a SELECT if all the columns bar the text column are covered by the index? How much of a performance hit is that? How does that compare to persisting the data to disk as attachments instead? - like mr mist suggested? |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-31 : 04:26:52
|
| Sorry for all the questions...The old database that I am converting from has a dictionary table of keywords.That old system takes all the keywords that the user selects for a company, puts it in a comma seperated list, and saves it to a text column on the company table. All this instead of a link table!The old developer is arguing the toss that doing it that way is better because he then uses arrays to take data out of that text column in put it back in. He says client-side arrays will always provide him with better performance than relying on SQL Joins. Is that true? (We have 500 users). Or can anyone provide me with any ammunition to put a strong case over that means there will not be a need for that text column in the new database? |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-31 : 06:12:49
|
| Boy oh boy, Just went and suggested a change.Response wasnt good. The argument is that as a user navigates our list of tens of thousands of companies, they want to see all the keywords as they flick through the companies. Having a comma-seperated list already pre-processed against the company record provides a (supposedly) significant performance increase because you dont have to go through a link table to get to the KeyWords table to build up the list of all the KeyWords. Even with all the right indexes they say pre-processed is faster.So against the company in the text column you may have "COBOL, Visual Basic, Clipper".But in the KeyWords table you would have 4 records: COBOL, Visual, Basic & Clipper.This is because they currently also split on space.I really dont like any of this and I want to come up with a system that is faster or the same but better designed. Could Views help?Or can all of this be replaced by Full-text searching in SQL2000 and provide better performance?I would especially appreciate a reply by Robvolk on this one but all advice is most appreciated! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-31 : 08:13:46
|
Here's a technique for doing keyword searches that might help you:http://www.sqlteam.com/item.asp?ItemID=5857Full-text searching should eliminate the need to use this technique, but it's there in case you need it. I think it's pretty frickin' cool anyway.  quote:
The old developer is arguing the toss that doing it that way is better because he then uses arrays to take data out of that text column in put it back in
Your old developer is a moron, and has no business using relational databases. There's no reason to "take data out" or "put it back in" if you're just searching it. Let him put all the data into a text file and search it, that WOULD be more efficient than putting it into a database the way he describes. If he protests, tell him that's exactly what he's doing with the current code anyway.You can always split keywords into multiple rows and reassemble them as CSV's on demand. Plenty of examples of that here:http://www.sqlteam.com/searchresults.asp?SearchTerms=csvI know someone(s) has posted a UDF that creates a CSV too, can't find it naturally but I think it's in the Script Library forum. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-31 : 08:37:51
|
| Like you advised though, I have questioned the use of the text column for storing all the keywords.So do you think Full-text searching will be faster? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-31 : 08:41:48
|
| I doubt it would be slower. There's no way to tell without looking at how the data is being used. I'd say you have nothing to lose by investigating it. The thing is, it won't help much if the rest of the app is written in a way that can't take advantage of it. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-31 : 08:51:39
|
| We are re-writing the VB6 app into VB.Net as well. So we have the opportunity to revise how it has been done and leverage new technology.Have you used Full-text searching? What's been your exprience with it if you have? |
|
|
|
Next Page
|
VARCHAR 4000 or TEXT?