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.

“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

Types of Stored Procedures & Functions

It may sound tricky question if someone ask you “What are the types of Stored procedures?”. Because you might have heard of “Type of Functions”. But some of newbie’s or sometimes some of our DB professionals got confused when they hear this question  (just like me – in my earlier days). In this short article, I’ll summarize the Types of Stored procedure. I hope it helps everyone.

Types of Stored procedure

  • System Stored Procedures – System Stored procedures are nothing but in-built stored procedures which deals which the internal metadata of the SQL Server. There is bunch of system stored procedure, for more details refer to MSDN or link : http://msdn.microsoft.com/en-us/library/ms187961.aspx

EX. sp_renamedb. This system procedure helps to rename the database name.

  • User-defined Store Procedures  – User Defined Store procedures are deals with the relational data in the database, created by the end-user.
    • Simple Stored procedure – In this type of SP, there are no In / Out parameter involve. Stored procedure with simple SQL Query with / without JOINS.

      CREATE PROCEDURE dbo.usp_GetPersons()
      BEGIN
          SET NOCOUNT ON

          SELECT * FROM dbo.Persons
      END

  • Parameterized Stored procedure – In this type of SP, there are In / Out / Both parameter involve.

    CREATE PROCEDURE dbo.usp_GetPersons( @PersonKey )
    BEGIN
        SET NOCOUNT ON

        SELECT * FROM dbo.Persons WHERE PersonKey = @PersoneID
    END

Implementing Concurrency Control

Technical definition for Concurrency is the ability of a database management system to allow multiple users to access data at the same time while maintaining the integrity and consistency of the data. In other words, Concurrency control deals with the issues involved with allowing multiple users simultaneous access to shared entities, objects, data records, or some other representation.

To understand and implement the concurrency control you need to understand the transactions, detecting and understanding cause of concurrency such as deadlocks, lock timeouts etc. This helps you to understand and implement the concurrency control in your system as well as avoid retrieving the Dirty reads, Phantom reads.Concurrency control ensures the transactions in the database occur in an orderly manner.The main job of these control is to maintain the data consistency and integrity. Technically concurrency must preserve the four characteristics of database transactions: ACID (Atomicity, Isolation, Consistency and Durability). You may find different ways to control the concurrency but Alex Kuznetsov has written a very good explanatory article on Developing Modifications that Survive Concurrency.

I hope you like it. Enjoy reading 🙂 !