Category : SQL Server


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

Extract the first number from an alphanumeric string in sql server 4

In this post, we are going to learn how to extract the first numeric value from an alphanumeric string in SQL Server. To demonstrate this, we will create a dummy table and then we will insert some dummy rows into that table. Finally, we will use a SELECT statement to extract the first numeric value from the given alphanumeric string for each row of the table.

Let’s create the dummy table as below:

--Create dummy table named testTable
IF(OBJECT_ID('dbo.testTable')) IS NOT NULL
DROP TABLE dbo.testTable
GO
CREATE TABLE dbo.testTable
(
val VARCHAR(100)
)
GO

Now, let’s insert some alphanumeric values in this table as below:

--Insert some dummy rows in testTable
INSERT INTO dbo.testTable
(val)
VALUES
('1846855445-DB-FC-D-B-FEDACAE'),
('912898124-B--EB-CBD-EEC'),
('AFAA---E-EDDEC-1080731029'),
('FFDE-DA-B-A-BCE-1807799782'),
('DDFAD-DDE-D-EB-FFDFBB-1683269209'),
('522822568-C-EFC--ACCA-EDB'),
('BDDAC-C-C-A-EC-450054394'),
('DBCD-D-AA-AD-EFA-385921800'),
('EF-B-ED-BBC-FEBC-599561669'),
('BED--F-BC-FD')

We have below sample records in the testTable:

--Get data from testTable
SELECT * FROM dbo.testTable
Sample records in testTable

Sample records in testTable

Now, to extract the … More


Covering index and its usage

What is a covering index

A covering index is an index which satisfies all the columns being used in a query without performing a further lookup either to the clustered index or to the base table. It contains all the columns whether they are used in SELECT, JOIN, WHERE, GROUP BY, or ORDER BY clause of the query. Covering index works like a pseudo-clustered index for the query. It improves the performance of the query by adding non-key columns in the index which help the query optimizer to remove any lookup which can be a key lookup or a RID lookup. A key lookup is performed in case the base table accommodates a clustered index otherwise a RID lookup is used by the query optimizer to fetch the non-key columns needed by the query. To know more about key lookup and RID lookup, click here.

The optimizer doesn’t need … More


STRING_SPLIT function – SQL Server 2016 1

Finally, STRING_SPLIT function, one of the most awaiting feature for a long time, has been introduced by Microsoft. In SQL Server 2016, now, we have a built-in system function to split a string with a specified separator.

Earlier, whenever we had to map a denormalized string (e.g. comma separated string values) with the values stored in a table column (normalized values) , we had to create a table valued function which could create a table from a given string value by splitting it using predefined separator. The problem with creating our own function to split the string was its performance, especially if we were not using either a CLR function or an highly optimized SQL function.

One of the most common scenario where we need this functionality very frequently is whenever we need to deal with SSRS’s multi value parameters. Reporting services supplies a comma separated string value to the … More


DATEDIFF and DATEDIFF_BIG – SQL Server 2016

We are already familiar with DATEDIFF function introduced in the very initial version of SQL Server. But, in SQL Server 2016, Microsoft has introduced DATEDIFF_BIG function which can be used to compute the difference between two given dates in terms of the given date part. In this post, we are going to explore the use of the DATEDIFF_BIG function.

Lets have a look on the syntax of both these functions:

Syntax:

DATEDIFF

DATEDIFF(<datepart>, startdate, enddate)

DATEDIFF_BIG

DATEDIFF_BIG(<datepart>, startdate, enddate)

Difference between DATEDIFF and DATEDIFF_BIG function

In above syntax, we can see that both the functions have similar syntax and any of them can be used to get the difference between two dates. However, these functions have different return types. DATEDIFF function returns an integer value as a difference between two dates, whereas DATEDIFF_BIG function returns a big integer value as a difference.

The range of the integer value in … More


Select all elements regardless of level in XML

We have already published a post on “Read XML Data as a table in SQL Server” and “Read and compare XML nodes dynamically with unknown elements“. In this post, we will see that how we can skip levels in XML string to read all the elements at a given level regardless of the level name. A wildcard character * (asterisk) can be used to extract all the elements of a XML string regardless of the level name. Lets have a demo to understand this.

Suppose, we have below XML data:

<Department>
<HR>
<EmpDetail>
<EmpName>Employee 1</EmpName>
<EmpId>1</EmpId>
<JoiningDate>20-Jan-2015</JoiningDate>
</EmpDetail>
<EmpDetail>
<EmpName>Employee 2</EmpName>
<EmpId>2</EmpId>
<JoiningDate>15-Jul-2015</JoiningDate>
</EmpDetail>
<EmpDetail>
<EmpName>Employee 3</EmpName>
<EmpId>3</EmpId>
<JoiningDate>18-Mar-2015</JoiningDate>
</EmpDetail>
</HR>
<Admin>
<EmpDetail>
<EmpName>Employee 4</EmpName>
<EmpId>4</EmpId>
<JoiningDate>22-Jan-2015</JoiningDate>
</EmpDetail>
</Admin>
<Engineering>
<EmpDetail>
<EmpName>Employee 5</EmpName>
<EmpId>5</EmpId>
<JoiningDate>26-Feb-2015</JoiningDate>
</EmpDetail>
</Engineering>
</Department>

In this data, the root level node is <Department> which has <HR>, <Admin>, and <Engineering> as their children … More


It’s time to install SQL Server 2016

Microsoft has launched the SQL Server 2016. We can download and install it from here. However, when we start installation of SQL Server 2016, we can see some installation changes there. Prior to SQL Server 2016, “SQL Server Management Studio (SSMS)” was a part of the feature list of main SQL Server installation (which starts after clicking on “New SQL Server stand-alone installation or add features to an existing installation“) but now it is available on a separate install. Below is the screen shot of the feature selection list of SQL Server 2016 and SQL Server 2014:

SQL Server 2016 Feature list 2016 vs SQL Server 2014 Feature List

SQL Server 2016 Feature list 2016 vs SQL Server 2014 Feature List

In above image, we can see that the management tools was a feature of the core SQL Server installation process in SQL Server 2014 (right side image) but in SQL Server 2016 (left side image), it is not … More


Set vs Select – Assigning variables

SET and SELECT both can be used to assign values to the local variables in SQL Server. However, SET is an ANSI standard and also recommended by Microsoft to be used for variable assignment. In this post, “Set vs Select – Assigning variables“, we will explore the behavior of SET and SELECT statement during variable assignment from a query.

When we use a scalar subquery (which returns a single value) to assign a local variable using SET or SELECT, both statements behave similar. It means, if subquery returns no rows, they set variable to NULL during variable assignment. But when a column name from a query (which is not a scalar subquery) directly assigns to a local variable using SELECT statement and the query returns 0 rows, then the variable value remains unchanged.

As per its nature, SET statement uses a scalar subquery to assign a local variable … More


Change stored procedure output column names and data types 2

Can we change the output column name and their data type being received from a stored procedure during execution?

Yes, SQL Server 2012 introduced “WITH RESULT SETS” option which can be used with EXECUTE command to redefine the metadata of result sets being received from stored procedures. This option can handle single as well as multiple result sets.

WITH RESULT SETS” option can be useful if we want to reuse an existing stored procedure’s result set(s) with different column name and / or data type. Lets understand this with the help of an example. Below is the stored procedure named “uspGetEmployeeManagers” created in “AdventureWorks2014” datababse. This procedure return the list of managers for a given employee as below:

EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 101
Current output columns

Current output columns

Imagine, this stored procedure is being used in many applications throughout organization. Now, in a new … More


The join order has been enforced because a local join hint is used – Warning in SQL Server 3

When we use a local JOIN hint in T-SQL query, sometimes, SQL Server throws a warning message as “Warning: The join order has been enforced because a local join hint is used.“. For example, below is a query with a local join hint:

SELECT * FROM Person.Person A 
INNER MERGE JOIN HumanResources.Employee B 
ON A.BusinessEntityID = B.BusinessEntityID

After execution, we will get below warning message with result set:

Warning Message

Warning Message

In above code, we are using a local JOIN hint “INNER MERGE JOIN” which forces this query to override the physical join (which could be nested loop or merge or hash join) chosen by SQL Server with the physical join given in the query hint (which is MERGE join in this case). If you want to read more about the physical joins, visit my post “Nested loop, Merge, and Hash Joins in SQL Server“.… More