标签归档:索引

mssql sqlserver 聚集索引、非聚集索引区分


摘要:
下文讲述聚集索引和非聚集索引的区别,如下所示:



聚集索引、非聚集索引区分:

聚集索引、非聚集索引:
聚集索引和非聚集索引的区别为:物理表的数据存放是否同索引的存放顺序来判断;
聚集索引:物理表按照索引排序存放
非聚集索引:物理表不按照索引的顺序存放
聚集索引:物理表只允许存在一个聚集索引
非聚集索引:物理表可允许存在多个非聚集索引

聚集索引、非聚集索引优缺点:

聚集索引:在特定的情况下 “检索数据的速度比非聚集索引快”(因为物理表上的数据按照索引顺序存放)
聚集索引:数据插入时比非聚集索引慢


聚集索引、非聚集索引应用场景:

说明

聚集索引

非聚集索引
列数据分组排序

两者无差异
返回指定范围数据

使用

频繁更新列

使用
外键列 主键列

两者无差异
索引列频繁修改

使用
索引列不同值非常多

使用
索引列相同值非常多

使用

mssql sqlserver 数据库-索引重建的方法分享


摘要:
下文分享索引重建的方法,如下所示:
实验环境:sql server 2008 R2


方式1:重建单个索引

  alter index [索引名称]  on [表名称]  rebuild;

方式2:重建整个数据库下的所有索引(批量重建索引)

  use [数据库名称]
   go
    
  alter index all  on [表名称]  rebuild;
   go

mssql sqlserver 使用sql脚本生成指定表的索引-创建脚本


摘要:
下文讲述sqlserver中使用sql脚本生成特定表上的所有索引的脚本,如下所示:
实验环境:sql server 2008 R2


  
if exists (
select null from sysobjects where xtype='P'
and name ='pr_outProcInfo'
)
begin
 drop proc pr_outProcInfo
end
 
go

/*
创建索引生成脚本
*/
create proc pr_outProcInfo 
@tableName sysname =''  
as
 
with t as (
select rank() over (order by b.name,a.name,c.name) as id,c.index_id, 
b.name as schema_name,a.name as table_name,c.fill_factor,c.is_padded,
c.name as ix_name,c.type,e.name as column_name,d.index_column_id,c.is_primary_key,
d.is_included_column,f.name as filegroup_name,c.is_unique,c.ignore_dup_key,
d.is_descending_key as is_descending_key,c.allow_row_locks,c.allow_page_locks
from sys.tables as a
inner join sys.schemas as b on a.schema_id=b.schema_id and a.is_ms_shipped=0
inner join sys.indexes as c on a.object_id=c.object_id
inner join sys.index_columns as d on d.object_id=c.object_id and d.index_id=c.index_id
inner join sys.columns as e on e.object_id=d.object_id and e.column_id=d.column_id
inner join sys.data_spaces as f on f.data_space_id=c.data_space_id 
where a.object_id like '%'+isnull(ltrim(object_id(@tableName)),'')+'%'
)
select distinct a.schema_name,a.table_name,a.ix_name,
case a.type when 1 then 'clustered' when 2 then 'nonclustered' else '' end as index_type,
case a.is_primary_key when 0 then 'no' else 'yes' end as is_primary_key,
m.ix_index_column_name,isnull(m.ix_index_include_column_name,'') as ix_index_include_column_name,
a.filegroup_name,replace('create '+ case when is_unique=1 then 'unique ' else '' end 
+ case when a.type=1 then 'clustered' else 'nonclustered' end  +' index '
+ a.ix_name+' on '+a.schema_name+'.'+a.table_name+'('+m.ix_index_column_name+')'
+ case when m.ix_index_include_column_name is null then '' else 'include('+m.ix_index_include_column_name+')'end 
+ case when fill_factor>0 or ignore_dup_key=1 or is_padded=1 or allow_row_locks=0 or allow_page_locks=0 then 'with(' else '' end
+ case when fill_factor>0 then ',fillfactor='+rtrim(fill_factor) else '' end 
+ case when is_padded=1 then ',pad_index=on' else '' end 
+ case when ignore_dup_key=1 then ',ignore_dup_key=on' else '' end 
+ case when allow_row_locks=0 then ',allow_row_locks=off' else '' end
+ case when allow_page_locks=0 then ',allow_page_locks=off' else '' end
+ case when fill_factor>0 or ignore_dup_key=1 or is_padded=1 or allow_row_locks=0 or allow_page_locks=0 then ')' else '' end,'with(,','with(')
as sqlscript
from t as a
outer apply  
(  
    select ix_index_column_name= stuff(replace(replace(  
            (  
                select case when b.is_descending_key =1 then column_name + ' desc' else column_name end as column_name  
                from t as b where a.id=b.id and is_included_column=0 order by index_column_id for xml auto  
            ), '', ''), 1, 1, '')  
            ,ix_index_include_column_name= stuff(replace(replace(  
            (  
                select column_name from t as b where a.id=b.id and is_included_column=1  
                order by index_column_id for xml auto  
            ), '', ''), 1, 1, '')  
)m  
order by a.schema_name,a.table_name,a.ix_name
 
go
---测试
create table test 
(keyId int)

create  index ix_test  on test(keyId) 

go

exec pr_outProcInfo  @tableName=  'test'

go
drop index ix_test on test 
drop table test 

相关代码下载:
mssql_sqlserver_sql生成特定表索引脚本的方法分享_7701