"select * from table" vs "select colA, colB и т.д. из таблицы" интересное поведение в SQL Server 2005

Апология для длинного сообщения, но мне нужно было опубликовать некоторый код, чтобы проиллюстрировать проблему.

Вдохновленный вопросом * В чем причина не использовать 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,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

Если вы выполните следующий запрос и посмотрите на результаты последних двух операторов select, результаты, которые вы увидите, будут следующими:

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

Как вы можете видеть в результатах выберите a, b, c из dbo.vStartest, данные столбца c были заменены данными из colum d.

Я считаю, что это связано с тем, как компилируются представления, я понимаю, что столбцы сопоставляются индексами столбцов (1,2,3,4) в отличие от имен.

Я думал, что опубликую его как предупреждение для людей, использующих select * в своем SQL и испытывающих неожиданное поведение.

Примечание. Если вы измените представление, которое использует select * каждый раз после изменения таблицы, оно будет работать, как ожидалось.

Ответы

Ответ 1

sp_refreshview, чтобы исправить представление или использовать WITH SCHEMABINDING в определении представления

Если представление не создано с помощью Предложение SCHEMABINDING, sp_refreshview должны выполняться при внесении изменений в объекты, лежащие в основе представления, влияют на определение представления. В противном случае представление может неожиданные результаты при запросе.

Ответ 2

Это довольно стандартное поведение для представлений в любой RDBMS, а не только MSSQL, и причина, по которой использование представлений, содержащих "select * from", должно обрабатываться с осторожностью.

SQL Engine скомпилирует каждое представление - это, в основном, лексикографические/синтаксические шаги и сохранение результата. Если вы, следовательно, меняете базовые таблицы, то явная перекомпиляция всегда требуется, если только в базе данных не существует метода пометки представления, чтобы быть проверенным при таких обстоятельствах.

Проблема может (будет) применяться также к хранимым процедурам и аналогичным объектам базы данных.