August 21, 2012 at 5:15 am
To create an index on a view in SQL Server, the view definition must not contain any of the following:
...
Nondeterministic expressions
That will make it hard to implement my suggested optimisation as well, unless you periodically recreate the view with a different, constant cut-off datetime for the modified date. What a pity.
August 21, 2012 at 1:29 pm
CELKO,
Thanks for taking the time to read the article and to respond.
Your data Mendel is wrong and your are trying to fix it with convoluted code,.
This is quite true. Sometimes data models are wrong, and sometimes people aren't willing or able to redesign their data models. These are detestable people and deserve all the scorn that can be heaped upon them. What can I say? I'm an enabler. Some people give bums on the street a dollar. Not me. I go to the liquor store and buy them a bottle of their favorite gin.
Proper SQL does not use assembly language bit flags.
I kept getting syntax errors until I set the EVIL_ROTTEN_NO_GOOD_SQL flag to True.
All dates are kept in ISO-8601 display format.
This really threw me for a loop. My dates aren't in ISO-8601 display format, yet you say that all dates are in that format. At first I thought I really messed up and that what I thought were dates were really watermelon instead. But I did some digging around and found out that the previous DBA had set the ALTERNATE_TIME_SPACE_CONTINUUM flag to 'Watermelon'. This is yet another example for the need for documentation.
We have ANSI Standard CAST() and not the old Sybase CONVERT()any more.
As a founding member of the International Brotherhood of CONVERT() Users, I take umbrage with this statement. Sure, everyone thinks we're a bunch of Sybase holdovers, but in truth most of have suffered broken limbs, and CAST() simply brings back too many painful memories. People scoff at our cause, but our Super-PAC has successfully lobbied to keep CONVERT() in SQL Server for yet another major release.
IDENTITY is never a relational key by definition
I found this out the hard way. All my database tables used to have IDENTITY columns as the primary key. But then one night, I heard furious knocking on my door. In my half-sleep I opened the door and suddenly I was being beaten with rubber chickens by Dr. Codd and Chris Date. Boy was that embarrassing, and damned difficult to explain to the Mrs.
I assuemt hat was just a quickie and not anything you would do in produciton code.
Your generosity knows no bounds. Good luck recovering from your eye surgery. Several years ago when I had eye surgery I saw double vision for a brief time, but I fixed it up with an indexed view (so to speak).
-Dan
August 22, 2012 at 5:26 am
Celko's post provides a humbling lesson that we ought to look at the underlying cause of a problem and not immediately reach for the box of band-aids :blush:.
August 22, 2012 at 6:55 am
Steve in Fairfax (8/22/2012)
Celko's post provides a humbling lesson that we ought to look at the underlying cause of a problem and not immediately reach for the box of band-aids :blush:.
Actually, it looks to me like that post changed the semantics of the situation and introduced a different model, one that isn't even properly normalised anymore.
Another, more practical problem with looking for underlying causes and redesigning the system is that in many cases (in my experience) "somebody higher up the food-chain" has already signed off on the budget for a band-aid. So even the most valuable lessons do not eliminate the need for band-aids...
August 23, 2012 at 6:34 am
CELKO (8/21/2012)
<snip 3 table solution>
Using this solution:
The status of a region is held in the relationship table.
A region with no offices assigned to it will have no rows in the relationship table.
Then a region with no offices has no status.
I doubt this is correct given the semantics implied by the original model.
All dates are kept in ISO-8601 display format.
You are the last person I would expect to make the assertion that storage is dependent on presentation!
September 13, 2012 at 4:02 am
allmhuran (8/23/2012)
CELKO (8/21/2012)
<snip 3 table solution>Using this solution:
The status of a region is held in the relationship table.
A region with no offices assigned to it will have no rows in the relationship table.
Then a region with no offices has no status.
It also appears that if there are two region offices assignments in the third table, then one could have both office and region statuses active, whereas the other could have both office and region statuses inactive.
The constraint only enforces compatible statuses on a per-row basis, not across multiple rows.
Therefore a region can have multiple statuses at the same time! :crazy:
October 5, 2012 at 3:08 am
I am of the opinion this sort of logic should be implemented in some sort of foreign keys check constraint (never seen it being present in a relational database, but it would be neat and often needed).
That would be a natuaral and easy way to maintain integrity without the overhead of materialisation. It would just order the engine to enforce the check intergity rules across the foreign key relation.
The code for this would look something like:
alter table Office add constraint fkck_Office_Region
foreign key ( RegionId )
references Region( RegionId )
check ( Office.IsActive = 0 or Region.IsActive = 1 );
Likewise I would like to see confitional foreign key enforcement, based on contitions on the referencing table. A bit like a filtered index if you will, but again, without the explicit need for materialisation of anything.
October 13, 2012 at 12:30 pm
I'm working on a fairly large database where we have implemented data validation by a combination of unique indexes, check constraints, foreign keys and, where the validation is between data from more than one table, triggers*.
In terms of performance alone, this indexed view approach strikes me as probably being a better one than doing the validation in triggers. Does anybody have any experience of using this in a real system? Particularly with respect to comparisons with the trigger-based approach.
* We've not implemented check constraints which use UDFs for the cross-table validation as we believe performance could be a serious problem - won't this be a case of RBAR?
December 25, 2012 at 8:48 am
Good article.
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply