Как сделать столбец представления NOT NULL

Я пытаюсь создать представление, в котором я хочу, чтобы столбец был только истинным или ложным. Однако кажется, что независимо от того, что я делаю, SQL Server (2008) считает, что мой битовый столбец каким-то образом может быть нулевым.

У меня есть таблица под названием «Продукт» со столбцом «Статус», который равен INT, NULL. В представлении я хочу вернуть строку для каждой строки в Product со столбцом BIT, для которого установлено значение true, если столбец Product.Status равен 3, в противном случае битовое поле должно быть ложным.

Пример SQL

SELECT CAST( CASE ISNULL(Status, 0)  
               WHEN 3 THEN 1  
               ELSE 0  
             END AS bit) AS HasStatus  
FROM dbo.Product

Если я сохраню этот запрос как представление и посмотрю на столбцы в обозревателе объектов, для столбца HasStatus будет установлено значение BIT, NULL. Но он никогда не должен быть NULL. Есть ли какой-нибудь магический трюк SQL, который я могу использовать, чтобы заставить этот столбец быть NOT NULL.

Обратите внимание, что если я удалю CAST() вокруг CASE, столбец будет правильно установлен как NOT NULL, но тогда для столбца будет установлен тип INT, а это не то, что мне нужно. Я хочу, чтобы это было BIT. :-)


nullable sql sql-server-2008 sql-view
person René    schedule 24.02.2010    source источник

Ответы (3)


arrow_upward
152
arrow_downward

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

SELECT ISNULL(CAST(
    CASE Status
        WHEN 3 THEN 1  
        ELSE 0  
    END AS bit), 0) AS HasStatus  
FROM dbo.Product

Одна из причин, по которой я нахожу это полезным, заключается в том, что при использовании ORM вам не нужен результирующий значение сопоставлено с типом, допускающим значение NULL. Это может упростить все вокруг, если ваше приложение считает, что значение никогда не может быть нулевым. Тогда вам не нужно писать код для обработки нулевых исключений и т. д.

person D'Arcy Rittich    schedule 24.02.2010
comment
@Gunder: не беспокойся, на самом деле это немного загадочно. Это также удобно использовать при создании вычисляемого битового столбца в таблице и желании, чтобы результат не допускал значение NULL. - person D'Arcy Rittich; 24.02.2010
comment
Мне нужно было что-то подобное, и я обнаружил, что COALESCE() не работает, на самом деле вам приходится использовать ISNULL() - person EvilBob22; 07.12.2013
comment
@EvilBob22 EvilBob22 Это странно, потому что и COALESCE, и ISNULL могут возвращать NULL. Просто причуда компилятора, я думаю. - person D'Arcy Rittich; 07.12.2013
comment
Это, умноженное на миллиард, за то, что EntityFramework выводит ключ, когда обычно он не выводится. - person Erik; 13.06.2014
comment
См. также: dba.stackexchange.com/questions/114260/ - person emragins; 31.08.2016

arrow_upward
3
arrow_downward

К вашему сведению, для людей, столкнувшихся с этим сообщением, добавление ISNULL() снаружи приведения/преобразования может испортить оптимизатор в вашем представлении.

У нас было 2 таблицы, использующие одно и то же значение в качестве ключа индекса, но с типами разной числовой точности (плохо, я знаю), и наше представление соединялось с ними для получения окончательного результата. Но наш промежуточный код искал определенный тип данных, и представление имело CONVERT() вокруг возвращаемого столбца.

Я заметил, как и ОП, что дескрипторы столбцов результата представления определяют его как обнуляемый, и я думал, что это первичный/внешний ключ в 2 таблицах; почему мы хотим, чтобы результат определялся как обнуляемый?

Я нашел этот пост, бросил ISNULL() по столбцу и вуаля - больше нельзя обнулить.

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

По какой-то причине явный CONVERT() в столбце результатов представления не испортил работу оптимизатора (он все равно должен был это сделать из-за разной точности), но добавление избыточной оболочки ISNULL() сделало это в большой способ.

person user1664043    schedule 14.01.2015
comment
Не могли бы вы показать решение, как обеспечить/указать необнуляемость с помощью CONVERT() в примере, пожалуйста? - person O. R. Mapper; 31.01.2017
comment
Привет, О. Р. - извини, что давно этого не видел. Вот пример. Если у вас есть CONVERT(BIT,U.RETIRED),0) AS Retired в вашем представлении, превращая, скажем, столбец byte или int в бит/bool, тогда он становится обнуляемым. Вы можете сделать этот столбец в своем представлении необнуляемым, заменив его на ISNULL(CONVERT(BIT,U.RETIRED),0) AS Retired. Если U.RETIRED не был нулевым для запуска, функционально он ничего не меняет, кроме столбца в представлении. ВНИМАНИЕ: ISNULL() может мешать оптимизации запросов и выбору индексов. - person user1664043; 09.02.2017

arrow_upward
-3
arrow_downward

Все, что вы можете сделать в операторе Select, — это управлять данными, которые механизм базы данных отправляет вам как клиенту. Оператор select не влияет на структуру базовой таблицы. Чтобы изменить структуру таблицы, вам необходимо выполнить оператор Alter Table.

  1. Сначала убедитесь, что в настоящее время в этом битовом поле в таблице нет пустых значений.
  2. Затем выполните следующую инструкцию ddl: Alter Table dbo.Product Alter column status bit not null

Если, во всяком случае, все, что вы пытаетесь сделать, это контролировать вывод представления, то того, что вы делаете, достаточно. Ваш синтаксис гарантирует, что вывод столбца HasStatus в результирующем наборе представлений фактически никогда не будет нулевым. Это будет всегда либо битовое значение = 1, либо битовое значение = 0. Не беспокойтесь о том, что говорит обозреватель объектов...

person Charles Bretana    schedule 24.02.2010
comment
Я не хочу менять столбец таблицы. Столбец определяется как целочисленный столбец, который допускает значение NULL. Это соответствует нашей спецификации. Но мне нужно представление, которое возвращает столбец с битовым полем, которое не может быть нулевым. Недостаточно, чтобы я знал, что он не может быть нулевым, столбец должен быть НЕ NULL, чтобы он правильно отображался в нашей ORM. - person René; 24.02.2010