Anyone have a better alternative for constants?

SQL Server doesn't appear to have the concept of a global
or public constant. For instance, if LanguageID 1 equals
English or LanguageID 2 equals Spanish, you'd typically have
to hard code 1 or 2 in any procedure that checks this permission
first.
I've recently started using User-Defined Functions named
something like dbo.English or dbo.Spanish that do nothing
but return 1, 2, etc...
So far I haven't noticed any performance problems. Is
there a better alternative to this?
Robbe Morris - 2004/2005 Microsoft MVP C#
Earn money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.aspHave you tried asking this in the SQL-Server 2005 newsgroup?
Axel Dahmen
"Robbe Morris [C# MVP]" <info@.eggheadcafe.com> schrieb im Newsbeitrag
news:uhIlU2foFHA.3316@.tk2msftngp13.phx.gbl...
> SQL Server doesn't appear to have the concept of a global
> or public constant. For instance, if LanguageID 1 equals
> English or LanguageID 2 equals Spanish, you'd typically have
> to hard code 1 or 2 in any procedure that checks this permission
> first.
> I've recently started using User-Defined Functions named
> something like dbo.English or dbo.Spanish that do nothing
> but return 1, 2, etc...
> So far I haven't noticed any performance problems. Is
> there a better alternative to this?
> --
> Robbe Morris - 2004/2005 Microsoft MVP C#
> Earn money answering .NET Framework
> messageboard posts at EggHeadCafe.com.
> http://www.eggheadcafe.com/forums/merit.asp
>
>|||You can build a table of constants within a schema using Standard SQL
like this:
CREATE TABLE Constant
(lock CHAR(1) DEFAULT 'X' NOT NULL PRIMARY KEY
CHECK (lock = 'X'),
pi REAL DEFAULT 3.141592653 NOT NULL,
e REAL DEFAULT 2.718281828 NOT NULL,
phi REAL DEFAULT 1.618033988 NOT NULL,
.);
INSERT INTO Constants DEFAULT VALUES;
The insertion creates one row, so the table ought to have a singular
name. The "lock" column assures you that there is always only one row.
In full SQL-92, you can create such as table as a VIEW with a row
constructor:
CREATE VIEW Constant (pi, e, phi, ..)
AS VALUES (3.141592653, 2.718281828, 1.618033988, ..);|||I typically use a constants or properties table. If it's small and accessed
enough, it will always be in memory, and it's a heck of a lot easier to
change than a bunch of user-defined functions.
A
"Robbe Morris [C# MVP]" <info@.eggheadcafe.com> wrote in message
news:uhIlU2foFHA.3316@.tk2msftngp13.phx.gbl...
> SQL Server doesn't appear to have the concept of a global
> or public constant. For instance, if LanguageID 1 equals
> English or LanguageID 2 equals Spanish, you'd typically have
> to hard code 1 or 2 in any procedure that checks this permission
> first.
> I've recently started using User-Defined Functions named
> something like dbo.English or dbo.Spanish that do nothing
> but return 1, 2, etc...
> So far I haven't noticed any performance problems. Is
> there a better alternative to this?
> --
> Robbe Morris - 2004/2005 Microsoft MVP C#
> Earn money answering .NET Framework
> messageboard posts at EggHeadCafe.com.
> http://www.eggheadcafe.com/forums/merit.asp
>
>|||Actually this can be devastatng to performance if you use it in a FROM or
WHERE clause. It often gets called once per row, especially if you haven't
made sure that the function is deterministic: objectproperty(function_id,
'isDeterministic'). If you don't use them in a queries, It will be fine.
I would suggest you probably want to build small tables to do this sort of
thing and check the table values. Then you can use a function to get the
value if you just want one value, but you can also join to it for queries if
you need to check a value for each row.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Robbe Morris [C# MVP]" <info@.eggheadcafe.com> wrote in message
news:uhIlU2foFHA.3316@.tk2msftngp13.phx.gbl...
> SQL Server doesn't appear to have the concept of a global
> or public constant. For instance, if LanguageID 1 equals
> English or LanguageID 2 equals Spanish, you'd typically have
> to hard code 1 or 2 in any procedure that checks this permission
> first.
> I've recently started using User-Defined Functions named
> something like dbo.English or dbo.Spanish that do nothing
> but return 1, 2, etc...
> So far I haven't noticed any performance problems. Is
> there a better alternative to this?
> --
> Robbe Morris - 2004/2005 Microsoft MVP C#
> Earn money answering .NET Framework
> messageboard posts at EggHeadCafe.com.
> http://www.eggheadcafe.com/forums/merit.asp
>
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124159052.329343.274620@.g14g2000cwa.googlegroups.com...
> You can build a table of constants within a schema using Standard SQL
> like this:
> CREATE TABLE Constant
> (lock CHAR(1) DEFAULT 'X' NOT NULL PRIMARY KEY
> CHECK (lock = 'X'),
> pi REAL DEFAULT 3.141592653 NOT NULL,
> e REAL DEFAULT 2.718281828 NOT NULL,
> phi REAL DEFAULT 1.618033988 NOT NULL,
> ..);
> INSERT INTO Constants DEFAULT VALUES;
Constants = Constant|||Shock! Celko used a singular! Could it be the end of the...world? :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Chris Hohmann" <nospam@.thankyou.com> wrote in message
news:OyLoV7noFHA.3316@.TK2MSFTNGP14.phx.gbl...
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1124159052.329343.274620@.g14g2000cwa.googlegroups.com...
> Constants = Constant
>
>|||>> CREATE TABLE Constant
I don't like this approach because you have to alter the table when you have
a new constant.
The other approaches I like a little bit better:
(a) create a user-defined function (e.g. dbo.pi()) if it is going to be used
in a local variable but NOT in a where clause, e.g. where col <= dbo.pi()...
this is also useful if you have a set of constants that don't match in data
type.
(b) for a set of, e.g., NUMERIC constants:
CREATE TABLE dbo.NumericConstants
(
ConstantName VARCHAR(16) NOT NULL PRIMARY KEY,
Value NUMERIC(15,4)
)
This gives you great flexibility in that you can add and remove constraints
without affecting the table schema. But it ties you to a table per data
type.
(c) for a set of varying datatype constants, you can do this kind of kludge:
CREATE TABLE dbo.Constants
(
ConstantName VARCHAR(16) NOT NULL PRIMARY KEY,
NumericValue NUMERIC(15,4),
DateTimeValue DATETIME,
NVarcharValue NVARCHAR(255)
)
-- of course with a check constraint that only one can be NOT NULL
-- or add a datatype column
Or you can use SQL_VARIANT but this incurs all kinds of conversion costs
going in and out of the table.|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:O$jo4QooFHA.3696@.TK2MSFTNGP10.phx.gbl...
> Shock! Celko used a singular! Could it be the end of the...world? :)
Singular was appropriate in this instance, since the table only contains one
(1) row. I simply pointed out the typo for the benefit of those who are
cutting-and-pasting. :)|||Actually this is a really interesting question and I didn't realize (you
were right :)
Strangely though, in this case the row contains multiple independent values,
but in one single row. So constants (in this case) would be the more
appropriate name, since what is represented is in fact is multiple
constants. Hmm.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Chris Hohmann" <nospam@.thankyou.com> wrote in message
news:u3c9yqooFHA.3084@.TK2MSFTNGP09.phx.gbl...
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:O$jo4QooFHA.3696@.TK2MSFTNGP10.phx.gbl...
> Singular was appropriate in this instance, since the table only contains
> one (1) row. I simply pointed out the typo for the benefit of those who
> are cutting-and-pasting. :)
>sql