为什么视图中的Row_Number给出一个可空列

Question

我有一个使用CTE的视图，我想使用行号来模拟Visual Studio中我的edmx的键

ALTER VIEW [dbo].[ViewLstTypesArticle]
AS
WITH cte (IdTypeArticle, IdTypeArticleParent, Logo, Libelle, FullLibelle, Racine) AS 
(
SELECT        
f.Id AS IdTypeArticle, NULL AS IdParent, 
f.Logo, f.Libelle, 
CAST(f.Libelle AS varchar(MAX)) AS Expr1, 
f.Id AS Racine
FROM            
dbo.ArticleType AS f 
LEFT OUTER JOIN
dbo.ArticleTypeParent AS p ON p.IdTypeArticle = f.Id
WHERE        
(p.IdTypeArticleParent IS NULL) 
AND (f.Affichable = 1)
UNION ALL
SELECT        
f.Id AS IdTypeArticle, p.IdTypeArticleParent, 
f.Logo, f.Libelle, 
CAST(parent.Libelle + ' / ' + f.Libelle AS varchar(MAX)) AS Expr1, 
parent.Racine
FROM            
dbo.ArticleTypeParent AS p 
INNER JOIN
cte AS parent ON p.IdTypeArticleParent = parent.IdTypeArticle 
INNER JOIN
dbo.ArticleType AS f ON f.Id = p.IdTypeArticle
)
SELECT
*,
ROW_NUMBER() OVER (ORDER BY FullLibelle) AS Id 
FROM
(SELECT    
IdTypeArticle, IdTypeArticleParent, Logo, Libelle, 
FullLibelle, Racine 
FROM cte) AS CTE1

当我查看列的属性时，我看到Id bigint…空

和我的edmx排除这个视图，因为没有找到一个列可以用来键

当我执行我的视图ID没有null。我有我所有的行号

如果有人遇到这个问题并解决了它…由于

Answer 1

SQL Server通常认为列在视图中是NULL-able的(当使用SELECT INTO时)

你可以通过使用ISNULL():

来说服SQL Server这不是事实

select *,
ISNULL(ROW_NUMBER() over(ORDER BY FullLibelle), 0) as Id
from . . .

注意:这适用于ISNULL()，但不适用COALESCE()，否则有非常相似的功能。