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

Azure SQL DW- Polybase

What is Polybase?

Polybase is a technology that accesses and combines both non-relational and relational data, all from within SQL Server. It allows you to run queries on external data in Hadoop or Azure blob storage.

  • It does not require to install additional software.
  • You can leverage T-SQL syntax for querying external data.
  • No knowledge about HADOOP or Azure is required.

PolyBase Scenarios:

  • Access data in Hadoop database with T-SQL.
  • Access Azure blob storage with T-SQL.
  • Import data from Hadoop or blobs as regular SQL Server tables.
  • Export data to Hadoop or Azure blob storage.
  • Integrate well BI tools – SSRS, SSAS, PowerPivot, PowerQuery, PowerView, Tableau, Microstrategy or Cognos.

PolyBase uses external tables to access data in Azure blob storage. Since data is not stored within Azure SQL DW, PolyBase handles authentication to external data by using a database-scoped credential.

Using PolyBase in Azure SQL DW Steps:

  • Create Master Key – to encrypt secret of your database scoped credential

    CREATE MASTER KEY;

  • Create Database Scoped Credential – to specify authentication information for your Azure storage account.

    CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
    WITH
    IDENTITY = ‘polybasepoc’,
    SECRET = ‘secretkeygenerated’
    ;

  • Create External Data Source – to specify the location of your Azure blob storage.

    CREATE EXTERNAL DATA SOURCE AzureStorage
    WITH (
    TYPE = HADOOP,
    LOCATION = ‘wasbs://sample@storage.blob.core.windows.net’,
    CREDENTIAL = AzureStorageCredential
    );

  • Create External File Format – to specify the format of your data.

    CREATE EXTERNAL FILE FORMAT DelimitedText
    WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (FIELD_TERMINATOR = ‘[||]’, STRING_DELIMITER = ‘[~~]’),
    DATA_COMPRESSION = ‘org.apache.hadoop.io.compress.GzipCodec’
    );

  • Create External Table – to specify the table definition and location of the data.

    CREATE EXTERNAL TABLE ExternalSource_CC (
    [CC_ID] [nvarchar](50),
    [CC_CODE] [nvarchar](100),
    [CC_NAME] [nvarchar](100)
    )
    WITH (
    LOCATION=’/SourceFiles/CC/’,
    DATA_SOURCE=AzureStorage,
    FILE_FORMAT=DelimitedText
    );

How to use?

select * from ExternalSource_CC

Limitations of Polybase:

  • PolyBase works with defined structure specified in External Table & File Format.
  • PolyBase does not allow to exclude Text Qualifiers.
  • PolyBase provides row number for failed records but does not give cell level failure information.
  • PolyBase does not recognize delimiter within data and in case delimiter is repeated in text, fails to read data.
  • Polybase only works with delimited text file, no other tabular format supported except Hadoop.
  • At present Polybase supports loading data files that have been UTF-8 encoded.

 

Azure SQL Data Warehouse – Pros & Cons

What is Azure SQL Data Warehouse?
Azure SQL Data Warehouse is PaaS based cloud solution for data storage. It is a massively parallel processing (MPP) cloud-based, scale-out, relational database capable of processing massive volumes of data.

Why you should consider moving to SQL Data Warehouse?
– Massively parallel processing solution. This makes queries many times faster than SQL DB.
– Combine relational data with cloud scale-out capabilities.
– Handles large volume data with massive storage. No storage limit.
– Processing power (compute) can be increase / decrease processing on one click within few seconds.
– Pause or resume DW on-demand, helps in cost saving when not in use.
– Supports SQL Server T-SQL language
– Platform as a service (PaaS) cloud based solution.
– Manages, detect and mitigate security threats Azure authentication features.
– High availability, gives you 99.9% up-time SLA in regions available to public.
– Built-in automated database backups.
– Minimal maintenance, reduced dependency on IT team.
– Good for OLAP environment.

Why you should not consider moving to SQL Data Warehouse?
– Supports SQL Server T-SQL language (not everything though).
– Supports only 32 concurrent connections.
– Supports only 1024 active connections.
– Does not support in-memory OLTP.
– Does not support CROSS database queries.
– Migration from on-premise or IaaS to SQL DW is a challenge.
– Blocking issues may take down entire Azure SQL DW.
– INSERT BULK API is not supported thoroughly.
– Not good for OLTP environment due to frequent changes.

Capture

Reference Link :- https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-overview-what-is

SQL Azure

It’s time to move on with SQL Azure platform. Microsoft has come-up with the new technology call “SQL Azure” a cloud based relational database service which is hosted database service provided by MICROSOFT at their own data centers. Storing relational data locally has extra overhead on buying, maintain the servers; it includes High Availability, and Fault Tolerance etc. SQL Azure is built on three key tenets: manageability, scalability, and developer agility and also provides the High Availability, and Fault Tolerance. The most important thing about SQL Azure is it supports T-SQL.

Microsoft has also provided the Training Kit to get familiar with SQL Azure technology.

http://view.atdmt.com/action/mrtyou_FY10AzureDownloadthetrainingkitSecondar_1

For further reading…

http://www.microsoft.com/windowsazure/sqlazure/

http://dotnetslackers.com/articles/sql/Introduction-to-SQL-Azure.aspx