November 12, 2014 at 12:28 pm
Hi all,
A recent discussion has arisen with fellow teammates with varying opinions on this subject. So, I decided to pose the question to everyone here at SSC for more thoughts on the subject. As the above topic indicates, the question is "Where do you create a Stored Procedure?". There are two options being discussed:
1. In the database with the data
2. In the application database
For example, I have a database for my application (DB: MyApplication) and a database for some other data the application uses (DB: OtherData). Now, I have a business need to create a stored procedure to get data from the OtherData database only to be used in the application (it doesn't join to any other database). Do you put it in the MyApplication database with the rest of the DB code for the application? Or, do you put it in the OtherData database where the data exists?
Prior to this discussion, I personally have always put the Stored Procedure in the database where the data resides without giving it a second thought. Since having this discussion with fellow team members, I can now see pluses and minuses to both scenarios.
So, where do you create yours? I look forward to seeing your response and discussion.
Thanks,
John
::Edited grammar mistake
November 12, 2014 at 2:12 pm
Your question is not totally clear but I would do everything I could to minimize cross database requirements. What I mean is that I would not even consider creating a procedure on DatabaseA that only looks at data on DatabaseB. That kind of thing is an absolute nightmare to maintain. If you have lots of cross database queries then it might make sense to me to keep them all in one database or the other.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 12, 2014 at 2:48 pm
Agreed Sean. I prefer to keep stuff in the same database to avoid cross database joins, but only when the data is related to each other. Unfortunately, in some cases you can't avoid cross database joins. For example, if you store data in a HR database some of the other databases code may need to cross database join for employee info.
Some opinions are the stored procedure code for an application should reside with the application database even if the stored procedure doesn't refer to any objects in the application database.
November 12, 2014 at 3:08 pm
John Dempsey (11/12/2014)
Some opinions are the stored procedure code for an application should reside with the application database even if the stored procedure doesn't refer to any objects in the application database.
Everybody is entitled to their own opinion but that seems like a recipe for a massive kluster#$%@ to me!!!! I would say it sounds like there isn't enough separation between the data layer and the business layer if there are procedures for an application. IMO procedures are there to manipulate data which is caused by interaction of objects in the business layer. The procedures do not belong to the application, they instead belong to the data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 12, 2014 at 4:22 pm
Sean Lange (11/12/2014)
John Dempsey (11/12/2014)
Some opinions are the stored procedure code for an application should reside with the application database even if the stored procedure doesn't refer to any objects in the application database.Everybody is entitled to their own opinion but that seems like a recipe for a massive kluster#$%@ to me!!!! I would say it sounds like there isn't enough separation between the data layer and the business layer if there are procedures for an application. IMO procedures are there to manipulate data which is caused by interaction of objects in the business layer. The procedures do not belong to the application, they instead belong to the data.
Agreed. I create mine in the database which has the data for the reasons you stated.
November 13, 2014 at 4:51 am
Think of each database as a service. A given service has a contract that says for these inputs, you get these outputs. With that in mind, any given access mechanism for a database is stored with that database (until and unless we're talking ORM-style coding, different discussion entirely). It makes perfect sense from a coding standpoint and follows traditional coding best practices. In fact, that makes more sense than the other approach.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply