起因:有个老程序,源代码版本也不知道是哪个了,有个界面的数据排序有客户反映自动变。
用事件探查器查询发现没有用order by
:select * from EMR_Record_Content
我把原来的标识列bm的聚集索引去了,加了列rowindex的聚集索引,这样以后是否能保证 select * from EMR_Record_Content
+ where 条件后的且只有这1个表的查询的顺序是按rowindex的顺序输出记录吗?
CREATE TABLE [dbo].[EMR_Record_Content](
[bm] [bigint] IDENTITY(1,1) NOT NULL,
[TempletKey] [bigint] NOT NULL,
[UserID] [bigint] NOT NULL,
[RecordID] [bigint] NOT NULL,
[RowIndex] [bigint] NOT NULL CONSTRAINT [DF_EMR_Record_Content_Order] DEFAULT ((0)),
[FiledValue] [varchar](max) NOT NULL CONSTRAINT [DF_EMR_Content_Record_FiledValue] DEFAULT (''),
[FieldData] [varchar](max) NOT NULL CONSTRAINT [DF_EMR_Content_Record_FieldData] DEFAULT (''),
[isprint] [bit] NULL,
[type] [tinyint] NULL,
[isdelete] [bit] NULL,
CONSTRAINT [PK_EMR_Record_Content] PRIMARY KEY NONCLUSTERED
(
[bm] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 30) ON [PRIMARY]
) ON [PRIMARY]
CREATE CLUSTERED INDEX IX_EMR_Record_Content ON dbo.EMR_Record_Content
(
RowIndex
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]go
用事件探查器查询发现没有用order by
:select * from EMR_Record_Content
我把原来的标识列bm的聚集索引去了,加了列rowindex的聚集索引,这样以后是否能保证 select * from EMR_Record_Content
+ where 条件后的且只有这1个表的查询的顺序是按rowindex的顺序输出记录吗?
CREATE TABLE [dbo].[EMR_Record_Content](
[bm] [bigint] IDENTITY(1,1) NOT NULL,
[TempletKey] [bigint] NOT NULL,
[UserID] [bigint] NOT NULL,
[RecordID] [bigint] NOT NULL,
[RowIndex] [bigint] NOT NULL CONSTRAINT [DF_EMR_Record_Content_Order] DEFAULT ((0)),
[FiledValue] [varchar](max) NOT NULL CONSTRAINT [DF_EMR_Content_Record_FiledValue] DEFAULT (''),
[FieldData] [varchar](max) NOT NULL CONSTRAINT [DF_EMR_Content_Record_FieldData] DEFAULT (''),
[isprint] [bit] NULL,
[type] [tinyint] NULL,
[isdelete] [bit] NULL,
CONSTRAINT [PK_EMR_Record_Content] PRIMARY KEY NONCLUSTERED
(
[bm] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 30) ON [PRIMARY]
) ON [PRIMARY]
CREATE CLUSTERED INDEX IX_EMR_Record_Content ON dbo.EMR_Record_Content
(
RowIndex
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]go
sql = String.Format("select * from EMR_Record_Content where recordid={0} and type=1 and isnull(isdelete,'0')=0 ", RecordID)
那删掉bm标识列的聚集索引,再新建一个就能好用吗
如果条件不命中聚集索引
而命中其他索引
没有order by 的查询会按照命中的索引排序
表结构是固定的,索引也是固定的2个其中rowindex是聚集的,见楼上,查询条件也是固定的几列
sql = String.Format("select * from EMR_Record_Content where recordid={0} and type=1 and isnull(isdelete,'0')=0 ", RecordID)
这样能100%保证按rowindex顺序输出否?