笔记136 聚集索引 非聚集索引 堆表的3种表之间的空间使用的比较
1 --聚集索引 非聚集索引 堆表的3种表之间的空间使用的比较
2 --因为非聚集索引会增加额外的空间，聚集索引在insert方面跟非聚集索引几乎没差别（根据sqlserver企业管理平台实践）
3 --所以在一个大的表格上一定要建立一个聚集索引（美彬建表的时候只有聚集索引没有非聚集索引）
4 --------------------------------------------------------------------------------
5 --堆表
6 CREATE TABLE [Sales].[SalesOrderDetail_hash](
7 [SalesOrderID] [int] NOT NULL,
8 [SalesOrderDetailID] [int] NULL,
9 [CarrierTrackingNumber] [nvarchar](25) NULL,
10 [OrderQty] [smallint] NOT NULL,
11 [ProductID] [int] NOT NULL,
12 [SpecialOfferID] [int] NOT NULL,
13 [UnitPrice] [money] NOT NULL,
14 [UnitPriceDiscount] [money] NOT NULL,
15 [LineTotal] [numeric](38, 6) NULL,
16 [rowguid] [uniqueidentifier] NULL,
17 [ModifiedDate] [datetime] NULL
18 ) ON [PRIMARY]
19
20
21 INSERT INTO [Sales].[SalesOrderDetail_hash]
22 SELECT * FROM [Sales].[SalesOrderDetail]
23 go
24
25
26 DBCC SHOWCONTIG('[Sales].[SalesOrderDetail_hash]')
27 GO
28
29 -----------------------------------------------------------------------------------------------------------------
30 --聚集索引
31 USE [AdventureWorks]
32 GO
33 /****** 对象: Table [Sales].[SalesOrderDetail_hash] 脚本日期: 08/29/2012 23:29:38 ******/
34 SET ANSI_NULLS ON
35 GO
36 SET QUOTED_IDENTIFIER ON
37 GO
38 CREATE TABLE [Sales].[SalesOrderDetail_C](
39 [SalesOrderID] [int] NOT NULL,
40 [SalesOrderDetailID] [int] ,
41 [CarrierTrackingNumber] [nvarchar](25) NULL,
42 [OrderQty] [smallint] NOT NULL,
43 [ProductID] [int] NOT NULL,
44 [SpecialOfferID] [int] NOT NULL,
45 [UnitPrice] [money] NOT NULL,
46 [UnitPriceDiscount] [money] NOT NULL,
47 [LineTotal] [numeric](38, 6),
48 [rowguid] [uniqueidentifier] ,
49 [ModifiedDate] [datetime] ,
50 CONSTRAINT [PK_SalesOrderDetailC_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED
51 (
52 [SalesOrderID] ASC,
53 [SalesOrderDetailID] ASC
54 )ON [primary]
55 ) ON [PRIMARY]
56
57
58 INSERT INTO [Sales].[SalesOrderDetail_C]
59 SELECT * FROM [Sales].[SalesOrderDetail]
60 go
61
62
63 DBCC SHOWCONTIG('[Sales].[SalesOrderDetail_C]')WITH ALL_INDEXES
64
65 ---------------------------------------------------------------------------
66 --非聚集索引
67 CREATE TABLE [Sales].[SalesOrderDetail_N](
68 [SalesOrderID] [int] NOT NULL,
69 [SalesOrderDetailID] [int] ,
70 [CarrierTrackingNumber] [nvarchar](25) NULL,
71 [OrderQty] [smallint] NOT NULL,
72 [ProductID] [int] NOT NULL,
73 [SpecialOfferID] [int] NOT NULL,
74 [UnitPrice] [money] NOT NULL,
75 [UnitPriceDiscount] [money] NOT NULL,
76 [LineTotal] [numeric](38, 6),
77 [rowguid] [uniqueidentifier] ,
78 [ModifiedDate] [datetime] ,
79 CONSTRAINT [PK_SalesOrderDetailN_SalesOrderID_SalesOrderDetailID] PRIMARY KEY NONCLUSTERED
80 (
81 [SalesOrderID] ASC,
82 [SalesOrderDetailID] ASC
83 )ON [primary]
84 ) ON [PRIMARY]
85
86 INSERT INTO [Sales].[SalesOrderDetail_N]
87 SELECT * FROM [Sales].[SalesOrderDetail]
88 go
89
90
91 DBCC SHOWCONTIG('[Sales].[SalesOrderDetail_N]')WITH ALL_INDEXES
