假设我们有如下表:
CREATE TABLE dbo.tab(id INT PRIMARY KEY
-- other columns
,is_active BIT);
INSERT INTO dbo.tab(id, is_active)
VALUES (1, NULL), (2, 1), (3,0);
此案是添加计算列,这将改变NULL
对0
并尽可能恢复原来的值。
最后结果:
UPDATE
和ALTER
表)VIEW/TRIGGER/...
因此,让我们只需添加该列:
CREATE TABLE dbo.tab(
id INT PRIMARY KEY
,is_active BIT
,calc_flag1 AS CAST(IIF(is_active IS NULL,0 ,is_active) AS BIT)
,calc_flag2 AS CAST(IIF(is_active IS NULL,0 ,ISNULL(is_active,0)) AS BIT)
,calc_flag3 AS IIF(is_active IS NULL,0 , ISNULL(is_active,0))
,calc_flag4 AS CAST(ISNULL(IIF(is_active IS NULL,0 , is_active), 0) AS BIT)
,calc_flag5 AS ISNULL(IIF(is_active IS NULL,0 ,is_active),0)
,calc_flag6 AS ISNULL(CAST(IIF(is_active IS NULL,0 ,is_active) AS BIT),
CAST(0 AS BIT))
);
数据:
SELECT * FROM dbo.tab;
╔══╦═════════╦══════════╦══════════╦══════════╦══════════╦══════════╦══════════╗
║id║is_active║calc_flag1║calc_flag2║calc_flag3║calc_flag4║calc_flag5║calc_flag6║
╠══╬═════════╬══════════╬══════════╬══════════╬══════════╬══════════╬══════════╣
║ 1║ NULL ║ False ║ False ║ 0 ║ False ║ 0 ║ False ║
║ 2║ True ║ True ║ True ║ 1 ║ True ║ 1 ║ True ║
║ 3║ False ║ False ║ False ║ 0 ║ False ║ 0 ║ False ║
╚══╩═════════╩══════════╩══════════╩══════════╩══════════╩══════════╩══════════╝
和元数据查询:
EXEC sp_help 'dbo.tab';
╔═════════════╦══════╦══════════╦════════╦══════╦═══════╦══════════╗
║ Column_name ║ Type ║ Computed ║ Length ║ Prec ║ Scale ║ Nullable ║
╠═════════════╬══════╬══════════╬════════╬══════╬═══════╬══════════╣
║ id ║ int ║ no ║ 4 ║ 10 ║ 0 ║ no ║
║ is_active ║ bit ║ no ║ 1 ║ ║ ║ yes ║
║ calc_flag1 ║ bit ║ yes ║ 1 ║ ║ ║ yes ║
║ calc_flag2 ║ bit ║ yes ║ 1 ║ ║ ║ yes ║
║ calc_flag3 ║ int ║ yes ║ 4 ║ 10 ║ 0 ║ no ║
║ calc_flag4 ║ bit ║ yes ║ 1 ║ ║ ║ yes ║
║ calc_flag5 ║ int ║ yes ║ 4 ║ 10 ║ 0 ║ no ║
║ calc_flag6 ║ bit ║ yes ║ 1 ║ ║ ║ no ║
╚═════════════╩══════╩══════════╩════════╩══════╩═══════╩══════════╝
第一次尝试:
,calc_flag1 AS CAST(IIF(is_active IS NULL,0 ,is_active) AS BIT)
正确的数据类型,但它不能得到空性。我可以理解,因为它具有硬编码的值和可为空的列,因此整个表达式被评估为可为空。
第二次尝试:
,calc_flag2 AS CAST(IIF(is_active IS NULL,0 ,ISNULL(is_active,0)) AS BIT)
与以前一样,但有明确的ISNULL(is_active, 0)
。现在,它应该工作,因为没有硬编码的价值,ISNULL
但事实并非如此。
,calc_flag3 AS IIF(is_active IS NULL,0 , ISNULL(is_active,0))
这很有趣,没有CAST
得到它nullable
- no
,但数据类型是INT
现在。
第三次尝试:
,calc_flag4 AS CAST(ISNULL(IIF(is_active IS NULL,0 , is_active), 0) AS BIT)
铸造ISNULL
时第二值是固定的。为什么会这样nullable
?
,calc_flag5 AS ISNULL(IIF(is_active IS NULL,0 ,is_active),0)
当然,如果不进行强制转换,它应该可以正常工作。
最后尝试:
,calc_flag6 AS ISNULL(CAST(IIF(is_active IS NULL,0 ,is_active) AS BIT),
CAST(0 AS BIT))
现在,我得到了正确的数据类型和可为空性,但是它有点难看。
问题是,为什么它的行为这样,当无法获得正确的元数据calc_flag2
或calc_flag4
使用。
首先要注意的是,当使用IIF
(扩展为CASE
幕后的表达式)时,如果所有返回表达式都不为空,则结果将为空,因此在使用时:
IIF(is_active IS NULL,0,is_active)
尽管从逻辑上讲,当您is_active
在表达式中获取false时,由于条件设置,它永远不会为null,但这与编译器无关,它只能看到返回的表达式之一是is_active
可为空的列,因此返回的类型可为空。
我认为问题可以简化为为什么ISNULL(is_active,0)产生不可为空的位列,而只是添加像CONVERT(BIT,ISNULL(is_active,0))这样的转换会导致同一列为可为空。
快速演示:
CREATE TABLE #tab(
id INT PRIMARY KEY
,is_active BIT
,calc_flag1 AS ISNULL(is_active, 0)
,calc_falg2 AS CONVERT(BIT, ISNULL(is_active, 0))
);
EXECUTE tempdb.dbo.sp_help '#tab';
给出了相关的结果
Column_name Type Computed Nullable
--------------------------------------------
id int no no
is_active bit no yes
calc_flag1 bit yes no
calc_falg2 bit yes yes
从使用的特定部分这个答案(感谢保罗·怀特)的原因是,一些设置会话可能会导致转换溢出返回NULL,所以要确保非空列的唯一途径是,如果最外面的功能ISNULL
。
可以使用ISNULL(is_active, 0)
上述方法简单地实现所需的解决方案,因为这将返回一个不可为空的bit列,但是值得注意的是,如果需要转换,例如,如果您需要将其转换为int列,则必须将转换在里面ISNULL
。由于ISNULL
将返回第一个参数的类型,仅需要一个转换,例如
CREATE TABLE #tab(
id INT PRIMARY KEY
,is_active BIT
,calc_flag1 AS ISNULL(is_active, 0)
,calc_falg2 AS CONVERT(BIT, ISNULL(is_active, 0))
,calc_flag_int AS ISNULL(CONVERT(INT, is_active), 0)
);
EXECUTE tempdb.dbo.sp_help '#tab';
给出了相关的结果
Column_name Type Computed Nullable
--------------------------------------------
id int no no
is_active bit no yes
calc_flag1 bit yes no
calc_falg2 bit yes yes
calc_falg_int int yes no
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句