I want to update a scalar function in our database. This function is used for several calculated columns in a key table so I get a dependency error if I try to update it. I can explicitly remove the columns, update the function and re-add the columns but, without a lot of extra fannying about, that will change column orders and might have other unintended consequences. I wondered if there was a cleaner way of doing it?
Sql-server – Scalar function used in calculated column, what’s the cleanest way to update the function
functionssql-server-2005sql-server-2008
Related Question
- Sql-server – Implications of using a Unique NonClustered Index with Covering Columns instead of a Primary Key
- Sql-server – User defined function performance disadvantages
- Sql-server – How to consider when deciding between passing a comma-delimited string to a stored procedure instead of calling it individually per record
- Sql-server – Why would call to scalar function inside a Table Value Function be slower than outside the TVF
- PostgreSQL – Ideal Way to Return a Scalar from Function: RETURN vs. SELECT
- SQL Server 2016 – Concurrency Limitations and Tuning for DB Concurrency
- SQL Server – How to Emulate User-Defined Scalar Function Without Preventing Parallelism?
Best Answer
This may be more work but it should get (approximately) what you are after:
1 - Create a new table that has just your PK from the main table and the updated function.
2 - Drop your old column
3 - Rename your old table to something like
TableName_Base4 - Create a
VIEWthat joins your main table to your lookup table, with the fields in the order that you want to see them. Name the view whatever your original table name was.This will potentially create some issues with
INSERTSandUPDATESthough since you'll be accessing via a view. To deal with that you can either insert into the table directly, bypassing the view.