I am looking into created an MD5 checksum persisted computed value for SQL Server 2008. There is gigabytes of data in this particular table(not my design) and I would like to know if I create the new computed column will this lock the entire table until the computation has finished?
Will the new column update all entries in the table or update them only when they are selected/updated via a SQL command?
What is the recommended practice for created MD5 computed values in SQL Server?
After kicking up an exact copy of the database on a virtual machine I experimented with adding the persisted column.
There were about 1 million rows and it took 2 hours to compute the MD5 hash column. In this time the entire table was locked for select, update and insert. On a production server you would have to factor in other issues.