Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Writing Nearly Codeless Apps: Part 3


Writing Nearly Codeless Apps: Part 3

Author
Message
David Ziffer
David Ziffer
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 203
Comments posted to this topic are about the item Writing Nearly Codeless Apps: Part 3
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8748 Visits: 11718
Hi David,

Thanks for the article (and the series).

I have one (minor) issue with this part. The audit table uses the primary key of the current-row table, plus a datatime column. The precision of datetime is 3/1000 of a second. This means that, on an extremely busy system, you run into the risk of getting duplicate rows errors when adding to the audit table.

On SQL Server 2008 and later, you could use DATETIME2(7), to get a precision of 100ns. That would reduce the chance of collisions, but not completely remove it.

I hardly ever recommend using IDENTITY, but this audit table would be a situation where I make an exception. Using IDENTITY to generate primary keys for this table is the only way to make 100% sure that there will not be any collisions.

Best, Hugo


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Cade Roux
Cade Roux
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 491
I have successfully used http://autoaudit.codeplex.com/ for triggers and I've been happy. The particular system I used it in wasn't what I would call a high volume OLTP environment, since the tables we managed were control tables for a dimensional data warehouse - so the majority of the big data was managed with a different paradigm. However, I certainly would recommend it and expect to use it in future products and am even looking for similar system for a more-OLTP-based Oracle project I'm working on now.
David Ziffer
David Ziffer
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 203
Hugo Kornelis (10/20/2010)

On SQL Server 2008 and later, you could use DATETIME2(7), to get a precision of 100ns. That would reduce the chance of collisions, but not completely remove it.

Thanks for this post Hugo. I will look into this and almost certainly make this change. RAP was originally developed under SQL Server 2005, which did not have DATETIME2. By the way RAP detects (and resolves) such collisions.

I hardly ever recommend using IDENTITY, but this audit table would be a situation where I make an exception. Using IDENTITY to generate primary keys for this table is the only way to make 100% sure that there will not be any collisions.

Even more importantly, using IDENTITY keys is the only way to actually preserve the identity of a row through an audit, since it is the only field guaranteed to remain forever invariant. The audit trail of a row using a business key as a primary key would be lost at every point where someone changes the business key.

But there are even larger benefits to using IDENTITY keys consistently. For example, RAP has a built-in ORM that manages all database/data-layer interactions. The complexity of this ORM is fantastically reduced because all keys in the system (including foreign keys) are the same. This is where the Henry Ford analogy starts kicking in. The benefits of consistency are rarely apparent at the component level, but startlingly clear at the system level.
jyates
jyates
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 186
Something else that we often forget is that, in order to get consistently good results, people using the system must be trained. As you stated, the Romans did this well.
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8748 Visits: 11718
David Ziffer (10/20/2010)
Hugo Kornelis (10/20/2010)

On SQL Server 2008 and later, you could use DATETIME2(7), to get a precision of 100ns. That would reduce the chance of collisions, but not completely remove it.

Thanks for this post Hugo. I will look into this and almost certainly make this change. RAP was originally developed under SQL Server 2005, which did not have DATETIME2. By the way RAP detects (and resolves) such collisions.

Don't forget that all datetime2 does is reduce the probability of collisions. It does not completely prevent them.


With respect to the IDENTITY column, I just want to stress that my comment was specific about the audit tables. I am not a big fan of IDENTITY in the tables that hold the actual data. Not because they are bad per say, but because they are almost always used in a bad way. I estimate that well over 90% of the people using IDENTITY columns make at least one, and usually two of the following two mistakes:
1) Using the IDENTITY column as the only key in the table, instead of also including and enforcing the actual "business" key.
2) Exposing the IDENTITY values to the end user, instead of keeping them internal to the application and using the actual "busness" key for data entry and reporting.

When accompanied by a UNIQUE constraint on the actual business key and not exposed to the end users, there is nothing wrong with using an IDENTIY column as a surrogate key.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
David Ziffer
David Ziffer
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 203
Hugo Kornelis (10/20/2010)
When accompanied by a UNIQUE constraint on the actual business key and not exposed to the end users, there is nothing wrong with using an IDENTIY column as a surrogate key.

Hugo: I wish I could always be working with people with your level of understanding.
GoofyGuy
GoofyGuy
SSC-Addicted
SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)

Group: General Forum Members
Points: 437 Visits: 971
David Ziffer quoted an article which stated:

The Romans created an army where systems, training, and supervision combined to create a world-conquering force ...

Systems fail when they cannot quickly adapt to changing circumstances. Remember Arminius.
David Ziffer
David Ziffer
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 203
Craig-315134 (10/20/2010)
David Ziffer quoted an article which stated:

The Romans created an army where systems, training, and supervision combined to create a world-conquering force ...

Systems fail when they cannot quickly adapt to changing circumstances. Remember Arminius.

Hmmmm ... I'm not sure what the implication is here, but I imagine it's that RAP would tie you down to a fixed structure that can't adapt. Like every other design it has its limitations, but actually by virtue of its extraordinary consistency a RAP-based design should be dramatically easier to modify than most hand-written apps. The consistency alone would lead the maintainer to be able to deduce the scope of the changes.

In a typical design, for example, a schema change to one table would require you to alter the table, then alter all the code in the database associated with that table, then alter the data layer to account for the change, and then finally adapt the business rules and UI to take advantage of the change. With RAP, you alter the table. Then RAP regenerates the database code and the data layer code for you, and the code is flawless. Finally of course you then have to modify the business rules and UI (as before) to take advantage of the change. The RAP cycle takes a lot less time and the code-generated portion is error free.

In my experience, which now spans over three decades, the apps that are the hardest to change are the hand-written ones with lots of inconsistencies. Once again I'll use the car analogy. Which is easier and cheaper to maintain and adapt .... a custom hand-built car, or one that came off an assembly line? I'll take the Honda Civic, thank you.
GoofyGuy
GoofyGuy
SSC-Addicted
SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)

Group: General Forum Members
Points: 437 Visits: 971
David Ziffer wrote:

In my experience, which now spans over three decades, the apps that are the hardest to change are the hand-written ones with lots of inconsistencies.

What a coincidence - I've been at this game well over three decades as well - but only a small part of it as a DBA, with a much longer stint in application development.

You're right, it's better to use libraries and built-in functions whenever and wherever possible, which explains the success of everything from .Net to JQuery. However ...

Once again I'll use the car analogy. Which is easier and cheaper to maintain and adapt .... a custom hand-built car, or one that came off an assembly line? I'll take the Honda Civic, thank you.

Once again? I must have missed something ... I'd thought you started and ended with the Roman Legions. But regardless: in my own experience, a Honda won't get you very far after the paved road (which, incidentally, was part of the Romans' system of domination) ends and the morass begins. I'll take a Range Rover, thank you.

Or, if you prefer a more recent historic analogy than the Roman Empire, look at the US/Viet-Nam war. The US certainly seemed far more systematic and technically adept than its foe - but it was scarcely as adaptable.

Arminius and Ho Chi Minh were both very good at stopping empires in their tracks, through adaptivity ... although I'm not sure either was much good at SQL.
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