USE DB GO IF NOT EXISTS(SELECT Name FROM SysColumns WHERE id=Object_Id('[HIS_QUEUE]') AND Name = 'InsertDateTime') BEGIN ALTER TABLE HIS_QUEUE ADD InsertDateTime datetime DEFAULT (CONVERT([date],CONVERT([varchar](100),getdate(),(23)),(23))) END GO IF NOT EXISTS(SELECT Name FROM SysColumns WHERE id=Object_Id('[QUEUEDETAIL]') AND Name = 'InsertDateTime') BEGIN ALTER TABLE QUEUEDETAIL ADD InsertDateTime datetime DEFAULT (CONVERT([date],CONVERT([varchar](100),getdate(),(23)),(23))) END GO update QUEUEDETAIL set InsertDateTime=(CONVERT([datetime], QUEUEDETAIL.APPOINTDATE ,(20))) update HIS_QUEUE set InsertDateTime=(CONVERT([datetime], HIS_QUEUE.[InsertDate] ,(20))) GO /****** Object: Index [DF_HISQUEUE_InsertDateTime] Script Date: 2023-05-31 10:21:54 ******/ IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'DF_HISQUEUE_InsertDateTime') DROP INDEX [DF_HISQUEUE_InsertDateTime] ON [dbo].[HIS_QUEUE] WITH ( ONLINE = OFF ) GO /****** Object: Index [DF_HISQUEUE_InsertDateTime] Script Date: 2023-05-31 10:21:54 ******/ CREATE CLUSTERED INDEX [DF_HISQUEUE_InsertDateTime] ON [dbo].[HIS_QUEUE] ( InsertDateTime desc, [APPOINTSDATE] DESC, [APPKIND] DESC, [HISEXAMNO] DESC, [HISID] DESC, [NAME] DESC, [QUEUENAME] DESC, [EXECUTEDEPT] DESC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO /****** Object: Index [DF_QUEUEDETAIL_InsertDateTime] Script Date: 2023-05-31 10:34:41 ******/ IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'DF_QUEUEDETAIL_InsertDateTime') DROP INDEX [DF_QUEUEDETAIL_InsertDateTime] ON [dbo].[QUEUEDETAIL] WITH ( ONLINE = OFF ) GO /****** Object: Index [DF_QUEUEDETAIL_InsertDateTime] Script Date: 2023-05-31 10:34:41 ******/ CREATE CLUSTERED INDEX [DF_QUEUEDETAIL_InsertDateTime] ON [dbo].[QUEUEDETAIL] ( InsertDateTime desc, [APPOINTDATE] DESC, [APPKIND] DESC, [HISEXAMNO] DESC, [QUEUENAME] DESC, DIRECTION DESC, --CheckItemRemark DESC, PARTOFCHECK DESC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)
注:主键列默认为聚簇索引,聚簇索引只能有一个,非聚簇索引可以有多个;一般建议在对表创建索引时,使用日期类型的列创建且创建为降序。
在创建复合索引是建议将日期列作为首列,同时在使用列上应遵循——使用频繁使用的列
索引分为:聚集索引、非聚集索引
1.聚集索引--表的存储顺序
在聚集索引中,每张表只能有一个;
在聚集索引中,索引的顺序决定表中数据的顺序;
在聚集索引中,在表中创建主键列或唯一约束时,会自动创建聚集索引(即在表中添加主键列时,数据库引擎会自动创建约束和索引);
2.非聚集索引
非聚集索引,可以有多个;
在非在聚集索引中,索引的结构完全独立与数据行的结构;
在非在聚集索引中,数据表中的数据行和索引的顺序不一致;
查看指定表的索引
exec sp_helpindex his_queue
go
exec sp_helpindex queuedetail
博客内容主要用于日常学习记录,内容比较随意,如有问题,还需谅解!!!