Writing Nearly Codeless Apps: Part 3

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by David Ziffer » Writing Nearly Codeless Apps: Part 3

19 posts, Page 1 of 2

1 2 »»

Writing Nearly Codeless Apps: Part 3

Rate Topic

Display Mode

Topic Options

Author

Message

David Ziffer

Posted Tuesday, October 19, 2010 8:01 PM

Valued Member

Group: General Forum Members
Last Login: Friday, February 15, 2013 9:16 PM
Points: 69, Visits: 176

Comments posted to this topic are about the item Writing Nearly Codeless Apps: Part 3

Post #1007418

Hugo Kornelis

Posted Wednesday, October 20, 2010 1:00 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:03 PM
Points: 5,244, Visits: 7,061

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

Post #1007487

Cade Roux

Posted Wednesday, October 20, 2010 1:53 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 9:10 PM
Points: 105, Visits: 481

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.

Post #1007504

David Ziffer

Posted Wednesday, October 20, 2010 7:29 AM

Valued Member

Group: General Forum Members
Last Login: Friday, February 15, 2013 9:16 PM
Points: 69, Visits: 176

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.

Post #1007723

jyates

Posted Wednesday, October 20, 2010 8:17 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, December 11, 2012 9:54 AM
Points: 8, Visits: 183

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.

Post #1007764

Hugo Kornelis

Posted Wednesday, October 20, 2010 8:28 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:03 PM
Points: 5,244, Visits: 7,061

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.

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

Post #1007778

David Ziffer

Posted Wednesday, October 20, 2010 8:59 AM

Valued Member

Group: General Forum Members
Last Login: Friday, February 15, 2013 9:16 PM
Points: 69, Visits: 176

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.

Post #1007799

Craig-315134

Posted Wednesday, October 20, 2010 9:15 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:53 AM
Points: 75, Visits: 285

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.

Post #1007810

David Ziffer

Posted Wednesday, October 20, 2010 3:17 PM

Valued Member

Group: General Forum Members
Last Login: Friday, February 15, 2013 9:16 PM
Points: 69, Visits: 176

Craig-315134 (10/20/2010)

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.

Post #1008062

Craig-315134

Posted Wednesday, October 20, 2010 3:39 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:53 AM
Points: 75, Visits: 285

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.

Post #1008077

« Prev Topic | Next Topic »

19 posts, Page 1 of 2

1 2 »»

Permissions