笔记136 聚集索引 非聚集索引 堆表的3种表之间的空间使用的比较

笔记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

 

posted @ 2013-08-03 23:00  桦仔  阅读(665)  评论(0)    收藏  举报