Developer Pills : SQL Server

The Myth of Stored Procedures Preference

Adel Khalil — Sat, 05 Jan 2008 22:16:28 GMT

When looking to the Stored Proscedures debate, there is always those three factors you should measure by.

Productivity

- Span code base over multiple staging environments is a pain and harder to maintain.

- Versioning sp code is way harder than versioning application code.

- Minor change to the design require changing in both the SPs and the DAL code.

- Todays IDEs are more advanced than most of the RDBMS offers, implementing on IDEs is obviously preferable.

- Switching between two seprate world to implement single method is always pain.

- It's impossible to cover every single scenario and write SP for it, which will lead to write these SP as you go, huge consistency problem.

- There is no way to only update single param in the Update method using SPs as there isn't optional parameters, on every update you need to supply full param collection.

- SPs are not portable if you want to develop application that run over multiple DBMS you will be writing SPs for each DBMS, standard SQL is portable.

Security

- Big myth over here, using SPs not guarante best security practice and certinly dosn't mean that your application is SQL Injection proof you can write code like this

string s = "EXEC sp_GetCustomerByEmail '" + txtEmailAddress.Text + "'";

and you will be using SP and still open to all kind of SQL Injection.

- Another myth regarding security is that if you are using Ad-hoc queries you *most likely* grand permissions for CRUD operations for your application user on the database, no you are not, that's why Views are invented.

Performance

often when SPs vs. Ad-hoc queries debate intoduced the performance card played, SPs advocates says SPs are pre-compiled which is not let met quate like Frans did from SQL Server Books Online

SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans.

So with no pre-compilation and caching for both SPs and SQL statments there is no advantage for SPs here, in some other databases the SPs compiled into C or C++ but this isn't the case in SQL Server 7.0/2000.

I have introuduced my view on the SP vs. Dynamic SQL i don't see any benfit of SPs over the huge amount of productivity, performance that you will gain with dynamic SQL, the only benfit in peformance you will get it when using Managed SPs (SQL Server 2005) but for 0.7/2000 SPs isn't the right choice for most of the scenarios.

Database diagrams won't work in SQL Server 2005

Adel Khalil — Fri, 16 Mar 2007 03:45:00 GMT

I had this annoying error when trying to add new database diagram to SQL Server 2005 i received the error

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

And even when i tried what the error message suggests didn't work.. i Google the issue i found many others suffering from the same and even when i tried the solutions introduced and never worked for me also reseting the compatibility for the database one step back to SQL Server 2000.. this how this error resolved which is itried before via Managment Studio but didn't work only did from a SQL Quary.

EXEC sp_dbcmptlevel 'Dashboard', '90';

ALTER AUTHORIZATION ON DATABASE::Dashboard TO sa

So keep in mind if you ever ran into this.

Live from MDC 07

Adel Khalil — Sun, 04 Feb 2007 03:06:00 GMT

Hi, guys posting live from MDC 07 and just finish the seconed session.... so far theres two sessions presented by TOP IT and New Horizon delivering an overview of .NET Framework 3.0 as the first session and the one just finished talked about SQL Server OLTP.

stay tuned...for these topics resources

In your face - SQL Server 2005 security flaws since release = 0

Adel Khalil — Wed, 29 Nov 2006 09:02:00 GMT

Usualy Oracle known as the most secure database since decade but this truth is now changed in dramatic way.

The two graphs above show the number of security flaws in the Oracle and Microsoft database servers that have been discovered and fixed since December 2000 until November 2006.

theres a blog post here by Dennis van der Stelt raised this issue and you may read the original research here [PDF] .

i used to have a friend he is a Java developer and Oracle big fan.. and we used to have long conversations about whos is more secure, relaiable ..etc.. and now finally i can say to him IN YOUR FACE....

Now guys what do you think ? is Microsoft products finally getting better ?