"select * from table" vs "select colA,colB,etc from table" interesting behaviour in SqlServer2005
- by kristof
Apology for a lengthy post but I needed to post some code to illustrate the problem.
Inspired by the question What is the reason not to use select * ? posted a few minutes ago, 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
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
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.
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 apposed to names.
I though I would post it as a warning for people using select * in their sql and experiencing unexpected behaviour.
Note: If you rebuild the view that uses select * each time after you modify the table it will work as expected
