SQL Server; TEXT列上的索引 [英] SQL Server; index on TEXT column

问题描述

我有一个包含多个列的数据库表;其中大多数是 VARCHAR（x）类型列，其中一些列有一个索引，以便我可以快速搜索其中的数据。

I have a database table with several columns; most of them are VARCHAR(x) type columns, and some of these columns have an index on them so that I can search quickly for data inside it.

但是，其中一列是 TEXT 列，因为它包含大量数据（23平原ascii文本的kb等）。我希望能够在该列中搜索（... WHERE col1 LIKE'％搜索字符串％'... ），但目前正在执行查询。我知道由于这个列搜索，查询很慢，因为当我从 WHERE 子句中删除该条件时，查询就会立即完成（我会考虑）。

However, one of the columns is a TEXT column, because it contains a very large amount of data (23 kb of plain ascii text etc). I want to be able to search in that column (... WHERE col1 LIKE '%search string%'... ), but currently it's taking forever to perform the query. I know that the query is slow because of this column search because when I remove that criteria from the WHERE clause the query completes (what I would consider), instantaneously.

我无法在此列上添加索引，因为该选项在SQL Server Management Studio的索引构建器/向导中对该列显示为灰色。

I can't add an index on this column because that option is grayed out for that column in the index builder / wizard in SQL Server Management Studio.

我在这里有什么选择，加快该列中的查询搜索？

What are my options here, to speed up the query search in that column?

感谢您的时间...

更新

好​​的，所以我调查了全文搜索并做了所有这些，现在我想要运行查询。但是，当使用包含时，它只接受一个单词;如果我需要一个确切的短语怎么办？ ... WHERE CONTAINS（col1，'search phrase'）... 抛出错误。

抱歉，我是SQL Server的新手

Sorry, I'm new to SQL Server

更新2
对不起，刚想通了;使用多个包含子句而不是一个包含多个单词的子句。实际上，这仍然没有得到我想要的（确切的短语）它只能确保短语中的所有单词都存在。

Update 2 sorry, just figured it out; use multiple "contains" clauses instead of one clause with multiple words. Actually, this still doesn't get what I want (the exact phrase) it only makes sure that all words in the phrase are present.

推荐答案

搜索TEXT字段总是很慢。尝试全文搜索并看看它是否适合你。

Searching TEXT fields is always pretty slow. Give Full Text Search a try and see if that works better for you.

这篇关于SQL Server; TEXT列上的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！