Добрый день, коллеги!
Подготовлена третья часть головоломок. Предыдущие выпуски можно посмотреть здесь:
[SQL Server] Головоломки (Часть 1)
[SQL Server] Головоломки (Часть 2)
1. Использование переменных в инструкции UPDATE
Дано: Таблица с двумя колонками и одной записью.
create table #Table1
(
A int,
B int
)
insert into #Table1(A, B)
select 10, 10
Вопрос: Что содержится в таблице (колонках A и B) и чему равно значение переменных (@A и @B) после выполнения запроса на обновление ?
declare @A int = 0
declare @B int = 0
update #Table1
set @A = A = A + 5
,B = @B + 5
,@B = B
select @A, t.A, @B, T.B
from #Table1 t
Выберите один вариант ответа:
a) 0, 15, 5, 0
б) 15, 15, 5, 0
в) 15, 0, 15, 15
г) 15, 0, 5, 0
д) 0, 15, 15, 15
е) 15, 15, 10, 15
ё) 0, 15, 0, 5
ж) 15, 0, 0, 5
з) Возникнет ошибка
| | Ответ |
| | е) 15, 15, 10, 15
Подсказка:
Правильный ответ содержится в документации к инструкции UPDATE
SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.
Вычисления выполняются в следующем порядке:
1. @A = A = A + 5 <=> @A = A = 10 + 5 = 15, значения переменной @A и A совпадают и равны 15
2. @B = B <=> @B = 10, значение переменной @B равно 10
3. B = @B + 5 <=> B = 10 + 5, значение колонки B равно 15
|
| | |
2. CASE и недетерминированная функция
Дано: Выражение floor(1 + rand() * 3), которое гарантированно генерирует числа от 1 до 3.
Вопрос: Какие возможные значения могут быть получены при использовании обозначенного выше выражения в инструкции case ?
select
case floor(1 + rand() * 3)
when 1 then 'один'
when 2 then 'два'
when 3 then 'три'
end
Выберите один вариант ответа:
а) один, два, три
б) один, два, три, 0
в) один, два, три, NULL
г) NULL
д) 0
| | Ответ |
| | в) один, два, три, NULL
Подсказка:
Несмотря на то, что в запросе используется синтаксис простого выражения CASE, проверка на равенство с вычислением входного выражения выполняется для каждого предложения WHEN. Если в качестве выражения используется переменная или колонка таблицы, то этот эффект не заметен. Однако в случае использования недетерминированной функции (floor(1 + rand() * 3)), возможны ситуации, когда ни одна из операций сравнения не выдаст TRUE, именно поэтому инструкция CASE вернет NULL. Запрос фактически будет выполнен следующим образом:
select
case
when floor(1 + rand() * 3) = 1 then 'один'
when floor(1 + rand() * 3) = 2 then 'два'
when floor(1 + rand() * 3) = 3 then 'три'
else null
end
|
| | |
3. CASE и агрегирующая функция
Дано: Три запроса, в каждом из которых переменная @i проверяется на равенство значению 1 через выражение case.
declare @i int = 1
/* 1 */ select case when @i = 1 then 1 else 1/0 end
/* 2 */ select case when @i = 1 then 1 else min(1/0) end
/* 3 */ select case when @i = 1 then 1 else (select min(1/0)) end
Вопрос: Какие из трех запросов выполнятся успешно, т.е. без ошибок ?
Выберите один вариант ответа:
а) 1 ,2, 3
б) 1, 3
в) 1, 2
г) 2, 3
| | Ответ |
| | б) 1, 3
Подсказка:
В документации для инструкции CASE сказано, что она последовательно оценивает свои условия и останавливается, когда находит первое удовлетворяющее. В некоторых ситуациях выражение оценивается до того, как инструкция CASE получает результаты выражения в качестве входных данных. При оценке этих выражений возможны ошибки. Агрегатные выражения в аргументах WHEN или THEN(!) оцениваются вначале, после чего передаются инструкции CASE.
Получается, что первый запрос /* 1 */ выполнится успешно, на втором /* 2 */ возникнет ошибка деления на 0, т.к. там используется агрегирующая функция MIN. А вот третий запрос вопреки документации выполнится успешно. Возможно, использование подзапроса возвращающего скалярное значение с агрегирующей функцией меняет порядок обработки, и до этого места выполнение не доходит.
Официальных комментариев от производителя на этот счет не было, тем не менее, описанное выше поведение инструкции CASE с агрегирующими функциями задокументировано относительно недавно после создания обращений на сайте Connect. Одно из обращений находится со статусом Closed as Fixed #691535, другое #690017 Closed as By Design
|
| | |
4. newid() и неравенство
Дано: Табличная переменная @Test с одной записью
declare @Test table
(
Id int primary key,
Uid uniqueidentifier
)
insert into @Test select 1, null
Вопрос: На ваш взгляд запрос следующего вида всегда возвращает одну запись ?
declare @uid uniqueidentifier = newid()
select *
from @Test t
where t.Id = 1 and isnull(t.uid, newid()) <> @uid
Выберите один вариант ответа:
а) Да, всегда
б) Нет, не всегда
| | Ответ |
| | б) Нет, не всегда
Подсказка:
Выполняя многократно запрос на выборку данных можно получать разное количество записей от 0 до 1. Если посмотреть план запроса, то можно увидеть, что оператор фильтрации разворачивает условие isnull(t.uid, newid()) <> @uid в предикат isnull(@Test.[Uid] as [t].[Uid],newid())<[@uid] OR isnull(@Test.[Uid] as [t].[Uid],newid())>[@uid]. Вместо одного запуска недетерминированной функции newid(), мы получаем два, поэтому существуют моменты времени, когда условие используемое в предикате не выполняется и возвращается 0 записей. Данное поведение замечено на табличных переменных.
|
| | |
5. Строки с концевыми пробелами и сравнение
Дано: Таблица с заполненными данными
create table #Test
(
Name varchar(100)
)
insert into #Test select 'abc'
insert into #Test select 'abc '
insert into #Test select 'abc '
insert into #Test select 'abc '
Вопрос: Какое количество строк вернет запрос ?
select distinct Name
from #Test
Выберите один вариант ответа:
а) 1
б) 2
в) 3
г) 4
| | Ответ |
| | а) 1
Подсказка:
В соответствии со стандартом ANSI SQL-92, перед сравнением строки с концевыми пробелами выравниваются до одинаковой длины. Поэтому с точки зрения сравнения все записи представленные в таблице #Test эквивалентны.
|
| | |
O>Добрый день, коллеги!
Ты так меня заставишь поверить, что я знаю T-SQL.