视图索引没有唯一列

create table tb(id int,A_xx int,H_Tyxx int,H_Sstate int,H_Mstate int,B_id int)--省略了中间的一些列
insert into tb select 11,7,1,1,0,16
insert into tb select 15,7,1,0,1,30
insert into tb select 16,7,1,0,1,30
insert into tb select 19,7,1,1,0,30
insert into tb select 20,7,1,1,1,30
go
create view gettb
as
select row_number()over(order by id)rn,H_Sstate,H_Mstate from tb
go
select * from gettb
/*
rn                   H_Sstate    H_Mstate
-------------------- ----------- -----------
1                    1           0
2                    0           1
3                    0           1
4                    1           0
5                    1           1

(5 行受影响)

*/
go
drop view gettb
drop table tb

---索引视图

索引视图是具体化的视图

--创建索引视图
create view 视图名 with schemabinding 
as
select 语句
go

---创建索引视图需要注意的几点
1. 创建索引视图的时候需要指定表所属的架构
--错误写法
create view v_f with schemabinding 
as
select
   a.a,a.b,b.a,b.b
from
   a join b 
on
   a.id=b.id
go

---正确写法：
create view v_f with schemabinding 
as
select
   a.a,a.b,b.a,b.b
from
   dbo.a join dbo.b 
on
   a.id=b.id
go


2.在创建索引视图的select语句时，不使用*,必须指定具体的列名
--错误写法
create view v_f with schemabinding 
as
select
   *
from
   dbo.a join dbo.b 
on
   a.id=b.id
go

---正确写法
create view v_f with schemabinding 
as
select
   a.a,a.b,b.a,b.b
from
   dbo.a join dbo.b 
on
   a.id=b.id
go

3.在创建索引视图的select 语句中，不能存在重复的列名，这个不举例了

4. 只能为索引视图创建唯一聚集索引
--正确的写法
create unique clustered index ix_uniquetb on v_tb
go

--错误的写法 
create clustered index ix_uniquetb on v_tb
go

這樣用

CREATE TABLE T(ID INT,ID2 int)

GO
INSERT T SELECT 1,2
INSERT T SELECT 1,2
INSERT T SELECT 2,3
INSERT T SELECT 2,3

GO
Create VIEW v_T WITH SCHEMABINDING
AS
SELECT ID,ID2,COUNT_BIG(*) AS BIG FROM dbo.T GROUP BY ID,ID2
GO
CREATE UNIQUE CLUSTERED INDEX IX_T ON v_T(ID,ID2)
GO
SELECT * FROM v_T
/*
ID    ID2    BIG
1    2    2
2    3    2
*/