How to update a column in a SQL Server table with millions of rows when its column "createdDate" passes 90 days from now? Can we use trigger?

Question

I have a table with millions of rows. I have a column which should be set to true 90 days after the record was created. I have a createdDate column to track the created date. How to handle this for large tables?

Use an event that runs and checks the dates once every day (at a time with little DB activity) — juergen d, Dec 27 '16 at 05:51
What is the point of setting another column to true when you already have the CreatedDate in the table? Just use the CreatedDate instead. — nvogel, Dec 28 '16 at 16:32

score 1 · Accepted Answer · edited May 23 '17 at 12:33

1

Since column should get updated depending on date created you need to place the query in a job and schedule it every day.You can use below query for updating the column values.

Link to add SQL code to run through job daily:how to schedule a job for sql query to run daily?

 Update Column='yourvalue'
 From TableName T
 Where DateDiff(DD,T.CreateDate,Getdate())>90

edited May 23 '17 at 12:33

Community

1
1

answered Dec 27 '16 at 06:02

vinay koul

348
1
9

So I should run this manually? I want something which runs like trigger automatically when the date difference is 90 days. – OLDRomeo Dec 27 '16 at 06:55
@OLDRomeo Even though update should take place when date difference is 90 days but the calculation of date difference should be done every day.You don't have to run it manually.You just have to create a new job in SQL server agent where you have to place this query and schedule it every day.I have updated above solution with link for How to schedule a job for SQL query to run daily – vinay koul Dec 27 '16 at 09:48
It is simple and straightforward. Nothing complex to even if table is huge it works fine with batches. – OLDRomeo Jan 23 '17 at 16:44

score 0 · Answer 2 · answered Dec 27 '16 at 06:09

0

It is better to run SQL command for updating query even it is a big database.

answered Dec 27 '16 at 06:09

eMythMakers.com

19
1
6

score 0 · Answer 3 · answered Dec 28 '16 at 09:44

Why not simply use a computed column? That way the value will always be correct and yet there is no need for (costly) updates that need to be run on a regular basis.

CREATE TABLE t_my_table (row_id  int IDENTITY (1,1) PRIMARY KEY,
                         value_x int NOT NULL,
                         value_y int NOT NULL,
                         createDate datetime NOT NULL,
                         is_90_days AS (CASE WHEN DateDiff(day, createDate, CURRENT_TIMESTAMP) >= 90 THEN Convert(bit, 1) ELSE Convert(bit, 0) END) 
                        )


GO

INSERT INTO t_my_table
      (value_x, value_y, createDate)
VALUES (10, 100, '18 aug 2016'),
       (20, 200, '25 nov 2016'),
       (30, 300, '12 dec 2016'),
       (40, 400, '14 may 2017')

GO

SELECT * FROM t_my_table

To add the computed column to an already existing table, use this syntax:

ALTER TABLE t_my_table ADD is_90_days AS (CASE WHEN DateDiff(day, createDate, CURRENT_TIMESTAMP) >= 90 THEN Convert(bit, 1) ELSE Convert(bit, 0) END)

Adding a computed column is pretty much instantaneous since it's only a meta-data operation. The actual value is calculated upon SELECTion of the record. Since the DateDiff operation is pretty quick you'll hardly notice it.

How to update a column in a SQL Server table with millions of rows when its column "createdDate" passes 90 days from now? Can we use trigger?

3 Answers3