how to use sql, is the major question. which situations i should consider for using sql front end or back end.
asked
using sql from front end or back end, which one is better??
can you rephrase the question please? - what do you call the front/back end. Are you referring to using the SSMS GUI vs T-SQL?
my question is if i am using java as front end then all i have to do with sql is create database with sql and all other searching and all i am doing with java code. which is batter sql searchig or searching using java code ?? this is just example on my question.
You mean, do you use stored procedures or client-side dynamic SQL?
Short answer, stored procedures.
Longer answer, it kind of depends. If you're working with an ORM product that creates good paramaterized SQL statements (and by good, I mean statements that will create a single query plan, not X number of plans like Hibernate does), then a well-structured paramaterized query is effectively the same thing as a stored procedure, so this is OK too. If you're just building SQL statements within the Java code and tossing them over the wall at the SQL Server instance, no, that's a problem for any number of reasons, not the least being a horrible thing called SQL Injection. There is a lot more subtlety & nuance that can be applied, but in general, this is the advice I give my own development teams.
I agree with everything Grant says, as usual. But I think a couple of those nuances he mentioned are worth elaborating on.
First, remember that ORM's are generally harder to optimize and troubleshoot than a stored procedure. This is not to say that ORMs are not great under certain circumstances, but that you should be very aware of the trade offs involved. There is some discussion of this at: http://ask.sqlservercentral.com/questions/1094/orms-from-a-dbas-perspective
Next, I do think there are some rare occassions where it makes sense to write code that directly sends SQL to the SQL Instance. I do this for instance for a few scripts where it is generating dynamic SQL based on sophisticated string parsing that is hard to achieve inside of SQL. But I do this rarely and only on programs that are meant to be run by the DBA team. These will break horribly and intentionally if run by someone without a high level of permissions. And again, while I do see some cases where it makes sense to encode SQL like that they are the rare exception.
As a general rule, I once again agree with Grant that stored procedures are the way to go most of the time and that ORMs are good in some situations. I would in particular very strongly advise avoiding encoded SQL on any program that is end user facing and especially if that program runs with higher permissions than the user has natively.
Excellent points. I used ORM's as my hanging point, but you could just be talking about any data access layer and the issues & rewards would be the same.
Bottom line - SQL is designed to do the heavy lifting, let it do its job.
