December 2, 2013 at 10:22 pm
Good Day,
we are running SQL Server 2008r2 in our Production environment and 1 of the requirements for the table is to store textmessages in one of its columns. Due to the variable lenght of the messages we are unsure whether we should use varchar(max) or Text . I understand that to add an index to this column could bring the system to a grinding halt. Any ideas ?
Regards.
Lian
December 2, 2013 at 10:38 pm
I heard somewhere that TEXT data type is being deprecated.
Is there any reason you can't use a VARCHAR(8000) type? That would be a pretty long text message.
And are all your text messages in English or other Latin-character based language?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 2, 2013 at 11:02 pm
Good Day,
we use varchar(max) , and I also read Text is to be depricated . My concern is this : You'll have poor performing queries on this column with or without an index, rendering it useless . The sad part is, we need the text message column . It is in English
December 3, 2013 at 6:18 am
Yes, the TEXT Data Type is on the Deprecation list.
http://technet.microsoft.com/en-us/library/ms143729.aspx
Depending on the kind of queries you'd be running on the column, I wonder if a Full Text Index might be helpful.
December 3, 2013 at 6:48 am
lianvh 89542 (12/2/2013)
Good Day,we use varchar(max) , and I also read Text is to be depricated . My concern is this : You'll have poor performing queries on this column with or without an index, rendering it useless . The sad part is, we need the text message column . It is in English
if you need to query a wide varchar field like that for content, i think you'll want to add a full text index on that column, and switch to using queries that take advantage of the full text index.
SELECT ID, Name,AssignedTo, textmessage
FROM dbo.workorders
WHERE AssignedTo= 'lianvh'
AND CONTAINS(textmessage, 'Mountain')
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply