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.