Sql Server deterministic user-defined function

by

You just need to create it with schemabinding.

SQL Server will then verify whether or not it meets the criteria to be considered as deterministic (which it does as it doesn’t access any external tables or use non deterministic functions such as getdate()).

You can verify that it worked with 

SELECT OBJECTPROPERTY(OBJECT_ID('[dbo].[FullNameLastFirst]'), 'IsDeterministic')

Adding the schemabinding option to your original code works fine but a slightly simpler version would be.

CREATE FUNCTION [dbo].[FullNameLastFirst] (@IsPerson  BIT,
                                           @LastName  NVARCHAR(100),
                                           @FirstName NVARCHAR(100))
RETURNS NVARCHAR(201)
WITH SCHEMABINDING
AS
  BEGIN
      RETURN CASE
               WHEN @IsPerson = 0
                     OR @FirstName="" THEN @LastName
               ELSE @LastName + ' ' + @FirstName
             END
  END

More Related Contents:

  1. formula for computed column based on different table’s column
  2. How can I alter this computed column in SQL Server 2008?
  3. Is it possible to write single query on two table which are not connected to each other?
  4. sql server 2008 management studio not checking the syntax of my query
  5. Is the NOLOCK (Sql Server hint) bad practice?
  6. How to create composite primary key in SQL Server 2008
  7. Parse comma-separated string to make IN List of strings in the Where clause
  8. OPTION (RECOMPILE) is Always Faster; Why?
  9. sql runs fast in ssms slow in asp.net
  10. Sub queries in check constraint
  11. Rename column SQL Server 2008
  12. How do I use structural annotations to set SQL type to Date in model first approach
  13. Transfer data from one database to another database
  14. How to connect to SQL Server from another computer?
  15. How to avoid Duplicate values for INSERT in SQL?
  16. Procedure or function !!! has too many arguments specified
  17. How to find third or nᵗʰ maximum salary from salary table?
  18. SQL “between” not inclusive
  19. Can I set ignore_dup_key on for a primary key?
  20. Selecting first row per group
  21. Using PIVOT in SQL Server 2008
  22. sql group by only rows which are in sequence
  23. Function in SQL Server 2008 similar to GREATEST in mysql?
  24. SQL server join tables and pivot
  25. How to retrieve field names from temporary table (SQL Server 2008)
  26. Why can’t I use an alias for an aggregate in a having clause?
  27. Using a database table as a queue
  28. When using GETDATE() in many places, is it better to use a variable?
  29. SQL . The SP or function should calculate the next date for friday
  30. How to execute a stored procedure inside a select query

Leave a Comment