Any research on the effectiveness of T-SQL in Stored Procedures vs. T-SQL in SSIS components, SSRS data sets, and SQL Agent job steps?
Hi Guys
Is there any research published on the effectiveness of T-SQL in Stored Procedures vs. T-SQL in other places such as SSIS components, SSRS data sets, and SQL Agent job steps?
I'm at a new client site that has a ton of SQL Agent jobs, and 80% of the job steps contain T-SQL within those steps, as opposed to T-SQL that is in a stored procedure and the SQL Agent job step just executes that SP.
As far as I know the benefits of T-SQL within Stored Procedures are...
The only benefit of T-SQL in SQL Agent jobs as far as this client is that SP's are checked into TFS and requires a deployment with rigid rules, whereas SQL Agent jobs are not checked into TFS and changing them only requires a ticket.
What I could really use help with is anything that speaks to performance increases in general.
Thanks in advance.
Jim
Is there any research published on the effectiveness of T-SQL in Stored Procedures vs. T-SQL in other places such as SSIS components, SSRS data sets, and SQL Agent job steps?
I'm at a new client site that has a ton of SQL Agent jobs, and 80% of the job steps contain T-SQL within those steps, as opposed to T-SQL that is in a stored procedure and the SQL Agent job step just executes that SP.
As far as I know the benefits of T-SQL within Stored Procedures are...
- The ability to store an execution plan on the database
- Impact Analysis is easier as the code is in the database and not in hundreds of other files or non-SP's.
The only benefit of T-SQL in SQL Agent jobs as far as this client is that SP's are checked into TFS and requires a deployment with rigid rules, whereas SQL Agent jobs are not checked into TFS and changing them only requires a ticket.
What I could really use help with is anything that speaks to performance increases in general.
Thanks in advance.
Jim
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I guess the big benefit is for developers. If they manage to have only stored procedure calls in their applications, they can easily migrate a database from a DBMS to another one (for example, SQL Server to Oracle or to Sybase) without the need of changing the code. What they need it's only to change the SP code to the proper native SQL code of the target DBMS.
ASKER
My guess is at this client site SP's are on TFS and participate in the deployment process and all of the constraints that come with that, whereas SQL Agent (and embedded T-SQL) was a much simpler ticket to the DBA's, so the original developers went with the path of least resistance.
ASKER
Only response, so closing quesion and awarding points.
ASKER
I'll keep this question open for awhile in case there is more feedback, and will reward points accordingly.
Thanks.