0

I have a column with TEXT datatype that needs to store huge amounts of text so I have to use the TEXT datatype.

But this column need to be frequently searched. What should I do if I want that search faster than before?

This is my query (max DATALENGTH for column workDescription are 399721)

SELECT [workID],
       [staffID],
       [workDate],
       [startTime],
       [endTime],
       [projectID],
       [timeFor],
       [workType],
       [workTitle],
       [workDescription], ---> text datatype
       [isDeleted],
       [createdDate],
       [createdBy],
       [lastUpdate],
       [updatedBy]
FROM [IndosoftDb_LIVE].[dbo].[tblTimesheet]
WHERE [workDescription] LIKE '%event%'
    OR [workTitle] LIKE '%event%'

Note : my PM doesn't want to change it to VARCHAR(MAX). He was afraid that data is truncated.

Devart
  • 119,203
  • 23
  • 166
  • 186
hernawan adi saputro
  • 25
  • 5
  • 5
    [`varchar(max)` is the replacement for `text`](http://stackoverflow.com/questions/834788/using-varcharmax-vs-text-on-sql-server), so your PM's fears are unfounded. Why is he making technical decisions for the project? – alroc Jan 08 '16 at 02:25
  • 2
    Take a look at this article: http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/ – Hackerman Jan 08 '16 at 02:38
  • What @Hackerman said. You should check out Full Text Search. – Dave Mason Jan 08 '16 at 03:36
  • 3
    `ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) – marc_s Jan 08 '16 at 05:51

1 Answers1

1

so i have to use TEXT datatype

Wrong, you don't - varchar(max) will suit just fine. Don't forget, text, ntext and image data types became deprecated back in 2005. That, and access to their contents via SQL is rather cumbersome, comparing to their (max) successors.

Your PM is incompetent in this particular question, and his suspicions are easy to refute - just open the documentation for the varchar(max) data type.

To answer your question - full-text index might help (the actual improvement will depend on your exact search patterns). But I must warn you - the thing has a rather steep learning curve, and can prove difficult to master.

Roger Wolf
  • 7,307
  • 2
  • 24
  • 33
  • i try to use full text index but the result is not the same so there just 1 solution that is to convert that with varchar(max)? – hernawan adi saputro Jan 08 '16 at 03:11
  • FTS differs from `like` in search capabilities and (sometimes) results. Most probably you will have to rewrite all search queries if you will go to use it. The column datatype does not affect it, however - you can still use `text` if you want. – Roger Wolf Jan 08 '16 at 05:26