Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Table with one row: Bad idea or not? Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 9:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 343, Visits: 1,062
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
)

Post #1412512
Posted Monday, January 28, 2013 9:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:58 AM
Points: 12,741, Visits: 31,045
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1412516
Posted Monday, January 28, 2013 10:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 11,935, Visits: 10,971
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.


_______________________________________________________________

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)
Post #1412549
Posted Monday, January 28, 2013 12:25 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 4:56 PM
Points: 8,271, Visits: 8,717
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.

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
Post #1412602
Posted Monday, January 28, 2013 12:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 11,935, Visits: 10,971
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.

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)
Post #1412608
Posted Monday, January 28, 2013 1:03 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:47 AM
Points: 110, Visits: 718
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[_]%'


Post #1412619
Posted Monday, January 28, 2013 3:11 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 343, Visits: 1,062
Thanks for the feedback.
I mainly wanted to make sure it wouldn't anger the SQL Gods (whoever they are)
Post #1412676
Posted Tuesday, January 29, 2013 8:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 11,935, Visits: 10,971
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)
Post #1413067
Posted Tuesday, January 29, 2013 8:40 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 4:56 PM
Points: 8,271, Visits: 8,717
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
Post #1413089
Posted Tuesday, January 29, 2013 8:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 11,935, Visits: 10,971
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)
Post #1413102
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse