storing large amount of text | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

storing large amount of text

This is my first posting here on this site but I am reading articles and postings on this site for a long time. I am developing a web site which would have more than 1 lac members and I have to store data entered by the user. The data would be in range of 100 characters to 2500 characters and there are two columns of this range per user(these columns would never be used in search, I just have to display them when the information about that user is requested). It also have about 40 other columns per user. My question is that, is it ok to store data of above mentioned two columns in the same table or do I store it in text files and read it from there and just have some code to indicate that file exists or not. As I mentioned,I have 1-3 lac users so is good to store this amount of text for each user in database or should I go for seperate table for these two columns. Regards
Gurpreet Sachdeva
Personally, I would keep the text away from the other columns in the user table, which perhaps might be accessed more frequently youd need to decide this. For the storing of actual text Id consider a seperate dedicated table; there should be no need for textfiles, that will only make life more difficult for you. Another decision to make is whether this table would store userid, and a bit of text (and then have 2 rows for each user), or whether it simply has one row per user with two columns. Again, youd need to think about this. Id tend to opt for 2 rows per user, because then it becomes very easy to maintain should you ever need to store three bits of text for each user, and also if you want to increase the maximum size of the text allowed it is less of an issue.
Chappy’s advice is good. You definintely want to avoid text datatypes as they are slow and hard to work with. If you find that you will need to be retrieving data from this text using wildcard type searches, you might want to consider a third-party program that speeds up text searches, such as the one available fromhttp://www.imptechnology.com. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
]]>