SQL Server中全文搜索的好变量类型是什么? [英] What is good variable type for full text search in SQL Server
问题描述
我在MS SQL Server中声明了一个新的存储过程来实现全文搜索,并且我的查询是这样的:
从表中选择* WHERE FREETEXT((col1,col2),@Search_Statement)
精确写成的波纹管:
什么是@Search_Statement变量的正确数据类型以支持大多数搜索并具有最佳性能?
(我知道如果我让@Search_Statement变量变长,它支持更多的搜索查询,但执行更长的@Search_Statement查询可能会影响性能,Google会将搜索查询限制为32个字词: https://www.linkedin.com/grp/post/1176637-216124982 )
我不知道在我的存储过程中使用@Search_Statement的哪些数据类型:Nvarchar(100),Nvarchar(500)或Nvarchar(MAX)
哪一个是最佳选择? (性能和所有其他方面)
更新:我知道我应该使用nvarchar,但是nvarchar的大小是什么? nvarchar(100),(200),(500),...甚至是nvarchar(MAX)?
< blockquote>
FREETEXT({column_name |(column_list)| *}
,'freetext_string'[,LANGUAGE language_term])
freetext_string是 nvarchar 。当另一个字符数据类型用作输入时,会发生隐式转换。在以下示例中,定义为varchar(30)的@SearchWord变量会在FREETEXT谓词中导致隐式转换。
I am declaring a new stored procedure in MS SQL Server for implementing Full Text Search and my query is some thing like this:
Select * from table WHERE FREETEXT((col1, col2), @Search_Statement)
The question has exactly written bellow:
What is the proper data type for @Search_Statement variable to support most of searches and also have the best performance?
(I know that if I make @Search_Statement variable long it supports more search queries but executing queries with longer @Search_Statement may affect performance and google limit search queries to 32 words: https://www.linkedin.com/grp/post/1176637-216124982)
I don't know which of these datatypes to use for @Search_Statement in my stored procedure: Nvarchar(100), Nvarchar(500) or Nvarchar(MAX) Which would be the best choice? (Performance, and all other aspects)
Update: I know I should use nvarchar but what is proper size of nvarchar? nvarchar(100),(200),(500),... or even nvarchar(MAX)?
According to FREETEXT Documentation
FREETEXT ( { column_name | (column_list) | * } , 'freetext_string' [ , LANGUAGE language_term ] )
freetext_string is nvarchar. An implicit conversion occurs when another character data type is used as input. In the following example, the @SearchWord variable, which is defined as varchar(30), causes an implicit conversion in the FREETEXT predicate.
这篇关于SQL Server中全文搜索的好变量类型是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!