Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stairway to Database Design STEP 6: Procedure Bodies Expand / Collapse
Author
Message
Posted Wednesday, November 3, 2010 4:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:20 PM
Points: 1,945, Visits: 2,999
Comments posted to this topic are about the item Stairway to Database Design STEP 6: Procedure Bodies

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1015112
Posted Saturday, April 30, 2011 2:31 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:01 PM
Points: 8,823, Visits: 9,381
Although on the whole goo, this article is bit sloppy in places, with some horrible uncorrected errors like this pseudocode
WHILE NOT EOF (bookstore)
DO BEGIN
IF price < 25.00
THEN UPDATE Books SET price = price * 1.10
ELSE UPDATE Books SET price = price * 0.85;
FETCH NEXT Bookstore;
END IF;
END WHILE;

which will not do what is claimed (it will repeatedly increase each price less than 25.00 until it is at least 25, instead of increasing it just once); and even if that is corrected by moving the FETCH statement to the right place it still has 1.10 and 0.85 where it should have 1.15 and 0.90 if the text describing the requirement is to be believed; and the requirement description doesn't say that any update is to be done when the original price is precisely 25.00, but both this pseudo-code and the supposedly correct SQL update the price in that case.
In addition, it is clear that the English text has never been subjected to the indignity of a spell-check.

These unfortunate errors detract greatly from the quality of what is in many ways a very good article; for example any article which contains "you could avoid all set oriented programming, treat each table like a file and code as if the database is a procedural file system. You could also drive nails into your body if you like pain." has a lot going for it; this is a statement that should be dinned into everyone learning SQL.

On the philosophical level I disagree with three of the articles contentions: one of them is probably a matter of elapsed time - the article was I imagine written a very long time ago, or surely it would not state that declarative programming is not yet at the stage where the theory is as helpful as the theory of structured programming.

The second philosophical bone of contention is that I have to disagree strongly that stored procedures exist only to support the monolithic programming style that existed in the bad old datys of file-based procedural programming. To me their main functions seems to be (i) to protect the schema from the molestations of idiots who write C++ or COBOL, have no understanding of the data model, and don't care if updates they make guarantee that things will stop working; and (ii) to limit access to the data much more severly than can be done with permissions only on tables, rows, and columns. these are essential functions to support the modularity and separation of concerns that are required for a layered approach. If a user program can access only some stored procedures, and neither read directly from nor write directly to tables or views, it is much less able to do undesirable things and much less able to rely on some unintentional quirk of the data that an application developer discovers before the database team has fixed it; indeed the application program can constrain the functionality of the database, but it can't constrain the schema other than in terms of the functionality expressed by the stored procedure interfaces - our database should be seen as an instance of an existential type instead of an instance of a concrete data type with openly visible structure, and the only way to ensure this with SQL is to stipulate that applications access it only through stored procedures. Some of the essential modularity that stored procedures achieve can be imposed with check constraints and referential integrity constraints, and some by thorough normalisation, but much of it can't (Fagin's DKNF is an impossible dream, nothing to do with normalisation despite its name, and this isn't a fault of SQL but something inherent in the relational model).

