SQL Clone
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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1711 Visits: 3217
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
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73605 Visits: 40974
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
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63865 Visits: 17974
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 Modens 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)
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26231 Visits: 12506
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
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63865 Visits: 17974
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 Modens 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
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2961 Visits: 2128
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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1711 Visits: 3217
Thanks for the feedback.
I mainly wanted to make sure it wouldn't anger the SQL Gods (whoever they are) :-)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63865 Visits: 17974
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 Modens 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)
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26231 Visits: 12506
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
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63865 Visits: 17974
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 Modens 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