July 30, 2008 at 7:57 am
Shaun McGuile (7/30/2008)
Jack Corbett (7/30/2008)
Michael Taylor (7/30/2008)
I would tend to disagree with this editorial in almost every way. The only real argument I saw as viable was the point about creating 4 sprocs per table. The problem here isn't SQL but the current set of technology tools. Imagine if the UI devs said they weren't going to use the designers because they didn't support some commonly used feature. We should be demanding that SQL add tools to auto-generate such boiler plate code.This is not hard to write yourself even using T-SQL. I have not done it for generic use in T-SQL, but have done it in VB.NET. But in reality MS should provide this in SSMS. It would be a simple to change to the Script Table As to add Insert SP, Delete SP, Update SP.
See first page of thread guys.....:D
??? Sorry, I read your post on the first page Shaun, but it does not really seem to address this. Can you quote it or provide a direct link?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 30, 2008 at 8:00 am
Bugger! 🙂 my Bad!
It was the second page.
Stewart Joslyn post of T-SQL.
Darth Barry - I couldn't write code like that without help, blood sweat, tears(someone else's) and the required ritual sacrifices! 😀
Hiding under a desk from SSIS Implemenation Work :crazy:
July 30, 2008 at 8:01 am
I guess I don't understand the security argument. We do not grant anything but execute access to stored procedures. No table, view or any other access. What part of that scenario is not secure?
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
July 30, 2008 at 8:01 am
Jack Corbett (7/30/2008)
Michael Taylor (7/30/2008)
I would tend to disagree with this editorial in almost every way. The only real argument I saw as viable was the point about creating 4 sprocs per table. The problem here isn't SQL but the current set of technology tools. Imagine if the UI devs said they weren't going to use the designers because they didn't support some commonly used feature. We should be demanding that SQL add tools to auto-generate such boiler plate code.This is not hard to write yourself even using T-SQL. I have not done it for generic use in T-SQL, but have done it in VB.NET. But in reality MS should provide this in SSMS. It would be a simple to change to the Script Table As to add Insert SP, Delete SP, Update SP.
I have done it with Dynamic SQL, Jack, it is not hard to do at all. Maintenance is easy too: after you modify the table, you just regenerate the sProcs.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 30, 2008 at 8:02 am
I have not yet read all of the comments but it seems that most of us are polarized to either extreme. I read into this editorial that we really need to evaluate why we are doing things in a particular way. The last application that I wrote used a combination of stored procedures as well as directly querying the database. For me, I looked at how complex each procedure was and then made a determination. I also did a little future projection to see where some things might change and others would remain stable. Finally, I had to balance how much work I thought the SQL Server should handle versus how much work the web server should be doing. This was definitely a learning moment for me since I had never developed a web application before this and was suddenly handed the assignment to make this work for a load of 100,000+ users (usually around 10,000 concurrent users).
In one particular example, the process was fairly complex (say 6 on a 10 point scale) in the initial development. At that point in time, I knew there would be changes to the back end so I intentionally put this into a stored procedure so the application itself would not need to be touched when the back end changes occurred. At that point, I simply made the necessary modifications to the SP and the application continued to work seamlessly. I dread to think what would have broken if I built this into the application since the complexity went to a 9 out of 10 with the new back end.
Typical thinking, as I have read, tells us that everything with the database needs to be handled within SQL Server itself for varying reasons. The primary application we use at my current employer is evidence of that thinking (nearly 1,200 SPs). I think if we move out of the conventional mold, we'll become better at developing applications that will use the technology in the best ways.
July 30, 2008 at 8:09 am
Michael Taylor (7/30/2008)
We should be demanding that SQL add tools to auto-generate such boiler plate code.
Microsoft is brilliant in this regard. I have long been saying that they are an 80% vendor. This leaves 20% for third party add on vendors. You want that boiler plating? Write a tool that inspects your database and adds the missing procedures. Look at it this way. If you change the way you want your procs coded then change the tool, drop all the procs, and re-run the tool. Make it robust and easy to use. Price it right and make a mint.
Imagine if the UI devs said they weren't going to use the designers because they didn't support some commonly used feature.
Umm. Sorry but I also work with a version of Basic where it is just as easy to write my UI in code as it is to use the designer. It's also dynamic. I change the max field width on a column and my code then sets the max number of characters you can type into the text box.
Even the Dot Net designers have bugs. We found one in the Mobile that fouls up a font parameter causing run time errors. We have to go in an fix the generate code every time the designer saves the form. :rolleyes:
ATBCharles Kincaid
July 30, 2008 at 8:11 am
Mark Harr (7/30/2008)
Regarding performance: I just don't see why or how these luddite DBA do not see that "dynamic SQL" and "stored procedures" are compiled exactly the same. ALL queries are precompiled - period. Then read the quote from BOL in the article: "When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles ...". The luddite DBA believes that the compile step occurs during the Create Proc execution. NO. Stored procedures are compiled when they are first executed. The compiled plan is then saved. For dynamic queries, it is exactly the same. They are compiled when first executed, then when the query is resubmitted (which it will be if the query is from an application), the saved plan is used. There is no difference.
To broadly generalize and call someone a luddite (one who is opposed to especially technological change - Mirriam-Webster Dictionary) because they take a stand on something or have not learned something is a a bit harsh. The caching of ad-hoc SQL statements was not done in earlier versions of SQL Server so while there may be ignorant DBA's out there I wouldn't call them luddites.
Colin Heming's example of encryption is a good example of the fallacy of using only stored procedures. What good is it to encrypt data when the only access to the data is sprocs that decrypt the data for you? Encrypting data HAS to include encrypting it from endpoint to endpoint to be effective at all. It must be encrypted once the data is entered, and not decrypted until it is needed to be used. And that is virtually always at the application level. If you decrypt in the stored procedure, then pass unencrypted data "over the wire" (though the connection", that is not securing your data.
I agree with you on this one for the reasons you specify. But, if you do give direct table access to your users then they can view data from other applications that do not include your encryption. So you may want to encrypt all your data using SQL encryption to obscure it from people who may be smart enough to use Access to get into the data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 30, 2008 at 8:14 am
From my perspective there are two other advantages to stored procedures that aren't addressed in this article because they are more process oriented than technical.
#1) When you have a team of developers working on a project and all the data access is through stored procedures, the team lead (usually me!) will have an easier time seeing how others are accessing the database and managing the quality of the queries being run. If they're writing all the SQL in the application code, it can be a nightmare to find it all and you often see many different routines doing the same action. If all they have to do to see if someone's already written a routine that does what they need is look at the list of procedures, there will be less duplication.
Seeing it all lined up like that also helps maintain standards.
#2) This is more of a developer efficiency issue, but I don't have to recompile my code when there is a bug in the SQL in the stored proc - I just fix the stored proc and keep going. On large projects with long build time, this really helps keep dev debugging time down.
There are always tradeoffs but these two things have really helped my projects in the past.
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant
July 30, 2008 at 8:16 am
By now it seems apparent that SQL is a religion.
I know that's a flame magnet 🙂
ATBCharles Kincaid
July 30, 2008 at 8:19 am
Burn Charles! Burn I say!
Hell hath no fury like a topic flamed!
😀
Hiding under a desk from SSIS Implemenation Work :crazy:
July 30, 2008 at 8:22 am
Ignore this posting, I am just subscribing to this thread. If anyone knows how to subscribe without making a dummy post, let me know. Regards
July 30, 2008 at 8:29 am
I've been the DBA and lead application developer at a Federal agency for 12 years, and I've seen a lot of different things touted as "best practice." Honestly, I don't remember hearing about using sprocs for all access to tables. I see the argument, but I don't agree with it entirely.
We typically use sprocs for more complex actions, like when a new application record may generate new records in several other tables, and has a number of values that have to be validated.
Regarding security, we grant users permission to the database, but no permission to any database objects. We assign them roles that the application uses to determine what forms/functions the user has permission to use. This way, the user MUST use our interface to access the data - they have no other options. For auditing, we do two different things. First, where the user is recorded in a record, we pass the user's login as a data element and save it like any other field. Second, we use an auditing package that records the logged user, even when that user account isn't the one being used to touch the data. We have to make a separate call to set up the user on the connection, so the auditing software knows about it, but we put that in the DAL and we don't have to worry about it after than.
Just my 2 cents.
July 30, 2008 at 8:32 am
Oh, and a thought on the security comments - there are two types of security (well, probably more but two I will distinguish here) - security from outside invaders and security of making sure your devs access the database the way they are supposed to.
If I have a rule that a certain table should only have "soft" deletes, then I will want to organize the database such that if a developer tries to do a hard delete on that table, they're going to get an error message saying that isn't permitted - which they then should know to come ask me about. Then I can point them to the stored proc which does the "soft" delete for them and the accompanying stored procs which know how to ignore soft-deleted records.
This would be darn near impossible to manage in the code without reviewing every line.
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant
July 30, 2008 at 8:38 am
J (7/30/2008)
Ignore this posting, I am just subscribing to this thread. If anyone knows how to subscribe without making a dummy post, let me know. Regards
On the top right hand side of the thread there is a link for "Topic Options" that lets you subscribe without posting.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 30, 2008 at 8:40 am
Whether you decide to spring for ORM such as NHibernate or stick with using stored procedures I like to use MyGeneration for this sort of thing. There are lots of downloadable templates, it's customizable and best of all it's free:
http://www.mygenerationsoftware.com/portal/default.aspx
To the point of 80% vendor 20% tools, I concur with this. There is a plethora of free and open source products which can give any project a jump start.
Viewing 15 posts - 46 through 60 (of 160 total)
You must be logged in to reply to this topic. Login to reply