SQLServerCentral / Discuss Content Posted by Solomon Rutzky / Article Discussions / Article Discussions by Author / A New (and Hopefully Better) Approach to Constants / Latest Posts

RE: A New (and Hopefully Better) Approach to Constants

brianr — Wed, 13 Feb 2008 15:07:35 GMT

Be cautious. Not all of these approaches optimize out the same.We use an object factory that is tightly coupled to database tables. To simplify data access we have a single view that becomes in essence a partitioned table, called dbo.ObjectThis view looks something like this:create view dbo.Objectasselect 1 as objecttype_id, ID, Description from dbo.object1union all select 2 as objecttype_ID, ID, Description from dbo.object2union all select 3 as objecttype_ID, ID, Description from dbo.object3union all select 4 as objecttype_ID, ID, Description from dbo.object4Now to query against that view, you can simply predicate on an objecttype. If you check the IO statistics, it is only looking up against the one table, provided you query it properly. Don't bother looking at the Query Plan as it does not truly reflect what the server does to fetch the data.use this to check io stats:set statistics io on--this query works properlyselect * from dbo.object where objecttype_id = 2--this query does not, as it evaluates each table, but at least it doesn't scan them.declare @jobtype intset @objtype = objecttype.object1() -- a udf that returns a valueselect * from dbo.object where objecttype_id = @objtype --this query is closer, but it is creating a working table and is evaluating for every rowselect * from dbo.object where objecttype_id = objecttype.object1()--tried this, hoping that the deterministic decisions were based on the input param to output, but to no avail--results were same as above.select * from dbo.object where objecttype_id = objecttype.object1(0)--Same resultselect * from dbo.object where objecttype_id = objecttype.GetType('object1')--this is the worst, as it scans each tableselect * from dbo.objectwhere objecttype_id = (Select objecttype.object1())Joining on a settings table is an option, unfortunately as we may be doing multiple joins in tables requiring these constants, we would have to do multiple joins on the settings table, which turns out to be very inefficient.So until we have a better CONST struct in SQL we will be forced to hardcode values for some of our queries.

RE: A New (and Hopefully Better) Approach to Constants

Robinson-203984 — Sun, 10 Feb 2008 12:15:06 GMT

Sorry to necro this thread, but I was looking for alternative solutions and came across this. Thought I'd throw my 2p in.I've had this problem a lot recently. My solution was to create a single scalar UDF, with a CASE inside it, which I passed a constant name in to, ie: @myValue = dbo.Constant ( 'asset' )rather than the more verbose: SELECT @myValue = Constants.Value FROM Constants WHERE Constants.Name='asset'I don't like use of CLR for this to be honest and applying UDF in queries is imho a disaster for performance in many cases.

RE: A New (and Hopefully Better) Approach to Constants

JP-317675 — Sun, 21 Oct 2007 23:45:04 GMT

[quote]Jonathon, it is a little confusing as to whom you are speaking in that last paragraph since you mostly address the post by tymberwyld, although you mention "the article", which I wrote.[/quote]Yes I meant the post. My slip.

RE: A New (and Hopefully Better) Approach to Constants

Solomon Rutzky — Sun, 21 Oct 2007 23:39:53 GMT

