Sql-server – Finding changes for table-valued function definition on SQL Server

sql serversql-server-2008

I had a Table-Valued Function in SQL Server and someone from my colleagues has altered it unexpectedly. How can I restore previous version easily? Is there any way to find the previous version?

Best Answer

The answer is unfortunately no. Without some form of source control or a functional back-up to go to you are kind of out of luck. As soon as you push execute on that alter statement you have changed that table/function/view/StoredProc. For future precautions you should probably have nightly back-ups make sure only the appropriate people have the user permissions required to make database changes. Also you want to make sure you have source control and are following a proper procedure for testing and deployment to make sure this does not happen in the future.

Related Solutions

SQL Server 2008 – T-SQL Table Valued Function to Split a Column on Commas

Re-worked it slightly...

DECLARE @FoundIndex INT
DECLARE @ReturnValue VARCHAR(MAX)

SET @FoundIndex = CHARINDEX(@delimiter, @String)

WHILE (@FoundIndex <> 0)
BEGIN
      SET @ReturnValue = SUBSTRING(@String, 0, @FoundIndex)
      INSERT @SplitValues (Asset_ID) VALUES (@ReturnValue)
      SET @String = SUBSTRING(@String, @FoundIndex + 1, len(@String) - @FoundIndex)
      SET @FoundIndex = CHARINDEX(@delimiter, @String)
END

INSERT @SplitValues (Asset_ID) VALUES (@String)

RETURN

SQL Server NOLOCK – Use NOLOCK Hint When Calling Table Valued Function

You cannot use a WITH (NOLOCK) on a Table-Valued Function, unless you put it on every single table in the code inside the function.

Your best bet would be, like you said, to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

To change this back to the default, you need to find out what isolation level is currently set (before changing it above).

This can be done by running DBCC USEROPTIONS and checking the value for isolation level.

The default tends to be READ COMMITTED, so to change it to that, you'll want to write a statement like the following:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Best Answer

Related Solutions

SQL Server 2008 – T-SQL Table Valued Function to Split a Column on Commas

SQL Server NOLOCK – Use NOLOCK Hint When Calling Table Valued Function

Related Question