SQL Server 2016 - Truncate at Partition level

Enhancements to TRUNCATE in SQL Server 2016:

SQL Server 2016 introduced truncation at partition level without logging at the individual rows.
This is kind of having WHERE clause to DELETE statement.

The new TRUNCATE statement in SQL Server 2016 is:

TRUNCATE TABLE dbo.PartitionedTable
WITH (PARTITIONS (2, 4, 6 TO 8))

Above statement will truncate all the partitions 2, 4, 6, 7 and 8.







SQL Server 2016 - Query Store

Query Store in SQL Server:
Query store is a new component in SQL Server that captures Queries, Query Plans, Runtime Statistics, etc. inside the database.

It is available at database level and we can enforce the SQL Server query processor to execute the queries in a specific manner by using forcing plans.

Query store collects query texts and all relevant properties, as well as query plan choices and performance metrics.

It will provide the details regarding fire queries, query plans that it is used to execute the queries which will help in trouble shooting the performance issues.

Find below some of the things that we can do easily with Query Store:
1. Conduct system-wide or database-level analysis and troubleshooting
2. Access the full history of query execution
3. Quickly pinpoint the most expensive queries
4. Get all queries whose performance regressed over time
5. Easily force a better plan from history

Typical Performance trouble shooting that we can follow through Query store is:

We can easily enable Query Store by right clicking on the database and selecting the properties:

After you enable this option then we can see Query Store folder on expanding the database:

Click on any of the options available on expanding QueryStore, you will see the window as shown below:

Select the specific query, query plan and then select the option of ForcePlan OR UnForcePaln plan for that particular query.

SQL Server 2016 - Temporal Tables

Temporal Tables:

Temporal tables are nothing but maintaining historical information for the rows by the SQL server itself for the given table.

Instead of implementing SCD for the tables by the developers, if you create the table as Temporal Table then SQL Server automatically will take care of maitaining the history for the changing data.

To Create the temporal tables we should use below syntax (Here i have created Department table):

CREATE TABLE dbo.Department ( DepartmentID int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, DepartmentName varchar(50) NOT NULL, ManagerID int NULL, ValidFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL DEFAULT CAST('1900-01-01' AS DATETIME2) , ValidTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL DEFAULT CAST('9999-12-31' AS DATETIME2) , PERIOD FOR SYSTEM_TIME ( StartDate, EndDate ) ) WITH ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory) ); GO

1. Two additional Start & End date Audit columns of datetime2 datatype for capturing the validity of records. You can use any meaningful column name here, we will use StartDate and EndDate column names

2. Both the column names have to be specified in PERIOD FOR SYSTEM_TIME (StartDate, EndDate) clause along with the columns.

3. Specify WITH (SYSTEM_VERSIONING = ON) option at the end of the CREATE TABLE statement with optional (HISTORY_TABLE = <<History_Table_Name>>) option.

Once you create the table it will automatically create two tables internally

one for current data and other for the historical data.

When you are querying the table, you can mention the table name that you have mentioned in Create table statement that will automatically call historical table internally and will return the results.

If we want to see only historical records then we can query the historical table that we have mentioned in our create table script.

If there are no updates to the data then no rows will be available in the historical table.

Limitation of Temporal Tables:

1. We can't query Temporal tables using Linked Server

2. History table should not have constraints (PK, FK, Table or Column constraints).

3. INSERT and UPDATE statements cannot reference the SYSTEM_TIME period columns.

4. TRUNCATE TABLE is not supported till SYSTEM_VERSIONING is ON and we can truncate if we Switch OFF this setting

5. Direct modification of the data in a history table is not permitted.

6. INSTEAD OF triggers are not permitted on either the tables.

7. Usage of Replication technologies is limited.

For more information refer Temporal tables Part 1 and Part 2

Differences between Stored Procedures and Functions

Store Procedure:

Stored Procedure is a group of sql statements that has been created once and stored in server database. It’s pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called.

Functions:

Function is not pre-compiled object it will execute every time whenever it was called.

Built in FUncitons: These are defined by the SQL server itself and we will not be able to do any modifications to these functions.

User Defined Funcitons: These will be created by the users using CREATE FUNCTION statement. We can have Zero or more input parameters to the funciton and every function should return a value.

Differences between Stored Procedure and User Defined Function in SQL Server

