When you index a nullable field, are the rows with nulls stored in the index? It’s easy enough to find out by creating a table with a nullable field, and then creating an index on it:
CREATE TABLE dbo.Employees
ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
EmployeeName VARCHAR(50) NULL
INSERT INTO dbo.Employees(EmployeeName) VALUES (NULL);
CREATE INDEX IX_EmployeeName ON dbo.Employees (EmployeeName);
/* Then get the execution plan for this: */
WHERE EmployeeName IS NULL;
Yep, SQL Server stores the nulls in the index. That makes sense, really, because sooner or later, you’re going to want to find the rows with nulls, and when you’ve only got a few nulls in a big table, the index can help a lot there.
To learn more about this stuff in action, watch my free 90-minute course, How to Think Like the SQL Server Engine.