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 Jun 6, 2018 at 15:18
  • Tried LEN > 0 but still no joy. Jun 6, 2018 at 15:22
  • I can't alter the table as it's not my database. Jun 6, 2018 at 15:22
  • Nope. Still no good. Also, isn't my SUBSTRING basically converting it to VARCHAR anyway? Jun 6, 2018 at 15:36

2 Answers 2


Can you use DATALENGTH?

    DATALENGTH(myDescriptionField) >= 1
  • This could be the answer. Thanks. I'll just run some test and let you know if it's solved my problem. Jun 6, 2018 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;
  • 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. Jun 6, 2018 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. Jun 6, 2018 at 15:00
  • I see, so you are using substring to cast text to varchar? I'll update my answer Jun 6, 2018 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. Jun 6, 2018 at 15:04
  • Still not working unfortunately. Jun 6, 2018 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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