T-SQL useful functions

Its been quite a while we have been working on SQL Server and and we know with every new release Microsoft comes up with improvised system function. Here in this post, I am going to list of some interesting system functions that developers (.net / SQL) can leverage in development area.

  • STRING_AGG – Convert columns to rows
    • Syntax: STRING_AGG ( expression, separator ) [ <order_clause> ]
    • Example
    • SELECT town,

      STRING_AGG (email, ‘;’) WITHIN GROUP (ORDER BY email ASC) AS emails

      FROM dbo.Employee

      GROUP BY town;

    • Supported SQL version: SQL Server 2017 & onward, Azure SQL Database.
  • STRING_SPLIT – Convert comma separated values in rows
    • Syntax: STRING_SPLIT ( string , separator )
    • Example
    • SELECT ProductId, Name, value

      FROM Product

      CROSS APPLY STRING_SPLIT(Tags, ‘,’);

    • Supported SQL version: SQL Server 2016 & onward, Azure SQL Database.
  • GROUPING – Calculate Total Value
    • Syntax: GROUPING ( <column_expression> )
    • Example
    • SELECT SalesQuota, SUM(SalesYTD) ‘TotalSalesYTD’, GROUPING(SalesQuota) AS ‘Grouping’

      FROM Sales.SalesPerson

      GROUP BY SalesQuota WITH ROLLUP;

      GO

    • Supported SQL version: SQL Server 2008 & onward, Azure SQL Database.
  • CUME_DIST – Calculates the cumulative distribution of a value in a group of values in SQL Server.
    • Syntax: CUME_DIST( ) OVER ( [ partition_by_clause ] order_by_clause )
    • Example
    • USE AdventureWorks2012;

      GO

      SELECT Department, LastName, Rate,

      CUME_DIST () OVER (PARTITION BY Department ORDER BY Rate) AS CumeDist,

      PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Rate ) AS PctRank

      FROM HumanResources.vEmployeeDepartmentHistory AS edh

      INNER JOIN HumanResources.EmployeePayHistory AS e

      ON e.BusinessEntityID = edh.BusinessEntityID

      WHERE Department IN (N’Information Services’,N’Document Control’)

      ORDER BY Department, Rate DESC;

    • Supported SQL version: SQL Server 2012 & onward, Azure SQL Database, Azure SQL Data Warehouse.
  • LAG – Access previous row in same result set, alternate for Self-join
    • Syntax:LAG (scalar_expression [,offset] [,default])

      OVER ( [ partition_by_clause ] order_by_clause )

    • Example
    • USE AdventureWorks2012;

      GO

      SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,

      LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota

      FROM Sales.SalesPersonQuotaHistory

      WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN (‘2005′,’2006’);

    • Supported SQL version: SQL Server 2012 & onward, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse.
  • LEAD – Access subsequent row in same result set, alternate for Self-join
    • Syntax:LEAD (scalar_expression [,offset] [,default])

      OVER ( [ partition_by_clause ] order_by_clause )

    • Example
    • USE AdventureWorks2012;

      GO

      SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,

      LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota

      FROM Sales.SalesPersonQuotaHistory

      WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN (‘2005′,’2006’);

    • Supported SQL version: SQL Server 2012 & onward, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse.
  • OPENJSON – Read JSON data
    • Syntax:OPENJSON( jsonExpression [ , path ] )  [ <with_clause> ]

      <with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,…n ] )

    • Example
    • DECLARE @json NVARCHAR(4000) = N'{

      “path”: {

      “to”:{

      “sub-object”:[“en-GB”, “en-UK”,”de-AT”,”es-AR”,”sr-Cyrl”]

      }

      }

      }’;

      SELECT [key], value

      FROM OPENJSON(@json,’$.path.to.”sub-object”‘)

    • Supported SQL version: SQL Server 2016 & onward, Azure SQL Database.
  • CONCAT – Concatenating two or more string values.
    • Syntax: CONCAT ( string_value1, string_value2 [, string_valueN ] )
    • Example
    • SELECT CONCAT ( ‘Happy ‘, ‘Birthday ‘, 11, ‘/’, ’25’ ) AS Result;

    • Supported SQL version: SQL Server 2012 & onward, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse.
  • CONCAT_WS – Concatenating two or more string values with delimiter specified.
    • Syntax: CONCAT_WS ( separator, argument1, argument1 [, argumentN]… )
    • Example
    • SELECT CONCAT_WS(‘,’,’1 Microsoft Way’, NULL, NULL, ‘Redmond’, ‘WA’, 98052) AS Address;

    • Supported SQL version: SQL Server 2012 & onward, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse.
