Problem with TEXT data column

Question

question

user-1430 asked Mar 23, '10

Problem with TEXT data column

I have a table with a text column defined that I cannot use the LIKE statement with. The SQL query does not ever return. When I use the same type queryon another table with a text column then the query returns promptly. If I go to another server and run the same query on the problem table it will return. It looks like there is a problem with the text column on this table on this server. Is there anything to look at and change or should I just rebuild the table and see if this will correct the column?

sql-server-2005

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-03-23T17:33:05Z

Matt Whitfield answered Mar 23, '10

Have you tried updating the statistics for the indexes on the table? By 'does not ever return' what do you actually mean - how long have you left it? And have you looked at whether it is stuck waiting for locks? Or even the execution plan (the actual plan rather than the estimated if possible)?

It's a bit difficult to give any meaningful advice with no DDL, no query plan etc.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

user-1430 commented · Mar 26, 2010 at 11:11 AM

I updated the statistice on this table and this corrected the query response. I had not thought of this since update statistics is turned on for this database. Thanks for the input.

0 ·

Matt Whitfield ♦♦ commented · Mar 26, 2010 at 03:12 PM

No worries. As a rule of thumb auto update statistics will update when 20% of the rows change since the last update...

0 ·

Answer 2 · 2010-03-23T18:11:54Z

TimothyAWiseman answered Mar 23, '10

I concur with Matt that the DDL and query plan would help a lot.

But since this is tagged as SQL-server 2005, have you thought about converting it to varchar(max)? There are certainly some reasons this may not be possible (legacy code, etc) but if it is possible varchar(max) tends to be much more intuitive and easier to work with. In SQL Server 2008 text/ntext are considered deprecated.
(http://msdn.microsoft.com/en-us/library/ms143729.aspx)

While it is hard to say without more information, there is a good chance the conversion would fix the problem as well.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-03-23T18:53:59Z

dvroman answered Mar 23, '10

TEXT fields are like the Access MEMO field. They can't be searched directly.
With SQL Server, it's a bit different.

SELECT TextField
FROM Table
WHERE CAST(TextField AS VARCHAR(MAX)) LIKE 'search string'

You may miss some data since the limit on a text field is greater than the limit on VARCHAR(MAX) but it will do it within the limits of VARCHAR.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield ♦♦ commented · Mar 26, 2010 at 02:04 PM

I have to say, this is complete rubbish. Yes you can do LIKE searches on text fields, and not the limit on varchar(MAX) is not less than the limit on text.

0 ·

question