在定义全文检索时,报错:没有可用的唯一索引,必须在此表上定义唯一列。但是我已经在表上创建了主键啊,请赐教!
CREATE TABLE [dbo].[JZGLDoc](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [bigint] NOT NULL,
[NAME] [varchar](128) NOT NULL,
[EXTENT] [varchar](20) NULL,
[CONTENT] [image] NULL,
[Uploader] [varchar](32) NOT NULL,
[FileYear] [int] NOT NULL,
[UpdateTime] [datetime] NOT NULL CONSTRAINT [DF_JZGLDoc_UpdateTime] DEFAULT (getdate()),
[Re] [varchar](64) NULL,
CONSTRAINT [PK_JZGLDoc] PRIMARY KEY NONCLUSTERED
(
[ParentID] ASC,
[NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[JZGLDoc](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [bigint] NOT NULL,
[NAME] [varchar](128) NOT NULL,
[EXTENT] [varchar](20) NULL,
[CONTENT] [image] NULL,
[Uploader] [varchar](32) NOT NULL,
[FileYear] [int] NOT NULL,
[UpdateTime] [datetime] NOT NULL CONSTRAINT [DF_JZGLDoc_UpdateTime] DEFAULT (getdate()),
[Re] [varchar](64) NULL,
CONSTRAINT [PK_JZGLDoc] PRIMARY KEY NONCLUSTERED
(
[ParentID] ASC,
[NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
但是,我觉得对ID一个字段建立索引太浪费了,这个字段用得很少。
我的建议是
id 聚集主键
(ParentID,Name)非聚集唯一索引这是根据你的表结构和经验得出的,具体情况是否符合你自己考虑下。
有全文检索的表一般会按自增字段(整数型)建立聚集主键,对提高全文检索的性能是有帮助的,具体原理这里就不说了,与全文索引的结构有关