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.

Temporal Table in SQL 2016

Most common challenges faced while managing large data:

  • How to maintain historical data.
  • How to handle data changes.
  • Impact on having triggers on table to capture every single change in data for auditing.
  • How to recover accidental changes.
  • Calculating trends over time.

To address these challenges Microsoft has introduced a system-versioned temporal table, a new type of user table in SQL Server 2016. A temporal tables are designed to maintain a full history of data changes which allows you to find the state of data at any point in time. This is completely manage by database engine. When you create Temporal table system creates two table 1- Current table & 2. History table.

Limitations:

  • FileTable & FILESTREAM features are not supported.
  • CASCADE option can be used in case of referencing tables.
  • INSTEAD OF triggers are not supported, though AFTER triggers are supported.

Every temporal table has two explicit defined columns with a datetime2 data type (period columns), that you can use as start (SysStartTime) and end (SysEndTime) periods for which row id valid.

Capture

How to create Temporal Table:

Capture

How to query Temporal Table:

Capture

Managing Data Retention:

Data grows very fast when we track each & every change from transnational table, historical data. So how long we should keep the data available in history table and how to move it out of table when retention period expires. Retention period can be decided based on business requirement, however to move data we have multiple options:

An interesting topic to learn more about. Refer to msdn:- https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables