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 123»»»

A New (and Hopefully Better) Approach to Constants Expand / Collapse
Author
Message
Posted Monday, October 15, 2007 10:11 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 1:54 PM
Points: 368, Visits: 1,940
Comments posted to this topic are about the item A New (and Hopefully Better) Approach to Constants




SQL# - http://www.SQLsharp.com/
Post #411110
Posted Tuesday, October 16, 2007 12:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 29, 2009 1:48 AM
Points: 1, Visits: 20
Why not declare a user defined function for each constant required? This will work in SQL Server 2000 as well.

For example:
CREATE FUNCTION Const_Address_Work()
RETURNS INT
AS
BEGIN
RETURN 1;
END;


Then simply use it like so:
SELECT addr.Line1, addr.Line2, addr.City, addr.State,
addr.Zip
FROM Addresses addr
WHERE addr.AddressType = dbo.Const_Address_Work()


This has the added benefit that you can then view dependencies for this specific function/constant to see what other source code refers to it. Very useful if a business rule change will affect code using that constant.
Post #411135
Posted Tuesday, October 16, 2007 12:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 4:59 PM
Points: 2,693, Visits: 1,204
The only real problem with this approach is that you have a lot of disk access as SQL Server might have to repetitively get the same value over and over again


If you've got disk access issues for what would be such a small table, then I'm sorry but you've got bigger problems. In reality this sort of table would reside almost permanently in data cache. Thus all requests would happen in memory and not result in disk access.

Personally, I'd go for the table and/or udf approach before installing an extra item on the server.




Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #411142
Posted Tuesday, October 16, 2007 2:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 3, 2010 5:43 AM
Points: 305, Visits: 134
There is a potentially big penalty to be paid with using functions in the maner you suggest.

The problem lies in the fact that functions are in-line by nature. This means that SQL Server will evaluate the function for each row in the table thus resulting in a full table scan.

Inorder to prevent this you will need to declare a variable that you can assign the value to, e.g

DECLARE @var

SET @var = dbo.udf()

SELECT * FROM sometable WHERE somefield = @var

Using

SELECT * FROM sometable WHERE somefield = dbo.udf()
will give you FTS
Post #411149
Posted Tuesday, October 16, 2007 2:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 4:59 PM
Points: 2,693, Visits: 1,204
I believe that by definition a function that is used to set/retrieve a constant should be used as you've shown.

Putting my mis-shaped, ill-fitting programmer hat on, isn't it standard practice to define constants at the start of a module/class/etc...

Ok, now I'll put my nice DBA hat back on. ;)



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #411156
Posted Tuesday, October 16, 2007 6:08 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Monday, September 15, 2014 9:16 AM
Points: 6,784, Visits: 1,895
I think at the method(stored proc) level defining constants as set @somevar=somevalue is easy to do, readable, and maintainable. I saw the earlier comment about module level constants and while not encouraged, they happen and often even make sense, and it's this area where SQL really has no option other than to table out the constants. Imagine that SQL had a built in CONST(constname) that worked in db scope, would we use it? I suspect some would, but in other cases the constant would probably be one more switch on some config table somewhere.

I guess - it being early and therefore being contrary - that I don't see what a .Net language does to help us in this case? Im not opposed to using the CLR where it fits, just not sure its valuable here.


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #411223
Posted Tuesday, October 16, 2007 6:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697
why why why ?????? SQL is set based why must developers constantly seek to overcomplicate things - you only need a lookup table.
I've encountered a software release ( on a major big system ) where the delopers sought to implement functions as constants to replace 12 simple values required in a lookup table. The result as pointed out earlier was to turn all the queries into row based ( cursor ) ops and actually made the whole project unworkable.
It probably looks fine on a laptop with a tiny data set but scale it up into production data sets and you're in for big trouble.
sorry.


The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #411225
Posted Tuesday, October 16, 2007 7:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:15 PM
Points: 1,945, Visits: 3,008
You can build a table of constants within a schema using Standard SQL like this:

CREATE TABLE Constants
(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; --resets table

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 Constants (pi, e, phi, ..)
AS VALUES (3.141592653, 2.718281828, 1.618033988, ..)

This avoids the overhead of CLR, the maintenance problems of CLR and the portability problems of CLR.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #411289
Posted Tuesday, October 16, 2007 9:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 11:06 AM
Points: 1,921, Visits: 396
The point made in a post above about using a scalar-valued function in the WHERE clause resulting in a full table scan is incorrect. When the function has no parameters or none of the parameters passed into the function come from columns in the table then SQL Server can evaluate the function once and use an index seek (assuming there is an appropriate index).

In the example below, the execution plans for queries in options 1 and 2 are the same, utilizing a clustered index seek. Option 3 (while admittedly silly) passes in a column from the table as a parameter to the function and thus requires a full table scan.

CREATE TABLE #Letter
(
Letter char(1) PRIMARY KEY NOT NULL,
Number int NOT NULL
)

INSERT #Letter
VALUES ('A', 1)

INSERT #Letter
VALUES ('B', 2)

INSERT #Letter
VALUES ('C', 3)

CREATE FUNCTION dbo.GetFirstLetter(@text varchar(100)) RETURNS CHAR(1)
AS
BEGIN
RETURN LEFT(@text, 1)
END

-- Option 1
DECLARE @firstLetter char(1)
SET @firstLetter = dbo.GetFirstLetter('Apple')
SELECT * FROM #Letter WHERE Letter = @firstLetter

-- Option 2
SELECT * FROM #Letter WHERE Letter = dbo.GetFirstLetter('Apple')

-- Option 3
SELECT * FROM #Letter WHERE dbo.GetFirstLetter(Letter) = 'A'

Post #411362
Posted Tuesday, October 16, 2007 10:25 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 15, 2012 10:26 AM
Points: 407, Visits: 27
Does the constant HAVE to be stored in a table??

Would it not be easier to just have the UDF return the value? (it is constant after all)

as in:

CREATE FUNCTION fnGetConst(@cWanted INT)
RETURNS VARCHAR(5)
AS
BEGIN
DECLARE @RetValVARCHAR(5)

SELECT @RetVal = case @cWanted when 1 THEN 'boo'
when 2 THEN 'hoo' END
return @RetVal
END
Post #411381
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse