January 28, 2013 at 9:42 am
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
)
January 28, 2013 at 9:46 am
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
January 28, 2013 at 10:34 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 28, 2013 at 12:25 pm
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
January 28, 2013 at 12:39 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 28, 2013 at 1:03 pm
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[_]%'
January 28, 2013 at 3:11 pm
Thanks for the feedback.
I mainly wanted to make sure it wouldn't anger the SQL Gods (whoever they are) 🙂
January 29, 2013 at 8:07 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 29, 2013 at 8:40 am
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
January 29, 2013 at 8:51 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply