February 7, 2019 at 3:59 pm
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.
February 7, 2019 at 4:04 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
February 7, 2019 at 4:12 pm
Thank you very much
February 7, 2019 at 4:15 pm
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?
February 8, 2019 at 9:00 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
February 11, 2019 at 12:04 pm
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())
February 11, 2019 at 2:54 pm
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