Why Row_Number in a view gives a nullable column

I have a view using a CTE and I want use a row number to simulate a key for my edmx in Visual Studio

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

When I look in properties of column I see Id bigint … NULL

And my edmx exclude this view cause don’t find a column can be used to key

When I execute my view ID have no null. I’ve all my row number.

If someone encounter this problem and resolved it … Thanks

Answer

SQL Server generally thinks that columns are NULL-able in views (and when using SELECT INTO).

You can convince SQL Server that this is not the case by using ISNULL():

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

Note: This works with ISNULL() but not with COALESCE() which otherwise has very similar functionality.

Advertisement

Answer