I have a table with a column of type NVarChar(Max).
I didn't take good precautions before allowing users to add data so right now I have more than 2.5 billion rows and the database is becoming super large.
I'm trying to alter the column to change the size to a fixed length where records > that width should be deleted.
This is not an indexed column.
I tried LEN(Text)>width but this is a very slow function as it would scan billions of rows.
We've also experimented later with changing Int to BigInt by creating a new table and moving data there but it took a week or so.
Alter Column would freeze the system.
Can you kindly suggest any other way or suggest the method you feel is best?
The database is hosted on Azure SQL
Thank you so much :)