“从表中选择*"与“从表中选择 colA、colB 等"；SQL Server 2005 中的有趣行为 [英] "select * from table" vs "select colA, colB, etc. from table" interesting behaviour in 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.

这篇关于“从表中选择*"与“从表中选择 colA、colB 等"；SQL Server 2005 中的有趣行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！