InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 03:06:12 GMT20http://www.sqlservercentral.com/Forums/Topic1412512-373-1.aspx[quote][b]L' Eomot Inversé (1/29/2013)[/b][hr][quote][b]Sean Lange (1/29/2013)[/b][hr]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.[code]CREATE TABLE Test_Table (PK BIT PRIMARY KEY, Comment VARCHAR(10));ALTER TABLE Test_Table ADD CONSTRAINT PK_check CHECK (PK <> 0);[/code]That is about as simple as you can make it.[/quote]I think [CODE]CREATE TABLE Test_Table (PK BIT PRIMARY KEY CHECK(PK <> 0), Comment VARCHAR(10));[/CODE]does the same thing and is easier to read. Of course it doesn't provide a user-specified name for the check constraint, but ....[/quote]True, that is how I would write it if I were coding it myself. I just copy pasted from the question. :-)Tue, 29 Jan 2013 08:51:15 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1412512-373-1.aspx[quote][b]Sean Lange (1/29/2013)[/b][hr]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.[code]CREATE TABLE Test_Table (PK BIT PRIMARY KEY, Comment VARCHAR(10));ALTER TABLE Test_Table ADD CONSTRAINT PK_check CHECK (PK <> 0);[/code]That is about as simple as you can make it.[/quote]I think [CODE]CREATE TABLE Test_Table (PK BIT PRIMARY KEY CHECK(PK <> 0), Comment VARCHAR(10));[/CODE]does the same thing and is easier to read. Of course it doesn't provide a user-specified name for the check constraint, but ....Tue, 29 Jan 2013 08:40:10 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1412512-373-1.aspxIt 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.[code]CREATE TABLE Test_Table (PK BIT PRIMARY KEY, Comment VARCHAR(10));ALTER TABLE Test_Table ADD CONSTRAINT PK_check CHECK (PK <> 0);[/code]That is about as simple as you can make it.Tue, 29 Jan 2013 08:07:35 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1412512-373-1.aspxThanks for the feedback.I mainly wanted to make sure it wouldn't anger the SQL Gods (whoever they are) :-)Mon, 28 Jan 2013 15:11:54 GMTtimwellhttp://www.sqlservercentral.com/Forums/Topic1412512-373-1.aspxPersonally 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.[code="other"]drop table constantsgoCREATE 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 zerorealPosMax 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 zerofloatPosMax float DEFAULT 1.79E+308 NOT NULL);goINSERT INTO Constants DEFAULT VALUES; --resets tablegoselect * from constantsgo------ 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[_]%'[/code]Mon, 28 Jan 2013 13:03:49 GMTBill Taladahttp://www.sqlservercentral.com/Forums/Topic1412512-373-1.aspx[quote][b]L' Eomot Inversé (1/28/2013)[/b][hr][quote][b]Sean Lange (1/28/2013)[/b][hr][quote][b]Lowell (1/28/2013)[/b][hr]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.[/quote]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[/quote]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.[/quote]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.Mon, 28 Jan 2013 12:39:46 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1412512-373-1.aspx[quote][b]Sean Lange (1/28/2013)[/b][hr][quote][b]Lowell (1/28/2013)[/b][hr]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.[/quote]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[/quote]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.Mon, 28 Jan 2013 12:25:18 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1412512-373-1.aspx[quote][b]Lowell (1/28/2013)[/b][hr]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.[/quote]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. :-DMon, 28 Jan 2013 10:34:39 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1412512-373-1.aspxin 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.Mon, 28 Jan 2013 09:46:55 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1412512-373-1.aspxI 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)Mon, 28 Jan 2013 09:42:08 GMTtimwell