Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table with one row: Bad idea or not?


Table with one row: Bad idea or not?

Author
Message
timwell
timwell
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1176 Visits: 2894
I was thinking of using a table with one row to keep configuration data and information about the server the database is hosted on.

Is there any reason not to do it this way? Is there a better way to do it?

It seems a major advantage to using a table and not (for example) items in the .config file is that stored procedures and queries could include information from the configuration directly without it having to be passed as parameters.

Here is my initial table def:

CREATE TABLE [dbo].[DataBaseInstanceInformation](
   [DbiId] [int] NOT NULL,
   [DbiDeptId] [int] NULL,
   [DbiIsRemoteDatabase] [tinyint] NULL,
   [DbiServerName] [varchar](255) NULL,
   [DbiComments] [varchar](max) NULL
)
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14942 Visits: 38936
in our case, we have a config file that has the connection string information, and a single row table that contains a lot of settings relevant to the application that uses that database.


I don't think you really want to store the connection info in the database, without also storing it outside of the database(how would you open teh conneciton to read the connection info) , but application type settings, you bet.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
Lowell (1/28/2013)
in our case, we have a config file that has the connection string information, and a single row table that contains a lot of settings relevant to the application that uses that database.


I don't think you really want to store the connection info in the database, without also storing it outside of the database(how would you open teh conneciton to read the connection info) , but application type settings, you bet.


I too have this type of setup in a number of applications. I have also added an instead of trigger to most of these applications to prevent adding/deleting. It can only have 1 row. :-D

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10700 Visits: 11991
Sean Lange (1/28/2013)
Lowell (1/28/2013)
in our case, we have a config file that has the connection string information, and a single row table that contains a lot of settings relevant to the application that uses that database.


I don't think you really want to store the connection info in the database, without also storing it outside of the database(how would you open teh conneciton to read the connection info) , but application type settings, you bet.


I too have this type of setup in a number of applications. I have also added an instead of trigger to most of these applications to prevent adding/deleting. It can only have 1 row. :-D

On single row tables, count me in - they are often useful; but rather than a trigger I use a check constraint on a column used as the primary key (checking that the value is 0). I know it seems crazy to bother with a primary key for a table that will have only one row, but I don't like tables without primary key and in conjunction with a check constraint it is a safe way to enforce the one row only requirement.

Tom

Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
L' Eomot Inversé (1/28/2013)
Sean Lange (1/28/2013)
Lowell (1/28/2013)
in our case, we have a config file that has the connection string information, and a single row table that contains a lot of settings relevant to the application that uses that database.


I don't think you really want to store the connection info in the database, without also storing it outside of the database(how would you open teh conneciton to read the connection info) , but application type settings, you bet.


I too have this type of setup in a number of applications. I have also added an instead of trigger to most of these applications to prevent adding/deleting. It can only have 1 row. :-D

On single row tables, count me in - they are often useful; but rather than a trigger I use a check constraint on a column used as the primary key (checking that the value is 0). I know it seems crazy to bother with a primary key for a table that will have only one row, but I don't like tables without primary key and in conjunction with a check constraint it is a safe way to enforce the one row only requirement.


It doesn't seem crazy at all...at least no more crazy than creating triggers on a single row table. I have never bothered with a primary key because it is a single row table. I like your approach. It is easier to create a check constraint. It far easier to make sure you get it right. Thanks for the idea Tom.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Bill Talada
Bill Talada
Old Hand
Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)

Group: General Forum Members
Points: 377 Visits: 1808
Personally I don't mind an overhead of 16k per table in the database. The table could be really wide with lots of columns to handle all your constants. I think I stole some ideas from Celko for my implementation.



drop table constants
go
CREATE TABLE Constants
(
lock CHAR(1) DEFAULT 'X' NOT NULL PRIMARY KEY CHECK (lock = 'X'),
smalldatetimeMin datetime DEFAULT '19000101' NOT NULL,
smalldatetimeMax datetime DEFAULT '20790606' NOT NULL,
datetimeMin datetime DEFAULT '17530101' NOT NULL,
datetimeMax datetime DEFAULT '99991231' NOT NULL,
bitMin bit DEFAULT 0 NOT NULL,
bitMax bit DEFAULT 1 NOT NULL,
tinyintMin tinyint DEFAULT 0 NOT NULL,
tinyintMax tinyint DEFAULT 255 NOT NULL,
smallintMin smallint DEFAULT -32768 NOT NULL,
smallintMax smallint DEFAULT 32767 NOT NULL,
intMin int DEFAULT -2147483648 NOT NULL,
intMax int DEFAULT 2147483647 NOT NULL,
bigintMin bigint DEFAULT -9223372036854775808 NOT NULL,
bigintMax bigint DEFAULT 9223372036854775807 NOT NULL,
smallmoneyMin smallmoney DEFAULT -214748.3648 NOT NULL,
smallmoneyMax smallmoney DEFAULT 214748.3647 NOT NULL,
moneyMin money DEFAULT -922337203685477.5808 NOT NULL,
moneyMax money DEFAULT 922337203685477.5807 NOT NULL,
realNegMin real DEFAULT -1.18E-38 NOT NULL,
realNegMax real DEFAULT - 3.40E+38 NOT NULL,
realPosMin real DEFAULT 1.18E-38 NOT NULL, -- or zero
realPosMax real DEFAULT 3.40E+38 NOT NULL,
floatNegMin float DEFAULT -2.23E-308 NOT NULL,
floatNegMax float DEFAULT -1.79E+308 NOT NULL,
floatPosMin float DEFAULT 2.23E-308 NOT NULL, -- or zero
floatPosMax float DEFAULT 1.79E+308 NOT NULL
);
go
INSERT INTO Constants DEFAULT VALUES; --resets table
go
select * from constants
go

------ OR ----------------
DECLARE
   @CONST_smalldatetimeMin smalldatetime = '19000101',
   @CONST_smalldatetimeMax smalldatetime = '20790606',
   @CONST_datetimeMin datetime = '17530101',
   @CONST_datetimeMax datetime = '99991231',
   @CONST_bitMin bit = 0,
   @CONST_bitMax bit = 1,
   @CONST_tinyintMin tinyint = 0,
   @CONST_tinyintMax tinyint = 255,
   @CONST_smallintMin smallint = -32768,
   @CONST_smallintMax smallint = 32767,
   @CONST_intMin int = -2147483648,
   @CONST_intMax int = 2147483647,
   @CONST_bigintMin bigint = -9223372036854775808,
   @CONST_bigintMax bigint = 9223372036854775807,
   @CONST_smallmoneyMin smallmoney = -214748.3648,
   @CONST_smallmoneyMax smallmoney = 214748.3647,
   @CONST_moneyMin money = -922337203685477.5808,
   @CONST_moneyMax money = 922337203685477.5807,
   @CONST_realNegMin real = -1.18E-38,
   @CONST_realNegMax real = - 3.40E+38,
   @CONST_realPosMin real = 1.18E-38, -- or zero
   @CONST_realPosMax real = 3.40E+38,
   @CONST_floatNegMin float = -2.23E-308,
   @CONST_floatNegMax float = -1.79E+308,
   @CONST_floatPosMin float = 2.23E-308, -- or zero
   @CONST_floatPosMax float = 1.79E+308;


-- select * from sys.syscomments where text like '%CONST[_]%'



timwell
timwell
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1176 Visits: 2894
Thanks for the feedback.
I mainly wanted to make sure it wouldn't anger the SQL Gods (whoever they are) :-)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
It seems that today's QOTD fits in nicely with this discussion. A very simple method was presented there using a constraint to allow a table to have only 1 row.


CREATE TABLE Test_Table (PK BIT PRIMARY KEY, Comment VARCHAR(10));

ALTER TABLE Test_Table ADD CONSTRAINT PK_check CHECK (PK <> 0);



That is about as simple as you can make it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10700 Visits: 11991
Sean Lange (1/29/2013)
It seems that today's QOTD fits in nicely with this discussion. A very simple method was presented there using a constraint to allow a table to have only 1 row.


CREATE TABLE Test_Table (PK BIT PRIMARY KEY, Comment VARCHAR(10));

ALTER TABLE Test_Table ADD CONSTRAINT PK_check CHECK (PK <> 0);



That is about as simple as you can make it.

I think
CREATE TABLE Test_Table (PK BIT PRIMARY KEY CHECK(PK <> 0), Comment VARCHAR(10));

does the same thing and is easier to read. Of course it doesn't provide a user-specified name for the check constraint, but ....

Tom

Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
L' Eomot Inversé (1/29/2013)
Sean Lange (1/29/2013)
It seems that today's QOTD fits in nicely with this discussion. A very simple method was presented there using a constraint to allow a table to have only 1 row.


CREATE TABLE Test_Table (PK BIT PRIMARY KEY, Comment VARCHAR(10));

ALTER TABLE Test_Table ADD CONSTRAINT PK_check CHECK (PK <> 0);



That is about as simple as you can make it.

I think
CREATE TABLE Test_Table (PK BIT PRIMARY KEY CHECK(PK <> 0), Comment VARCHAR(10));

does the same thing and is easier to read. Of course it doesn't provide a user-specified name for the check constraint, but ....


True, that is how I would write it if I were coding it myself. I just copy pasted from the question. :-)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search