select 'Hello, World!'

The single biggest challenge to learning SQL programming is unlearning procedural programming (Joe Celko)

[SQL Server] Головоломки (Часть 1)

01.07.2015
Olaf Olaf
Добрый день, коллеги!

В ходе работы иногда возникают интересные аспекты использования MS SQL Server. В свое время откладывал их в виде запросов, заметок и всего прочего с надеждой, что доберутся руки, чтобы оформить это в виде головоломок (задач) с ответами и пояснениями. И вот первые пять вопросов готовы. Возможно, кому-то покажется это интересным или будут предложены свои головоломки. Вопросы и ответы составлены мной, поэтому ошибки и заблуждения не исключены. Обсуждение приветствуется

1. select *
Дано: Запрос вида
select * 
from dbo.Table1 
where exists(select *)

Вопрос: Что произойдет при выполнении запроса?
a) Успешно выполнится
б) Не выполнится, т.к. возникнет ошибка
Выберите один вариант ответа
  Ответ

а) Успешно выполнится
Подсказка: Запрос выполнится успешно, т.к. в SQL Server наличие выражения FROM является необязательным, поэтому в подзапросе c EXISTS выражение SELECT * допустимо. Обращение зафиксировано в Microsft Connect и закрыто с пометкой By Design select 'test' where exists (select *)


2. Сортировка по полю с псевдонимом и без
Дано: БД с уровнем совместимости 90+ и два запроса.
/* 1 */ select id, id from dbo.Table1 order by id
/* 2 */ select id, id from dbo.Table1 t order by t.id

Вопрос: Что произойдет при выполнении запросов 1 и 2? Запросы выполняются отдельно друг от друга.
а) Запрос 1 выполнится успешно
б) Запрос 1 не выполнится, т.к. возникнет ошибка
в) Запрос 2 выполнится успешно
г) Запрос 2 не выполнится, т.к. возникнет ошибка
Выберите два варианта ответа
  Ответ

б) Запрос 1 не выполнится, возникнет ошибка
в) Запрос 2 выполнится успешно
Подсказка : В запросе 1 имеется неоднозначность по какому полю таблицы сортировать данные, ведь используя аналогичное имя, его можно переопределить, скажем так select id, id = id * -1 from dbo.Table1 Именно поэтому возникает ошибка "Ambiguous column name xxx" Во втором запросе, в отличие о первого, конкретно указывается источник поля, т.е. таблица, поэтому он выполнится успешно. Данное поведение обусловлено появлением уровня совместимости 90 и описано в разделе "Различия между уровнями совместимости 80 и 90"


3. Использование более одной функции getdate()
Дано: SQL Server 2005+. select запрос, который использует больше одной функции getdate()
select *, 
       (
           select t3.Name 
           from dbo.Table3 t3
           where t3.Table3Id = t1.Table3Id and t3.ModifiedDate < getdate()
       )
from dbo.Table1 t1
join dbo.Table2 t2 on t1.Table1Id = t1.Table1Id
where t1.ModifiedDate < getdate() and t2.ModifiedDate < getdate()

Вопрос: На ваш взгляд значения, возвращаемые функцией getdate() в данном запросе будут?
а) Одинаковые
б) Разные
Выберите один вариант ответа
  Ответ

a) Одинаковые
Подсказка: Функция getdate() несмотря на то, что является недетерминированной, относится к разряду Foldable Expressions, а точнее Compile-Time Expression. Т.е. значение может быть определено однократно перед выполнением и использовано во всех участках запроса. Более подробно можно посмотреть здесь Troubleshooting Poor Query Performance: Constant Folding and Expression Evaluation During Cardinality Estimation


4. getdate() в функции
Дано: Таблица, содержащая 1 000 000 записей. Скалярная и табличная функции возвращающие текущую дату и запрос на обновление данных.
create function dbo.ufn_getdate1()
returns table
as
    return (select getdate() as tdt)
go

create function dbo.ufn_getdate2()
returns datetime as
begin
    return getdate()
end
go

update dbo.Table1
set A1 = (select tdt from dbo.ufn_getdate1())
    ,B2 = (select dbo.ufn_getdate2())