Advertisements

Error : “The file ‘tempdev2’ cannot be removed because it is not empty.”

I have multiple data files created for Tempdb database let’s say tempdev2. Now when I try to remove additional mdf files from filegroup, I got below issue. SQL Server says..

Msg 5042, Level 16, State 1, Line 1
The file 'tempdev2' cannot be removed because it is not empty.
Cause : The reason for such behavior is there are some activities going on that is using files created your TempDB database. The general behavior is to restart the SQL server that will create your TempDB files and reset it. But the only problem with it is it will down the other environments / databases.
Fix :
USE [tempdb]
GO
DBCC SHRINKFILE('tempdev2', EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev2]
GO

I hope this will help you!

Like this post and post comments if you like it.

How to execute SQL files using T-SQL?

Sometimes ago I read an article on how to execute the sql file using powershell by Martin Bell. After reading it the question came into my mind is “Why not just use T-SQL? Would that be simpler? “. Indeed, powershell gives you a wide approach of doing the database activities, the same can be achieved using T-SQL as well. The below code snippet gives a overview of “How to execute sql using T-SQL”

DECLARE @fPath VARCHAR(4000) = 'C:\SQL\Script1.sql'
DECLARE @outPath VARCHAR(4000) = 'C:\SQL\Script_exec_log.txt'
DECLARE @serverPath VARCHAR(20) = 'localhost'
DECLARE @sqlString VARCHAR(4000)
DECLARE @dbList VARCHAR(1000), @dbName VARCHAR(100), @rowCount INT
SELECT @dbList=COALESCE(@dbList + ',', '')+ name FROM sys.databases WHERE name like '%Test'
SET @rowCount = @@rowcount WHILE 1= 1
BEGIN
SET @dbName = CASE WHEN (@rowCount = 1 OR @dbList != @dbName) THEN @dbList ELSE SUBSTRING(@dbList, 1, CHARINDEX(',', @dbList)-1) END
SET @sqlString = 'SQLCMD -E -S'+@serverPath+' -d'+@dbName+' -i'+@fPath +' -o'+ @outPath
PRINT @sqlString
EXEC master..xp_cmdshell @sqlString
IF @dbList= @dbName BREAK
SET @dbList = SUBSTRING(@dbList, CHARINDEX(',', @dbList)+1, LEN(@dbList))
END

Error message “The query processor ran out of stack space during query optimization. Please simplify the query.”

You may have faced problem like this before also, when you were trying to delete a single/multiple record from a table. But have you got the solution for it?

Here is the problem I faced recently with SQL Server 2005 SP2 and the way I handled it after spending enough time on reading MSDN, various articles?

Scenario:

We have one table called “User” which is referenced by nearby 500 tables as well as self-referenced. Now when I tried to run a simple DELETE statement with primary key ID, I keep getting the stack overflow error (as mentioned in the Title). I was completely blank after reading MSDN, articles; I got no resolution anywhere.

Here are few reasons I have pointed out that could be the possibilities for this problem…

  1. Recursion during query optimization. Ref: http://support.microsoft.com/kb/945896
  2. The problem lies in number of Foreign Keys. In this scenario, the user table is referenced by many tables.Ref: http://www.sqldev.org/sql-server-database-engine/internal-query-processor-error-server-msg-8621-96775.shtml
  3. In some extreme situations stack size may become limit for given query. Ref: http://msdn.microsoft.com/en-us/library/aa226411(SQL.80).aspx

I used the traditional way to solve this problem. It could not be the correct way to resolve this, we can discuss on this as well as if I ‘get any resolution I’ll post it here.

