Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

"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.

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

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 opposed to names.

I thought 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.

like image 700
kristof Avatar asked Nov 26 '08 17:11

kristof


People also ask

What is the difference between SELECT and SELECT * in SQL?

SELECT column returns only the value of that column. SELECT * returns the value of every column in the table.

Should you use SELECT * in SQL?

That is why you should not use SELECT * in an SQL query anymore. It's always better to use the explicit column list in a SELECT query than a * wild card. It not only improves performance but also makes your code more explicit.

What is SELECT table * in SQL?

The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.

What is the difference between SELECT * and SELECT column name?

The reason I use SELECT COLUMN_NAMES is when using Stored Procedure, adding columns to the table will not screw your application. select * will give additional column (which you've just added to the table) and application will get additional column and may raise error.


2 Answers

sp_refreshview to fix the view, or use WITH SCHEMABINDING in the view definition

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.

like image 76
gbn Avatar answered Oct 21 '22 07:10

gbn


This is pretty standard behavior for views under any RDBMS, not just MSSQL, and the reason why the usage of views comprising "select * from" must be treated with caution.

The SQL Engine will compile each view - which is basically the lexicographical/parsing steps and store the result of that. If you hence change the underlying tables a explicit recompile is always required unless the database has some method of tagging the view as to be checked in such circumstances.

The issue may (will) also apply to stored procedures and similar database objects too.

like image 25
Cruachan Avatar answered Oct 21 '22 05:10

Cruachan