I have a table as large as 8GB and with 20M records. There is an int field called mth. The mth field stores date information in form of YYYYMM, I want to transform the mth field to consecutive integers. So I use a formula to get the year and month from field mth and calculate the month order, specifically, I use the following code:
create function mth_to_num(@month int) returns int as begin return(round(@month/100,0)*12+@month-100*round(@month/100,0)) end
Then I use the following code to update the value in the huge table
update full_orig_month_Q1_1999 set mth_order = dbo.mth_to_num(period) Go
However, the code took quite long time to execute, roughly 2-3 minutes. My system is windows 10 64 bit with SQL server 2016. Is there any way I can speed it up?
Another problem is that I found SQL server occupying as much as 8GB in the database after executing the above query. Does it need so much memory? How can I release them?
Thanks for your help in advance!