3

I use C# entity framework code first.

My knowledge regarding databases is almost nothing. I don't know if it's common or good practice so I wanted to ask you guys how I should do it.

Say I have a table of letters and a table of stations.

I develop an application that sends letters from one station to other stations. Each letter can be sent to different stations - it's a many to many relationship.

Each letter that is sent is associated with a station - the source station of the letter (a letter cannot be sent to it's own source station, etc..).

Each database instance represents a station.

On application startup I know which station is my source station and want to save that information in the database.

How should I save which station is the source station? Should I have a flag in the stations table in each row indicating if it is the current instance station? This sounds bad to me.

Is there a way to have a table with only 1 value? For example that will be called InstanceStation that will contain only one row with a single column - StationId? Is this a good practice?

I tried to be as clear as possible, I hope my situation is clear.

  • 1
    There's nothing wrong with a single-column single-row table (cf. Oracle's DUAL), however, from your explanation it sounds like you don't need to share this bit of information with other applications or clients, so why store it in the database in the first place? Can't you keep it as the application environment variable or something? – mustaccio Apr 6 '16 at 20:43
  • 1
    Never thought about setting this as an enviorment variable. Can you briefly explain why do you think ot fits as an eviorment variable? Won't it be easier to use it as a database record? – S. Peter Apr 6 '16 at 20:51
  • 1
    In my view, one should store in the database only the data that need to be persisted between application starts or something that needs to be shared between multiple application instances or different clients. From your vague description I understood that neither is the case. If only your running application instance cares about this piece of information, while it's running, this is its environment by definition. However, as I said, your description is too vague. – mustaccio Apr 6 '16 at 21:32
6

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:

enter image description here

And this, for the column that is not nullable:

enter image description here

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:

enter image description here

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:

enter image description here

3

Max Vernon's answer is correct in all but:

No need for NULL in a bit value. It should be non-null, default 0. Querying a field which could be NULL adds overhead to your writing/reading/code maintenance.

Basically the "correct" way to query a bit field is WHERE flag <> 0, if you might have NULL in the bit field you can still ask WHERE flag <> 0 (the engine ignores the NULL value). In order to keep the advantage of speed that Max Vernon suggests, the developer has to remember to update to NULL, not to 0. You need to remember to ask "WHERE flag IS NULL", not "WHERE flag = 0"

Another objection for "null" in a bit value is that in this case the field has 3 possible values (Null, 0 & 1). Also, if you're using Ms-Access in front end (which the user isn't doing) then you could get weird error message (there are problems in the ODBC connections). When you update the field it is update to 0, not to NULL; when you populate, you populate the field with the default of 0. It is a simpler process.

The overhead is for the developer to know what's the underlying set: what's the difference between flag IS NULL and flag= 0? it is simpler to write and simpler to read.

NULL means unknown. If you know the value, don't put NULL. It may be quicker but it is not bullet proof as far as a human is concerned.

  • I agree. Let's say you have a bit column bound to a GUI "checkbox", then unchecked = 0, and checked = 1. There is no unknown state - unless the database allows it. – Gary Apr 7 '16 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.