Tag : functions


Use NEWID() inside function in SQL Server

In SQL Server, if we use the NEWID() function inside an user-defined function, it throws an error as “Invalid use of side-effecting or time-dependent operator in ‘newid()’ within a function.

For any reason, if we need to use the NEWID() function inside an user-defined function, we need to do some work around. In this post, we will discuss few tips which can be used to deal with such kind of scenarios.

To demonstrate this problem, first, let’s create a user defined function which uses NEWID() function inside. Though in production environment we might be using more complex logic with the NEWID value, but in this demo, to keep it simple, we will be using the LOWER() function which converts the given value to the lower case.

IF OBJECT_ID('dbo.fn_TestNEWID') IS NOT NULL
DROP FUNCTION dbo.fn_TestNEWID
GO
CREATE FUNCTION dbo.fn_TestNEWID()
RETURNS NVARCHAR(50)
AS
BEGIN
--do some stuff with NEWID and 
More

Dynamic management views and functions in SQL Server 1

In this post named “Dynamic management objects in SQL Server”, we will explore the DMVs and DMFs in SQL Server.¬†Dynamic management views (DMVs) and dynamic management functions (DMFs) are used to extract current internal information of the Server State using T-SQL commands. Formerly such types of data were only available in Performance Monitor (a Microsoft tool to analyze System performance), now SQL Server has exposed such information with some other important information related to current server state through dynamic management views and dynamic management functions. Both dynamic management views and dynamic management functions are collectively known as Dynamic Management Objects (DMOs). These objects were introduced primarily in SQL Server 2005 which allow us to monitor the current internal status of SQL Server using simple T-SQL.

DMVs and DMFs are very useful in collecting current performance related data of the system. We can categorize these objects in two groups as … More


Deterministic and non-deterministic functions in SQL Server 6

In this topic “Deterministic and non-deterministic functions”, we are going to discuss the deterministic and non-deterministic¬†functions in sql server. Lets start with “what is a deterministic function in sql server?”. It’s a common interview question also. I¬†will elaborate the definition of deterministic as well as non-deterministic functions and also provide some good examples. So after reading this article you will be able to distinguish deterministic and non-deterministic functions.

Deterministic Functions

The functions which always return same output when they called with a particular set of input values in the same state of the database are known as deterministic functions.

SELECT ISNULL('ABCDEF', '1')

Above t-sql code will always return the output as ‘ABCDEF’ and thus ISNULL is a deterministic function. Below is the list of some other deterministic functions.

ISNULL, ISNUMERIC, DATEDIFF, POWER, CEILING, FLOOR, DATEADD, DAY, MONTH, YEAR, SQUARE, SQRT etc.

Non-deterministic Functions

The functions which may return … More