January 29, 2013 at 5:03 am
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
January 29, 2013 at 5:32 am
Easy one, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 29, 2013 at 5:37 am
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
January 29, 2013 at 5:48 am
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
January 29, 2013 at 6:02 am
Good question!
January 29, 2013 at 6:32 am
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
January 29, 2013 at 6:37 am
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"?
January 29, 2013 at 6:53 am
Thank you for the easy question.
January 29, 2013 at 7:31 am
Nice creative question.
I think that is the first time I've seen a bit column used as a primary key.
January 29, 2013 at 7:55 am
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
January 29, 2013 at 8:38 am
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:
January 29, 2013 at 8:58 am
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
January 29, 2013 at 9:11 am
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*
January 29, 2013 at 9:19 am
Sneaky... 😉 thanks!
January 29, 2013 at 9:41 am
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 41 total)
You must be logged in to reply to this topic. Login to reply