实现全文搜索
全文搜索对于每个 SQL Server 实例都可用且在 SMO 中由 FullTextService 对象来表示。FullTextService 对象位于 Server 对象下。它用于管理 Microsoft 全文搜索服务的配置选项。FullTextCatalogCollection 对象属于 Database 对象,并且它是表示为数据库定义的全文目录的 FullTextCatalog 对象的集合。与普通索引不同,只能为每个表定义一个全文索引。此索引由 Table 对象中的 FullTextIndexColumn 对象表示。
若要创建全文搜索服务,必须为数据库定义一个全文目录,并为数据库的其中一个表定义一个全文搜索索引。
首先,通过调用 FullTextCatalog 构造函数并指定目录名称,为数据库创建全文目录。其次,通过调用该构造函数并指定要为其创建全文索引的表,创建全文索引。接着,通过使用 FullTextIndexColumn 对象并提供表中列的名称,可以为全文索引添加索引列。然后,为已创建的目录设置 CatalogName 属性。最后,调用 Create 方法并为 SQL Server 实例创建全文索引。
示例
若要使用所提供的任何代码示例,您必须选择创建应用程序所需的编程环境、编程模板和编程语言。有关详细信息,请参阅如何在 Visual Studio .NET 中创建 Visual Basic SMO 项目或如何在 Visual Studio .NET 中创建 Visual C# SMO 项目。
在 Visual Basic 中创建全文搜索服务
此代码示例为 AdventureWorks2008R2 示例数据库中的 ProductCategory 表创建全文搜索目录。然后,它会为 ProductCategory 表中的 Name 列创建全文搜索索引。全文搜索索引要求已为该列定义唯一索引。
' compile with:
' /r:Microsoft.SqlServer.SqlEnum.dll
' /r:Microsoft.SqlServer.Smo.dll
' /r:Microsoft.SqlServer.ConnectionInfo.dll
' /r:Microsoft.SqlServer.Management.Sdk.Sfc.dll
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Sdk.Sfc
Imports Microsoft.SqlServer.Management.Common
Public Class A
Public Shared Sub Main()
' Connect to the local, default instance of SQL Server.
Dim srv As Server = Nothing
srv = New Server()
' Reference the AdventureWorks database.
Dim db As Database = Nothing
db = srv.Databases("AdventureWorks")
' Reference the ProductCategory table.
Dim tb As Table = Nothing
tb = db.Tables("ProductCategory", "Production")
' Define a FullTextCatalog object variable by specifying the parent database and name arguments in the constructor.
Dim ftc As FullTextCatalog = Nothing
ftc = New FullTextCatalog(db, "Test_Catalog")
ftc.IsDefault = True
' Create the Full-Text Search catalog on the instance of SQL Server.
ftc.Create()
' Define a FullTextIndex object varaible by supplying the parent table argument in the constructor.
Dim fti As FullTextIndex = Nothing
fti = New FullTextIndex(tb)
' Define a FullTextIndexColumn object variable by supplying the parent index and column name arguements in the constructor.
Dim ftic As FullTextIndexColumn = Nothing
ftic = New FullTextIndexColumn(fti, "Name")
' Add the indexed column to the index.
fti.IndexedColumns.Add(ftic)
fti.ChangeTracking = ChangeTracking.Automatic
' Specify the unique index on the table that is required by the Full Text Search index.
fti.UniqueIndexName = "AK_ProductCategory_Name"
' Specify the catalog associated with the index.
fti.CatalogName = "Test_Catalog"
' Create the Full Text Search index on the instance of SQL Server.
fti.Create()
End Sub
End Class
在 Visual C# 中创建全文搜索服务
此代码示例为 AdventureWorks2008R2 示例数据库中的 ProductCategory 表创建全文搜索目录。然后,它会为 ProductCategory 表中的 Name 列创建全文搜索索引。全文搜索索引要求已为该列定义唯一索引。
// compile with:
// /r:Microsoft.SqlServer.SqlEnum.dll
// /r:Microsoft.SqlServer.Smo.dll
// /r:Microsoft.SqlServer.ConnectionInfo.dll
// /r:Microsoft.SqlServer.Management.Sdk.Sfc.dll
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using Microsoft.SqlServer.Management.Common;
public class A {
public static void Main() {
// Connect to the local, default instance of SQL Server.
Server srv = default(Server);
srv = new Server();
// Reference the AdventureWorks database.
Database db = default(Database);
db = srv.Databases ["AdventureWorks"];
// Reference the ProductCategory table.
Table tb = default(Table);
tb = db.Tables["ProductCategory", "Production"];
// Define a FullTextCatalog object variable by specifying the parent database and name arguments in the constructor.
FullTextCatalog ftc = default(FullTextCatalog);
ftc = new FullTextCatalog(db, "Test_Catalog");
ftc.IsDefault = true;
// Create the Full-Text Search catalog on the instance of SQL Server.
ftc.Create();
// Define a FullTextIndex object varaible by supplying the parent table argument in the constructor.
FullTextIndex fti = default(FullTextIndex);
fti = new FullTextIndex(tb);
// Define a FullTextIndexColumn object variable by supplying the parent index and column name arguements in the constructor.
FullTextIndexColumn ftic = default(FullTextIndexColumn);
ftic = new FullTextIndexColumn(fti, "Name");
// Add the indexed column to the index.
fti.IndexedColumns.Add(ftic);
fti.ChangeTracking = ChangeTracking.Automatic;
// Specify the unique index on the table that is required by the Full Text Search index.
fti.UniqueIndexName = "AK_ProductCategory_Name";
// Specify the catalog associated with the index.
fti.CatalogName = "Test_Catalog";
// Create the Full Text Search index on the instance of SQL Server.
fti.Create();
}
}
在 PowerShell 中创建全文搜索服务
此代码示例为 AdventureWorks2008R2 示例数据库中的 ProductCategory 表创建全文搜索目录。然后,它会为 ProductCategory 表中的 Name 列创建全文搜索索引。全文搜索索引要求已为该列定义唯一索引。
# Example of implementing a full text search on the default instance.
# Set the path context to the local, default instance of SQL Server and database tables
CD \sql\localhost\default\databases
$db = get-item AdventureWorks2008R2
CD AdventureWorks\tables
#Get a reference to the table
$tb = get-item Production.ProductCategory
# Define a FullTextCatalog object variable by specifying the parent database and name arguments in the constructor.
$ftc = New-Object -TypeName Microsoft.SqlServer.Management.SMO.FullTextCatalog -argumentlist $db, "Test_Catalog2"
$ftc.IsDefault = $true
# Create the Full Text Search catalog on the instance of SQL Server.
$ftc.Create()
# Define a FullTextIndex object variable by supplying the parent table argument in the constructor.
$fti = New-Object -TypeName Microsoft.SqlServer.Management.SMO.FullTextIndex -argumentlist $tb
# Define a FullTextIndexColumn object variable by supplying the parent index
# and column name arguments in the constructor.
$ftic = New-Object -TypeName Microsoft.SqlServer.Management.SMO.FullTextIndexColumn -argumentlist $fti, "Name"
# Add the indexed column to the index.
$fti.IndexedColumns.Add($ftic)
# Set change tracking
$fti.ChangeTracking = [Microsoft.SqlServer.Management.SMO.ChangeTracking]::Automatic
# Specify the unique index on the table that is required by the Full Text Search index.
$fti.UniqueIndexName = "AK_ProductCategory_Name"
# Specify the catalog associated with the index.
$fti.CatalogName = "Test_Catalog2"
# Create the Full Text Search Index
$fti.Create()