[quote][b]Jonathon Prosper (10/21/2007)[/b][hr]On another note I do appreciate you sticking your head out and writing the article on what is a common database design issue. I'd hate to think that I had contributed to an environment where discussion was negative and not constructive. You have your opinion on your design and its merits and I gave a response. Let's leave it at that.[/quote][p]Jonathon, it is a little confusing as to whom you are speaking in that last paragraph since you mostly address the post by tymberwyld, although you mention "the article", which I wrote. Regardless, I think you touched on a subject that needs to be discussed, and that is the level of politeness and professionalism in these discussion forums. I do not mind that there are several responses to my article that very much disagree with what I was suggesting because, as you pointed out, everyone is entitled to their own opinion. However, I am a bit shocked at some of the pointedly rude, insulting, and down-right unprofessional comments made by a few people.[/p][p]There is absolutely NO REASON whatsoever for anyone in these forums to be rude to someone else. We aren’t talking politics here, and even if we were it should still be polite. We are all database professionals trying to learn from one another and these forums -- as a means of information and idea exchange -- are a great way for people to learn. But it is a shame when people who are new to this learn that they most likely shouldn't ask questions or share a solution of theirs out of fear of being put-down for it. If someone is knowledgeable enough to know a better way to approach the problem, then it is quite easy to simply say: I would approach it in this way as it has these merits. For example, Joe Celko's first response to my article did just that. He stated that it should be a one-row table, or even a view, and that the CLR poses certain problems. Ok, fine. But I am really bothered by Joe's response to the post by tymberwyld in which he copies the code only to point out, line by line, how much he doesn't like it and also calls it a nightmare and some of the worst code he ever saw. Personally, whether anyone feels that way or not, it is simply not acceptable on any level for someone in these forums to speak to anyone else in that manner and tone. There is just no good reason for it.[/p][p]Joe, given how well-known you are from writing various books on the subject and being widely regarded as a SQL expert, I would really think that you could use your wonderful insight into these technical issues to more positively teach others better way of doing things; yelling at someone and calling them stupid actually discredits both you and our profession by making us look like the stereo-typical techies that are lacking certain social skills. And once such an attitude comes out in these forums it is all too easy for others -- especially the one being yelled at -- to respond in kind and hence what was a productive discussion turns into immature insults. If someone posts code that appears hideous in your knowledgeable opinion, then rant and rave all you want in your mind, but when writing an actual response please be courteous. You have a lot of knowledge to impart upon others and it is great that you share that with us, but it is hard to listen to even a correct answer when the format the answer is given in is so harsh; I am not even the person being yelled at in this instance and I still don't want to take seriously any part of your response to tymberwyld, no matter how correct you may be, simply due to the tone and insults.[/p][p]But, I am not singling out Joe since I have seen several posts in these forums that are also insulting and that just has no place here. We are supposed to be helping each other out, not competing for "I am better than you" points. By all of us being respectful of each other we will create a much stronger and more productive community. :)[/p]

RE: A New (and Hopefully Better) Approach to Constants

JP-317675 — Sun, 21 Oct 2007 18:08:55 GMT

[quote]If there is a concern that the number of lookup tables will exponentially grow and you wont know where anything is....I guess that is a different topic.[quote]And hence forth, you know why I created this...[/quote][/quote]It would be good to have the simplicity of your single lookup table with a simple design back end. Perhaps wrapping many lookup tables in a single access point through a view is an option but like I said that is a different topic. If the design works for you then it works for you.On another note I do appreciate you sticking your head out and writing the article on what is a common database design issue. I'd hate to think that I had contributed to an environment where discussion was negative and not constructive. You have your opinion on your design and its merits and I gave a response. Let's leave it at that.

RE: A New (and Hopefully Better) Approach to Constants

tymberwyld — Fri, 19 Oct 2007 19:09:06 GMT

