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

Question

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

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

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

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 были заменены данными из столбца d.

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

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

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

Answer 1

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

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

Answer 2

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

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

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