Вопрос: Какие данные будут содержаться в колонке A1 и B2 после обновления ?
a) В колонке A1 найдутся значения, отличающиеся от остальных (в пределах колонки A1)
б) В колонке A1 все значения будут одинаковые
в) В колонке B2 найдутся значения, отличающиеся от остальных (в пределах колонки B2)
г) В колонке B2 все значения будут совпадать
Выберите два варианта ответа
  Ответ

б) В колонке A1 все значения будут одинаковые
в) В колонке B2 найдутся значения, отличающиеся от остальных (в пределах колонки B2)
Подсказка: Т.к. табличная встроенная функция будет развернута в выражение getdate(), то часть ответа пересекается с ответом на вопрос выше, т.е. колонка A1 будет содержать одно и тоже значение, которое вычисляется перед выполнением. А вот в колонке B2 найдутся значения, которые будут отличаться от остальных. Связано это с тем, что user-defined функция, является Nonfoldable Expressions, т.е. значения вычисляются для каждой записи в момент обновления данных. Более подробно можно посмотреть здесь Troubleshooting Poor Query Performance: Constant Folding and Expression Evaluation During Cardinality Estimation, 13 Things You Should Know About Statistics and the Query Optimizer


5. Тип данных date и datetime
Дано: Таблица, содержащая одно значение типа date и запрос вида
create table #DateAndTime(Dt date)
insert into #DateAndTime select '2013-11-19'

declare @dt datetime = '2013-11-19 19:00:00'
declare @d datetime = '2013-11-19 00:00:00'

/* 1 */ select 1 as n, * from #DateAndTime where Dt = @dt union all
/* 2 */ select 2, * from #DateAndTime where Dt = @d union all
/* 3 */ select 3, * from #DateAndTime where Dt = cast(@Dt as date) union all
/* 4 */ select 4, * from #DateAndTime where Dt = '2013-11-19 19:00:00'

Вопрос: Какое количество записей вернет запрос ?
a) 0
б) 1
в) 2
г) 3
д) 4
Выберите один вариант ответа
  Ответ

г) 3
Подсказка: В результирующем запросе вернутся 3 записи — строки 2,3,4
Во второй строке запроса значение для типа datetime указано без времени
В третьей строке используется явное преобразование datetime к типу date
В четвертой строке используется неявное преобразование литерала к типу date

Отсутствовать будет только строка номер 1, т.к. в переменной @dt типа datetime указано время, а сравнение между двумя типами работает только в представлении dd.MM.yyyy против dd.MM.yyyy 00:00:00.000
01.07.2015 3 комментария
комментировать обсудить в форуме
ZAMUNDA
ZAMUNDA
02.07.2015 08:07
Здравствуйте, Olaf, Вы писали:

O>Добрый день, коллеги!
И вам не хварать.

O>В ходе работы иногда возникают интересные аспекты использования MS SQL Server.
Вот во вторую часть: 
DECLARE @d AS date;
DECLARE @dt AS datetime2(0);

SET    @dt = '19790219 15:35:00';

SELECT foo = ISNULL(@d, @dt), bar = COALESCE(@d, @dt)

Вопрос: Будут-ли отличаться значения в foo и bar полях?

O>5. Тип данных date и datetime
Что делают "-" в дате и куда ушла "T"? Вот у меня на глазах пришлось остатки по счетам в довольно крупном банке пересчитывать, из-за таких косяков.
https://msdn.microsoft.com/en-us/library/ms187819.aspx
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d0f28d86-fea1-40f6-b982-65f6849b9eca/how-to-code-datetimeliteral-in-sql-server?forum=transactsql
ответить
#
Tigor
Tigor
02.07.2015 10:40
+

select 1 where null not in (select 1 where 1 = 0)
select 1 where null not in (select 1 where 1 = 1)
ответить
#
Sinclair
Sinclair
06.07.2015 10:59
Здравствуйте, Olaf, Вы писали:

O>Добрый день, коллеги!

O>В ходе работы иногда возникают интересные аспекты использования MS SQL Server. В свое время откладывал их в виде запросов, заметок и всего прочего с надеждой, что доберутся руки, чтобы оформить это в виде головоломок (задач) с ответами и пояснениями. И вот первые пять вопросов готовы. Возможно, кому-то покажется это интересным или будут предложены свои головоломки. Вопросы и ответы составлены мной, поэтому ошибки и заблуждения не исключены. Обсуждение приветствуется
0% попаданий. Был близок в паре мест
ответить
#
комментировать обсудить в форуме