BIT Primary Key

  • Gotta love a table that will only ever accept one row. 😀

    Ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • Easy one, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • ronmoses (1/29/2013)


    Gotta love a table that will only ever accept one row. 😀

    Ron

    That can actually be quite useful. For instance for applications that do stuff on the current date, but need the ability to fake a date (e.g. because the nightly batch doesn't finish until after midnight, or because test scenarios require the emulation of a different rundate); or for applications that need values for specific settings that are database-wide. For that, you can use a table that is constrained to have only a single row. However, I would make it more self-descirbing than the example in this question.

    CREATE TABLE dbo.MySettings

    (DummyKey tinyint NOT NULL PRIMARY KEY DEFAULT(1),

    RunDate date NULL,

    OtherParameter varchar(20) NULL,

    CONSTRAINT OneRowOnly CHECK (DummyKey = 1)

    );

    INSERT INTO dbo.MySettings DEFAULT VALUES;

    go

    CREATE TRIGGER AlwaysOneRowInSettings

    ON dbo.MySettings AFTER DELETE

    AS

    ROLLBACK TRANSACTION;

    RAISERROR (N'Removing the settings is not allowed', 16, 1);

    go


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis (1/29/2013)


    That can actually be quite useful. For instance [...] for applications that need values for specific settings that are database-wide.

    Having used a single-row table for just such a purpose, I probably should have considered that. 😉

    Ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • Good question!

  • Good Question!

    Although the "conversion" of a number to either a 1 or a 0 bit - I did appreciate the "look at the most significant bit - if it's 1 the value is 1, if it's 0, then the value is 0." That may be one way to help explain the "conversion" of values into bits for someone who is struggling with the concept.

    J

  • jdamm (1/29/2013)


    Although the "conversion" of a number to either a 1 or a 0 bit - I did appreciate the "look at the most significant bit - if it's 1 the value is 1, if it's 0, then the value is 0." That may be one way to help explain the "conversion" of values into bits for someone who is struggling with the concept.

    You really think that explanation is easier to understand than "every non-zero value is converted into a 1"?


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Thank you for the easy question.

  • Nice creative question.

    I think that is the first time I've seen a bit column used as a primary key.

  • Good question. I liked that it required investigation or at least consideration of several matters: the operation of the CHECK constraint, the significance of the primary key declaration (especially that it implicitly makes the column non-nullable), and the behavior of implicit conversions of integers to the bit datatype.

    Oh, and thank you for including the DR0P TABLE statement. I always answer the question before running the QotD code, but I also run it sometimes to observe the behavior or modify it to test different scenarios. I don't like having to write the T-SQL myself to clean up after the QotD code in my sandbox databases.

    (And yes, I know I spelled DR0P TABLE with a zero instead of an "O". In posting on this forum, I have discovered the my employer seems to have some kind of security filter that blocks outgoing web traffic that includes certain SQL statements. If I spelled DR0P TABLE correctly, that filter would catch this post and return a message that my machine is unable to communicate with the server.)

    Jason Wolfkill

  • wolfkillj (1/29/2013)


    (...)

    (And yes, I know I spelled DR0P TABLE with a zero instead of an "O". (...))

    I thought you were just trying to be 1337. :hehe:

  • Primo Dang (1/29/2013)


    wolfkillj (1/29/2013)


    (...)

    (And yes, I know I spelled DR0P TABLE with a zero instead of an "O". (...))

    I thought you were just trying to be 1337. :hehe:

    If I were truly 1337, I'd be able to post whatever I want in spite of that filter. Unfortunately, my h4x0r skillz just don't measure up.

    Jason Wolfkill

  • wolfkillj (1/29/2013)


    (And yes, I know I spelled DR0P TABLE with a zero instead of an "O". In posting on this forum, I have discovered the my employer seems to have some kind of security filter that blocks outgoing web traffic that includes certain SQL statements. If I spelled DR0P TABLE correctly, that filter would catch this post and return a message that my machine is unable to communicate with the server.)

    *lol* This sounds like someone read the famous "Bobby Tables" cartoon[/url] - and then completely misunderstood it! *lol*


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Sneaky... 😉 thanks!

  • Hugo Kornelis (1/29/2013)


    ...you can use a table that is constrained to have only a single row. However, I would make it more self-descirbing than the example in this question.

    CREATE TABLE dbo.MySettings

    (DummyKey tinyint NOT NULL PRIMARY KEY DEFAULT(1),

    RunDate date NULL,

    OtherParameter varchar(20) NULL,

    CONSTRAINT OneRowOnly CHECK (DummyKey = 1)

    );

    INSERT INTO dbo.MySettings DEFAULT VALUES;

    go

    CREATE TRIGGER AlwaysOneRowInSettings

    ON dbo.MySettings AFTER DELETE

    AS

    ROLLBACK TRANSACTION;

    RAISERROR (N'Removing the settings is not allowed', 16, 1);

    go

    1. Good question and the concept of a one-row table is great.

    2. Hugo, thanks for supplying this code, it is also very useful

    3. But I also have to agree with Revenant, this is a bit sneaky.

    Thanks +1

    Not all gray hairs are Dinosaurs!

Viewing 15 posts - 16 through 30 (of 42 total)

You must be logged in to reply to this topic. Login to reply