Thirdly, I can't agree completely with the statements about converting between representations; recently SQL added a new type: DateTime2. People may have old applications lying around that use DateTime as the interface format for dates if no conversion is done, and if DateTime is the interface format, no conversion in the database means that it's also teh storage format. Suppose I now decide to add to my database some data about things which occurred at dates that can't be expressed in the DateTime format? Do all those old applications have to be rewritten to use a new interface format, or fogo the new applications that need the extra date capacity? Often it will be better to provide convrsion in interface procedures (in fact on the principal of modularity I should never have allowed the application to constrain the storage format in the database in the first place - the interface representation should never have been the storage format, it should have been something reasonably universal, like ISO 8601, in the first place. And as this seems to be a sort of typing issue, I'll throw in the comment here that (a) for many scientific applications real and float are extremely useful (although I find it irritating that we don't have a 128-bit float in SQL) and (b) it's 15 years since last I had no floating point hardware in my desktop, and those desktops were all run of the mill commercial machines, not scientific workstations (maybe that statement in the article was also an artifact of its epoch?).


Tom
Post #1101230
Posted Wednesday, September 21, 2011 12:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 22, 2011 1:18 AM
Points: 6, Visits: 66
It seemed to me, as well, that the article was written a long time ago, particularly due to the anecdotes used and the talk about floating point processors. And I also have to voice disagreement with some of the guidelines.

First, SQL may not be intended for display, but the fact is that you should be using explicit typing a lot using cast to avoid math issues that result from implicit conversions. There are also times when you need to use a datetime, e.g. when you need to find the difference between two events in terms of days and time, but then you want to filter for a specific date from your application. There is nothing easier and cleaner than a quick convert(varchar(10),date,1) to handle this.

Second, the idea that stored procedures should be one screen max is ludicruous. I have pure SQL queries that take up several screens, much less the t-sql statements. Also, sometimes it is actually desirable to run the same sql multiple times instead of using joins. For example, if you are processing a multilevel bill of materials, there is a point at which using joins becomes as painful as driving nails into yourself and the process can be greatly simplified and performance actually increased by just running the same query multiple times against the the growing result table.

Finally, it seems to me that if you are keeping all of the functionality in the front-end, you are losing some of the benefits of multiple layers. Why would I put the code to explode a bill of materials in the front end, when if I code it in TSQL, when I develop a web-based interface for the customers to access (instead of the desktop interface for the internal employees), I can just worry about the interface development and don't have to redo the whole algorithm.

On the whole, I didn't feel that the article really deal with present day real-world design challenges or gave me any insight whatsoever the mysteries of stored procedures.
Post #1178460
Posted Wednesday, September 21, 2011 2:31 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 4:06 AM
Points: 988, Visits: 804
Some good points (like don't do presentation layer stuff in sql (!)) but sorry, I have to disagree with a whole lot in this article.

Stored procs are good at retrieving and manipulating data efficiently and more securely than running queries from the front end.

If you can limit your SPs to 30 lines of code you have a seriously simple data model, or maybe your presentation layer is trying to do back end stuff
Post #1178494
Posted Wednesday, September 21, 2011 3:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 23, 2011 2:36 AM
Points: 4, Visits: 27
I have to concur with some of the other criticism. Also I found it largely off-topic making a lot of it a waste of the reader's time, but then without the off-topic waffle there wouldn't be much left, which is a pity because there's definitely more to be said and a lot more to say about what has been said. For instance where's the example of using a series table in practice? I'm pretty sure I could work out what the writer is getting at, eventually, but I know people who couldn't.
Post #1178518
Posted Wednesday, September 21, 2011 8:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:20 PM
Points: 1,945, Visits: 2,999
I disagree about using the proprietary CONVERT( CHAR(10), something_datetime, 1) to get a string in United States dialect. I prefer CAST (something_datetime AS DATE) to get a temporal data type result in an ISO-8601 format.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1178687
Posted Wednesday, September 21, 2011 8:40 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
I appreciate what this article had to say. Even at the beginning of the article. I've been in my current position for 15 years (yeah, long time), and all those years ago some bad T-SQL practices were used. For example, a developer who used to be here (and thank God she isn't anymore) wrote a lot of T-SQL that would take numeric data and perform CAST statements, padding the result with so many blank spaces. She did this a lot. I wasn't sure why she did this, until years went by and we started to upgrade the software to other systems not based upon VB6. You see, back in the day, she had written several VB6 forms which would show the results of the queries she had written in T-SQL into things like listboxes, and she depended upon the T-SQL to format the display as it would appear in those listboxes in her VB6 programs. (And to ensure that would work, she had to use a fixed character format, because proportional would skew the appearance.) It's been one of my tasks, since she's left, to replace her tendency to use T-SQL to format displayed data, into just the data so that whatever was displaying it could display it appropriated for itself.

Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1178691
Posted Wednesday, September 21, 2011 8:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 22, 2011 1:18 AM
Points: 6, Visits: 66
I will consider that advice, thank you. To be honest, I'll have to look it up because I'm not even sure what you said. I have no academic background in database theory, I am just one of those people that make things like ERPs actually work for companies that have a handful of I.T. people., and write custom process automation software for them as well.

I don't know what a "temporal data type" is, and the only ISO I know is ISO9000, which is obviously not a character set. I use default collation, default everything else. But I never fail at making a system that works, has data integrity, and does exactly what someone wanted. Even if to save a few bucks the front end has to be done in MS Access with VBA. Whatever gets the job done. I use Postgres quite a bit too.

Post #1178713
Posted Wednesday, September 21, 2011 9:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 22, 2011 1:18 AM
Points: 6, Visits: 66
Old Hand - I dealt with a database like that too. Each table had two keys, the primary incrementing identity field, and then the company's code # field. e.g. ProductKey, ProductCode. All of the code fields, and ever table had them, were char types and the data was right aligned with spaces. I couldn't figure out why they did this and then they told me well, if you use alphanumeric codes, one with at least one letter, it left aligns them so the program uses this to determine if the field is alphanumeric or not.

???

What a royal pain in the butt it was to get that ERP integrated with other systems.
Post #1178726
Posted Wednesday, September 21, 2011 9:13 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:01 PM
Points: 8,823, Visits: 9,381
Today's revised article is an improvement on the original. Unfortunately, the false statement that we are further forward on formal proofs for structured procedural languages than for declarative languages is retained, as is the nonsense about avoiding inexact numerics. If I could rate it again I might give it a higher score than last time.

Tom
Post #1178752
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse