SQL Server 2005中的有趣行为："select * from table"与"select colA，colB等 from table"的区别

Question

很抱歉篇幅有点长，但我需要贴一些代码来说明问题。

受到问题*为什么不使用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

正如你在 select a,b,c from dbo.vStartest 的结果中所看到的，列 c 的数据已被列 d 的数据替换。

我认为这与视图的编译方式有关，我的理解是列是按照索引（1、2、3、4）而非名称进行映射的。

我想发出一个警告，告诫使用 select * 的 SQL 语句并遇到意外行为的人们。

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

- kristof

---

我不明白，示例代码中根本没有D列。 - Hogan

@Hogan，您需要向下滚动代码示例，表starTest被删除并重新创建，这次有4列：A、B、D、C。 - kristof

谢谢，不知道我怎么会错过那个。 - Hogan

很棒的信息，但我认为这应该放在“社区维基”中，因为它不是一个问题。 - Andrew Steitz

Answer 1

sp_refreshview用于修复视图，或在视图定义中使用WITH SCHEMABINDING

如果一个视图没有使用SCHEMABINDING子句创建，当更改影响视图定义的基础对象时，应该运行sp_refreshview。否则，当查询该视图时，可能会产生意外的结果。

- gbn

---

好观点。我总是使用SCHEMABINDING，它不允许这样做。 - gbn

Answer 2

这是任何关系型数据库视图的标准行为，不仅限于MSSQL。这就是为什么使用"select * from"组成的视图必须谨慎对待的原因。SQL引擎将编译每个视图 - 基本上是词法/解析步骤并存储结果。因此，如果更改底层表，则始终需要显式重新编译，除非数据库具有在这种情况下检查视图的某些方法。该问题也可能（将）适用于存储过程和类似的数据库对象。

- Cruachan