Updates and not requiring a parameter


  • CREATE PROCEDURE [dbo].[Update_table]
      @TableId int,
      @StartDttm datetime = NULL,
     as
    UPDATE [dbo].[Update_table] 
    SET StartDttm = ISNULL(@StartDttm,[StartDttm])
    WHERE id =@TableId

    Above is just an example do not grade it on syntax and naming please. 😀
    If you have a table with 40 fields, and do this would it be a large impact on performance?  I know using ISNULL in the where clause causes scans, and issues.  I am not sure if in the SET it will do the same thing.  If it is a performance impact is there a better way to get the same objective. 

  • Nah, ISNULL() in a SET is fine.  It's only in WHERE and join-related clauses that it makes a difference.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thank you very much

  • In the SET my thinking is that the fields data is already in the SQL cache.  It would have to be because it is needed for any triggers also by the lookup from the ID.  Since it is in Cache it can simply swap the data.  Is that correct logic?

  • Effectively yes.  If you're updating a single row in a non-columnstore table, then the main row (from the clus index or from the heap) must be read in order to update it, so all columns will be in that buffer (to be technically correct: LOB columns may not be in-row, but you're not likely updating those anyway).

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • 2 methods I use a lot, depending on frequency:

    1. SET MyColumn = COALESCE(@paramTimestamp, GETUTCDATE()) ...
    and
    2. Use a table constraint on a DATETIME (or DATETIME2) column. For example: ALTER TABLE dbo.MyTable ADD CONSTRAINT [DF__MyDatabase_MyTable_MyColumn] DEFAULT (GETUTCDATE()) FOR MyColumn

    I generally put it on tables for tracking record insert/update/softDelete, using it at the SET level only for occasional use.

    Another way I define it at the schema level is in a column declaration, e.g. MyColumn DATETIME NOT NULL DEFAULT (GETUTCDATE())

  • I usually check for NULL with parameters and skip the update if there is nothing to change.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply