×

Что может подстерегать новичков при работе с SQL Server

В свое время я зачитывался Рихтером и усиленно штудировал Шилдта. Думал, что буду заниматься разработкой под .NET, но судьба на первом месяце работы распорядилась иначе. Один из сотрудников неожиданно покинул проект, и во вновь образовавшуюся дыру докинули свежего людского материала. Именно тогда и началось мое знакомство с SQL Server.

С тех пор прошло чуть меньше 6 лет, и вспомнить можно многое... Про бывшего клиента Джозефа из Англии, который переосмыслил жизнь за время отпуска в Таиланде, и в моем скайпе стал подписываться Жозефиной. Про веселых соседей по офису, с которыми приходилось сидеть в одной комнате: один страдал от аллергии на свежий воздух, а другой маялся от неразделенной любви к С++, дополняя это аллергией на солнечный свет. Чего только не было... Один раз по команде свыше пришлось на время стать Александром, отцом двух детей, и изображать из себя обросшего скилами сениора по JS. Но самый лютый треш, наверное, связан с историей про резиновую утку-пищалку. Один коллега снимал ею стресс и, однажды, в порыве эмоций, отгрыз ей голову. С тех пор уточка потеряла прежний лоск и вскоре была заменена на мячик, который он пытался иногда грызть... увы, уже безуспешно.

К чему это было рассказано? Если хотите посвятить свою жизнь работе с базами данных, то первое чему нужно научиться — это стрессоустойчивости. Второе — взять на вооружение несколько правил при написании запросов на T-SQL, которые многие из начинающих разработчиков не знают или игнорируют, а потом сидят и ломают голову: «Почему что-то не работает?»

NOT IN vs NULL

Долго думал, с какого примера стоило бы начать. Бесспорный лидер среди вопросов на собеседовании Junior DB Developer — конструкция NOT IN.

Например, нужно написать запрос, который вернет всем записи из первой таблицы, которых нет во второй. Очень часто начинающие разработчики не заморачиваются и используют NOT IN:

DECLARE @t1 TABLE (a INT)
INSERT INTO @t1 VALUES (1), (2)

DECLARE @t2 TABLE (b INT)
INSERT INTO @t2 VALUES (1)

SELECT * FROM @t1
WHERE a NOT IN (SELECT b FROM @t2)

Запрос вернул нам двойку. Давайте теперь во вторую таблицу добавим еще одно значение — NULL:

INSERT INTO @t2 VALUES (1), (NULL)

При выполнении мы не получим никаких результатов. Поменяем NOT IN на IN и сможем увидеть какую-то магию — IN работает, а NOT IN отказывается. Это первое, что нужно «понять и простить» при работе с SQL Server, который при операции сравнения руководствуется третичной логикой: TRUE, FALSE, UNKNOWN.

При выполнении SQL Server интерпретирует условие IN:

a IN (1, NULL) === a=1 OR a=NULL

NOT IN:

a NOT IN (1, NULL) === a<>1 AND a<>NULL

При сравнении любого значения с NULL возвращается UNKNOWN. 1=NULL, NULL=NULL. Результат будет один — UNKNOWN. А поскольку у нас в условии используется оператор AND, то все выражение вернет неопределенное значение.

Скажу честно, написано реально скучно. Но важно понимать, что такая ситуация встречается достаточно часто. Хороший пример из жизни: раньше колонка была NOT NULL, потом какой-то добрый человек разрешил записывать в нее NULL значение. Итог: у клиента перестает работать отчет после того, как в таблицу попадет хотя бы одно NULL значение.

Что делать? Можно явно отбрасывать NULL значения:

SELECT * FROM @t1
WHERE a NOT IN (
        SELECT b FROM @t2
        WHERE b IS NOT NULL
    ) 

Можно использовать EXCEPT:

SELECT * FROM @t1
EXCEPT
SELECT * FROM @t2

Если нет желания много думать, то проще использовать NOT EXISTS:

SELECT * FROM @t1
WHERE NOT EXISTS(
        SELECT * FROM @t2
        WHERE a = b
    )

Какой вариант запроса более оптимальный? Чуточку предпочтительнее выглядит последний вариант с NOT EXISTS, который генерирует более оптимальный predicate pushdown оператор при доступе к данным из второй таблицы.

Date Format

Еще часто спотыкаются на различных нюансах с типами данных. Например, нужно получить текущее время. Выполнили функцию GETDATE. Скопировали результат и вставили его в запрос. Корректно ли так делать? Дата задается строковой константой, и в некоторой степени SQL Server позволяет вольности при ее написании:

SET DATEFORMAT DMY

DECLARE
      @d1 DATETIME = '05/12/2016'
    , @d2 DATETIME = '2016/12/05'
    , @d3 DATETIME = '2016-12-05'
    , @d4 DATETIME = '05-dec-2016'

SELECT @d1, @d2, @d3, @d4

Все значения однозначно интерпретируются:

----------- ----------- ----------- -----------
2016-12-05  2016-05-12  2016-05-12  2016-12-05

И это не будет приводить к проблемам до тех пор, пока бизнес-логику не начнут выполнять на другом сервере, на котором настройки могут отличаться:

SET DATEFORMAT DMY

DECLARE
      @d1 DATETIME = '05/12/2016'
    , @d2 DATETIME = '2016/12/05'
    , @d3 DATETIME = '2016-12-05'
    , @d4 DATETIME = '05-dec-2016'

SELECT @d1, @d2, @d3, @d4

Первый вариант может привести к неверному толкованию даты:

----------- ----------- ----------- -----------
2016-05-12  2016-12-05  2016-12-05  2016-12-05

Более того, подобный код может привести к ошибке. Например, нам нужно вставить данные в таблицу. На тестовом сервере все прекрасно работает:

DECLARE @t TABLE (a DATETIME)
INSERT INTO @t VALUES ('05/13/2016')

А у клиента, из-за разницы настройках сервера, вот такой запрос будет приводить к проблемам:

DECLARE @t TABLE (a DATETIME)
SET DATEFORMAT DMY
INSERT INTO @t VALUES ('05/13/2016')

Msg 242, Level 16, State 3, Line 28
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Так в каком же формате задавать константы для дат? Давайте посмотрим на еще один пример:

SET DATEFORMAT YMD

SET LANGUAGE English

DECLARE
      @d1 DATETIME = '2016/01/12'
    , @d2 DATETIME = '2016-01-12'
    , @d3 DATETIME = '12-jan-2016'
    , @d4 DATETIME = '20160112'

SELECT @d1, @d2, @d3, @d4
GO

SET LANGUAGE Deutsch

DECLARE
      @d1 DATETIME = '2016/01/12'
    , @d2 DATETIME = '2016-01-12'
    , @d3 DATETIME = '12-jan-2016'
    , @d4 DATETIME = '20160112'

SELECT @d1, @d2, @d3, @d4
GO

В зависимости от установленного языка, константы также могут по-разному интерпретироваться:

----------- ----------- ----------- -----------
2016-01-12  2016-01-12  2016-01-12  2016-01-12

----------- ----------- ----------- -----------
2016-12-01  2016-12-01  2016-01-12  2016-01-12

И напрашивается вывод использовать последние два варианта. Сразу скажу, что задавать месяц явно — это хорошая возможность наткнуться на «же не манж па сис жур» ошибку:

SET LANGUAGE French
DECLARE @d DATETIME = '12-jan-2016'

Msg 241, Level 16, State 1, Line 29
Échec de la conversion de la date et/ou de l'heure à partir d'une chaîne de caractères.

Итого — остается последний вариант. Если хотите, чтобы константы с датами однозначно толковались в системе вне зависимости от настроек и фазы Луны, то указывайте их в формате ISO (yyyyMMdd) без всяких тильд, кавычек и слешей.

Еще стоит обратить внимание на различие в поведении некоторых типов данных:

SET LANGUAGE English
SET DATEFORMAT YMD

DECLARE
      @d1 DATE = '2016-01-12'
    , @d2 DATETIME = '2016-01-12'

SELECT @d1, @d2
GO

SET LANGUAGE Deutsch
SET DATEFORMAT DMY

DECLARE
      @d1 DATE = '2016-01-12'
    , @d2 DATETIME = '2016-01-12'

SELECT @d1, @d2

Тип DATE, в отличие от DATETIME, корректно интерпретируется при различных настройках на сервере:

---------- ----------
2016-01-12 2016-01-12

---------- ----------
2016-01-12 2016-12-01

Но нужно ли держать этот нюанс в голове? Вряд ли. Главное помните, что задавать даты нужно в формате ISO, остальное уже от лукавого.

Date Filter

Далее рассмотрим, как фильтровать эффективно данные. Почему-то на DATETIME столбцы приходится наибольшее число костылей, так что с этого типа данных мы и начнем:

USE AdventureWorks2014
GO

UPDATE TOP(1) dbo.DatabaseLog
SET PostTime = '20140716 12:12:12'

Теперь попробуем узнать, сколько строк вернет запрос за определенный день:

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE PostTime = '20140716'

Запрос вернет 0. Почему? При построении плана SQL Server пытается преобразовать строковую константу к типу данных столбца, по которому идет фильтрация:

Есть правильные и неправильные варианты вывести требуемые данные. Например, обрезать время:

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE CONVERT(CHAR(8), PostTime, 112) = '20140716'

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE CAST(PostTime AS DATE) = '20140716'

Или задать диапазон:

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE PostTime BETWEEN '20140716' AND '20140716 23:59:59.997'

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE PostTime >= '20140716' AND PostTime < '20140717'

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

Поле PostTime не входит в индекс, и особого эффекта от использования «правильного» подхода при фильтрации нам не увидеть. Другое дело, когда нам нужно вывести данные за месяц. Чего только не приходилось видеть:

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE CONVERT(CHAR(8), PostTime, 112) LIKE '201407%'

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE DATEPART(YEAR, PostTime) = 2014
    AND DATEPART(MONTH, PostTime) = 7

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE YEAR(PostTime) = 2014
    AND MONTH(PostTime) = 7

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE EOMONTH(PostTime) = '20140731'

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE PostTime >= '20140701' AND PostTime < '20140801'

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

ALTER TABLE dbo.DatabaseLog
    ADD MonthLastDay AS EOMONTH(PostTime) --PERSISTED
GO
CREATE INDEX ix ON dbo.DatabaseLog (MonthLastDay)
GO

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE MonthLastDay = '20140731'

В сравнении с прошлым запросом разница в логических чтениях будет очень существенная:

Table 'DatabaseLog'. Scan count 1, logical reads 782, ...
Table 'DatabaseLog'. Scan count 1, logical reads 7, ...

Но суть не в этом. Как я уже говорил, любые вычисления на индексных полях снижают производительность и приводят к увеличению логических чтений:

USE AdventureWorks2014
GO

SET STATISTICS IO ON

SELECT BusinessEntityID
FROM Person.Person
WHERE BusinessEntityID * 2 = 10000

SELECT BusinessEntityID
FROM Person.Person
WHERE BusinessEntityID = 5000

Table 'Person'. Scan count 1, logical reads 67, ...
Table 'Person'. Scan count 0, logical reads 3, ...

Если взглянуть на планы выполнения, то в первом случае SQL Server приходится выполнить IndexScan:

Во втором же случае мы увидим IndexSeek:

Convert Implicit

Теперь поговорим про такую редиску, как convert implicit, но для начала пример:

USE AdventureWorks2014
GO

SELECT BusinessEntityID, NationalIDNumber
FROM HumanResources.Employee
WHERE NationalIDNumber = 30845

SELECT BusinessEntityID, NationalIDNumber
FROM HumanResources.Employee
WHERE NationalIDNumber = '30845'

Смотрим на планы выполнения:

В первом случае — предупреждение и IndexScan, во втором — все хорошо. Что произошло? Столбец NationalIDNumber имеет тип данных NVARCHAR(15). Константу, по значению которой необходимо отфильтровать данные, мы передаем как INT. В итоге получаем неявное преобразование типов, которые может снижать производительность.

Решение достаточно простое — нужно контролировать, чтобы типы данных при сравнении совпадали. Особенно это актуально при использовании EntityFramework.

LIKE ’%...%’

Что еще нужно знать? Даже когда у вас есть покрывающий индекс, еще не факт что он будет эффективно использоваться. Классический пример: вывести все строки, которые начинаются с ...

USE AdventureWorks2014
GO

SET STATISTICS IO ON

SELECT AddressLine1
FROM Person.[Address]
WHERE SUBSTRING(AddressLine1, 1, 3) = '100'

SELECT AddressLine1
FROM Person.[Address]
WHERE LEFT(AddressLine1, 3) = '100'

SELECT AddressLine1
FROM Person.[Address]
WHERE CAST(AddressLine1 AS CHAR(3)) = '100'

SELECT AddressLine1
FROM Person.[Address]
WHERE AddressLine1 LIKE '100%'

Логические чтения:

Table 'Address'. Scan count 1, logical reads 216, ...
Table 'Address'. Scan count 1, logical reads 216, ...
Table 'Address'. Scan count 1, logical reads 216, ...
Table 'Address'. Scan count 1, logical reads 4, ...

Планы выполнения, по которым можно быстро найти победителя:

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

Но что если нужно найти все вхождения подстроки в строку? Это задачка уже явно интереснее:

SELECT AddressLine1
FROM Person.[Address]
WHERE AddressLine1 LIKE '%100%'

Но сначала нам нужно узнать много чего занимательного про строки и их свойства.

Первое, что нужно помнить — строки бывают UNICODE и ANSI. Для первых предусмотрены типы данных NVARCHAR/NCHAR (по 2 байта на символ). Для хранения ANSI строк — VARCHAR/CHAR (1 байт — 1 символ). Есть еще TEXT/NTEXT, но про них лучше забыть изначально. И вроде бы на этом можно было закончить, но нет...

Если в запросе задается юникодная константа, то перед ней нужно обязательно ставить символ N. Чтобы показать разницу, достаточно простого запроса:

SELECT '致死罪 ANSI', N'致死罪 UNICODE'
---------- -------------
??? ANSI   致死罪 UNICODE

Если не указывать N перед константой, то SQL Server будет пытаться искать подходящий символ в ANSI кодировке. Если не найдет, то подставит знак вопроса.

COLLATE

Вспомнился один очень интересный пример, который любят спрашивать при собеседовании на позицию Middle/Senior DB Developer. Вернет ли данные следующий запрос?

DECLARE
      @a NCHAR(1) = 'Ё'
    , @b NCHAR(1) = 'Ф'

SELECT @a, @b
WHERE @a = @b

И да... и нет... Тут как повезет. Обычно я так отвечаю.

Почему такой неоднозначный ответ? Во-первых, перед строковым константами не стоит N, поэтому они будут толковаться как ANSI. Второе — очень многое зависит от текущего COLLATE, который является набором правил при сортировки и сравнении строковых данных.

USE [master]
GO

IF DB_ID('test') IS NOT NULL BEGIN
    ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE test
END
GO

CREATE DATABASE test COLLATE Latin1_General_100_CI_AS
GO

USE test
GO

DECLARE
      @a NCHAR(1) = 'Ё'
    , @b NCHAR(1) = 'Ф'

SELECT @a, @b
WHERE @a = @b

При таком COLLATE вместо кириллицы мы получим знаки вопросов, потому что символы знака вопроса равны между собой:

---- ----
?    ?

Стоит нам поменять COLLATE на какой-нибудь другой:

ALTER DATABASE test COLLATE Cyrillic_General_100_CI_AS

И запрос уже не вернет ничего, потому что кириллица будет правильно интерпретироваться. Поэтому мораль тут простая: если строковая константа должна принимать UNICODE, то не надо лениться ставить N перед ней. Есть еще и обратная сторона медали, когда N лепиться везде, где можно, и оптимизатору приходится выполнять преобразования типов, которые, как я уже говорил, приводят к неоптимальным планам выполнения.

Что еще я забыл упомянуть про строки? Еще один хороший вопрос из цикла «давайте проведем собеседование»:

DECLARE
      @a VARCHAR(10) = 'TEXT' 
    , @b VARCHAR(10) = 'text'

SELECT IIF(@a = @b, 'TRUE', 'FALSE')

Эти строки равны? И да... и нет... Опять ответил бы я.

Если мы хотим однозначного сравнения, то нужно явно указывать COLLATE:

DECLARE
      @a VARCHAR(10) = 'TEXT' 
    , @b VARCHAR(10) = 'text'

SELECT IIF(@a COLLATE Latin1_General_CS_AS = @b COLLATE Latin1_General_CS_AS, 'TRUE', 'FALSE')

Потому что COLLATE могут быть как регистрозависимыми (CS), так и не учитывать регистр (CI) при сравнении и сортировке строк. Разные COLLATE у клиента и на тестовой базе — это потенциальный источник не только логических ошибок в бизнес-логике... Еще веселее, когда COLLATE между целевой базой и tempdb не совпадают.

Создадим базу с COLLATE, отличным от дефолтного:

USE [master]
GO

IF DB_ID('db') IS NOT NULL BEGIN
    ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE test
END
GO

CREATE DATABASE test COLLATE Albanian_100_CS_AS
GO

USE test
GO

CREATE TABLE t (c CHAR(1))
INSERT INTO t VALUES ('a')
GO

IF OBJECT_ID('tempdb.dbo.#t1') IS NOT NULL
    DROP TABLE #t1
IF OBJECT_ID('tempdb.dbo.#t2') IS NOT NULL
    DROP TABLE #t2
IF OBJECT_ID('tempdb.dbo.#t3') IS NOT NULL
    DROP TABLE #t3
GO

CREATE TABLE #t1 (c CHAR(1))
INSERT INTO #t1 VALUES ('a')

CREATE TABLE #t2 (c CHAR(1) COLLATE database_default)
INSERT INTO #t2 VALUES ('a')

SELECT 'a' AS c
INTO #t3

DECLARE @t TABLE (c VARCHAR(100))
INSERT INTO @t VALUES ('a')

SELECT 'tempdb', DATABASEPROPERTYEX('tempdb', 'collation')
UNION ALL
SELECT 'test',   DATABASEPROPERTYEX(DB_NAME(), 'collation')
UNION ALL
SELECT 't',   SQL_VARIANT_PROPERTY(c, 'collation') FROM t
UNION ALL
SELECT '#t1', SQL_VARIANT_PROPERTY(c, 'collation') FROM #t1
UNION ALL
SELECT '#t2', SQL_VARIANT_PROPERTY(c, 'collation') FROM #t2
UNION ALL
SELECT '#t3', SQL_VARIANT_PROPERTY(c, 'collation') FROM #t3
UNION ALL
SELECT '@t',  SQL_VARIANT_PROPERTY(c, 'collation') FROM @t

При создании таблицы COLLATE наследуется от базы данных. Единственное отличие — для первой временной таблицы, для которой мы явно определяем структуру без указания COLLATE. В этом случае она наследует COLLATE от базы tempdb.

Сейчас остановимся на нашем примере, потому что если COLLATE не совпадают — это может привести к потенциальным проблемам. Например, данные не будут правильно фильтроваться из-за того, что COLLATE может не учитывать регистр:

SELECT *
FROM #t1
WHERE c = 'A'

Либо SQL Server будет ругаться на невозможность соединения таблиц из-за различающихся COLLATE:

SELECT *
FROM #t1
JOIN t ON [#t1].c = t.c

Последний пример очень часто встречается. На тестовом сервере все идеально, а когда развернули бэкап на сервере клиента, то получаем ошибку:

Msg 468, Level 16, State 9, Line 93
Cannot resolve the collation conflict between "Albanian_100_CS_AS" and "Cyrillic_General_CI_AS" in the equal to operation.

После чего приходится везде делать костыли:

SELECT *
FROM #t1
JOIN t ON [#t1].c = t.c COLLATE database_default

BINARY COLLATE

Теперь, когда «ложка дегтя» пройдена, посмотрим, как можно использовать COLLATE с пользой для себя. Помните пример про поиск подстроки в строке?

USE AdventureWorks2014
GO

SELECT AddressLine1
FROM Person.[Address]
WHERE AddressLine1 LIKE '%100%'

Данный запрос можно оптимизировать и сократить его выполнения. Но для того, чтобы была видна разница, нам нужно сгенерировать большую таблицу:

USE [master]
GO

IF DB_ID('db') IS NOT NULL BEGIN
    ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE test
END
GO

CREATE DATABASE test COLLATE Latin1_General_100_CS_AS
GO
ALTER DATABASE test MODIFY FILE (NAME = N'test', SIZE = 64MB)
GO
ALTER DATABASE test MODIFY FILE (NAME = N'test_log', SIZE = 40MB)
GO

USE test
GO

CREATE TABLE t (
     ansi VARCHAR(100) NULL
   , unicod NVARCHAR(100) NULL
)
GO

;WITH
    E1(N) AS (
        SELECT * FROM (
            VALUES
                (1),(1),(1),(1),(1),
                (1),(1),(1),(1),(1)
        ) t(N)
    ),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b),
    E4(N) AS (SELECT 1 FROM E2 a, E2 b),
    E8(N) AS (SELECT 1 FROM E4 a, E4 b)
INSERT INTO t
SELECT v, v
FROM (
    SELECT TOP(50000) v = REPLACE(CAST(NEWID() AS VARCHAR(36)) + CAST(NEWID() AS VARCHAR(36)), '-', '')
    FROM E8
) t
GO

Создадим вычисляемые столбцы с бинарными COLLATE, не забыв при этом создать индексы:

ALTER TABLE t
    ADD ansi_bin AS UPPER(ansi) COLLATE Latin1_General_100_Bin2

ALTER TABLE t
    ADD unicod_bin AS UPPER(unicod) COLLATE Latin1_General_100_BIN2

CREATE NONCLUSTERED INDEX ansi ON t (ansi)
CREATE NONCLUSTERED INDEX unicod ON t (unicod)

CREATE NONCLUSTERED INDEX ansi_bin ON t (ansi_bin)
CREATE NONCLUSTERED INDEX unicod_bin ON t (unicod_bin)
GO

Далее выполняем фильтрацию:

SET STATISTICS TIME ON

SELECT COUNT_BIG(*)
FROM t
WHERE ansi LIKE '%AB%'

SELECT COUNT_BIG(*)
FROM t
WHERE unicod LIKE '%AB%'

SELECT COUNT_BIG(*)
FROM t
WHERE ansi_bin LIKE '%AB%' --COLLATE Latin1_General_100_BIN2

SELECT COUNT_BIG(*)
FROM t
WHERE unicod_bin LIKE '%AB%' --COLLATE Latin1_General_100_BIN2

SET STATISTICS TIME OFF

И можем увидеть результаты выполнения, которые приятно удивят:

SQL Server Execution Times:
   CPU time = 250 ms,  elapsed time = 254 ms.

 SQL Server Execution Times:
   CPU time = 235 ms,  elapsed time = 255 ms.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 17 ms.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 17 ms.

Вся суть в том, что поиск на основе бинарного сравнения происходит намного быстрее, и если нужно часто и быстро искать вхождение строк, то данные можно хранить с COLLATE, которые заканчивается на BIN.

ISNULL и COALESCE

Идем дальше. Что еще потенциально интересного? Есть две функции: ISNULL и COALESCE. С одной стороны все просто — если первый оператор NULL, то вернуть второй оператор или следующий, если мы говорим про COALESCE. С другой стороны, есть коварное различие между ними. Что вернут эти функции?

DECLARE @a CHAR(1) = NULL

SELECT ISNULL(@a, 'NULL'), COALESCE(@a, 'NULL')

DECLARE @i INT = NULL

SELECT ISNULL(@i, 7.1), COALESCE(@i, 7.1)

Ответ и вправду не очень очевидный:

---- ----
N    NULL

---- ----
7    7.1

Почему? Функция ISNULL преобразует к наименьшему типу из двух операндов. COALESCE преобразует к наибольшему типу. Вот мы и получаем такую радость, над которой я в первый раз очень долго просидел в попытках понять, «что не так».

Math

Еще интереснее, когда сталкиваешься с математикой на SQL Server. Вроде бы разницы не должно быть:

SELECT 1 / 3
SELECT 1.0 / 3

Но по факту оказывается, что она есть — все зависит от того, какие данные участвуют в запросе. Если целочисленные, то и результат будет целочисленным:

-----------
0

-----------
0.333333

Еще интересный пример, который часто встречается на собеседованиях в том или ином виде:

SELECT
      COUNT(*)
    , COUNT(1)
    , COUNT(val)
    , COUNT(DISTINCT val)
    , SUM(val)
    , SUM(DISTINCT val)
    , AVG(val)
    , SUM(val) / COUNT(val)
    , AVG(val * 1.)
FROM (
    VALUES (1), (2), (2), (NULL), (NULL)
) t (val)

Что вернет запрос? COUNT(*)/COUNT(1) вернет общее число строк. COUNT по столбцу вернет количество не NULL строк. Если добавить DISTINCT, то количество уникальных значений, которые не NULL.

Интереснее с подсчетом среднего. Операция AVG раскладывается оптимизатором на SUM и COUNT. И тут мы вспомним про пример выше. Если значения целочисленные, то какой будет результат? Целочисленный. Об этом часто забывают.

UNION ALL

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

Но тут еще важно знать об интересном различии между этими двумя конструкциями: UNION выполняется параллельно, а UNION ALL — последовательно. И это не относится к параллельным планам, просто это такая особенность доступа к данным, которая может помочь при оптимизации.

Предположим, нам нужно вернуть 1 строку, исходя из разного набора условий:

USE AdventureWorks2014
GO

DECLARE @AddressLine1 NVARCHAR(60)
SET @AddressLine1 = '4775 Kentucky Dr.'

SELECT TOP(1) AddressID, AddressLine1, AddressLine2
FROM Person.[Address]
WHERE AddressLine1 = @AddressLine1
    OR (AddressLine2 IS NOT NULL AND AddressID > 1500)

Тогда за счет использования OR в условии у нас будет IndexScan:

Table 'Address'. Scan count 1, logical reads 6, ...

Перепишем запрос с использованием UNION ALL:

USE AdventureWorks2014
GO

DECLARE @AddressLine1 NVARCHAR(60)
SET @AddressLine1 = '4775 Kentucky Dr.'

SELECT TOP(1) AddressID, AddressLine1, AddressLine2
FROM (
    SELECT TOP(1) AddressID, AddressLine1, AddressLine2
    FROM Person.[Address]
    WHERE AddressLine1 = @AddressLine1

        UNION ALL

    SELECT TOP(1) AddressID, AddressLine1, AddressLine2
    FROM Person.[Address]
    WHERE AddressLine2 IS NOT NULL
        AND AddressID > 1500
) t

И посмотрим на план выполнения:

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

Table 'Worktable'. Scan count 0, logical reads 0, ...
Table 'Address'. Scan count 1, logical reads 3, ...

Scalar func

О чем я еще забыл упомянуть? Специально для любителей ООП. Не используйте скалярные функции в запросах на T-SQL, которые оперируют большим числом строк.

Вот пример из жизни, которым я когда-то страдал, когда еще не знал о потенциальных минусах скалярных функций:

USE AdventureWorks2014
GO

UPDATE TOP(1) Person.[Address]
SET AddressLine2 = AddressLine1
GO

IF OBJECT_ID('dbo.isEqual') IS NOT NULL
    DROP FUNCTION dbo.isEqual
GO

CREATE FUNCTION dbo.isEqual
(
    @val1 NVARCHAR(100),
    @val2 NVARCHAR(100)
)
RETURNS BIT
AS BEGIN
    RETURN
        CASE WHEN (@val1 IS NULL AND @val2 IS NULL) OR @val1 = @val2
            THEN 1
            ELSE 0
        END
END
GO

Запросы возвращают идентичные данные:

SET STATISTICS TIME ON

SELECT AddressID, AddressLine1, AddressLine2
FROM Person.[Address]
WHERE dbo.isEqual(AddressLine1, AddressLine2) = 1

SELECT AddressID, AddressLine1, AddressLine2
FROM Person.[Address]
WHERE ISNULL(AddressLine1, '') = ISNULL(AddressLine2, '')

SET STATISTICS TIME OFF

Но за счет, того что каждый вызов функции ресурсоемкий, получаем вот такую разницу:

SQL Server Execution Times:
   CPU time = 62 ms,  elapsed time = 58 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

Кроме того, использование скалярных функций в запросе мешает SQL Server строить параллельные планы выполнения, что при больших объёмах данных может существенно подкосить производительность.

CURSORs

И самое важное, что нужно понимать при работе с SQL Server. Не используйте курсоры для модификации данных. Или используйте по минимуму их, когда других вариантов уже не остается. Часто можно встретить такой вот код:

DECLARE @BusinessEntityID INT

DECLARE cur CURSOR FOR
    SELECT BusinessEntityID
    FROM HumanResources.Employee

OPEN cur

FETCH NEXT FROM cur INTO @BusinessEntityID

WHILE @@FETCH_STATUS = 0 BEGIN

    UPDATE HumanResources.Employee
    SET VacationHours = 0
    WHERE BusinessEntityID = @BusinessEntityID

    FETCH NEXT FROM cur INTO @BusinessEntityID

END

CLOSE cur
DEALLOCATE cur

Который лучше переписать вот так:

UPDATE HumanResources.Employee
SET VacationHours = 0
WHERE VacationHours <> 0

Приводить время выполнения и число логических чтений не стоит, но поверьте, разница действительно есть. Как вариант, просто расскажу про недавний пример из жизни. Встретил скрипт, в котором было два вложенных курсора. При выполнении данный код приводил к таймауту на клиенте, а всего он выполнялся примерно 38 секунд. Переписал запрос без использования курсоров — 600 мс.


Что можно сказать для послесловия? Это мой первый опыт подготовки материала для DOU, и, наверное, вышло немного сумбурно. Однако, надеюсь, все, что здесь написано, будет кому-то полезным при написании запросов на T-SQL.

Продолжени: часть 2.

Все про українське ІТ в телеграмі — підписуйтеся на канал DOU

👍ПодобаєтьсяСподобалось0
До обраногоВ обраному3
LinkedIn

Схожі статті




Найкращі коментарі пропустити

Редкий случай прочитать что-то стоящее на ДОУ.

195 коментарів

Підписатись на коментаріВідписатись від коментарів Коментарі можуть залишати тільки користувачі з підтвердженими акаунтами.

Заканчивается текущий проект, готовлюсь к новым собеседованиям, и вернулся к этой статье. Очень информативная и полезная статья. Огромное спасибо автору. И актуальной эта статья будет всегда.
Поэтому есть небольшая коррекция по секции «ISNULL и COALESCE».
В статье:

Функция ISNULL преобразует к наименьшему типу из двух операндов. COALESCE преобразует к наибольшему типу.

Немного не так. Из документации: ISNULL — Replaces NULL with the specified replacement value. И чуть дальше: Returns the same type as check_expression. Check_expression в нашем случае было CHAR(1), а replacement_value ’NULL’ - четыре символа. Вот и получим в ответе первый символ из replacement_value. Хотя, как на меня, могли бы и синтаксическую ошибку здесь высветить.
По COALESCE ситуация другая. Здесь никаких преобразований или замен нет, просто выдать первое не NULL значение из приведенного списка. Evaluates the arguments in order and returns the current value of the first expression that initially doesn’t evaluate to NULL. В списке допускаются типы данных, которые могут быть неявно преобразованы друг в друга (implicit conversion), хотя в документации об этом явно не указано. Есть только что вернет тип данных с наивысшим приоритетом: Returns the data type of expression with the highest data type precedence. Так что со списком из разных типов данных и результатом COALESCE нужно быть аккуратным.
Надеюсь, моё дополнение будет полезно.

Что могу сказать... приятно что народу данный контент полезный. Со временем примеров стало больше и если интересно можно на гите все это посмотреть: Common SQL Mistakes

отличная статья, спасибо!

Секция Cursors искусственно надуманная, тем не менее апдейт в курсоре не идентичен переписанному апдейту ниже. Зачем фильтр WHERE VacationHours <> 0
В статье для новичков интересно было бы почитать про отличия временных таблиц и табличных переменных.

P.S. Одна из наиболее частых проблем у новичков — операторы соединения, в каком случае использовать Join, а в каких Apply. И какие последствия могут быть в результате неправильных соединений.

Постараюсь с Вами не согласиться. Пример как раз очень часто встречается, особенно когда человека с оракла на сиквел пересаживают. Относительно условия... тут все просто. Зачем обновлять то, что и так содержит правильное значение? SQL Server все равно, что там было... все строки, которые подпадают под условие будут модифицироваться.

Про временные таблицы и табличные переменные я уже отвечал ниже — планируется продолжение по мере свободного времени. Ведь лучше ничего не написать, чем сделать это поверхностно... И Вы правы там действительно хватает приколов с параллелизмом, транзакциями, коллейшенами, кардинальностью, статистикой и тд.

PS. Увидел в профиле что Вы из Днепра, приходите 26 ноября на SQL Saturday Dnepr :)

Вопрос только в том, считается ли Null «правильным» значением. Если нет, то мы получаем ошибку начинающего разработчика при работе с Null-значениями ;-)

Вспомнилось как я первый раз на MS SQL искал НДС из суммы с ндс.
select (1+1/2) — результат 1
select (1+1/2.00) — результат 1.500000

SSmaker.ru/45587cf8.png

Сейчас уже ничего не удивляет

Удивляет, что эта диверсия тянется и автоматически переносится бездумными идиотами в новые языки. Есть места, где принципиально разделили два вида деления (Pascal/Modula/Oberon, Python...), или совершить тупость маловероятно за счёт того, что откровенно запрещены операции с разными типами без явного приведения (Go), но языковый майнстрим делается по принципу «скопипастим самое популярное, потом разберёмся с полученной кучей дерьма».

Молодец, хорошо написал !

пара ремарок насчет NULL

1. Логика называется «троичная» или «трехзначна» :)
2.

при работе с SQL Server, который при операции сравнения руководствуется третичной логикой: TRUE, FALSE, UNKNOWN.
При выполнении SQL Server интерпретирует условие IN:
a IN (1, NULL) === a=1 OR a=NULL
NOT IN:
a NOT IN (1, NULL) === a<>1 AND a<>NULL

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

NULL AND TRUE = UNKNOWN
NULL OR TRUE = TRUE
NULL AND FALSE = FALSE
NULL OR FALSE = UNKNOWN

результат вытекает логически если NULL разложить на возможные комбинации (TRUE or FALSE)
например почему

NULL AND TRUE = UNKNOWN ? Поскольку NULL это неизвестное или несуществующее значение, и потенциально может принимать значение как TRUE так FALSE (как предикат) тогда
потенциально возможно только два варианта предиката

1. TRUE or FALSE
2. FALSE or FALSE

первый евалюируетс в TRUE второй в FALSE. То есть трезультат может быть как TRUE так и FALSE, именно поэтому конечный результат UNKNOWN.

и так далее....

поэтому в предикат

NOT IN (1, NULL) == a<>1 AND a<>NULL
трансформируется в более понятный
NOT IN (1, NULL) == TRUE AND UNKNOWN => UNKNOWN

SQL Server was designed for maximum level of confusion © Хтось з Великих

Це перебільшення, щоб поржать.
У mssql є багато скелетів у шафі, але правда у тому, що кожна СУБД не є ідеальною і містить свої скелети, так що про кожну можна сказати maximum level of confusion.
(А mysql так взагалі писали врєдітелі та вороги народу)

Есть одна СУБД идеальная :) но никак не взлетит. Счас автор подтянется.

Ну то ви шановні не працбвали з MS SQL 6.5 в якому єдиною операцією ALTER TABLE було ADD COLUMN. Ні поміняти тип поля, ні видалити поле було неможливо через просто ALTER TABLE.

О, де виховання :) ! Структура раз и на завжди, тiльки розширення

Та отож... Важка спадщина Sybase. Але дициплінує, так.

То вы не дизайнили memory optimized таблицы s SQL 2014 где нету даже возможности сделать ALTER TABLE. )

О! Все новое это хорошо забытое старое ! :)

Круговорот. Вот с новой версией стало легче. Так само было с переходом на Sphinx

Сергію, молодець, що написав статтю. Так, у мене є питання. Але заголовок так і звучить, що вона для новачків. Мене вражають коменти Senior dev-ів, що це бриліант або золота стаття і т.д. Виходить, що на рівні сенйора програмісти цього не знають?! Для мене це нонсенс...Або ви не сенйори або я щось не розумію...

Тепер буду перевіряти Senior .NET девів на інтерв’ю на UNKNOWN. Мій естімейт — 80% завалить.

Увы и не которые Middle/Senior БД разработчики этого не знают. Из 14 интервью, которые я проводил, более половины не могли ответить про простую арифметику на уровне AVG(DISTINCT ...) Вывод весьма простой — лейблы и звания не коррелируются с реальными знаниями.

Это правда, поскольку такие мелочи никто не копает. А затыкается все это через ISNULL, потом, со сканами и дикими планами.

Тем не менее незнание работы с NULL не есть признак не синьориточти дева.
А тем более для NET/Java/etc девов. Если знает то плюс в карму как любознательному.
Важней все же как кандидат дизайнит схему, мыслит множествами и как логически правильно начинает рисовать запрос.

Ну згоден що дизайн важливіший, але NULL має знати...

Как Вам угодно .

Выучить концепцию работы с NULL — пара дней.
Научится дизайну — года (не факт что прийдет).

Можно закрыть глаза на незнание мелочей. Подтянет в процессе.

Эммм, у меня один вопрос. Что там с нуллами учить на пару дней? :) Ну для опытного ДБ дева?

Действительно, что там учить ! Чем опытней тем быстрее учишься :)

За 16 лет работы с БД — ни разу, ну вот НИ РАЗУ не было необходимости использовать функцию AVG :) Ну и того-этого. Голова ж не мусорный ящик — знать всё обо всем, даже о том, чего никогда не использовал.
Из недавно открытого. В шарпе при выполнении округления — первый раз 0.5 округляется в ноль. При всех последующих — в 1. Ну и что? При собеседованиях требовать от всех знания этого дебилизма?

что-то мне подсказывает, что там не про AVG, а про комбинацию агрегирующей функции и DISTINCT.
count(DISTINCT field) при наличие NULL тоже может удивить, не?

В шарпе при выполнении округления — первый раз 0.5 округляется в ноль. При всех последующих — в 1. Ну и что?
офигеть. можно подробнее? сходу не нагуглил.

был не прав, вон, ниже человек дал линку на MSDN:
Except for COUNT, aggregate functions ignore null values

Напомните днем — у меня комментарий в коде содержит все подробности

Нашел :)
msdn.microsoft.com/...y/wyk4d9cy(v=vs.110).aspx
Смотреть пример в самом конце, в секции “Заметки...”
Обратите внимание на результат обработки данных:
// 11.4 —> 11
// 11.5 —> 11
// 11.6 —> 12
//
// 11.5 —> 12

Бинго, матьиво. Пока нашел этот “ньюанс” — чуть голову не того-этого.
In the following example, because the floating-point value .1 has no finite binary representation, the first call to the Round(Double) method with a value of 11.5 returns 11 instead of 12.

ну так он же дба, в скл не так много всего, чтобы он за долгое время работы до чего-то не добрался, для него стремно звучит что авг бывает не знают, это как будто бы кто-то из программистов строки не умеет конкатинировать, а то что эти знания могут нифиг не нужны это уже другой вопрос

Я совмещаю дб девелопера и Шарп девелопера в одном мне :)
И тем не менее, не приходилось

поздравляю, тогда то, что выше написал Сергей это про тебя

видимо всегда попадал на простые проекты, где это было не надо, такое часто встречается

хотя не написать авг сходу хз кем надо быть... и то что из 14 человек половина не смогла как-то вообще стремно

Спасибо. Надо будет рассказать коллегам что биллинговая система укртелекома это «простые проекты»

Ну вот зачем скатываться в негатив? Бывают разные проекты и задачи... например на текущем у меня OLTP система на 1 ядре и Express редакция с 12Гб базой активных данных. Свои приколы и заморочки... Пост был ориентирован на то, чтобы указать потенциальные ошибки для «проекта в вакууме»...

Дык я исключительно поддерживаю. Статья полезная. Некоторые моменты заставили задуматься — отдельное спасибо. Но вот товарищ полностью исключил налл из жизни... И доказывает что наллы вообще зло, которого не должно и не может быть в прожектах...
Ну не знаю. Из серии «очевидное-невероятное».

ПС: и таки да, в OLTP системах думаю можно избежать налл. Но это же такое мааааленькое подмножество решений с применением БД... Хотя... Может человек занимался только такими штуками — из серии обработать мгновенные показания ста тыщ датчиков в (условно) реальном времени...

За 16 лет работы с БД
Снимок размера сразу в студнию бы....
Ну и того-этого. Голова ж не мусорный ящик — знать всё обо всем, даже о том, чего никогда не использовал.
Это правильная мысль но формулировка неверная.
Инженер не должен знать все, но должен знать это найди как сделать, даже не использовал.
.
ни разу, ну вот НИ РАЗУ не было необходимости использовать функцию AVG
Нисколько не характеризует ваш опыт, но выглядит правда стремно.
Снимок размера сразу в студнию бы....
Айнанэ, дома оставил :)
Это правильная мысль но формулировка неверная.Инженер не должен знать все, но должен знать это найди как сделать, даже не использовал.
Эмм. Прошу прощения, а на собеседованиях часто дают возможность инженеру поискать как сделать? Речь-то изначально шла о собеседованиях? Вот этих вот, олдскульных — с решением задач на листике?
И таки что в моей мысли в данном контексте неверное? :)
Нисколько не характеризует ваш опыт, но выглядит правда стремно.
Ну спасибо хоть за то, что не характеризует :)

Грубо говоря, я вижу три варианта ответа на интервью.
1. Кандидат знает
2. Кандидат слышал
3. Кандидат ни в зуб ногой
Третий вариант кот в мешке. Толи несталкивался (с годами опыта таких ответов должно быть все меньше и меньше) толи игнорировал или непонимает.

Ок, попробую раскрыть суть проблемы, как она мне видится со стороны.
Вот например в моем случае. Мне не приходилось заниматься статистикой, хотя что-то слышал про всякие lag, avg и проч. При этом совершенно случайно знаю, что avg — просто сокращение от average. И да, теоретически зная об этом, можно ДОГАДАТЬСЯ, как эта функция обрабатывает значения null. Но вот например не знаю впринципе что такое функция mid. Или тот же lag. Когда-то где-то слышал названия, но не запомнил — потому что не пригодится.
И вот возникает вопрос. Стоит ли делать какие-то выводы по ситуации, когда пациент не знает достаточно специфические функции SQL. Ведь как верно было отмечено кем-то, в случае ДБ девелопера важнее понимание принципов проектирования, подходов к грамотному построению sql запросов. А прочитать как посчитать среднее по части выборки... Это ж вопрос наличия гугла и еще минут 10. В особо сложных случаях — 20-30 минут.

Вадим !

1. функция AVG — это базовая функция TSQL и не являается чем-то новым.
Заменить ее вычисление на SUM/COUNT ? не вариант и чесно выглядит нелепо (кроме случаев как вычисление среднего от среднего: AVG of AVG with one GROUP BY ).
2. Все агрегирующие функции (MIN/MAX/COUNT/AVG/SUM) работают с NULL одинаково msdn.microsoft.com/...-us/library/ms173454.aspx. With one exception for COUNT
3. Lag и ежели им подобные — интродюснуты в 2012. Относительно недавно, в вариант «не знаю» слабо но верится. Однак если кандидат рисует конструкцию которая функционально идентична функции LAG, то это НАМНОГО лучше чем тупо знание синтаксиса функции LAG.

Из таких вот простых постулатов я сделаю простой вывод, если не работал с AVG значит
— не работал с агрегатами.
— будет плавать с GROUP BY,
— не знаком со сложными запросами
— DWH/ETL не спрашивать
и т.д.

Вот позволю себе заметить что с незнанием NULL таких далеких выводов я б не сделал.

Тому в целом — несогласен.

PS речь идет за ДБ Дев.
DBA к примеру, допускаю и не знать, хотя в моем понимании DBA должен быть с дев бекграундом.

Я видимо неверно выразил свою мысль. Вопрос не в синтаксисе функции avg или lag. Потенциальный кандидат может просто не знать, что эта функция делает. Вот и все. Я по случайности знаю как расшифровывается avg, не знаю в принципе что делает lag. При наличии интернета — пробел восполняется за 5 минут. Собеседование не предполагает возможности подсмотреть в гугле. И этот момент не дает возможности человеку раскрыть свои возможность инженера.
Вот и все.
Не знаю, смог ли донести мысль.
Если вкратце — проблема не в тонкостях обработки NULL значений, а в понимании, что эта функция вообще делает.
Ну нельзя знать все справочники наизусть.

— не работал с агрегатами.
ну их же много разных. Может Вы имели ввиду аналитические функции?
— будет плавать с GROUP BY,
с чего бы?
— не знаком со сложными запросами
вот уж точно «с чего бы». Ну или каждый понимает свое под «сложным запросом».
— DWH/ETL не спрашивать
скорее всего
Вот позволю себе заметить что с незнанием NULL таких далеких выводов я б не сделал.
Дык я например не спорю с тем, что понимать NULL — необходимо.

Только замечу что

ну их же много разных. Может Вы имели ввиду аналитические функции?
Немного, на пальцах руки поместятся msdn.microsoft.com/...-us/library/ms173454.aspx
Аналитические функции отдельно

Вообще из мелочей и состоит наша жизнь. Для меня лично кандидат (да и сотрудник) который мыслит только гуглом — не может быть хорошим по определению. Может у вас другие стандарты. ХЗ.

Ну что же Вы так — из крайности в крайность

А, ну и того-этого. Перечисленные Вами варианты не предполагают применения/выявления упомянутого важного качества инженера — умения найти ответ на поставленную задачу. Об чем, сосбно, и речь.

4. Кандидат догадался

Гы. Вопросы с односложным ответом Yes/No исключаем ;)

void Test()
{
    for (int i = 0; i < 1000; ++i) if (Math.Round(0.5) != 0) throw new Exception("Impossible!");
}

куайн проси написать, завалят 99%, больше наркомании на собеседованиях

Наркоманія — знати про UNKNOWN в SQL Server? Ви знаєте, у нас з вами ніколи нічого не вийде ©

наркомания — это когда менеджер, ничего не понимающий в программировании, выхватил какую-то мелкую фигуличку из статьи, которую многие программисты никогда в жизни не встречают, и решил спрашивать это на собеседовании

:) Я на .НЕТ співбесіди проводжу в ЕРАМі і координую інтерв’юверів, виріс з джуна до сіньйора і був 2 роки тімлідом на 2 різних проектах. Давайте поговоримо за спінлоки?)

ты перестал быть программистом, когда поперся в тимлиды, ты не найдешь о чем со мной поговорить про дотнет, чтобы ты знал, а я нет

Перестав, коли в РМи поперся. Можливо з тобою не знайду, з 95% — знайду.

Как бы это помягче... я плохо представляю себе задачи, в которых никогда не сталкиваешься с NULL и особенностями работы с ним. Возможно, грамотный DBA замёл весь этот мусор под ковёр в чулан, но даже в этом случае столкнуться слишком легко.

Поэтому, может, не ответ на каверзные вопросы, но постоянная готовность ожидать подвоха в каждом SQL-выражении — необходимый признак даже миддла. А сеньор таки должен сходу опознавать основные потенциальные подводные камни вместе с путями их решения/замены.

Поэтому, может, не ответ на каверзные вопросы, но постоянная готовность ожидать подвоха в каждом SQL-выражении

С SQL легко стать параноиком )

в нуле нет надобности, вообще нет, он значит «неопределенность», а нафига может понадобится не определенность в программе? бозон хиггса рассчитывать? нул реально не нужен в более чем 95% задач, часто его добавляют как маленькое зло, чтобы меньше кода написать заложившись на условие «этого никогда не произойдет», проходит время и появляются проблемы, потом огораживаются костылями

поэтому если решил добавить нул куда-нибудь, то ты уже плохой человек, если нул уже добавлен заботливыми коллегами, то придется потыкать все варианты, а не только успешный кейс, нул не от хорошей жизни добавили, скорее всего найдешь и костыли рядом, и то что ты допилил работать будет неправильно из-за них

просто нужно поменять отношение к нулу, раз он значит неопределенность, то перетянув его в объектную модель у тебя будет неконсистентный объект, что является ошибкой, не отказываясь от инкапсуляции назад консистентным он никогда не станет, работа с классом с нарушенным состоянием очень плохо, порождает технические долги, которые можно только переписать заново, рефакторить такое не получится

Поэтому, может, не ответ на каверзные вопросы, но постоянная готовность ожидать подвоха в каждом SQL-выражении — необходимый признак даже миддла
когда насрано с горкой, то ожидать подвохов стоит вообще от всего, а не только скл
нул реально не нужен в более чем 95% задач, часто его добавляют как маленькое зло
null в Java это огромное зло. И миллиарды долларов и не меньше часов человеческих страданий потрачено на обход этой болезни языка.
null в Java это огромное зло.

Вы вообще в курсе, что речь шла про SQL? Или Вам пофиг, куда влезть, лишь бы знакомые слова были?

И миллиарды долларов и не меньше часов человеческих страданий потрачено на обход этой болезни языка.

«У каждой проблемы есть простое, ясное, неправильное решение» ™
Избавление от null — именно такое решение. Зато оно популярно среди сайентологов от программирования и у тех, кто их слушает.

Вы вообще в курсе, что речь шла про SQL? Или Вам пофиг, куда влезть, лишь бы знакомые слова были?
а скл он как, сам по себе? или просто вспомогательный компонент для программы?
«У каждой проблемы есть простое, ясное, неправильное решение» ™
Избавление от null — именно такое решение. Зато оно популярно среди сайентологов от программирования и у тех, кто их слушает.
большинство людей, которые любят нулы нет объяснения нафига они их везде тулят, топовое объяснение это «нет данных», на вопрос «а когда записи нет это значит данные есть?» я получаю такой же ответ и на вопрос «на нафига мне 2 способа чтобы данных не было?» никто уже не может ответить, потому что ответ «нафиг не надо, скл и так обычно тормоз номер один в системе, чтобы еще партии нулов хранить»
а скл он как, сам по себе? или просто вспомогательный компонент для программы?

С точки зрения соотношения с программой — сам по себе. Между ним и программными сущностями — слой посредников и трансляции с кучей своих особенностей.

и на вопрос «на нафига мне 2 способа чтобы данных не было?» никто уже не может ответить

Или ты не слышишь их ответ?

потому что ответ «нафиг не надо, скл и так обычно тормоз номер один в системе, чтобы еще партии нулов хранить»

SQL NULLs обычно много места не занимают :) а вот каждая колонка в своей таблице, или как там ещё это представить — ой как занимают. Особенно когда на них всех ещё и индексы вести надо, вот радость-то.

Или ты не слышишь их ответ?
я разве непонятно написал?
SQL NULLs обычно много места не занимают :) а вот каждая колонка в своей таблице, или как там ещё это представить — ой как занимают. Особенно когда на них всех ещё и индексы вести надо, вот радость-то.
ну это классно, что они места не занимают, только когда данных сотни гигабайт фиг запрос за адекватное время сделаешь
я разве непонятно написал?

Да. Потому что слишком часто, когда не устраивает ответ, пишут «не может ответить». Даже если в 99% случаях, правда, кто-то не мог ответить и нёс чушь, остаётся 1% на непонимание.

ну это классно, что они места не занимают, только когда данных сотни гигабайт фиг запрос за адекватное время сделаешь

Я вот почему-то уверен, что NULLʼы в этом не виноваты. Виноваты плохие индексы, медленные диски и т.п.

Я вот почему-то уверен, что NULLʼы в этом не виноваты. Виноваты плохие индексы, медленные диски и т.п.
ну конечно, мусорные записи не виноваты, просто диски не могут их обслуживать, когда их милларды, виноваты диски

Для этих случаем Net бекграунд не годится. Время ORM закончилось ;) Sr. DB Dev вам в помощь

и что он сделает? если просто нужно добавить колонку

Если проблемы нет — добавьте сами.

тебя Дейт покусал?
NULL нужен, ибо он означает «нет данных». То есть, какое-либо значение — это данные есть, а NULL — данных нет и результат операций с ними неизвестен.
Другое дело, что его и использовать надо с умом, каждый раз спрашивая себя «может ли этот аттрибут быть NULL?» Даже больше — если в таблице большое количество полей нулябельны, то вполне возможно провести дальнейшую нормализацию и вынести их в отдельную таблицу, согласно с функциональными зависимостями — и таблица уже, и данных будет меньше.
А в случаях, когда поле не нулябельно, то нужно рассматривать, давать ли полю значение по умолчанию.
Как-то так.

NULL имеет два понятия (когда вводился этот термин)
1. Значение отсутсвует
2. Значение неопредлелено

Пример
1. Мы знаем что у кастомера точно есть телефон, но мы не знаем его номера
2. Попуас на необитаемом острове не имеет телефона по определнию

Попуас на необитаемом острове...

Папуас (від малайського «папува» — кучерявий) — представник населення Нової Гвінеї, Маланезії та Індонезії.

Именно так я и представляю среднего обитателя ДОУ !
А по теме поста. Ну включите воображение. Папуас (спасибо через «а») оказался вне новой гвинеи, на необитаемом острове. И телефона у него отродясь небыло, на острове связи нет.
ЗЫ второй яплакаль

в нуле нет надобности, вообще нет, он значит «неопределенность», а нафига может понадобится не определенность в программе?

В программе совершенно нормальная ситуация, когда у неё каких-то данных нет. Например, данных ещё нет. Данных за конкретный момент нет, например, в результате отказа связи с датчиком. Данных нет, потому что датчик явно сказал об ошибке измерения. Всё это даёт «какую-то» неопределённость — данных нет. У кого-то может быть неизвестно имя, у кого-то — возраст, у кого-то — ещё какой-то параметр. Наличие всех данных в базе — ситуация теоретически красивая, практически же нереальная. И поэтому появляются разные NULL. В США тебе напишут, за грабёж разыскивается John Doe, или Mary Roe. Это такой же NULL, хоть и специфически оформленный. И даже если узнают имя, могут не знать место жительства и прочие параметры. Везде будет какой-то NULL в этом случае.

Но:

Ошибка дизайна SQL не в наличии NULL. Ошибка в том, что он только один, и при этом не получил конкретный смысл «данных нет», он получил смысл «про данные ничего не известно». Данных нет — это известное состояние отсутствия, с ним можно работать, и, например, 1 <> NULL в этом случае будет по определению.

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

Именно! Он не перекладывается нормально на модель, принятую в собственно программировании. И проблема в том, что он практически всегда используется именно в смысле «отсутствие данных», а не «просто неопределённость».

Аналогичные проблемы есть в плавающей точке: там не отличают NaN в результате невозможности рассчитать — от NaN от явного отсутствия значения. При необходимости знать это — приходится заводить какие-то внешние признаки. Так что не SQL один совершает эту ошибку, и с теми же последствиями (NaN != NaN).

В программе совершенно нормальная ситуация, когда у неё каких-то данных нет.
так вот пусть их и не будет, а не какие-то сурогаты с нулами
Данных за конкретный момент нет, например, в результате отказа связи с датчиком. Данных нет, потому что датчик явно сказал об ошибке измерения.
нет данных — нет записи, нафига она без данных? в логи запиши что связи нет
Всё это даёт «какую-то» неопределённость — данных нет.
нет данных и неопределенность очень разные вещи
Именно! Он не перекладывается нормально на модель, принятую в собственно программировании. И проблема в том, что он практически всегда используется именно в смысле «отсутствие данных», а не «просто неопределённость».
и что с таким делать? кроме обкладывания костылями и выворотом кишок наружу, чтобы «нет данных» заполнить данными? я знаю что многие по глупости такое делают, но это нифига не правильно, скл всего лишь хранилище инфы, зачем под него подстраиваться, пусть оно подстраивается, если по производительности что-то не может менять на то что может, пятидесятые давно закончились, чтобы от бд строить все
скл всего лишь хранилище инфы
думаю дальнейшая дискуссия бесполезна :)

о да, о да, оскорбил чувства верующих

Да ладно, Денис !

Я такие фразы слышу от каждого второго Net/Java/PHP/etc синьора.
В таких случаях я предлагаю такое решение — раз БД это свалка а давайте ка писать все в один текстовый файл, в одну строку !
Ненуашо ? И как то в мозгу таких «синьоров» щелкает что, таки эволюция от файлов произошла таки. Гы.

А вообще был красочный пример когда комманда нетчиков (чел 10-20) дизайнила солюшн на миллионы баксов банку и дизайн БД был сделан через ORM.... Code First так сказать. Результат был неплохой пока не задеплоили этот солюшн. Locks, deadlocks, long running queries — типичный походный набор таких солюшнов.
Как только плевались потом DB Devs от этого ужаса. Переписывать ? Поздно. так и произошел фейл.

Очень показательный пример опрометчивости.

Ну а если чел не видел базы больше 10GB что от него взять ? все в RAM влезло и поехали ;)

Очень жаль, что на тот момент ты только институтт закончил.
Счас бы ты им дал прикурить .... Правда ?

да при чем здесь я вообще? 20 человек гребли не пойми куда, ни одному в светлую голову не пришло даже проверить, а будет ли их месево работать вообще

согласен :) Одно удивляет — что этот тип делает в Оракле. Если конечно он что-то делает отличное от доставки кофе от кофеварки програмиздам

Не дерзкие, но удивленные. Как можно не понимать необходимости налл для дб — ну не знаю. Опыт должен быть минимальным.

Так оракл давно индусская контора)

То что индусы пилят инсталляху для оракла — еще не означает, что оракл это индусская контора

Баг с синхронизацией тредов в OCI клиенте всплывал только по тому, что я видел, раз 20. Всё дедлоки в глубинных стеках вызова из функций типа exczm0.
Верно, это не индусы. Это даже не знаю кто. Реднеки?

ХЗ. Но могу сказать две вещи. Индусы не занимаются (не занимались) разработкой ядра и критически важных модулей. Общий уровень новых релизов оракла упал — видимо, в связи с некоторой потогонностью (из серии — даешь пятилетку в четыре года, тобишь новый релиз на год раньше)

складывается впечатление что ты думаешь что оракл только базу данных делает

складывается впечатление, что упомянутые проблемы имеют отношение к каким-то другим продуктам оракла.

так вот пусть их и не будет, а не какие-то сурогаты с нулами

Так что именно в этом случае должно быть записано? Если из 50 предусмотренных полей можно заполнить только 40?
Выносить каждое поле в отдельную таблицу не предлагать, тем более что попытка их объединить породит такие же NULL.

нет данных — нет записи, нафига она без данных? в логи запиши что связи нет

Ряд алгоритмов фильтрации данных лучше работает при наличии явных признаков сбоя, а не просто провала.

нет данных и неопределенность очень разные вещи

Так в том и дело, что вторая используется как первая. И так у большинства использований SQL.

пусть оно подстраивается

На уровне «мышки, станьте ёжиками» я могу и не такое рассказать. А практически — что там сделать? В своих приложениях я могу породить тип данного с любым разумным количеством значений типа «точно нет», «вероятно нет» и т.д., но потом это ж пихать в базу — получится минимум 2 поля вместо одного в каждом случае.

Так что именно в этом случае должно быть записано? Если из 50 предусмотренных полей можно заполнить только 40?
Выносить каждое поле в отдельную таблицу не предлагать, тем более что попытка их объединить породит такие же NULL.
ты костыли костылями пытаешься оправдать, нафига плодить год обжекты? что потом делать с сущностью с 50 полями? он один все в программе делает?
Ряд алгоритмов фильтрации данных лучше работает при наличии явных признаков сбоя, а не просто провала.
проблемы индейцев шерифа не волнуют
На уровне «мышки, станьте ёжиками» я могу и не такое рассказать. А практически — что там сделать? В своих приложениях я могу породить тип данного с любым разумным количеством значений типа «точно нет», «вероятно нет» и т.д., но потом это ж пихать в базу — получится минимум 2 поля вместо одного в каждом случае.
серебряной пули нет, проектировать без нулов точно можно, просто возьми за аксиому что нул это ошибка и проектируют с оглядкой на это, все получится, не нарушая все принципы ооп, ты не сможешь в коде пользоваться сущностями, у которых нет части данных, зачем они тебе нужны будут в скл, если ты их ни использовать, ни записать не можешь? ты себе вбил в голову вот эту безумную поддержку говнокода с нулами и не хочешь видеть, что есть мир программирования без страданий и мучений, и на крошечных проектиках и на огромных с бигдатой это все точно работает
что потом делать с сущностью с 50 полями?

Работать. На любимую авторами картинок к БД сущность employee и побольше, чем 50, бывает.

проблемы индейцев шерифа не волнуют

Мы не индейцы, поэтому такой «шериф» идёт лесом и берётся тот, кто умеет выполнять свою работу.

проектировать без нулов точно можно

Проектировать — может быть. Реально работать — обычно нет.

просто возьми за аксиому что нул это ошибка

Зачем мне такие аксиомы?

и на крошечных проектиках и на огромных с бигдатой это все точно работает

URL на сборник case studies в студию. Посмотрим, как оно работает и какое оно имеет отношение к реальности.

Работать. На любимую авторами картинок к БД сущность employee и побольше, чем 50, бывает.
клево что ты приводишь в пример какой-то треш, книжка с картинками не «вредные советы»?
Мы не индейцы, поэтому такой «шериф» идёт лесом и берётся тот, кто умеет выполнять свою работу.
... работу по генерации рабочих мест?
Проектировать — может быть. Реально работать — обычно нет.
если ты кроме плохого ничего не видел, то о чем вообще речь?
Зачем мне такие аксиомы?
та незачем, год класс, нулы, без ооп с солидом, лепи-говнячь дальше
URL на сборник case studies в студию. Посмотрим, как оно работает и какое оно имеет отношение к реальности.
как Якубовичем станешь, так и в студию начнут заносить вещи по требованию, такие книги никто не пишет, их никто не продать в большом количестве, большинству надо ляп-ляп и в продакшен, а не дзен постигать
книжка с картинками не «вредные советы»?

Ну, может, и вредные. Но тогда 90% книг по БД это сплошные вредные советы, и, главное, от авторов СУБД — тоже. Странно, да?

если ты кроме плохого ничего не видел, то о чем вообще речь?

А с чего ты решил, что это плохое? :)

та незачем, год класс, нулы, без ооп с солидом, лепи-говнячь дальше

EPARSE.

такие книги никто не пишет

«У нас есть та-а-акие данные, но мы вам их не покажем»

Ну, может, и вредные. Но тогда 90% книг по БД это сплошные вредные советы, и, главное, от авторов СУБД — тоже. Странно, да?
в скл ничего не происходит уже 60 лет, то старье что рерайтят до сих пор читают
А с чего ты решил, что это плохое? :)
логичный вывод с того что ты говоришь
"У нас есть та-а-акие данные, но мы вам их не покажем"
=\
в скл ничего не происходит уже 60 лет, то старье что рерайтят до сих пор читают

Ну а чё менять-то. Концепция устоялась, хоть и с дикими странностями. Шлифуют мелочи и начинают наконец прислушиваться к практикам :)

логичный вывод с того что ты говоришь

Плохо как раз использовать, не понимая всей глубины наших глубин. Но, к сожалению, так везде.

в нуле нет надобности, вообще нет, он значит «неопределенность», а нафига может понадобится не определенность в программе?
если у нас данные могут отсутствовать, то есть четыре пути:
1. какое-то исключительное магическое значение(-1 для поля возраста); запросы сложнее, логика обработки «отсутсвующих данных» расплескивается по всему приложению и грозит устареть в любой момент(начали учитывать данные младенцев с возрастом 0 лет)
2. отдельная таблица для связи 0..1; в случае того же адреса это будет много-много таблиц, а запрос на вытягивание списка, скажем, клиентов с адресами, будет сплошь состоять из LEFT JOIN, так как отсутствие поля «подъезд» в адресе — еще не повод не доставлять еду.
3. отдельная таблица EAV; запросы становятся еще сложнее, на клиенте БД надо будет оперировать безструктурным списком вместо хеш-таблицы(как когда у нас разные поля одной записи, а не разные записи)
4. NULL как единственно допустимое «магическое значение» из пункта 1; единственная сложность — тот самый нюанс со сравнением(кто сталкивался с NaN, даже не удивится)

Охренеть. Т.е. у Вас не бывает свойств объектов, которые вот сейчас — НАЛЛ, а завтра — со значением? Внешние ключи, вот это вот все, вот эта вот вся нуллофобия — она для Вас моветон??

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

говнячить с нулами, естественно, куда проще и удобнее, но только в краткосрочной перспективе, в долгосрочной такой код говнокодом зовут и никто не хочет с ним возится

Все свойства на таблицах с пропертями? Типа многие ко многим? А на больших объемах данных пробле

Все свойства на таблицах с пропертями? Типа многие ко многим? А на больших объемах данных проблем со скоростью не возникает?
А бд с количеством таблиц хотя бы в сотню (без учета вашей денормализации) видеть приходилось?
А в коде тоже без наллов обходитесь? Или каким-то чудом определяете, что вот у этого объекта некоторое свойство не определено?

Все свойства на таблицах с пропертями? Типа многие ко многим? А на больших объемах данных проблем со скоростью не возникает?
ну на работах скл обычно сразу есть с нулами, косяками и прочим, личные проекты никогда с скл не связываю, я большой сторонник носкл решений, мне сложно тебе на это ответить, когда мне таки приходится рисовать таблицы я стараюсь чтобы они были без нулов, но ведь я не могу единолично принимать решения
А бд с количеством таблиц хотя бы в сотню (без учета вашей денормализации) видеть приходилось?
приходилось и больше
А в коде тоже без наллов обходитесь? Или каким-то чудом определяете, что вот у этого объекта некоторое свойство не определено?
да, неопределенное свойство — это либо нарушение целосности объекта, либо сломана инкапсуляция, либо у объекта миллион ответственностей и по нулам внутри он понимает что ему делать в этот раз

Вот тот вот вопрос выше — ведь Вы так и не ответили на него. Банальная таблица учета клиентов. Каждое свойство, которое может быть пустым — Вы выносите в отношение «многие-ко-многим»? К примеру — у клиента нет домашнего телефона. Или мобильного. Или никакого. Или на момент регистрации о нем неизвестно. Налл? или многие-ко-многим? А если — ну вот так получилось — у клиента СТО свойств, которые могут быть не указаны? А могут быть указаны?
Без наллов умеете решать такие вопросы? Ну чтоб потом при выборке на тыщу клиентов с скажем двумя десятками свойств это все работало относительно быстро?

ты ставишь вопрос так что на него нет ответа «как же это сделать», если ты хочешь сделать именно так, то ты либо захлебнешься на джоинах, либо натулишь нулов

тут важен контекст как ты будешь данными пользоваться, в общем случае мне на сервисе никогда не нужен класс с сотней полей и неважно нулы там или джоины, мне тупо не нужны все эти связи для большинства действий, а ты меня такой таблицей хочешь принудить либо тянуть это все, либо делать миллион сущностей чтобы тянуть по разному разный набор полей, оба варианта неприемлимые

если ты не уяснил с прошлого объяснения, повторюсь: как бы ты там не разложил в бд с нулами или нет, сама бд ничего не делает, ей нужен инициатор, инициатору (сервису) все за раз не надо, да, ты сможешь с нулами вернуть любой набор данных и это будет очень быстро, только это никому не надо и все жертвование в угоду производительности было напрасно

Минуточку, не надо передергивать. Я давал шанец на пути к отступлению. Мне в конкретной выборке не нужны, конечно же, вся сотня полей. Но штук двадцать — вполне. Из серии ФИО, пол, домашний адрес (хотя — адрес, согласен, имеет смысл хранить в отдельной таблице), но все те же телефоны, ДР, номер документа, пол, номер страховки. Те параметры, которые нужны (практически) всегда и значения которых зачастую отсутствуют. Особенно на начальных этапах ведения клиента.
И вот эти данные, повторюсь, нужны для большинства действий.
И мы тут кагбе говорим не про сферический вебсервис в вакууме (или какую подобную хрень, на основе данных которой можно подтянуть детали при необходимости).

Ну и про «если ты не уяснил». Так вот, если ты не уяснил. Как бы ты там не разложил получение данных в данном конкретном запросе — в БД должно быть место, откуда все это брать. И если сейчас тебе не нужны какие-то специфические параметры клиента, то вот через секунду в другом запросе — нужны. И нужны не по три nullable свойства, а по тридцать три. И вот тогда — да. Ты обязательно закопаешься в джоинах и в пересечении одной и той же таблицы тридцать три раза.
И в данном конкретном примере — абсолютно ВСЕ свойства клиента могут быть nullable, за исключением какого-нибудь логина и пароля. И банально на форму редактирования параметров клиента тебе придется вытянуть ВСЕ его свойства.

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

мать моя женщина. Почему говногод-то? Ну да ладно.

потому что 16 лет работы с скл сделали с тобой необратимое, я уверен что код на любом языке у тебя в процедурном стиле будет

Ну блин. Человече. Ну не знакомы же. Ну да ладно. Так получилось, что все это время приходилось педалить и клиентскую, и серверную часть.
Ну так бывает.

Если атрибутов много в одной сущности (что само по себе наводит на мысль о «правильном дизайне»), то группировать атрибуты и выносить один-к-одному (почему многие ко многим ?!).
Тут классический вариант решения без гимора.
Если аттрибутов по настолько много > 1000 и большую часть они NULL то юзайте SPARSE/Wide table/XML & JSON in row etc

первый вариант (группировать и выносить в таблицы) карош тогда, когда атрибуты четко делятся на группы и таких групп мало. Как в случае с тем же адресом. Да и то — совершенно не исключает NULL значений. Как у того же адреса — когда есть дом, или блок, или общага какая-нибудь.
Многие-ко-многим — случай, когда Вы хотите максимально сэкономить место, объявляете таблицу типовых свойств объекта, и таблицу экземпляров свойств. Многие-ко-многим. Ура — место сэкономлено. NULL значения устранили..
Ну и про последний вариант. Говорить о том, что большая часть много — не совсем верно для БД с «живыми» данными, а не со статическими. Приведенный «классический» пример клиента — ситуация, когда по мере обслуживания клиента информация о нем наполняется.И если на старте «большую часть они NULL», то через непродолжительное время ситуация здорово меняется.
Признаюсь — упомянутые Вами инструменты не использовал, как впрочем и не имел таблиц с тысячей свойств. Но вот возникает вопрос — насколько быстро будут идти выборки по значениям свойств, хранящихся в JSON. Ну да то такэ.

Sparse — здорово экономит место для хранения NULL. Space saving зависит от типа и % NULL в таблице. гуглится легко.
как и Wide Table

Многие-ко-многим — случай, когда Вы хотите максимально сэкономить место,

не делайте себе проблемы c M2M. Сложные запросы, джойны и как следствие масшабируемость такого решения.

Я бы предложил микс 1-к-1 и для вынесенных NULL таблиц — SPARSE option. Ну как вариант. Задачи не знаю )

XML показывает себя чудесно. Правда, проблем особых нет.
JSON напрямую не поддерживается ( 2012 & 2014 в продакшне), однако несложный CLR based конвертор решает вопрос. Однако мы в прод такой солюшн не пускаем. Ждем SP1 for 2016.

не делайте себе проблемы c M2M. Сложные запросы, джойны и как следствие масшабируемость такого решения.
Ви меня таки с кем-то путаете :) Я не борец за отсутствие NULL в таблицах. Спор зашел с гражданином, который уверял — NULL это зло недопустимое. И без них леххко можно обойтись.
Это не мой тезис :)
Да и вообще — на тех задачах, которыми я занимаюсь — мне абсолютно не интересна экономия места на хранилище, да еще и за счет танцев с nullable колонками. С этими фантазиями, повторюсь, не ко мне :)
Вы, видимо, ответили в длинную ветку, посмотрев только на одно сообщение.
Ессно я не горожу приведенные решения М2М. Это были фантазии на тему того, как гражданин в реальной жизни может избежать nullable колонок, не более того!
XML показывает себя чудесно. Правда, проблем особых нет.
ну что ж — значит будет тема для «почитать».
Ждем SP1 for 2016.
в моей сегодняшней реальности используется 2005 сервер как минимально допустимый в продакшене. Такие дела.

Ну и того-этого. Про носкл БД знаю только в общем. Но к чему лезть с носкл решениями в статью про MS SQL Server?

дык я и не лез, я написал что наркомания спрашивать на собеседовании, то что нифиг не надо знать на память, после этого мне начали доказывать что нулы самое важное в мире, а я написал что от них больше проблем чем пользы

вот как можно написать так противоречиво? С одной стороны — согласен, требовать знания специфических функций типа avg — не стоит. С другой — наллы это действительно весьма важная штука для дб разработчика. Не понимать хотя бы общих тонкостей работы — это, действительно, недопустимо. Поскольку большинство приложений, не завязаных чисто на статистику и расчеты — обречено на использование наллов

ты оправдываешь использование налов тем, что иначе скл медленно работает, нафиг он нужен, если не справляется с задачей, надо с умом выбирать технологии для проекта, а не потому что «ну скл, мы же уже с ним работали, он многое умеет... всеми пользуются — берем»

... для дб разработчика.
это рудиментарная профессия, лет 10-15 пройдет и они будут как дельфисты нафиг никому не нужны, вместе с скл в текущем виде, все чаще появляются проекты, в которых запись в хранилище нужна чаще, чем позволяет скл

Эммм. По первой ремарке. Я оправдываю использование наллов производственной необходимостью. КАк-то сразу после института усвоил, что программирование ради программирования никому не нужно. Этот момент уже тут обсуждался — когда реальные посоны сделали красиво, но оно в жизни не взлетело.
Фразу про «надо с умом выбирать технологии для проекта»... Ну кагбе того-этого. Могу спросить словами одного героя из кино. «Дядя, ты дурак?».

По второй ремарке — могу сказать одно. Ахаха. Такие вещи я слышал лет 10 назад. И ничего — вот очередные крутые посоны сдвигают дату гибели еще на десяток годков.
Ну и того-этого. Если «с умом выбирать технологии для проекта», то sql server прекрааааасно справляется с массовой записью данных в БД. Ну не одиночными инсертами, конечно...
Вобщем, если у пана будет час та натхнення — пусть почитает про такие штуки как ОИК, и представит, что ОИК по управлению техпроцессом НЭК Укрэнерго в трех областях Украины (ПС: да, надо и про НЭК почитать) прекрасно справляется на Оракле даже не Enterprise Edition (хотя он бы там не помешал). На достаточно слабом кластере надежности (не балансирования). С одним хранилищем. Сливая ежесекундно данные с десятков тысяч датчиков, разнесенных территориально. Приходилось, знаете ли, учавствовать в создании.
И вот это все работает не на пределе возможностей. И, слышал, есть аналогичная штука в масштабах всей Украины. Вот не помню — на Оракле или Скуль Сервере. Но да — лет 10-15 — и можно выбрасывать. Профессию. Шоб значицца на автоматическом маппинге с додНеда на таблицы БД (или на хранилище носкл) все это закидувать.

КАк-то сразу после института усвоил, что программирование ради программирования никому не нужно. Этот момент уже тут обсуждался — когда реальные посоны сделали красиво, но оно в жизни не взлетело.
да какая разница взлетел или нет, у меня программирование — искусство, а не ремесло
Ну и того-этого. Если «с умом выбирать технологии для проекта», то sql server прекрааааасно справляется с массовой записью данных в БД. Ну не одиночными инсертами, конечно...
на средних и маленьких проектах, а какой-нибудь СО или твиттер, где серьезная нагрузка, с скл не сделаешь никогда
да какая разница взлетел или нет, у меня программирование — искусство, а не ремесло
обычно, в общем-то, цель работы сводится к получению результата, а не к процессу.
на средних и маленьких проектах, а какой-нибудь СО или твиттер, где серьезная нагрузка, с скл не сделаешь никогда
читал, что какой-нибудь твиттер работает на каком-то mySQL.
читал, что какой-нибудь твиттер работает на каком-то mySQL.
тогда, наверное, ты суть не уловил

StackOverflow — большой проект? Он к слову... крутится на SQL Server 2016.

Да собс упомянутый Свиттер крутится на SQL БД

Промахнулся, когда отвечал. Извините.

Да не, не промахнулись :) Это просто к слову о том, что аффтар уверял что свиттер не потянет sql субд.

Средний. 60 гиг база в архиве.
ЗЫ ее мейтенят люди хорошего уровня. Хотя если верить ДОУ спецам они скоро без работы останутся :)

Это вы имели ввиду ссылку, которую Брент Озар когда-то зашарил? Если да, то там не вся база SO, а только её часть. Насколько я помню за несколько месяцев.

Насколько я знаю Brent Ozar этим занимается :) Если не сейчас то в прошлом точно он им базу тюнил.

не совсем, в нем просто вся инфа хранится, большинство оперативных действий в него попадает уже для конечного сохранения через какое-то время

обожаю собеседования в духе "сам не знаю, но в интернете вычитал"(на самом деле — нет)

В Бен-Гана вичитав, уоли джуном був

А в чем изюм заваливать кандидатов идиотскими вопросами? Чтобы взять кого-то другого с 4-м размером на его место?

Ответ комплексный, начиная от «сбить спесь» заканчивая траблами, на которые можно защитить не дурную работу по психологии :))

Вопросы ни капельки не идиотские. Ну разве что в варианте, когда сеньор на .NET ни разу ничего не делал с SQL. Но вероятность слишком мала.

Вероятность хоть и мала, но почему-то я регулярно с таким сталкивался. Крайне мало .NET девов имеют полное представление об планах выполнения и хотя бы просто об SQL... наивно полагая, что ORM сделает за них всю работу. Когда данных в БД мало этот прикол еще работает, а потом как перевалит за определенный предел — начинается паника. Про истории из жизни промолчу... но примеров было много.

То есть всё с ними сильно хуже, чем я думал... спасибо за информацию.

Бывает по-разному... за то время сколько я работаю ровно 4х .NET девов смог насчитать которые с пониманием и трепетом относились к работе с БД. Когда мы вместе с ними архитектуру промывали, экономили на оборудовании с помощью простой оптимизации запросов и правильных индексов. По остальным товарищям есть куча невеселых историй в стиле «давай хранить дату как строку» или «давай сделаем два вложенных курсора» потому что ему так понятнее...

Это беда не только MS SQL и .NET. Увы языки и фреймворки автоматизирующие data access abstraction layer отдаляют прогеров от БД и они перестают понимать что там происходит. А потом начинается — ой, ну че там , я ж одно поле добавилЮ чого это миграция БД вторые сутки идет. Индексы? Нет, не слышал.

Брилiант, тому що 99% статей i форуму на DOU це зарплата, податки, «вайтi-вайтi» i емiграцiя. А тут нарештi цiкава дискусiя.

Прекрасная статья, прям таки бриллант на DOU. Пишите еще.

C IN а особенно NOT IN другая проблема у начинающих, если ее дать на достаточно большую выборку, то получим переполнение строковой переменной на серваке и его крах, не говоря уж о памяти. Может уже исправили в новых версиях, но тем не менее. Можно делать так:

declare @t1 table (id int)
declare @t2 table (id int)
insert into @t1 values (1),(2),(3)
insert into @t2 values (1),(null),(3)