[quote]If there is a concern that the number of lookup tables will exponentially grow and you wont know where anything is....I guess that is a different topic.[/quote]And hence forth, you know why I created this...[quote][b]Joe Celko (10/19/2007)[/b][hr]Let me be brutal about this. I can see one -- and only ONE -- thing you have done right in this non-relational schema. This thing is so bad I want to use it in a book on bad SQL. [/quote]Wow guys, pretty brutal. No [b]constructive[/b] criticism? I used to have respect for you but obviously you just like to hear yourself talk. Apparently you think I am a naive database coder or something.Yes, I've read your article before and it basically has no merit. I've read ALL the pros and cons on this design! You made the whole thing way too complicated plus your little example table design was completely flawed from the beginning. No one in their right mind would use the design you mentioned. For one there's no Primary Key on it. I'm sure you didn't notice because you were too busy trying to think up witty quips, but I store the Primary Key of the values, not some Magical number.I only use these "reference" tables for simple lookup values (IDs & Descriptions). When the Lookup "Type" (i.e. AddressTypes) becomes too complex or merits additional schema, that is when I offload it into it's own table. Removing the current Foreign Key and a couple of update statements later, I can reapply the new F-Key to the new stand-alone lookup table. So, flexibility and future growth are accounted for.There are two sides to designing. The Logical design says "Hey we need 600 lookup tables!". The Physical design says "Umm, they're all the same schema and they only store IDs and Descriptions". It doesn't make sense to me to store 30 tables with an ID and Description. To me, designing databases is about storing the most data with the least bit of space. Even if the single lookup values table had many values, I've never seen mine grow above 500 rows. Too little for indexing to be effective but all the same I do set some indexes. It's also just right for pinning in memory. If this design becomes too large, then it's time to reevaluate and re-read the 1st paragraph. Just because you do not understand the design doesn't make it bad.[quote]You have no relational keys. Didn't you know that proprietary auto-numbering is NEVER a relational key by definition? All you have is an exposed physical locator, as if you were creating an index into a sequential file.[/quote]What are you yacking about "Proprietary numbering"? I didn't realize the IDENTITY function was no longer used!? In case you don't understand, the Constant column is ONLY used when necessary for the values. Taking AddressTypes for example, maybe one row's Constant would be 'MAIN' and another 'MAILING'. The ID should never matter to a programmer, T-SQL coder or anyone. Constants do not change but IDs may.I've used this design for about 3 years now and never had a problem with it. I was able to consolidate over 30 "lookup" tables. Yes, there are Foreign Keys. Every table that uses this as a Lookup HAS a Foreign Key pointing back to the Value ID. Obviously one flaw of this is that it's possible that one ID from another List ends up in your column, but how often have I had it beat into my head that "Business logic doesn't exist in the database". So, if you're that stupid of a programmer to give a user a drop-down of ALL the values instead of just those that belong in that column, then shame on you.[quote]Did you know that keeping audit data in a table is illegal? And that if it were not illegal, it is stupidly dangerous. Do you also keep the log file on the same hard disk as the DB? DUH! [/quote]Also, what crack pipe are you smoking, who mentioned anything about auditing? I manage all my auditing through a Service Broker which hands off the audit data to a separate database asynchronously so it doesn't block inserts / updates. The modified dates are used to ensure there are no concurrency conflicts when two users are attempting to update the same row. The trigger is in place in case some stupid programmer creates a Proc that doesn't specify the ModifiedDate.[quote]Do you have any idea why the BIT and BIT VARYING data types were removed from SQL? They are assembly language and are a sure sign of design flaws.[/quote]Oops, you got me on one thing! A Nullable Bit field! Overlooked on my part. However, bits are very useful and should not be counted out. I guess if you were king then all FLAGS in every programming language would be removed as well? What happens when I need to store multiple Statuses for my Application Members (ex. LockedOut but Active)? I suppose you'd tell me to add another bit column? And when that changes to another status that needs to be combined into the first two? Another column?[quote]Since the rest of the schema is soooo screwed up, you are trying to patch it with procedural code and you did it in the worst way. Use a trigger to replace a DEFAULT??? [/quote]Apparently you have no idea what the trigger is actually doing. AND Default values are only inserted for new records. If you didn't notice, the Trigger is AFTER UPDATE. Again, you love to talk before you read.I didn't just fall off the boat yesterday and I didn't wake up one day and think this would be a great idea. I've proven it's usefulness time and again over many years and I will continue to use it.I wonder, since this design is so non-relational in your book, how I would love to see your proposed solution to a GIS database.

RE: A New (and Hopefully Better) Approach to Constants

CELKO — Fri, 19 Oct 2007 15:07:05 GMT

