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.

SQL Server Memory Has Been Paged Out

Microsoft SQL Server manages memory dynamically based on the memory requirements of server load. But sometime you experience problem such as…

  • The performance of SQL Server decreases suddenly.
  • A computer that is running SQL Server stops responding for a short time.
  • A time-out occurs for applications that connect to SQL Server.
  • Problems occur when you run even simple commands or use applications on the system

This problem occurs because the Windows operating system pages out the working set of the SQL Server process. While looking for workaround for this problem I came across a very good article “How to reduce paging of buffer pool memory in the 64-bit version of SQL Server” by Microsoft Support Team which solved my problem.

Resolving Sort Warnings

While googling for “Sort Warning” in execution plan I came a across a very nice article by Margarita Naumova. In this article Margarita has explained very well what exactly the Hash and Sort warning are and how they cause performance problem in SQL SERVER.
Further, How memory grant is related to Hash and Sort warning and the solution such as updating statistics, using compile hints etc. can be leverage to remove these warning.

Link:http://blogs.technet.com/b/magi/archive/2011/03/01/inside-hash-and-sort-warnings.aspx

“Index was outside the bounds of the array” while connecting to SQL Server 2012 instance using lower version SSMS

When you try to connect SQL server 2012 instance using SQL server 2008 or 2008 R2 management studio you receive below errors…

Cause:

This is because  SQL Server does not support backward compatibility. We cannot use lower version of SSMS to connect higher version of SQL, Server which means we cannot connect to SQL Server 2012 using SQL Server 2008 or 2008 R2 SSMS.

reference :

FIX:

There are multiple workarounds with some limitations as mentioned below…

1. Install Microsoft® SQL Server® 2012 Express – you can download it from here http://www.microsoft.com/en-us/download/details.aspx?id=29062. The only limitation is supported operating systems: Windows 7, Windows Server 2008 R2, Windows Server 2008 Service Pack 2, Windows Vista Service Pack 2.

2. If you have SQL Server 2008 or 2008 R2 install you may choose for this fix –http://support.microsoft.com/kb/2459027

Like this post and reply with your comments if this works for you!

SQL Server Long Drive!

Every one of us must have started their career path in SQL server from some version. But very few of us hold the knowledge of when Microsoft gave birth to the SQL Server. Even I was not the exception for this, but long back I attended one session in Ahmadabad by Pinal Dave who shared the very great information on SQL Server birth and its childhood. Today I am thankful to him and also feeling honour to share such history with all of you. It not a big deal if you don’t know the history (“How many of us know the history of our great leaders?). But I am telling you guys Microsoft has taken a very long drive with SQL server. Rather than saying anything I would prefer go through this link this speaks more than my words…

http://en.wikipedia.org/wiki/Microsoft_SQL_Server

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

Microsoft® SQL Server® 2008 R2 Best Practices Analyzer is now available

The SQL Server Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server recommendations and best practices to determine if there are potential issues in the database environment. For SQL Server 2008 R2 Microsoft has come up with new version. Here is the link for it.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591&displaylang=en

Overview

The Microsoft SQL Server 2008 R2 BPA is a diagnostic tool that performs the following functions:

  • Gathers information about a Server and a Microsoft SQL Server 2008 or 2008 R2 instance installed on that Server
  • Determines if the configurations are set according to the recommended best practices
  • Reports on all configurations, indicating settings that differ from recommendations
  • Indicates potential problems in the installed instance of SQL Server
  • Recommends solutions to potential problems
System Requirements

  • Supported Operating Systems:Windows 7;Windows Server 2003;Windows Server 2008;Windows Server 2008 R2;Windows Vista

SQL Server 2008 R2 Best Practices Advisor is supported on the following Operating Systems:

  • Windows Server 2003
  • Windows Server 2008
  • Windows Vista
  • Windows Server 2008 R2
  • Windows 7

The following are required for using SQL Server 2008 R2 Best Practices Analyzer: