Useful tools

1.  ZoomIt: ZoomIt is a screen zoom and annotation tool for technical presentations that include application demonstrations. 
2. Devart SQL Complete: Code completion, refactoring and formatting tool.
3. sp_whoisactive: sp_whoisactive is a comprehensive activity monitoring stored procedure that works for all versions of SQL Server from 2005 through 2017.
4. sp_SQLskills_helpindex: Tool to get index information. sp_helpindex displays limited information. This tool displays lot of information like included columns, filters, etc.

Difference between Table Variables, Temporary Tables (Hash Tables) and Temporal Tables

Table Variables

• Table variable is a special data type that can be used to store a result set for processing at a later time. 
• Table variable is primarily used for temporary storage of a set of rows returned as the result set of a table-valued function. 
• Functions and variables can be declared to be of type table. 
• Table variables can be used in functions, stored procedures, and batches. 
• To declare variables of type table, use DECLARE @local_variable
• A table variable behaves like a local variable. It has a well-defined scope. This is the function, stored procedure, or batch that it is declared in.

Example:

DECLARE @myTable TABLE

(

       Id INT PRIMARY KEY,

       FirstName VARCHAR(100) UNIQUE,

       LastName VARCHAR(100) NOT NULL

)

INSERT INTO @myTable(Id, FirstName, LastName) VALUES (1,'A','A')

INSERT INTO @myTable(Id, FirstName, LastName) VALUES (2,'B',NULL) --Fail

INSERT INTO @myTable(Id, FirstName, LastName) VALUES (3,'A','A') --Fail

SELECT * from @myTable

SELECT * INTO @This_Will_Not_Work FROM @myTable

Only constraints allowed on the table variables are:

1. Primary Key
2. UNIQUE
3. NULL

Further, SELECT * INTO will not work

Temporary Tables (Hash Tables)

There are two types of temporary tables; one is local and the other is global.

Local Temporary Tables 

Local temporary tables are the tables stored in tempdb. Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session. They are specified with the prefix #, for example #table_name and these temp tables can be created with the same name in multiple windows.

Example:

--Local temporary table

CREATE TABLE #myLocalTable

(

       Id INT PRIMARY KEY,

       FirstName VARCHAR(100) UNIQUE,

       LastName VARCHAR(100) NOT NULL DEFAULT('')

)

INSERT INTO #myLocalTable(Id, FirstName, LastName) VALUES (1,'A','A')

INSERT INTO #myLocalTable(Id, FirstName) VALUES (2,'B') --Default LastName

Global Temporary Tables  

Global temporary tables are also stored in tempdb. Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. They are specified with the prefix #, for example ##table_name.

Example:

--Global temporary table

CREATE TABLE ##myLocalTable

(

       Id INT PRIMARY KEY,

       FirstName VARCHAR(100) UNIQUE,

       LastName VARCHAR(100) NOT NULL DEFAULT('')

)

INSERT INTO ##myLocalTable(Id, FirstName, LastName) VALUES (1,'A','A')

INSERT INTO ##myLocalTable(Id, FirstName) VALUES (2,'B') --Default LastName

Temporal Tables

SQL Server 2016 introduced support for system-versioned temporal tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time.

Example:

CREATE TABLE dbo.TemporalTable

(

       ID INT NOT NULL PRIMARY KEY CLUSTERED,

       FirstName VARCHAR(100),

       LastName VARCHAR(100),

       ValidFrom datetime2 GENERATED ALWAYS AS ROW START,

       ValidTo datetime2 GENERATED ALWAYS AS ROW END,

       PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

)

WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalTableHistory));

--Insert a record

INSERT INTO dbo.TemporalTable(ID, FirstName, LastName) VALUES(1,'Akash','Agrawal')

--Current table will have one record. Notice the ValidFrom and ValidTo columns

SELECT * FROM dbo.TemporalTable

SELECT * FROM dbo.TemporalTableHistory --zero records

--Now, update record in the table

UPDATE TemporalTable

SET FirstName = 'Aakash'

WHERE ID = 1

--Data will be updated in current table. Notice the ValidFrom and ValidTo columns

SELECT * FROM dbo.TemporalTable

--History table will have one record with ValidFrom and ValidTo columns populated.

SELECT * FROM dbo.TemporalTableHistory

--Update again

UPDATE TemporalTable

SET FirstName = 'Aakaash'

WHERE ID = 1

--Notice multiple rows in History table

SELECT * FROM dbo.TemporalTable

SELECT * FROM dbo.TemporalTableHistory

--How to query History data

SELECT *

FROM dbo.TemporalTable

FOR SYSTEM_TIME BETWEEN '2018-01-22 08:12:26.2254807' AND '2018-01-22 08:14:44.7907589'

SELECT *

FROM dbo.TemporalTable

FOR SYSTEM_TIME AS OF '2018-01-22 08:14:44.7907589'

SELECT *

FROM dbo.TemporalTable

FOR SYSTEM_TIME FROM '2018-01-22 08:12:26.2254807' TO '2018-01-22 08:14:44.7907589'

SELECT *

FROM dbo.TemporalTable

FOR SYSTEM_TIME CONTAINED IN('2018-01-22 08:12:26.2254807' , '2018-01-22 08:15:44.7907589')

SELECT *

FROM dbo.TemporalTable

FOR SYSTEM_TIME ALL

ORDER BY ValidFrom

Use of functions on indexed columns in predicates

Use of functions on indexed columns which are used in predicates results in index not getting used for seek operation.

For example:

Use tempdb

set nocount on

go

IF OBJECT_ID(N'Customers', N'U') IS NOT NULL

   DROP TABLE dbo.Customers;

  

CREATE TABLE dbo.Customers (

 customer_nbr INT NOT NULL PRIMARY KEY,

 customer_name VARCHAR(35) NOT NULL);

CREATE NONCLUSTERED INDEX ix_customers

ON dbo.Customers(customer_name);

INSERT dbo.Customers VALUES(1, 'Joe');

INSERT dbo.Customers VALUES(2, 'Leo');

INSERT dbo.Customers VALUES(3, 'Dave');

INSERT dbo.Customers VALUES(4, 'Lenny');

INSERT dbo.Customers VALUES(5, 'Larry');

INSERT dbo.Customers VALUES(6, 'Lefty');

INSERT dbo.Customers VALUES(7, 'Lemur');

GO

If I want to write a query on this table to get all the customers whose first name starts with 'L'.

SELECT customer_name

FROM dbo.Customers

WHERE LEFT(customer_name, 1) = 'L';

Looking at the execution plan for above query. We can see index scan in here and not seek:

Now, what if we rewrite this query without the use of LEFT function as below:

SELECT customer_name

FROM dbo.Customers

WHERE customer_name LIKE 'L%';

Looking at the execution plan for above query shows us index seek and the output of query is same as before.

Functions on indexed column used in predicates makes them non-sargable and impacts performance.

Reference:

https://sqlbits.com/Sessions/Event14/Common_TSQL_Mistakes

SQL Server important links

• Joins

              https://www.techonthenet.com/sql_server/joins.php

• Indexes

              https://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/

• Index seek, scan, lookup and others in execution plan

              https://www.simple-talk.com/sql/performance/identifying-and-solving-index-scan-problems/

• Temporary tables vs table variables

              https://www.mssqltips.com/sqlservertip/2825/sql-server-temp-table-vs-table-variable-performance-testing/

• SARGable expressions

1. https://sqlsunday.com/2014/01/19/sargable-expression-performance/
2. https://datatellblog.wordpress.com/2016/11/24/sql-server-query-optimization-sargable-functions/
3. https://datatellblog.wordpress.com/2016/11/24/sql-server-query-optimization-sargable-functions/

           

Store and retrieve data into XML from table in SQL Server

The aim of this post is to store data in a table in the form of XML along with schema, and to retrieve the same from XML in a tabular structure.

Let us take an example of a table say Employee:

CREATE TABLE Employee

(

       ID INT,

       FName VARCHAR(200),

       LNAME VARCHAR(200),

       Salary FLOAT

);

INSERT INTO Employee(ID, FName, LName, Salary)

VALUES(1,'A','B',10.1),

(2,'C','D',12.123),

(3,'E','F',123.13)

DECLARE @xml XML

SELECT @xml = (

       SELECT

              ID,

              FName,

              LName,

              Salary

       FROM Employee

       FOR XML AUTO, ELEMENTS, XMLSCHEMA('AnySchema'), ROOT('Root');

)

SELECT @xml

This is how Table can be stored in XML format along with schema. If you see the output XML, it will have two portions, one with schema and another with actual data.

