Skip to main content

Posts

Showing posts from December, 2010

Practical Useage Of The Insert-Output Construct

I recently wanted to look into the possibility of using the insert-output construct and did some testing around it. I created two following procedures and executed them with IO, Time and profile ( SET STATISTICS IO, TIME , PROFILE ON ) 1.) One with the Insert-output construct                CREATE PROCEDURE sp_WithOutputCluse                @FirstName VARCHAR (30)               ,@LastName VARCHAR (30)                AS                 INSERT INTO HR.Staff ( FirstName, LastName )                OUTPUT INSERTED.StaffID, DATEADD (d,90, GETDATE ()), '90-Day Review'                INTO HR.Notification                (                   StaffID,                   NotificationDate,                   NotificationType                )                VALUES ( @FirstName,@LastName); 2.) One with the SET operation to accept the Scope_Identity()               CREATE PROCEDURE sp_WithOut_OutputCluse               @FirstName VARCHAR (30)              ,@LastNam

Why Should You Know Your Data

Today I read one great article on “Improving 2D range query performance" ( http://www.sql-server-performance.com/articles/per/2d_range_query_p1.aspx ). The article very clearly explained the reasons for the 2 D range query to perform poorly and what you need to do inorder to turn things around. I won’t go into the technical details, but just say that by tweaking the query, the performance changed significantly. You really need to read this article….. I definitely did gather some very good technical details on the subject, but what struck me most was the assumption that   was taken( where an events life time would be no more than 5 days). This assumption made the biggest difference in getting the query performance on the right track. Question As technical people do we ask the correct/sufficient questions from the business team?   Do we take the time to understand the data in our tables? Conclusion As Sql Server people(technical people) we know how to write queries to retrieve da