September 3, 2010 at 7:02 am
I inherited an SQL Server project and I noticed that the previous developer only places views inside his stored procedures. Is this considered best practice? He did this for the entire application. Is there an advantage to doing this?
September 3, 2010 at 7:45 am
I'm confused. You mean he only used views in queries inside of stored procedures? It sounds like you're saying he created views using stored procedures, which is weird.
In general, I'd say no, there's no benefit to using views inside stored procedures, unless your security is such that the people developing the procedures can't see tables but can only see the views.
Lots of views inside queries can lead to issues. When people have views that are complex sets of joins and then they start joining one view to another, the optimizer gets overwhelmed and you get some pretty nasty queries and bad performance.
"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
September 3, 2010 at 1:18 pm
That's right.
He only used views inside Stored Procedures.
For example, when I open one called uspSelCustomers, I see:
SELECT CustomerID, CustomerName
FROM dbo.vwCustomers
WHERE Active = 1
And the view is defines as:
SELECT CustomerID, CustomerName, Address, Phone
FROM dbo.tblCustomers
Other stored procedures are even more complicated. He does INNER JOINS between views like:
SELECT blah, blah
FROM dbo.vwView1 INNER JOIN dbovwView2 ON blah blah
WHERE blah blah...
There are dozens of stored procedures and dozens of views in the database.
It's the first time I see this. I'm no SQL Server expert but I do know about database design. I'm sure this is not how Micrososft intended a SQL Server database to be used.
By the way, the developers all have full access to the tables so they weren't obligated to use views only.
Thanks !
September 3, 2010 at 1:20 pm
Then it sounds messed up. I suspect you may have some poor performance in some areas because of this.
"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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply