SQLServer索引和视图详解

create [unique] [clustered | noclustered]
index index_name
on table_name (column_name ...)
[with fillfactor=x]

if (exists (select * from sys.indexes where name = 'idx_stu_name'))
    drop index student.idx_stu_name
Go
create index idx_stu_name
on
student(name);
 
--联合索引
if (exists (select * from sys.indexes where name = 'idx_uqe_clu_stu_name_age'))
    drop index student.idx_uqe_clu_stu_name_age
go
create unique clustered index idx_uqe_clu_stu_name_age
on student(name, age);
 
if (exists (select * from sys.indexes where name = 'idx_cid'))
    drop index student.idx_cid
go
 
if (exists (select * from sys.indexes where name = 'idx_cid'))
    drop index student.idx_cid
go
 
--非聚集索引
create nonclustered index idx_cid
on
student (cid)
with fillFactor = 30;    --填充因子
 
--聚集索引
if (exists (select * from sys.indexes where name = 'idx_sex'))
    drop index student.idx_sex
go
create clustered index idx_sex
on
student(sex);
 
--聚集索引
if (exists (select * from sys.indexes where name = 'idx_name'))
    drop index student.idx_name
go
create unique index idx_name
on
student(name);

--创建视图
if (exists (select * from sys.objects where name = 'v_stu'))
    drop view v_stu
go
create view v_stu
as
select id, name, age, sex from student;

alter view v_stu
as
select id, name, sex from student;
 
alter view v_stu(编号, 名称, 性别)
as
    select id, name, sex from student
go
select * from v_stu;
 
select * from infORMation_schema.views;

alter view v_stu
as
select id, name, sex from student;
 
alter view v_stu(编号, 名称, 性别)
as
    select id, name, sex from student
go
select * from v_stu;
 
select * from information_schema.views;