主键顺序的不同,查询代价为什么不一样。
对于两个表,主键相同,但是主键的顺序不同例如:
表Atest1
CREATE TABLE [develop].[Atest1] (
[YEAR_DATE_CD] [smalldatetime] NOT NULL ,
[PRICELIST_CD] [int] NOT NULL ,
[JAN] [varchar] (13) COLLATE Japanese_CI_AS NOT NULL ,
[DATE_CD] [int] NOT NULL ,
[PRODUCT_NM] [varchar] (40) COLLATE Japanese_CI_AS NOT NULL ,
[PRODUCT_SHORT_NM] [varchar] (20) COLLATE Japanese_CI_AS NOT NULL ,
[STANDARD_NM] [varchar] (20) COLLATE Japanese_CI_AS NOT NULL ,
[STANDARD_SHORT_NM] [varchar] (12) COLLATE Japanese_CI_AS NOT NULL ,
[PROD_LEVEL1_CD] [smallint] NOT NULL ,
[PROD_LEVEL2_CD] [smallint] NOT NULL ,
[PROD_LEVEL3_CD] [smallint] NOT NULL ,
[PROD_LEVEL4_CD] [smallint] NOT NULL ,
[PROD_LEVEL5_CD] [smallint] NOT NULL ,
[PROD_LEVEL6_CD] [smallint] NOT NULL ,
[PROD_LEVEL7_CD] [smallint] NOT NULL ,
[RETAIL_PRICE] [money] NOT NULL ,
[UNIT_COST_PRICE] [money] NOT NULL ,
[VENDOR_CD] [int] NOT NULL ,
[TAX_TYP] [tinyint] NOT NULL ,
[AMEND_DT] [smalldatetime] NOT NULL ,
[UPDATE_DT] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [develop].[Atest1] ADD
CONSTRAINT [PK_Atest1] PRIMARY KEY CLUSTERED
(
[YEAR_DATE_CD],
[PRICELIST_CD],
[JAN],
[DATE_CD]
) ON [PRIMARY]
GO
表Atest2
CREATE TABLE [develop].[Atest2] (
[DATE_CD] [int] NOT NULL ,
[YEAR_DATE_CD] [smalldatetime] NOT NULL ,
[PRICELIST_CD] [int] NOT NULL ,
[JAN] [varchar] (13) COLLATE Japanese_CI_AS NOT NULL ,
[PRODUCT_NM] [varchar] (40) COLLATE Japanese_CI_AS NOT NULL ,
[PRODUCT_SHORT_NM] [varchar] (20) COLLATE Japanese_CI_AS NOT NULL ,
[STANDARD_NM] [varchar] (20) COLLATE Japanese_CI_AS NOT NULL ,
[STANDARD_SHORT_NM] [varchar] (12) COLLATE Japanese_CI_AS NOT NULL ,
[PROD_LEVEL1_CD] [smallint] NOT NULL ,
[PROD_LEVEL2_CD] [smallint] NOT NULL ,
[PROD_LEVEL3_CD] [smallint] NOT NULL ,
[PROD_LEVEL4_CD] [smallint] NOT NULL ,
[PROD_LEVEL5_CD] [smallint] NOT NULL ,
[PROD_LEVEL6_CD] [smallint] NOT NULL ,
[PROD_LEVEL7_CD] [smallint] NOT NULL ,
[RETAIL_PRICE] [money] NOT NULL ,
[UNIT_COST_PRICE] [money] NOT NULL ,
[VENDOR_CD] [int] NOT NULL ,
[TAX_TYP] &