Below, is how we can get back the data from XML into tabular structure.

DECLARE @XSD XML, @sql VARCHAR(MAX), @TableName VARCHAR(100)

SET @XSD = (SELECT

              x.query('.')

       FROM @XML.nodes('/Root/*[1]') a (x))

SELECT

       @XML = (SELECT

                     CAST(STUFF(

                     CAST(@XML.query('*[1]/*') AS VARCHAR(MAX))

                     , 1

                     , CHARINDEX(

                     '</xsd:schema>'

                     , CAST(@XML.query('*[1]/*') AS VARCHAR(MAX))

                     ) + 12

                     , ''

                     ) AS XML)

              FOR XML RAW ('Root'))

SELECT

       @XML = CAST(REPLACE(CAST(@XML AS VARCHAR(MAX)), ' xmlns="AnySchema"', '') AS XML)

SELECT

       @TableName = a.x.value('@name', 'varchar(100)')

FROM @XSD.nodes('/*/*') a (x)

SELECT

       @XSD = (SELECT

                     a.x.query('.')

              FROM @XSD.nodes('/*/*[2]/*/*') a (x))

SELECT

       @XSD = CAST(REPLACE(CAST(@XSD AS VARCHAR(MAX)), 'p1:', '') AS XML)

IF @XSD IS NULL

BEGIN

SELECT

       'XML Schema was not specified'

RETURN

END

IF EXISTS (SELECT

              1

       FROM Tempdb..sysobjects

       WHERE id = OBJECT_ID('Tempdb..#TableDefinition'))

DROP TABLE #TableDefinition

CREATE TABLE #TableDefinition

(

       ID INT IDENTITY(1,1),

       ColumnName VARCHAR(255),

       ColumnDataType1 VARCHAR(255),

       ColumnDataType2 VARCHAR(255),

       ColumnDataType2Length VARCHAR(255),

       hasLength INT

)

INSERT INTO #TableDefinition

              SELECT

                     a.x.value('@name', 'varchar(100)') ColumnName

                     ,REPLACE(CAST(a.x.value('@type', 'sysname') AS VARCHAR(100)), 'sqltypes:', '') ColumnDataType1

                     ,REPLACE(CAST(a.x.value('*[1]/*[1]/@base', 'sysname') AS VARCHAR(100)), 'sqltypes:', '') ColumnDataType2

                     ,REPLACE(CAST(a.x.value('*[1]/*[1]/*[1]/@value', 'sysname') AS VARCHAR(100)), 'sqltypes:', '') ColumnDataType2Length

                     ,0 AS hasLength

              FROM @XSD.nodes('/*/*') a (x)

UPDATE #TableDefinition

SET    hasLength = 1

       ,ColumnDataType2Length =

       CASE

              WHEN ColumnDataType2 IN ('numeric', 'decimal') THEN RTRIM(ColumnDataType2Length) + ',10'

              ELSE ColumnDataType2Length

       END

WHERE ColumnDataType2Length IS NOT NULL

DECLARE @TableDefinition VARCHAR(MAX)

SET @TableDefinition = (SELECT

              CAST(CASE

                     WHEN hasLength = 1 THEN ColumnName + ' ' + ColumnDataType2 + '(' + ColumnDataType2Length + '),'

                     ELSE ColumnName + ' ' + ColumnDataType1 + ','

              END AS VARCHAR(MAX))

       FROM #TableDefinition a

       FOR XML PATH (''))

SELECT

       @TableDefinition = SUBSTRING(@TableDefinition, 0, LEN(@TableDefinition))

IF EXISTS (SELECT

              1

       FROM Tempdb..sysobjects

       WHERE id = OBJECT_ID('Tempdb..#XML'))

DROP TABLE #XML

SELECT

       @XML AS XMLDATA INTO #XML

SELECT

       @sql =

       ' DECLARE @handle INT, @PrepareXMLStatus INT, @XML2 XML

  SET @XML2 = (SELECT XMLDATA FROM #XML);

  EXEC @PrepareXMLStatus = sp_xml_preparedocument @handle OUTPUT, @XML2;

  SELECT *

  FROM OPENXML(@handle, ''/Root/' + @TableName + ''', 2)

       WITH (' + @TableDefinition + ')

  EXEC sp_xml_removedocument @handle;'

EXEC (@sql)