Sr.No.	User Defined Function	Stored Procedure
1	Function must return a value.	Stored Procedure may or may not return values.
2	It Will allow only Select statements	It can have select statements as well as DML statements such as insert, update, delete etc.
3	It will allow only input parameters	It can have both input and output parameters.
4	It will not allow us to use try-catch blocks.	For exception handling we can use try catch blocks.
5	Transactions are not allowed within functions.	Can use transactions within Stored Procedures.
6	We can use only table variables, it will not allow using temporary tables.	Can use both table variables as well as temporary table in it.
7	Stored Procedures can't be called from a function.	Stored Procedures can call functions.
8	Functions can be called from a select statement.	Procedures can't be called from Select/Where/Having and so on statements. Execute/Exec statement can be used to call/execute Stored Procedure.
9	A UDF can be used in join clause as a result set.	Procedures can't be used in Join clause

Unpivot in SQL Server

UnPivoting:

UnPivoting is nothing but converting rows data into columns.

In order to Unpivot the data we need to identify:

a. columns which are not participating in UnPivoting

b. Columns which are participating in UnPivoting

c. Data to be presented in UnPivoted columns

Let's see how we can do this in SQL:

CREATE TABLE dbo.MobileNum

(

PersonID INT,

Home BIGINT,

Mobile BIGINT,

Work BIGINT,

Personal BIGINT

)

Insert the data into the table:

INSERT INTO dbo.MobileNum

(

PersonID

,Home

,Mobile

,Work

,Personal

)

VALUES

(1, 1234567890, 8956231470, 5874693210, 9856231470)

,(2, NULL, NULL, 8745236910, 6541239870)

,(3, 1230546987, 5289631047, NULL, NULL)

,(4, NULL, 8547236901, NULL, 8546971203)

,(5, NULL, NULL, NULL, 8546320179)

We have to Unpivot this data such that, Home, Mobile, Work, Personal should be displayed as PhoneType column and and values available in those columns should be displayed in PhoneNum column.

SELECT

PersonID

,PhoneType

,PhoneNum --UnPivoted columns

FROM

(SELECT

PersonID

,Home

,Mobile

,Work

,Personal --Source Data

FROM

dbo.MobileNum

) SRC

UNPIVOT

(

PhoneNum For PhoneType IN (Home, Mobile, Work, Personal) --UnPivoting

) AS UNP

If you see the query, first we have selected the required data from the MobileNum table and then on top of that we are writing Unpivoting criteria for PhoneType and PhoneNum.

We can leave any value from the PhoneType if we don't want that PhoneType data in the result set.

In UnPivot clause, we are saying PhoneNUm should be displayed in PhoneType and the value of PhoneTypes we need to consider should be Home, Mobile, Work, Personal

On executing the above query our result set will look like this.

Want to learn Pivoting go here.

Pivoting in SQL Server

Pivoting:

Pivoting is nothing but converting the data available in columns into rows.

In order to pivot the data we need to identify:

a. columns which are not participating in Pivoting

b. Columns which are participating in Pivoting

c. Aggregate operation that needs to be performed

Let's see how we can do this in SQL:

CREATE TABLE [dbo].[UnPivotData]

(

[PersonID] [int] NOT NULL,

[PhoneType] [nvarchar](100) NULL,

[PhoneNum] [bigint] NULL

)

Insert the data into the table:

INSERT [dbo].[UnPivotData]

(

[PersonID]

,[PhoneType]

,[PhoneNum]

)

VALUES

(1, 'Home', 1234567890)

,(1, 'Mobile', 8956231470)

,(1, 'Work', 5874693210)

,(1, 'Personal', 9856231470)

,(2, 'Work', 8745236910)

,(2, 'Personal', 6541239870)

,(3, 'Home', 1230546987)

,(3, 'Mobile', 5289631047)

,(4, 'Mobile', 8547236901)

,(4, 'Personal', 8546971203)

,(5, 'Personal', 8546320179)

We have to pivot this data such that, Home, Mobile, Work, Personal should be displayed as columns and PhoneNum should be the value available in those columns.

SELECT

PersonID

,Home

,Mobile

,Work

,Personal --Pivoted columns

FROM

(SELECT

PersonID

,PhoneType

,PhoneNum --Source data

FROM

dbo.UnPivotData

) SRC

PIVOT

(

MAX(PhoneNum) FOR PhoneType IN (Home, Mobile, Work, Personal) --Pivoting

) PIV

If you see the query, first we have selected the required data from the UnPivotData table and then on top of that we are writing pivoting criteria for PhoneType.

We can leave any value from the PhoneType if we don't want that PhoneType data in the result set.

Aggregate operator that we have chosen over here is MAX, because if we have more than one record for those columns which are not participating in Pivoting then SQL Server should know which one it should take from those values.

On executing the above query our result set will look like this.

Want to learn Unpivoting go here.