sql - SQL Server 2005 中的 "select * from table"与 "select colA, colB, etc. from table"有趣的行为

Question

为一篇冗长的帖子道歉，但我需要发布一些代码来说明问题。

灵感来自问题 * 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,b,c from dbo.starTest
go


select a,b,c from dbo.vStartest
select a,b,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,1) NOT NULL,
    [A] [varchar](50) NULL,
    [B] [varchar](50) NULL,
    [D] [varchar](50) NULL,
    [C] [varchar](50) NULL
) ON [PRIMARY]

GO

insert into dbo.starTest(a,b,d,c)
select 'a1','b1','d1','c1'
union all select 'a2','b2','d2','c2'
union all select 'a3','b3','d3','c3'

select a,b,c from dbo.vExplicittest
select a,b,c from dbo.vStartest

如果您执行以下查询并查看最后 2 个 select 语句的结果，
您将看到的结果如下:

select a,b,c from dbo.vExplicittest
a1  b1  c1
a2  b2  c2
a3  b3  c3

select a,b,c from dbo.vStartest
a1  b1  d1
a2  b2  d2
a3  b3  d3

正如您在 的结果中所见从 dbo.vStartest 中选择 a,b,c c 列的数据已替换为 d 列的数据。

我相信这与 View 的编译方式有关，我的理解是列是按列索引 (1,2,3,4) 而不是名称映射的。

我想我会把它作为警告发布给在他们的 SQL 中使用 select * 并遇到意外行为的人。

注意:如果在每次修改表后重建使用 select * 的 View ，它将按预期工作。

Answer 1

sp_refreshview修复 View ，或在 View 定义中使用 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.