I'm not used to querying text columns with a large amount of data as I've always used NVARCHAR(MAX) and VARCHAR(MAX) but this is not my Database that I'm querying.

myTable has 305,000 rows.

myDescriptionField is a text column and allows nulls & has loads of data in it.

When I run the following query, it returns a row instantly.

SELECT TOP 1 userid
FROM myTable 
WHERE ISNULL(userID,0) > 0 
AND TRIM(SUBSTRING(myDescriptionField,1,1)) is not null

However, when I add this line to the where clause, the query just sits for over a minute (not sure how long it actually takes to finish as I always end up stopping the execution).

AND myDescriptionField NOT LIKE ''

I thought it might be because I'm forced to do a not like as it's a text column and also that it may be where a lot of rows have blanks or null so I tried different combinations of the following :-

AND ISNULL(TRIM(SUBSTRING(myDescriptionField,1,1)),'') <> ''

None of this worked.

So now a quick explanation as to what I need to achieve :-

I just need to find out if at least one row has data in the column myDescriptionField and if so, I can move on to the next table.

Is there a quick way of returning just one row once it's hit one?

  • Can you use LEN? - where len(TRIM(SUBSTRING(myDescriptionField,1,1))) > 0 – Scott Hodgin Jun 6 '18 at 15:18
  • Tried LEN > 0 but still no joy. – J.B. Jun 6 '18 at 15:22
  • I can't alter the table as it's not my database. – J.B. Jun 6 '18 at 15:22
  • Nope. Still no good. Also, isn't my SUBSTRING basically converting it to VARCHAR anyway? – J.B. Jun 6 '18 at 15:36

Can you use DATALENGTH?

    DATALENGTH(myDescriptionField) >= 1
| improve this answer | |
  • This could be the answer. Thanks. I'll just run some test and let you know if it's solved my problem. – J.B. Jun 6 '18 at 15:46

Not sure if you added another predicate, or if you changed the existing one, but you could try like:

SELECT TOP 1 userid
FROM myTable 
WHERE ISNULL(userID,0) > 0 
  AND NULLIF(SUBSTRING(myDescriptionField,1,1),'') is not null;
| improve this answer | |
  • Yes. I did try this but of course it doesn't work because it's a Text field and it returns the following error :- The data types text and varchar are incompatible in the equal to operator. – J.B. Jun 6 '18 at 14:56
  • Totally agree with you. I only added the Trim bit out of desperation and after reading someone else's suggestion on the net. However, taking that out has no affect on the performance. – J.B. Jun 6 '18 at 15:00
  • I see, so you are using substring to cast text to varchar? I'll update my answer – Lennart Jun 6 '18 at 15:00
  • Exactly. As mentioned in my original question, I'm not used to handling Text fields so if there's a better way of doing this I'd be interested. Thanks for your help so far by the way. Also, the substring was an attempt to look at less data. – J.B. Jun 6 '18 at 15:04
  • Still not working unfortunately. – J.B. Jun 6 '18 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.