Let me be brutal about this. I can see one -- and only ONE -- thing you have done right in this non-relational schema. This thing is so bad I want to use it in a book on bad SQL. You have no relational keys. Didn't you know that proprietary auto-numbering is NEVER a relational key by definition? All you have is an exposed physical locator, as if you were creating an index into a sequential file. You have an insanely complex and dangerous design. And you implemented this disaster in the worst code I have seen. Did you know that keeping audit data in a table is illegal? And that if it were not illegal, it is stupidly dangerous. Do you also keep the log file on the same hard disk as the DB? DUH! Do you have any idea why the BIT and BIT VARYING data types were removed from SQL? They are assembly language and are a sure sign of design flaws. Here is a simple attempt to make it readable with a try add ISO-11179 names. I also took the time to edit the DDL, which you did not. CREATE TABLE ReferenceLists(magical_physical_locator_id INTEGERIDENTITY(1,1) NOT NULL -- proprietary codePRIMARY KEY, -- non-key crap!!!foobar_name VARCHAR(50) NOT NULL, -- magic size!generic_constant VARCHAR(25),-- a varying length constant?? Wow!created_date DATETIME DEFAULT CURRENT_TIMESTAMP,-- audit datamodified_date DATETIME DEFAULT CURRENT_TIMESTAMP -- audit data);CREATE TABLE ReferenceValues -- metadata design flaw!!!(magical_physical_locator_id INTEGERIDENTITY(1,1) NOT NULL -- proprietary codePRIMARY KEY, -- no it is not!!list_id INTEGER NOT NULL -- the only thing you did rightREFERENCES ReferenceLists (magical_physical_locator_id),foobar_name VARCHAR(50) NOT NULL,foobar_abbreviation VARCHAR(50), -- magic size!foobar_constant VARCHAR(25) NULL, -- magic size!foobar_description VARCHAR(255) NULL, -- magic size !obsolete_flag BIT DEFAULT 0, -- stupid, non-SQL nullable bit flag!! created_date DATETIME DEFAULT CURRENT_TIMESTAMP, --audit datamodified_date DATETIME DEFAULT CURRENT_TIMESTAMP --audit data);Since the rest of the schema is soooo screwed up, you are trying to patch it with procedural code and you did it in the worst way. Use a trigger to replace a DEFAULT??? -- Proprietary trigger syntax!!! -- Hey, why port code or make it easy to read?? CREATE TRIGGER Trg_UpdateReferenceValue -- prefixes on schema names? ON ReferenceValuesAFTER UPDATEASBEGINSET NOCOUNT ON;--Non-standard SQL syntax!! UPDATE ReferenceValues SET modified_date = CURRENT_TIMESTAMPFROM ReferenceValues AS RINNER JOIN INSERTED AS ION (R.id = I.id)WHERE (I.modified_date IS NULL) -- audit data!SET NOCOUNT OFF;END;This nightmare should have been implemented as simple lookup table for each code. Google up this article for detail:[url]http://www.dbazine.com/ofinterest/oi-articles/celko22[/url]

RE: A New (and Hopefully Better) Approach to Constants

JP-317675 — Thu, 18 Oct 2007 18:41:09 GMT

Doesn't this overly complicate what should be a simple design.If you want to store address_types then create a table called address_types and store them in there. If you need a lookup table for phone number types then create a table called phone_types and store them in there. If you start mixing 'types' of things in the same table then you have changed the table from being an entity of one thing to being an entity of many things and make a joke of what a foreign key constraint is supposed to be used for.What happens in future when you want to add more detailed information for an adress type? You cannot simply add a column and insert the data because your table does not only contain address_types. On top of that if you did decide to create a new address_type table then making all the modifications to your original design would be time consuming and complicated.All foreign key constraints used in a mixed lookup table can never be relied upon to be 100% accurate. You can keep adding check constraints in every table that uses your one lookup table but this once again complicates what should be simple. I prefer the design of many different lookup tables each unique and unambiguous in their purpose. It allows for foreign key constraint checks to be 100% accurate (what could be more important than accurate data) while having the flexibility to expand the tables columns as you see fit. If there is a concern that the number of lookup tables will exponentially grow and you wont know where anything is....I guess that is a different topic.

RE: A New (and Hopefully Better) Approach to Constants

tymberwyld — Thu, 18 Oct 2007 08:05:41 GMT

I think I'm in the boat of not agreeing with this. Although I do like bleeding edge stuff being a developer AND a DBA, this just doesn't jive. I use constants in another way.All my Lookup tables are in one table (well, two if you count the parent table that is just there to categorize the values). The parent table ReferenceLists stores the distinct list of Lookup "Lists". The ReferenceValues stores all the values for each list. Each List has a constant and each value has a constant (optional). I use the Constant for the Lists all the time (which you'll see in the View Triggers). However, the Constants for the values are only used when needed. This strategy gives complete flexibility for adding new Lookup Lists where I can even allow end-users to create their own Lookups (in the case of a CRM application where users want to add their own User-Defined Fields and one of those Fields has specific values).[h3]Table Scripts[/h3][code]CREATE TABLE [dbo].[ReferenceLists]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, [Constant] [varchar](25) NULL, [CreatedDate] [datetime] NULL CONSTRAINT [DF_ReferenceLists_CreatedDate] DEFAULT (getdate()), [ModifiedDate] [datetime] NULL CONSTRAINT [DF_ReferenceLists_ModifiedDate] DEFAULT (getdate()), CONSTRAINT [PK_ReferenceLists] PRIMARY KEY CLUSTERED ([ID] ASC) ON [PRIMARY], CONSTRAINT [UIX_ReferenceList_Constant] UNIQUE NONCLUSTERED ([Constant] ASC) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [dbo].[ReferenceValues]( [ID] [int] IDENTITY(1,1) NOT NULL, [ListID] [int] NOT NULL, [Name] [varchar](50) NOT NULL, [Abbreviation] [varchar](50) NULL, [Constant] [varchar](25) NULL, [Description] [varchar](255) NULL, [Obsolete] [bit] NULL CONSTRAINT [DF_ReferenceValues_Obsolete] DEFAULT ((0)), [CreatedDate] [datetime] NULL CONSTRAINT [DF_ReferenceValues_CreatedDate] DEFAULT (getdate()), [ModifiedDate] [datetime] NULL CONSTRAINT [DF_ReferenceValues_ModifiedDate] DEFAULT (getdate()), CONSTRAINT [PK_ReferenceValues] PRIMARY KEY CLUSTERED ([ID] ASC) ON [PRIMARY]) ON [PRIMARY]GOCREATE TRIGGER [dbo].[trg_OnUpdateReferenceValue]ON [dbo].[ReferenceValues]AFTER UPDATEASBEGINSet NoCount On-- Update the ModifiedDate on any Rows where it wasn't specified explicitlyUpdate dbo.ReferenceValues Set ModifiedDate = GetDate()From dbo.ReferenceValues RInner Join inserted I ON (R.ID = I.ID)Where (I.ModifiedDate Is Null)Set NoCount OffENDGOALTER TABLE [dbo].[ReferenceValues] WITH CHECK ADD CONSTRAINT [FK_ReferenceLists_ReferenceValues] FOREIGN KEY([ListID]) REFERENCES [dbo].[ReferenceLists] ([ID])GO[/code][h3]Usage[/h3]Now, to make this easier on developers and me, I usually wrap views around these values for specific cases (although it could all be handled in code).Let's say we want to store Address Types in the database. So we create an Address table and put AddressTypeID on it. Next we add a Foreign Key to the ReferenceValues table's ID column. We could also specify a Check Constraint that the value must only be one where the ListID = 1 ('ADDRESS_TYPE'). In order to maintain this list of values for AddressTypes, we create a nice little view for it.Notice how the View doesn't expose the "ListID" column because it isn't necessary. This view should be used as a Table. So, the INSTEAD OF trigger looks up the ListID auto-magically (inserting it if neccessary).[h3]"AddressTypes" View Script[/h3][code]CREATE VIEW [dbo].[AddressTypes]WITH SCHEMABINDINGASSelect ID, Name, Abbreviation, Constant, Obsolete, CreatedDate, ModifiedDateFrom dbo.ReferenceValues WITH(NOLOCK)Where ListID IN( Select ID From dbo.ReferenceLists WITH(NOLOCK) Where Constant = 'ADDRESS_TYPE')GOCREATE TRIGGER [dbo].[trg_OnUpdateAddressType]ON [dbo].[AddressTypes]INSTEAD OF INSERTASSet NoCount On-- Determine the ListIDDeclare @ListID IntSelect @ListID = ID From dbo.ReferenceLists Where (Constant = 'ADDRESS_TYPE')If (@ListID Is Null) BEGIN Insert Into dbo.ReferenceLists (Name, Constant) Values('Address Types', 'ADDRESS_TYPE') Set @ListID = SCOPE_IDENTITY() END-- Insert the ValuesInsert Into dbo.ReferenceValues (ListID, Name, Abbreviation, Constant, Obsolete, CreatedDate, ModifiedDate)Select @ListID, Name, Abbreviation, Constant, Obsolete, IsNull(CreatedDate, GetDate()), IsNull(ModifiedDate, GetDate())From insertedSet NoCount OffGO[/code]You can also add other columns to the ReferenceLists table (ex. SequenceNo if you want to display values in a specific order).

RE: A New (and Hopefully Better) Approach to Constants

JP-317675 — Thu, 18 Oct 2007 01:53:25 GMT

I'd agree with post instructing everyone to hard-code the value into a variable at the top of your code although I would always do so by referencing a lookup table of some kind. Despite the fact that the value in the id column in your lookup table will never change you may find that your need to use that value in your code will. If you need to stop using that constant and replace it with another you need to be able to identify all your procedures/functions/views that use it.As an example, say a table that contains status information has the status "failed" split into three new values.Eg. Your original status FAILED (1) is split into FAILED - Pending (5) and FAILED - Notified (6) and FAILED - complete (7).Now you want to change all stored procedures that use the constant value of 1 and change it to 5 (Dont' ask me why you just do). How would you easily identify which of your stored procedures have used the old failed status of 1 if the only reference to the value in your stored procedures is a declaration followed a set statement of SET @Failed = 1?It's for that reason i'd advocate looking up the value in a lookup table within your code as much as possible. It is a simple thing to see which objects rely on other objects in your database if you refer to it within your code. If you simply hard code a value then well....good luck when you need to find it later.This ability to "find" where you have used constants could be achieved using functions but it is my preference not to do so.