Solution:      

  1. Disable all the referencing Foreign Keys. You can use the custom script or the below script. In below you will need to update the referenced entity.
    DECLARE @FKName			VARCHAR(255)
    DECLARE @MainEntity		VARCHAR(255)
    DECLARE @SQLString			VARCHAR(4000)
    
    DECLARE cur_FK CURSOR FOR
    	SELECT	name FKName, OBJECT_NAME(parent_object_id) MainEntity
    	FROM	sys.foreign_keys
    	WHERE	OBJECT_NAME(referenced_object_id) = 'User'
    OPEN cur_FK
    FETCH NEXT FROM cur_FK INTO @FKName, @MainEntity
    
    WHILE @@FETCH_STATUS = 0 BEGIN
    	SET @SQLString = 'ALTER TABLE ['+ @MainEntity + '] CHECK CONSTRAINT [' + @FKName +']'
    
    	--PRINT @SQLString
    	EXEC (@SQLString)
    
    FETCH NEXT FROM cur_FK INTO @FKName, @MainEntity
    END
    CLOSE cur_FK
    DEALLOCATE cur_FK
  2. Run the Delete statement.
  3. Enable all the Foreign Keys. You can use the same script mentioned above just change the “NOCHECK” keyword to “CHECK”.

I hope this will help you all. Share your ideas to handle this problem.

Working with comma separated string using XML

SQL DBAs and Database Developers often require parsing or splitting the comma separated string into rows. I guess, you might have already written or used any Split FUNCTION in your database. Here, I would like to demonstrate a different technique to split the delimited string; I used this technique while working with LINQ technology.


IF OBJECT_ID('Table1') IS NOT NULL
      DROP TABLE Table1
GO

CREATE TABLE Table1(KeyID INT)
GO

INSERT Table1
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
GO

DECLARE @p2 VARCHAR(100)
SET @p2= '1,3'

SELECT      *
FROM  Table1
WHERE KeyID IN (
            SELECT  Split.PId
            FROM  (SELECT CAST('<r>' + REPLACE(@p2, ',', '</r><r>') + '</r>' AS XML) AS P ) xmlP
            CROSS APPLY (SELECT P.PId.value('.', 'varchar(10)') PId FROM xmlP.P.nodes('r') AS P(PId) ) Split
)
GO

Delete records in batch using TOP clause and @@ROWCOUNT

How T-SQL can be used to delete millions of records in batch from the table. Here we go…

SET NOCOUNT ON
DECLARE @tblRecords TABLE(RowID INT )

INSERT  @tblRecords
SELECT  ROW_NUMBER() OVER(ORDER BY A.name)FROM sys.objects A CROSS JOIN sys.objects B
PRINT '# of Records added ---- ' + CAST(@@ROWCOUNT AS VARCHAR(10))

DECLARE @i INT
WHILE 1 = 1 BEGIN
      DELETE TOP(1000) ---define the batch size
      FROM @tblRecords

      SET @i = @@ROWCOUNT

      IF @i = 0 BREAK
      PRINT '# of Records deleted in batch ---- ' + CAST(@i AS VARCHAR(10))
END
GO

Different ways to Kill Active Connections in Database

There are multiple ways to kill all active connections in the current or specific database. To kill all the connections to your database, you can use the ALTER DATABASE command or you can loop through the system process and kill them.

One way is…

DECLARE @p_SPID int, @p_SQL nvarchar(2000),@dbName nvarchar(100)
SET @dbName = DB_NAME()
DECLARE #cur_Processes CURSOR FOR SELECT p.SPID FROM master.dbo.sysprocesses AS p JOIN master.dbo.sysdatabases AS d ON( d.dbid = p.dbid ) WHERE d.Name = @dbName AND p.SPID > 50 -- AND spid >= 51 (because spids of 50 or less are reserved for internal use.) 
OPEN #cur_Processes 
FETCH NEXT FROM #cur_Processes INTO @p_SPID 
WHILE @@FETCH_STATUS = 0 BEGIN
IF @@SPID != @p_SPID BEGIN

SET @p_SQL = 'KILL ' + CONVERT( nvarchar(30), @p_SPID ) PRINT @p_SQL EXECUTE( @p_SQL ) END

FETCH NEXT FROM #cur_Processes INTO @p_SPID 
END
CLOSE #cur_Processes 
DEALLOCATE #cur_Processes
GO

and another way is…


ALTER DATABASE AdventureWorksLT2008 SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
GO
ALTER DATABASE AdventureWorksLT2008 SET MULTI_USER
GO