SQL Interview 24: DROP and CREATE vs ALTER for T-SQL Functions

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

You are writing a script to change the code in a T-SQL function.

You can choose to either DROP IF EXISTS and CREATE the procedure, or to use an ALTER statement.

What advantage would you get from using an ALTER statement?

Answer:

When you DROP a function, you lose not only the function code, but also permissions that have been assigned on the function. CREATE does not put that back. You would need to GRANT the permissions again.

When you use ALTER, the permission grants are preserved.

2025-03-07