sql语句大全之索引：如何让主键不自动创建聚集

--前提代码：

--创建数据库

create database UsersDBTest  --sqlserver这一句话就可以创建数据库，其它按默认值创建。

 --创建表

  create table UserInfo

  (

      ID int identity(1,1) not null,

      Uname nvarchar(max) not null,

      Pwd nvarchar(max) not null,

      ShowName nvarchar(max) null,

 

  );

        

--代码创建主键

 

 Alter table UserInfo

    add constraint PK_UserInfo  --给主键约束取别名,寓意:UserInfo表的主键约束。取别名的好处是1删除方便2为多个列定义同时约束，即主键组。

        Primary Key (ID Asc); --主键约束 ，根据ID升序排序

--但是刷新表后发现 索引项多了 PK_UserInfo

---sqlserver自动对主键列创建了聚集索引（补充：主键默认是唯一的所以也是唯一索引） 

 

--相当于  

 Alter table UserInfo

    add constraint PK_UserInfo 

        Primary Key Clustered (ID Asc);

        

 

--问题：1能不能把建立索引与主键分离,然后单独对添加、修改和删除索引？

--百度得：

--添加索引：CREATE INDEX   索引名称    ON  表名(列)

--删除索引：drop index 索引名称  on  表名

--查寻索引: select * from sys.indexes where name='索引名称';

 --并没发现直接修改索引的语法，那就先删除再添加吧。

 

--drop Index PK_UserInfo On UserInfo

--报错:不允许对索引 'UserInfo.PK_UserInfo' 显式地使用 DROP INDEX。该索引正用于 PRIMARY KEY 约束的强制执行。

 

 alter table UserInfo drop constraint PK_UserInfo

 --结果：聚集索引没了，但主键约束也没来。

 

 --单独建立UserInfo表索引列和索引类型

 create Index IX_UserInfo On UserInfo(ID); --为了好区分，IX_UserInfo是我为索引取的别名。

--结果：不唯一，非聚集索引

 

 Alter table UserInfo

    add constraint PK_UserInfo

        Primary Key  (ID Asc);

--重建主键约束,结果：默认又为主键创建了聚集索引！

 

--删除IX_UserInfo

  drop Index IX_UserInfo On UserInfo

--为什么对于自己创建的索引，显式删除有效,对系统默认创建索引的就无效。。

 

--问题2：如何取消主键自动创建索引？？？

 

--方案一

-- 1. 首先删除主键, 然后重新创建主键,

  Alter table UserInfo drop constraint PK_UserInfo

--2重新创建主键的时候, 需要说明本主键是使用 非聚集索引

  Alter table UserInfo

    add  constraint PK_UserInfo

      Primary Key NonClustered (ID);

--3为需要聚集索引的列创建聚集索引 

    Create  Clustered  Index IX_UserInfo_UName on UserInfo (UName); 

--4想加唯一约束,鼠标却删不掉,代码删除

    Drop index IX_RoleInfo_UName on UserInfo;

    Create Unique Clustered  Index IX_UserInfo_UName on UserInfo (UName);  

  

--查询索引：刷新表后查看索引项，或者代码查看  

select * from sys.indexes where name='IX_UserInfo_UName';   

 

 

--方案二

------设置非主键为聚集索引【4步】

--1. 查看所有的索引，默认情况下主键上都会建立聚集索引

    sp_helpindex UserInfo

    

-- 2. --删除主键约束,从而删除主键上的索引约束。

 alter table UserInfo drop constraint PK_UserInfo

 

-- 3.--创建聚集索引到其它列

 create  Clustered  Index IX_UserInfo_UName on UserInfo (UName);

 

-- 4.—修改原来的主键字段还是为主键，此时会自动建立非聚集索引【一张表聚集索引只能有一个】

 alter table UserInfo add primary key (ID)

--如果想自定义名称

   Alter table UserInfo

    add  constraint PK_UserInfo

      Primary Key(ID);

 

 

--最终结论：两种方案大同小异，只能把主键改为非聚集索引，但是会有索引。

--不过把聚集索引的位置腾出来了（一张表只能创建一个聚集索引）。

--但是索引本质到底是什么？

--百度百科：

--索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。

--在数据库关系图中，可以在选定表的“索引/键”属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表，或保存该表所在的关系图时，索引将保存在数据库中。 

--当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作； --第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

--注意创建索引也需要时间和计算量，一般只用于经常需要访问的列。

--聚集索引：物理存储顺序与逻辑顺序一致，线性结构（一条直线）。
--非聚集索引：物理存储顺序与逻辑顺序不一致：树状（多条分支）。

--其他资料：

-- http://blog.csdn.net/kennyrose/article/details/7532032

-- http://outofmemory.cn/mysql/mysql-index-and-slow-query#6762184-tsina-1-89901-3400cac556b7c64aaad138f696c2c997

--额，还是不太懂。。