SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A New (and Hopefully Better) Approach to Constants


A New (and Hopefully Better) Approach to Constants

Author
Message
Solomon Rutzky
Solomon Rutzky
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2181 Visits: 2965
Comments posted to this topic are about the item A New (and Hopefully Better) Approach to Constants

SQL# - http://www.SQLsharp.com/
janderson-264275
janderson-264275
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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.
philcart
philcart
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5391 Visits: 1441
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
Anders Bendix Nielsen-392963
Anders Bendix Nielsen-392963
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 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
philcart
philcart
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5391 Visits: 1441
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. Wink

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
Andy Warren
Andy Warren
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: Moderators
Points: 15079 Visits: 2730
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
colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6359 Visits: 715
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/
Matt Marston
Matt Marston
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2001 Visits: 412
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'


Jobob Smith
Jobob Smith
SSC-Addicted
SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)

Group: General Forum Members
Points: 413 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search