title: "select * from table" vs "select colA, colB, etc. from table" interesting behaviour in SQL Server 2005
body: <p>Apology for a lengthy post, but I needed to post some code to illustrate the problem.</p>
  
  <p>Inspired by the question *<a href="https://stackoverflow.com/questions/321299">What is the reason not to use select <em>?</a></em>, I decided to point out some observations of the select * behaviour that I noticed some time ago.</p>
  
  <p>So let's the code speak for itself:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>As you can see in the results of <strong>select a,b,c from dbo.vStartest</strong> the data of column c has been replaced with the data from colum d. </p>
  
  <p>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.</p>
  
  <p>I thought I would post it as a warning for people using select * in their SQL and experiencing unexpected behaviour.</p>
  
  <p>Note: If you rebuild the view that uses select *  each time after you modify the table it will work as expected.</p>