Are null values allowed in any column of a table in SQL?
Allowing Null Values


  Columns defined with a PRIMARY KEY constraint or IDENTITY property
  cannot allow null values.
  
  The nullability of a column determines whether the rows in the table
  can contain a null value for that column. A null value, or NULL, is
  different from zero (0), blank, or a zero-length character string such
  as "". NULL means that no entry has been made. The presence of NULL
  typically implies that the value is either unknown or undefined.


Microsoft SQL Server's definition of NULL:


  A value of NULL indicates that the value is unknown. A value of NULL
  is different from an empty or zero value. No two null values are
  equal. Comparisons between two null values, or between a NULL and any
  other value, return unknown because the value of each NULL is unknown.
  
  Null values generally indicate data that is unknown, not applicable,
  or that the data will be added later. For example, a customer's middle
  initial may not be known at the time the customer places an order.


SET ANSI_NULLS setting can change the behavior of NULL.  In future versions this setting will be ON by default and you will not be able to turn it off.
Details here:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql

This is an article which go in depth about NULL.
https://www.simple-talk.com/sql/t-sql-programming/how-to-get-nulls-horribly-wrong-in-sql-server/

Few example of NULL in TSQL here.