道歉为一个冗长的职位,但我需要发布一些代码来说明问题.
受到这个问题* What is the reason not to use select ?的启发,我决定指出一些我之前注意到的select *行为的一些观察.
所以让代码自己说：
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[starTest]') AND type in (N'U'))
DROP TABLE [dbo].[starTest]
CREATE TABLE [dbo].[starTest](
[id] [int] IDENTITY(1,1) NOT NULL,[A] [varchar](50) NULL,[B] [varchar](50) NULL,[C] [varchar](50) NULL
) ON [PRIMARY]
GO
insert into dbo.starTest(a,b,c)
select 'a1','b1','c1'
union all select 'a2','b2','c2'
union all select 'a3','b3','c3'
go
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vStartest]'))
DROP VIEW [dbo].[vStartest]
go
create view dbo.vStartest as
select * from dbo.starTest
go
go
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vExplicittest]'))
DROP VIEW [dbo].[vExplicittest]
go
create view dbo.[vExplicittest] as
select a,c from dbo.starTest
go
select a,c from dbo.vStartest
select a,c from dbo.vExplicitTest
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[starTest]') AND type in (N'U'))
DROP TABLE [dbo].[starTest]
CREATE TABLE [dbo].[starTest](
[id] [int] IDENTITY(1,[D] [varchar](50) NULL,d,'d1','d2','d3','c3'
select a,c from dbo.vExplicittest
select a,c from dbo.vStartest
如果执行以下查询并查看最后2个select语句的结果,
您将看到的结果如下：
select a,c from dbo.vExplicittest a1 b1 c1 a2 b2 c2 a3 b3 c3 select a,c from dbo.vStartest a1 b1 d1 a2 b2 d2 a3 b3 d3
正如你可以从dbo.v选择a,c的结果中看到的那样,列c的数据已被替换为来自colum d的数据.
我认为这与编译视图的方式有关,我的理解是列被列索引(1,2,3,4)映射而不是名称.
我以为我会把它作为一个警告,在他们的sql中使用select *并遇到意想不到的行为.
注意：如果您在修改表后重建每次使用select *的视图,它将按预期工作.
解决方法
sp_refreshview修复视图,或在视图定义中使用WITH SCHEMABINDING
If a view is not created with the SCHEMABINDING clause,sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise,the view might produce unexpected results when it is queried.
