1. Maintaining user defined functions
I know maintenance doesn't sound as exciting as creating and executing, but if you don't take good care of your hard work who will?
2. ALTER Function
You might have already asked yourself, how can I change a function which has already been created? The ALTER keyword can be used for this. Here, we are changing the SumLocationStats() function we created previously. The input parameter name is now @EndDate instead of @BeginDate and is compared to the DropOffDate column instead of PickupDate.
3. CREATE OR ALTER
You can also use CREATE OR ALTER keywords together. This is helpful during the development process when you are making many subsequent changes. SQL Server won't let you CREATE a function that already exists, but CREATE OR ALTER will execute without issue.
4. DROP Function
There are some limitations to ALTER. If you want to change a table valued function from a Multi Statement to an Inline or vice versa, you can't use ALTER. Instead, to make this type of structural change, you need to use DROP to delete the function.
After its deleted use CREATE as either an Inline or Multi statement.
DROP should be used whenever you need to delete a function. Make sure to keep your work tidy, and delete functions you created but don't plan to use in the future. DROP will execute as long as your database user has permission to do so.
5. Determinism improves performance
Determinism is a characteristic of a function and is true when a function will return the same result given the same input parameters and state of the database. A function is nondeterministic if it could return a different value given the same input parameters and database state.
6. Determinism improves performance
GetRideHoursOneDay() is an example of a deterministic function while GetTomorrow() is nondeterministic. GetTomorrow(), just like GETDATE(), doesn't return the same result because it's always in relation to the current date. Sometimes functions can't be made deterministic, but we should try to make them deterministic if possible.
When a function is deterministic, SQL Server can index the results which contributes to improved performance. Built-in SQL functions determinism cannot be adjusted. As you use native SQL functions you should review whether they are deterministic or not. One of the aspects of determinism is returning the same result based on the same state of the database. How can a function verify that the database state won't change in a way that would affect its result? The function's schemabinding option can be enabled.
7. Schemabinding
A schema is a collection of database objects associated with one particular database owner like dbo. UDFs can reference database objects like tables, columns, data types or other functions. If the SCHEMABINDING option is enabled within the function, SQL Server will prevent changes to the database objects that it references.
For example, if someone attempted to rename the PickupDate column of the YellowTripData table, SQL would prevent this change because the GetRideHrsOneDay() function is bound to its schema. We do this by including the WITH SCHEMABINDING keywords after the RETURNS clause.
If a change is needed to a database object referenced in your schemabound function, you can temporarily remove the SCHEMABINDING, make the database object change, and then change the UDF to reflect the database object change. Don't forget to turn back on SCHEMABINDING to protect against future changes.
8. Let's practice!
Let's practice maintaining these fantastic functions you created.