—не правильно и опасно, перегруз строки и падение сервера при большом количестве в @t2
select id from @t1 where id not in (select id from @t2)
—правильно и безопасно
select id from @t1 t1 where not exists(select id from @t2 t2 where t2.id = t1.id)

Можно узнать на какой версии у Вас возникает проблема с «перегруз строки и падение сервера»? Единственное, что будет плохого в такой конструкции — так это огромное число IndexSeek, но чтобы из-за этого падал сервер... В общем странно как-то.

Была проблема в SQL 2008R2. Если залить данные в table variable or temp table без clustered index и потом сделать weirdly query like

SELECT/UPDATE
--- some expressions
FROM T
WHERE T.x in (SELECT FROM T )

то запрос падал с ошибкой таблица не найдена, И она пропадала на самом деле.
Сервер не падал :) просто валилась сессия.

Я MS SQL по работе знаю с 2000 версии, уже не скажу. Возможно это был и не MS, жизнь заставила работать с целым зоопарком серверов. Просто с тех пор избегаю этой конструкции ( в разумных пределах, конечно)

Все це тривіально обходиться через джойни.

Спасибо! Полезная статья для ознакомления не только джуниорам.
Я бы рискнул предложить перед первым примером разобрать отдельно выражение NULL = NULL, например по моему опыту следующий запрос нередко вызывает ступор:
if null = null print 'true' else print 'false'
Понимание, какой и при каких условиях будет результат в этом примере сильно помогает с пониманием NOT IN vs NULL и других подобных моментов в построении запросов.

А мне больше нравится вариант ;-)
select case when null = null then 'TRUE' else 'FALSE' end,case when null <> null then 'FALSE' else 'TRUE' end

Очень интересно и полезно, спасибо!

Помню читал похожее: www.simple-talk.com/...sql-programming-mistakes
Я так понимаю, что данная статья и была вдохновителем.

Статейка хорошая, но вдохновителем была подготовка к sqlsaturday.com/577

О, зайду послушать тогда (thumb)

Спасибо вам большое, за ваши труды!
И, я, с вами, абсолютно согласен, самое страшное, что может подстерегать новичков при работе с MSSQL — это не знание предметной области(ну и не новичков, в том числе).

Оставлю тут общий комментарий... Рад, что информация оказалась полезной. В скором времени постараюсь сделать продолжение, потому что на 18й странице голова перестала формулировать мысли... и очень многое осталось за бортом: parameter sniffing, sql injection, приколы с табличными переменными и временными таблицами и прочее.

Часть из того, что не успел написать будет на вебинаре: www.sql.ru/...tual-chapter-19-oktyabrya

Статья в плане технических аспектов шикарна.

один страдал от аллергии на свежий воздух
А это что значит?

Лучше не будем :) история не настолько интересная, чтобы рассказывать. К слову... есть еще «аллергия на учебу». Лично с ней познакомился, когда дали мне в нагрузку молодого падавана на старом проекте.

Мне просто интересно, не связано ли это как-то с кондиционерами. Некоторые люди воздух из кондиционера называют «свежим» :)

Курсоры... Это штука кот-я не совсем вписывается в декларативную парадигму SQL. Как правило использование курсора говорит о том, что разработчик просто не может уложить свой мозг в SQL. Правда, у меня был случай использования курсоров — в динамической структуре БД когда нужно было написать стандартную сохраненку кот-я работает с разными таблицами в зависимости от типа объекта. Имена таблиц брались из словаря данных, курсор создавался динамически с помощью EXEC. А дальше с этим курсором можно было работать благодаря isolation level. Эххх... Прикольная БД была. В начальном виде несколько таблиц составляющих словарь данных и сохраненки создающие нужные наборы таблиц и т.д.

не может уложить свой мозг в SQL

Я бы переформулировал — «SQL в свой мозг» :)

Не не. Человек обычно думает что у него SQL уложен, но оказывается что ни в реляционных БД ни в декларативном программирований он по прежнему не понимает.

Есть простая задача. которую я так и не могу просто решить без курсора, может кто просветит :)

— есть процедура, к примеру
create procedure p1 (@id int ) as select @id
— и некая выборка
declare @t1 table (id int)
insert into @t1 values (1),(2),(3)
select id from @t1 where id = 3

— нужно выполнить для каждого ID из выборки
exec p1 ID

ну и не смог решить «задачу о ранце»

ну можна переписати або обернути SP в UDF и так
SELECT T.id, UDF.out_int
FROM
Table1 T
CROSS APPLY
dbo.fn_My_UDF(T.id) AS UDF

або в умовно-бескінечному циклі бігати.
щось типу
DECLARE @Id int
WHILE EXISTS(SELECT * FROM Table1 WHERE Processed=’N’)
BEGIN
SELECT Top 1 @Id = id from Table1 WHERE Procesed=’N’
EXEC dbo.Sproc @Id
UPDATE Table1 SET Processed = ’Y’ WHERE Id = @Id
END

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

что разработчик просто не может уложить свой мозг в SQL
" не верно, есть задачи и для них.

Я написал «как правило» :). Т.е. не всегда.
Ну и конечно же если бы не было задач для курсоров то их бы в SQL просто не было бы.
PS
Вполне допуская что сейчас положение изменилось, но в мое время программисты БД были испорчены Foxpro и локальными БД и тупо пытались работать с SQL серверами как с плоскими таблицами.

а чем функция не подходит? сделать p1 функцией и вызывать себе на здоровье:
select id, p1(id) from @t1 where <все наши условия>

функция не может менять состояние базы данных да и вообще там по мелочи много отличий- в основном не пользу хранимок blogs.msdn.microsoft.com/...d-procedure-and-function

Ясно — плюс один скелет в шкафу у mssql.
У mysql с этим намного легче, оказывается — обьявил функцию not deterministic и прогнал запросом через нее все данные — удобно капец, никаких курсоров не нужно. А я так гоню на них, аж стыдно стало ;)

Редкий случай прочитать что-то стоящее на ДОУ.

Ну преобразования типов это красота! У меня был случай когда в формуле типа A=B+C-D*F промежуточный результат D*F (и то и то INT) не влазил в INT и обрезался. При чем логика почему ПРОМЕЖУТОЧНАЯ переменная была INT а не больше досих пор для меня загадка...

Полностью согласен... Приколов и вправду много при преобразовании типов. Особенно веселит в таких случаях Entity Framework который передает параметры в одном типе, а ожидается другой. Что потом подобные приколы разгребать приходится:

DECLARE @a DECIMAL(8,4), @b CHAR(3) = '0.125' SET @a = @b SELECT @a

То не приколи Entity Framework то криворукі програмісти не вірно пописали мапінги для обєктів. Який мапінг написав такий тип і пришле.

Бинарная арифметическая операция над двумя int имеет результат int — это как раз логика практически всех языков уровня выше ассемблера и Форта. Иногда это таки очень грустно — как раз когда нужно реализовать защиту таких операций от переполнений — приходится для умножения с результатом двойной ширины спускаться до ассемблера.

Бинарная арифметическая операция
эээ?
это как раз логика практически всех языков
окей.

python => print(1/2) => 0.5
javascript => alert(1/2) => 0.5
php => echo 1/2 => 0.5

java => System.out.println(1/2) => 0
ruby => puts 1/2 => 0
c# => Console.WriteLine (1/2); => 0
go => fmt.Println(1/2) => 0

не сказал бы, что «практически все»

эээ?

Что?

не сказал бы, что «практически все»

Javascript не в счёт, там целых вообще нет, и операция одна (зато можно результат привести к целому, кому надо).

Python3 чётко разделяет / (деление вещественное) и // (деление нацело), 2-й ещё не разделял. Его я как раз привёл как положительный пример.

Java, C# - как раз то, что я говорил. Go — тоже, но во втором смысле — он решает не проблему двузначности оператора, а проблему неожиданной двузначности оператора, за счёт невозможности деления при разных типах. При одинаковых же типах понять происходящее значительно проще.

PHP обычно за пределами моего восприятия о майнстримовых языках, куда входят JS, C/C++, Java, C#, Python. Но у него это, неожиданно, решено правильно.

Вообще я говорил совсем не о том. Не о том, что некоторые операции уходят во float. А о том, что int32*int32, например, может давать int32, как в языках с фиксированной размерностью, а может int64. В x86, обычные mul/imul с аккумулятором дают двойную ширину результата, и делению на вход подаётся аргумент двойной ширины. Так же практически во всех классических ISA. В новых это частично ломают (AArch64, RiscV, некоторые другие например, предусматривают делимое только одинарной ширины) — jIMHO, экономят на спичках за счёт принесения существенного неудобства.
В Forth есть операции M*, UM*, M/MOD, UM/MOD, которые работают именно с двойной шириной произведения или делимого. А вот в C эта возможность уже, к сожалению, похерена. Использовать же хаки типа (int64_t)x*y может давать заметную потерю эффективности, а в случае int64*int64 уже просто невозможно — тогда кроме ассемблера пути нет.

Что?
что значит сочетание «бинарная арифметическая»? какие еще есть типы арифметических операций? логическая арифметическая?
Вообще я говорил совсем не о том.
ну, я ответил в контексте именно деления и перехода целое-не целое.
А так, да. И с делением можно получить переполнение: 1e+20 / 1e-38 или какие там граничные значения экспоненты
какие еще есть типы арифметических операций? логическая арифметическая?

Унарная арифметическая (как унарный минус, унарный плюс). Вы не в курсе, что когда пишете -x, это унарная арифметическая операция? Ну так будете знать :)

А ещё есть:
Бинарная логическая (булевская) (&&, ||). Бинарная побитовая (&, |, ^).
Унарная логическая (not, ~ в C).
Это не все, но достаточно для иллюстрации. И у них тоже такие же проблемы с типами — для типов короче int происходит (C, C++, многие близкие к ним) «повышение» типа (даже для унарных), далее приведение к общему типа (для бинарных), явное или неявное.

ну, я ответил в контексте именно деления и перехода целое-не целое.

Я вижу. Но в исходном выражении деления не было, а было умножение и выход за пределы принятого целого именно в результате умножения.

Вы не в курсе, что когда пишете -x, это унарная арифметическая операция?
в курсе. странно, что вы внезапно вспомнили про арность применительно хоть к умножению, хоть к сложению.
Но вообще, это хороший способ запутать собеседника. Надо будет запомнить, спасибо.
в курсе. странно, что вы внезапно вспомнили про арность применительно хоть к умножению, хоть к сложению.

Что странного-то?

Но вообще, это хороший способ запутать собеседника. Надо будет запомнить, спасибо.

Это очень плохо, что вместо понимания Вы считаете, что вас хотели запутать, и ещё и «запоминаете» как метод.

А я смотрю технический пост так неслабо раскрыл... И SQL это просто хранилище, и глубины арифметики открылись...

А я смотрю технический пост так неслабо раскрыл... И SQL это просто хранилище
ну есть странные люди, которые натулят функций, хранимых процедур, плагинов, тригеров и прочего говна, а потом этого монстра суппортить никто не хочет, хрупкая фигня, которую ни изменить, ни расширить

Натулят классов в своем .net-е, а потом этого монстра суппортить никто не хочет...
ой, шо это я... то, что в субд нету классов или еще чего, не означает, что там не нужно придерживаться всех тех хороших практик — структуризация кода (да-да, старое доброе структурное программирование), DRY и KISS, нормализация везде, индексы где надо, и щепотка денормализации в качестве приправы :)

В Тайланд — ни ногой!

Отличная статья!

Підписатись на коментарі