RE: A New (and Hopefully Better) Approach to Constants

Ian Yates — Wed, 17 Oct 2007 20:42:12 GMT

I cannot see the benefit of invoking the CLR for such a trivial purpose. The ideas of a single row table/view with the constants as columns is probably best. Someone else has already rightly pointed out that a function that does not accept params and only has a simple return statement is easily handled by the SQL engine and doesn't result in table scans.The other big problem I saw with the bit of the article I read was the author's claim that the .NET hashtable equivalent stores and returns varchars, which can IMPLICITLY be converted to ints, etc.... When you compare your int data in a table with the varchar result of this hashtable, you may have your nicely indexex int column being implicitly converted to a varchar... yuk. :)

RE: A New (and Hopefully Better) Approach to Constants

colin.Leversuch-Roberts — Wed, 17 Oct 2007 14:37:52 GMT

constants don't belong in sql , set a view as a constant? god help us!

RE: A New (and Hopefully Better) Approach to Constants

RiK Muñoz — Wed, 17 Oct 2007 04:52:18 GMT

[font="Verdana"][size="2"]Well, I would have to agree that the article is a bit of a shameless plug in order to get us to download the cool stuff offa their site! But as far as I can see, the stuff I downloaded IS quite cool, so no harm done IMO, in fact saved me a bit of work! :P[/size][/font]

RE: A New (and Hopefully Better) Approach to Constants

Paul. — Wed, 17 Oct 2007 02:00:55 GMT

