locked
Why I prefer Stored Procedure rather than LINQ

    Question

  • I am a DBA + .Net developer. I got certified in SQL 2005 and .Net 1.1. I would like to share with you that the stored procedure offers much more functions than LINQ

    1. Use of Lock Hint. A common example will be inserting an unique email into the login table. Most people do the following:
    IF NOT EXISTS(SELECT * FROM Login WHERE EMAIL = @email)
    INSERT INTO LOGIN(email) SELECT @email

    the correct way to do is
    BEGIN TRAN
    SELECT * FROM Login WITH (UPDLOCK) WHERE EMAIL = @email
    IF @@ROWCOUNT=0
    INSERT INTO LOGIN(email) SELECT @email
    COMMIT


    The reason is to stop duplicate email to put into Login table. There are many other scenarios where LOCK hint is the ONLY solution. In the above example, an insert statment can be inserted after excuting the EXISTS but before INSERT.

    2. Control of deadlock free query in stored procedure. Writing deadlock free query requires the queries to be runnning in "specific order" between several tables

    3. Use of recursive SQL with CTE

    4. Use of SQL 2008 new data type such as hierachyID, spatial datatype

    5. You can assign a query plan to a specific query or specify query hint. It can solve the Parameter sniffing issue. In SQL 2008, the FORSEEK option helps a lot

    6 You want to do a SELECT sql using ORDER BY COLLATE the column by different collation in sql. This important especially working with 2 different db with 2 different collation. Especially in different language, the sorting order is different. Example: Try adventureworks
    SELECT LastName, FirstName FROM Person.Contact
    WHERE LastName LIKE 'R%'
    ORDER BY FirstName COLLATE Latin1_General_CI_AS DESC, LastName DESC


    For case sensitive search. This is commone as SQL server default the collation NOT TO BE CASE SENSITIVE. It is common for business rules to enforce case-sensitive search, especially in password search
    SELECT LastName, FirstName FROM Person.Contact
    WHERE LastName COLLATE Latin1_General_CS_AS LIKE 'r%'
    (NO RESULT DUE TO CASE SENSITVE)

    7, Update 800+rows at a same time using OPEN XML. I have an application updating payroll hours for 300+ employees. Use of OPEN XML took a split seconds, use of seperate insert statement took at least 3 seconds

    8 There has been complaints about paging millions of records using LINQ. In sql server, use of temp table and temp table variables in paging are very subtle. The performance differs from case to case

    9 Use of PIVOT table
    • Edited by benoityip Thursday, March 05, 2009 10:18 PM
    Tuesday, March 03, 2009 11:30 AM

Answers

All replies