“select * from table”vs“select colA,colB等从表”中有趣的

受到这个问题* 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 *的视图,它将按预期工作.

解决方法

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.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!