I would've thought a simple view would be OK for the previous example, and hard-code the "3" in it. This view can then be used everwhere, thus there should only be one hard-coded occurence of "3", (which is the minimum required - even constants hard-code the value in their definition!). Or am I missing something?As for all other "constants", why can't you define it anywhere you like? They'll all require the hard-coding of something. A function for its name, (plus any parameters if you follow the article's suggestions), whilst tables need their name and any identifying column/value, (depending if it only has one row/multiple columns, or two columns/multiple rows).Until SQL Server offers an actual "CONSTANT" keyword, then just use whatever's appropriate to your project, and stop moaning about everyone else's choices.Paul H

RE: A New (and Hopefully Better) Approach to Constants

CELKO — Tue, 16 Oct 2007 16:31:04 GMT

No, for several reasons:1) UDFs are proprietary and will not port.2) UDFs will not optimize -- how would you index them? 3) UDFs cannot be materialized and shared.

RE: A New (and Hopefully Better) Approach to Constants

noeld — Tue, 16 Oct 2007 15:17:30 GMT

[quote][b]Jobob Smith (10/16/2007)[/b][hr]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 BEGINDECLARE @RetValVARCHAR(5)SELECT @RetVal = case @cWanted when 1 THEN 'boo'when 2 THEN 'hoo' ENDreturn @RetValEND[/quote]In relational databases lookup tables are the way to go. Period. For several reasons:1. Flexibility2. The engine is optimized to handle tables ( use statistics of the values etc...)3. That is the way Relational databases are supposed to be handled.4. Totally agree that the table will remain in memory most of the time and no disk access is incurred!Cheers,

RE: A New (and Hopefully Better) Approach to Constants

Eric Wilson — Tue, 16 Oct 2007 10:32:50 GMT

This just shows how people will overly complicate a set-oriented or SQL based solution because they are trying to fit it into a world they are more comfortable with.And this article is just a shameless plug to go to the author's site to download his cool stuff.In compiled-programming we use constants so we don't have the magic #100 spread throughout the code. When the "max-cartons-per-palette" rule changes to 150, we don't want to find all the cases of "100" and try to figure out whether it was *that* 100 or some other 100 in order to safely change it to 150.Under the hood, the compiler turns "MAX_CARTONS" into the literal 100 for when the program actually executes.In SQL and table-oriented designs, there are 2 major differences.First, the code is interpreted (generally) and not compiled. Other posters have already pointed out the pitfalls with some of these "functions-for-constants" approach.Second, and more importantly, this is precisely what lookup tables were designed for-- to assign a simple constant to something bigger that could change, like a status-code-description.Far and away the most common "constant" situation I encounter are things like "status code 3 means 'failed'". So people start coding around it in all sorts of convoluted ways because they come from a programming background where they were taught that hard-coding a "3" in their code is EVIL and they will go to programmer hell for it. I saw one case where the programmer was doing:[quote]select ...from ...where StatusCode = (select StatusCode from LookupTable where description = 'failed')[/quote]Yikes!The problem is-- it's not the NUMBER that will change. It's the other parts. Anything *except* the number might change. "Failed" might become "Failure" or "Didn't Work". But in good lookup table design, the 3 will remain always and forever the surrogate to identify the underlying concept of a failed state.The lookup table provides a constant, never changing source of this fact and is always available for reference if needed. The 3 will never change.It would be NICE if T-SQL allowed more structured programming, like defining other modules that you can "include" and such. Basically if it were compiled. But for now it's not. And workarounds like this article proposes just make it more complex without adding value.The best solution I've seen is simply to work-around T-SQL's language by declaring constants at the top of a procedure:[quote]declare @STATUS_FAILED int; set @STATUS_FAILED = 3;... (later in code) ...select fieldfrom tablewhere Status = @STATUS_FAILED;[/quote]... Then use that variable (upper-case to make visually distinct from ones that can change during the procedure) in the rest of the code.We don't have to worry about ever changing the 3 to mean something else, so it isn't a case where having some named constant makes maintenance easier.An almost-as-good alternate is to just hard-code the numeric with a comment:[quote]SELECT ....FROM MyTableWHERE Status = 3 /*failed*/[/quote]Remember, for all the cases driven by lookup tables, it's the *number* (or whatever PK you used) that doesn't change.For other numeric constants like Int-Upper-Bound or Pi or something, I agree with the other poster about using a one-row table. I personally use an instead-of upd/del trigger to disallow changes to that table except for official rollout scripts we publish. But same idea.[b]To the author:[/b] By the way, please go back and read more on relational. Not only because of the nonsense about constants, but to also understand why identifying them by "Western" or "Region.Western" has nothing to do with relational theory at all. Start with Chris Date's stuff.

