SQL Server – Creating a Table with a Single Constant Value

Having a flag on the Stations table that indicates which station is local would be exactly how I'd handle this, presuming you don't have a billion stations. I'd likely call the column IsSourceStation or something, and make it a BIT value, that can accept NULL. I would mark the local station row as 1, and leave all other rows as NULL, since that won't take any space (see my comments below regarding space).

I'd add a filtered index to the IsSourceStation column, filtered as WHERE IsSourceStation = 1. This index will allow extremely fast lookups to determine the name of the local station, if that is required.

Looking for the Stations row that corresponds to our "home" station could be accomplished by:

SELECT *
FROM Stations
WHERE IsSourceStation = 1;

This will be very fast with the index I suggested, regardless of how many rows are in the Stations table.

Looking to confirm a station is not the home station? Use this:

IF EXISTS (
    SELECT 1
    FROM Stations
    WHERE IsSourceStation IS NULL
        AND StationID = 1234
    )
BEGIN
    -- StationID 1234 is NOT the home station
END

The null bitmap used in SQL Server is a fantastic optimization designed for just this type of situation where very few rows in a nullable column actually contain a value.

My statement above, while technically correct in that the null bitmap is used to save space, in the case of a table with a single bit column, there is no appreciable difference between defining the column as nullable vs having it be not nullable, with a default value of 0. I used the following test bed to determine this on SQL Server 2012:

USE tempdb;
IF EXISTS (SELECT 1 FROM sys.tables t WHERE t.name = 'TestBit')
DROP TABLE dbo.TestBit;
IF EXISTS (SELECT 1 FROM sys.tables t WHERE t.name = 'TestBitNotNull')
DROP TABLE dbo.TestBitNotNull;

CREATE TABLE dbo.TestBit
(
    TestBitID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , IsBit BIT NULL
);

INSERT INTO dbo.TestBit (IsBit)
SELECT TOP(1000000) NULL
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3
    , sys.objects o4;


CREATE TABLE dbo.TestBitNotNull
(
    TestBitID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , IsBit BIT NOT NULL
        CONSTRAINT DF_TestBitNotNull
        DEFAULT ((0))
);

INSERT INTO dbo.TestBitNotNull(IsBit)
SELECT TOP(1000000) 0
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3
    , sys.objects o4;

The above code creates two tables, each with a single INT column, and a single BIT column. The first table allows the BIT column to be NULL; the second table does not.

I used the following to inspect the actual on-disk data pages for the first page of each table:

SELECT TOP(10) *
    , %%PHYSLOC%%
FROM dbo.TestBit
    CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%);

SELECT TOP(10) *
    , %%PHYSLOC%%
FROM dbo.TestBitNotNull
    CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%);

The DBCC PAGE command can be used with the last option set to "3" to see the actual column values stored on the page, along with quite a bit of detail about each row "slot". In my two tables above, the first page of each table was 334 and 342 respectively.

DBCC PAGE (2, 1, 334, 3) WITH TABLERESULTS;

DBCC PAGE (2, 1, 342, 3) WITH TABLERESULTS;

The output for slot 0 from each of the DBCC PAGE commands above shows the following for the table with the nullable column:

And this, for the column that is not nullable:

The "memory dump" value in the 2nd column on the first row shows the actual data stored on disk in hex format; both variants are precisely the same.

Indeed, when looking at the on-disk size for both tables using this query:

SELECT o.name
    , i.name
    , p.partition_number
    , p.rows
    , UsedMB = au.used_pages / 8192E0
    , TotalMB = au.total_pages / 8192E0
    , AvgRowsPerPage = p.rows / CONVERT(DECIMAL(10,2), au.used_pages) 
FROM sys.allocation_units au WITH (NOLOCK)
    INNER JOIN sys.partitions p WITH (NOLOCK) ON ((au.type = 1 OR au.type = 3) AND au.container_id = p.hobt_id) OR (au.type = 2 AND au.container_id = p.partition_id)
    INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
    INNER JOIN sys.objects o WITH (NOLOCK) ON p.object_id = o.object_id
    INNER JOIN sys.schemas s WITH (NOLOCK) ON o.schema_id = s.schema_id
WHERE o.name = 'TestBit'
    OR o.name = 'TestBitNotNull'
ORDER BY o.name;

We see both tables are identically sized:

My conclusion in light of the above data is that it is probably easier just to use a non-nullable column with a default value of 0 since that eliminates the potentially problematic null handling required for nullable columns.

Where the null bitmap does help is when you have more than 8 nullable bit fields. If you take my sample tables TestBit and TestBitNotNull and give them 16 bit fields each, you'll see the following table sizes for 1,000,000 rows: