“从表中选择 *"与“从表中选择 colA、colB 等"；SQL Server 2005 中的有趣行为
为冗长的帖子道歉，但我需要发布一些代码来说明问题.
Apology for a lengthy post, but I needed to post some code to illustrate the problem.
受到问题 *不使用 select 的原因是什么? 的启发，我决定指出我前段时间注意到的 select * 行为的一些观察结果.
Inspired by the question *What is the reason not to use select ?, I decided to point out some observations of the select * behaviour that I noticed some time ago.
让我们的代码不言自明:
So let's the code speak for itself:
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 语句的结果，您将看到的结果如下:
If you execute the following query and look at the results of last 2 select statements, the results that you will see will be as follows:
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 列的数据.
As you can see in the results of select a,b,c from dbo.vStartest the data of column c has been replaced with the data from colum d.
我认为这与视图的编译方式有关，我的理解是列是按列索引(1,2,3,4)而不是名称映射的.
I believe that is related to the way the views are compiled, my understanding is that the columns are mapped by column indexes (1,2,3,4) as opposed to names.
我想我会把它作为警告发布给在他们的 SQL 中使用 select * 并遇到意外行为的人.
I thought I would post it as a warning for people using select * in their SQL and experiencing unexpected behaviour.
注意:如果在每次修改表后重建使用 select * 的视图，它将按预期工作.
Note: If you rebuild the view that uses select * each time after you modify the table it will work as expected.
推荐答案
sp_refreshview 修复视图，或在视图定义中使用 WITH SCHEMABINDING
sp_refreshview to fix the view, or use WITH SCHEMABINDING in the view definition
如果视图不是用SCHEMABINDING 子句，sp_refreshview应在更改时运行视图下的对象影响视图的定义.否则，视图可能会产生查询时出现意外结果.
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.
相关文章