SQL Server: which columns can be null
Which columns can be null
-- name: ListOrders
SELECT o.id, o.placed_at, c.name
FROM dbo.orders AS o
LEFT JOIN dbo.customers AS c ON c.id = o.customer_id;
For each column, the tool records whether it can be null, starting from what SQL Server says. If orders.id, orders.placed_at and customers.name are all NOT NULL, then id and placed_at above are never null. name can be null: the LEFT JOIN gives a null name to an order with no customer.
SQL Server treats a column as one that can be null unless it can show that the column never is. SqlSource keeps that answer unless it can show from your query that the column is never null. A column that it treats as one that can be null, and that never is, costs a null check in your code.
SQL Server says that a column can be null wherever a null can come from:
- a column of a table that can hold null;
- a column of a view that the view's query can fill with null;
- the optional side of a
LEFT,RIGHTorFULLjoin; - the optional side of an
OUTER APPLY; - an aggregate;
- the value of a subquery;
- a window function;
ROLLUP,CUBEandGROUPING SETS;- the
insertedcolumns of aMERGE'sOUTPUT, when theMERGEcan delete, since it leavesinsertedempty for the rows that it deletes; - the
deletedcolumns of aMERGE'sOUTPUT, when theMERGEcan insert, since it leavesdeletedempty for the rows that it inserts.
A MERGE that only updates leaves neither empty. So its inserted and deleted columns can be null only where the columns of their table can hold null.
SQL Server says that a column is never null when no outer join of the query can fill it with null, and it is one of these:
- a
NOT NULLcolumn of a table; - a column of a view that the view's query never fills with null;
- a constant other than
NULL; ISNULLwhose second argument is never null;CONCAT;- a
CASEor anIIFwhose every result is never null.
SqlSource also treats these as never null, where SQL Server does not, in a query that is one SELECT, with or without a WITH clause:
- A count:
COUNTandCOUNT_BIG, with or withoutOVER (...). A count is0for no rows, never null. - A subquery that is a count alone and returns one row, such as
(SELECT COUNT(*) FROM dbo.orders AS o WHERE o.customer_id = c.id), with noGROUP BY,HAVING,TOPorOFFSET. - A rank:
ROW_NUMBER(),RANK()andDENSE_RANK(), andNTILEof a whole number,NTILE(4), withOVER (...). SUM,MAXandMINof a value that is never null, in a query with aGROUP BYwhose every group has a row. AGROUP BYwithROLLUP,CUBEorGROUPING SETScounts too.SUM,MAXandMINof a value that is never null, withOVER (...)whose frame holds the current row. A window that gives no frame has such a frame.COALESCE,GREATESTandLEASTwith an argument that is never null, and no argument of a spatial type, ofhierarchyidor of another .NET type.CASTandCONVERTof a value that is never null to a built-in type, such asint,decimal(10, 2),nvarchar(max)ordatetime2. This holds when the type is written as SQL Server names it, and when the style ofCONVERT, if it has one, is a whole number written in the query.TRY_CASTandTRY_CONVERTcan be null.- Arithmetic of values that are never null:
+,-,*,/and%, as inSUM(quantity * unit_price). JSON_OBJECTandJSON_ARRAY, on SQL Server 2022 and later.- A computed column declared
PERSISTED NOT NULL, read from its table alone, when all of these are true:- the
FROMof the query names that one table and nothing else; - the column is an item of its own, or the query is
SELECT *from that table; - the query has no
ROLLUP,CUBEorGROUPING SETS.
- the
SqlSource treats none of these items as never null in two kinds of query:
- a query with
GROUP BY ALL, which returns a group with no row; - a query whose select list mixes
*with other items, such asSELECT *, COUNT(*) OVER () AS total.
What counts as a value that is never null in the items above:
- One of the items above.
- A value that SQL Server says is never null where the query reads it, such as a
NOT NULLcolumn that no outer join reaches. - An expression that holds an aggregate,
OVERor a function of your own counts only when it is one of the items above. - Under
ROLLUP,CUBEandGROUPING SETS, no column outside an aggregate is a value that is never null, whatever its table says. A column of theGROUP BYis null in the rows of a total. - A call that names a window of the
WINDOWclause,OVER w, is never one of the items above. - A function of your own, called with its schema,
dbo.f(x), is never one of the items above.
These answers assume that the connection that runs the query leaves ANSI_WARNINGS on, as Microsoft.Data.SqlClient does: see limits.
SqlSource treats these as columns that can be null, though some of them never are:
LAG(...)andLEAD(...)with a default.- Other functions of values that are never null, such as
EOMONTH,DATEADD,AT TIME ZONEandNEWID(). - A computed column that is not persisted, unless SQL Server can show from its expression that it is never null, as for
ISNULL(quantity, 0). - A computed column declared
PERSISTED NOT NULLand read through a join, a view, a subquery or aWITHquery, with the same exception. - A column of a recursive
WITHquery. COALESCEover a column of each side of aFULL JOIN, as inCOALESCE(i.id, p.invoice_id).- A
CASEwhoseELSEis the column that it tests for null,CASE WHEN x IS NULL THEN 0 ELSE x END. WriteISNULL(x, 0), which is never null. - A column that a filter keeps from being null. An example is a column that can hold null, under
WHERE x IS NOT NULL. Another is the optional side of aLEFT JOINthat theWHEREmakes inner. - A column of a query that is not one
SELECT. Such a query keeps the answer of SQL Server, so a count or a sum in it can be null. This holds for theOUTPUTof anINSERT,UPDATE,DELETEorMERGE, for aUNION,INTERSECTorEXCEPT, forFOR XMLorFOR JSON, and for several statements in one query. - A conversion to
geometry,geography,hierarchyidor another .NET type, since SQL Server can convert the text'NULL'to such a type as null:CAST(N'NULL' AS geometry)is null. COALESCE,GREATESTorLEASTover a value of such a type, for the same reason:COALESCE(shape, 'NULL')is null.- A column of a query that uses
windowas a name, an alias or a column without brackets, as inFROM dbo.panes window. Such a query keeps the answer of SQL Server. Write[window].
A column that can be null gets a nullable property in the query's output model, int? or string?. A column that is never null gets a property that cannot be null, int or string. Where you know better, you decide instead: a -- column: marker says that a column is never null, or can be null, and leaves its name alone. An alias that ends in ! or ? does the same.
-- name: ListOrderMonths
-- column: month_end not null
SELECT id, EOMONTH(placed_at) AS month_end
FROM dbo.orders;
If placed_at is NOT NULL, the last day of its month is never null, though SQL Server treats EOMONTH as a value that can be null. So the marker gives the property MonthEnd the type System.DateTime, and not System.DateTime?.
- The build does not check the marker. A query method throws if such a column is null after all.
- An alias
AS [month_end!]does the same without the marker. The mark is left out of the name of the property. It stays in the name of the column, which Dapper matches to the name of the property, so with Dapper write the marker.
See which properties can be null and the -- column: marker.
Limits
A property can be null though its column is never null
What you see. A column that is never null gets a property that can be null, such as int? where int would do. No value is wrong, but your code checks for a null that never comes.
Why. SQL Server treats a column as one that can be null when it cannot show that the column never is. SqlSource keeps its answer where it cannot show otherwise from the query. Among the columns that get such a property are:
EOMONTH,AT TIME ZONEand other functions of values that are never null;LAGandLEADwith a default;- the columns of a recursive
WITHquery; COALESCEover both sides of aFULL JOIN;- a computed column that is not persisted;
- a column that a filter keeps from being null;
- a count or a sum in an
OUTPUTclause or aUNION.
What to do. Do one of these:
- Give such a column a
-- column: name not nullmarker, as which columns can be null shows. - End the alias of the column with
!. - In the place of a
CASEthat testsxfor null, writeISNULL(x, 0).
Null from a column whose property cannot be null, with ANSI_WARNINGS off
What you see. A column whose property cannot be null holds a null when the query runs. It can be a count, a sum, a cast or a conversion, COALESCE, GREATEST, LEAST or arithmetic that SqlSource treats as never null.
Why. The connection turns ANSI_WARNINGS off. With ANSI_WARNINGS and ARITHABORT both off, SQL Server returns null in place of some errors: an arithmetic overflow, a value too large for its type, or a division by zero.
What to do. Leave ANSI_WARNINGS on, as Microsoft.Data.SqlClient turns it on when it connects. Run no SET ANSI_WARNINGS OFF on a connection that runs your queries.