RE: A New (and Hopefully Better) Approach to Constants

Jobob Smith — Tue, 16 Oct 2007 10:25:50 GMT

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 BEGINDECLARE @RetValVARCHAR(5)SELECT @RetVal = case @cWanted when 1 THEN 'boo'when 2 THEN 'hoo' ENDreturn @RetValEND

RE: A New (and Hopefully Better) Approach to Constants

Matt Marston — Tue, 16 Oct 2007 09:32:45 GMT

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.[code]CREATE TABLE #Letter(Letter char(1) PRIMARY KEY NOT NULL,Number int NOT NULL)INSERT #LetterVALUES ('A', 1)INSERT #LetterVALUES ('B', 2)INSERT #LetterVALUES ('C', 3)CREATE FUNCTION dbo.GetFirstLetter(@text varchar(100)) RETURNS CHAR(1)ASBEGINRETURN LEFT(@text, 1)END-- Option 1DECLARE @firstLetter char(1)SET @firstLetter = dbo.GetFirstLetter('Apple')SELECT * FROM #Letter WHERE Letter = @firstLetter-- Option 2SELECT * FROM #Letter WHERE Letter = dbo.GetFirstLetter('Apple')-- Option 3SELECT * FROM #Letter WHERE dbo.GetFirstLetter(Letter) = 'A'[/code]

RE: A New (and Hopefully Better) Approach to Constants

CELKO — Tue, 16 Oct 2007 07:40:01 GMT

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 tableThe 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.

RE: A New (and Hopefully Better) Approach to Constants

colin.Leversuch-Roberts — Tue, 16 Oct 2007 06:10:10 GMT

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.

RE: A New (and Hopefully Better) Approach to Constants

Andy Warren — Tue, 16 Oct 2007 06:08:48 GMT

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.

RE: A New (and Hopefully Better) Approach to Constants

philcart — Tue, 16 Oct 2007 02:39:58 GMT

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. ;)

RE: A New (and Hopefully Better) Approach to Constants

Anders Bendix Nielsen-392963 — Tue, 16 Oct 2007 02:01:12 GMT

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.gDECLARE @var SET @var = dbo.udf()SELECT * FROM sometable WHERE somefield = @varUsing SELECT * FROM sometable WHERE somefield = dbo.udf() will give you FTS

RE: A New (and Hopefully Better) Approach to Constants

philcart — Tue, 16 Oct 2007 00:40:13 GMT

[quote]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[/quote]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.

RE: A New (and Hopefully Better) Approach to Constants

janderson-264275 — Tue, 16 Oct 2007 00:20:53 GMT

Why not declare a user defined function for each constant required? This will work in SQL Server 2000 as well.For example:[font="Courier New"]CREATE FUNCTION Const_Address_Work()RETURNS INTASBEGINRETURN 1;END;[/font]Then simply use it like so:[font="Courier New"]SELECT addr.Line1, addr.Line2, addr.City, addr.State,addr.ZipFROM Addresses addrWHERE addr.AddressType = dbo.Const_Address_Work()[/font]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.

A New (and Hopefully Better) Approach to Constants

Solomon Rutzky — Mon, 15 Oct 2007 22:11:12 GMT

Comments posted to this topic are about the item [B]A New (and Hopefully Better) Approach to Constants[/B]