Category Archives: SQL Server 2012

Extended Events in SQL Server 2012 – Part 1

In SQL Server 2008, Microsoft introduced a feature called Extended Events. Extended Events helped in collecting and analyzing event driven data about the SQL Server instance. Extended Events introduced lesser load than trace or profiler events on the server. But in SQL Server 2008 there was no GUI that allowed direct communication with the events. Complex T-SQL had to be written to fetch the information which was returned in xml format.

In SQL Server 2012, MS has introduced a GUI in the Management Studio (SSMS)  that allows working with Extended Events and viewing event related data a much simpler process. In this article we will understand and see step by step how we can create and manage Extended events via the GUI that is built in the SSMS.

Step 1: Connect to SQL Server 2012 instance -> Open Management Tab -> Open Extended Events -> Right Click on Sessions -> New Session
Ee1
Step 2 -> In the new session dialog box type in a name of the session. In this case we will use ‘Session1’. For now we will not choose any template.
EE2

Step 3 -> Click on Events tab -> On the below window you will find many Events listed from which we need to pick an event or multiple events that we want to capture. Once selected we need to click on the button to add it. For this article we will use the Events
a) query_post_execution_showplan and b) sql_statement_completed
EE3AEE4Step 4 -> Click on Data Storage -> Here you specify how you can capture data so you can view it later. Here we will save it as Event File. Mention the path where we would save the file -> Click on OK.
EE5Step 5 -> Go to management studio -> Under sessions you will find New session listed. It would be in a stopped state as this is the default behavior. Right Click on the session and click on ‘Start Session
EE6EE7EE8Step 6 -> Now right click on the created session and select ‘Watch Live Data’. If there is no activity on the instance you will not see any data.
EE9EE10Step 7 -> Let us fire few queries on the AdventureWorks2012 database and watch the window. Following are queries that we would run on the database for testing. After running the queries go back and open the ‘Watch Live Data’ window again. Click on the Event data and observe the below.

USE AdventureWorks2012
go
SELECT * FROM Production.Product pp
WHERE pp.ProductID > 40
go
SELECT * FROM Production.BillOfMaterials Pb
WHERE pb.BillOfMaterialsID > 100
go

EE11EE12EE13In this article we have seen how we can start a new event trace from SSMS. In my next article we will explore more into the new GUI feature that MS has added to SQL Server 2012.


Advertisement

Data page restore from the SSMS UI in SQL Server 2012

How about a UI that lets you restore data pages in SQL Server? Well that’s exactly what we have in SQL Server 2012. The UI allows you to select the pages from the backup to restore rather than restoring the entire database.

Here are the steps how we can restore a data page in SQL Server 2012

Step 1:dp1Step 2: Once you click on Check database pages you will have list of pages that are corrupt. Click on Add. Choose the correct backup set and click on OK. The corrupt data page\s will be restored.
dp2

Timeline – Point in time recovery with SQL Server 2012

How about a nice GUI that lets you select the time to which you want to restore all your available backups in sequence in one go. A TIMELINE of restoration points. That would be fantastic and that is what SQL server 2012 has introduced. Point in time recovery via the TIMELINE feature. Let us understand this feature with an example.

I have created a database called MyDB which I will restore to a point in time. I have kept the DB in full recovery mode. The following backups have been taken.
1) 1 full backup
2) 1 Diff backup
3) 5 t-log backups
Snapshot of the backups
tl2Now lets recover this database to a point in time from the available backup via TIMELINE option.
Step 1: Right click on the database -> Tasks-> restore -> database
tl3Step 2: select device. Give a new name to the restored DB
tl4Step 3: Click on the add button and select all the backup files
tl5Step 4: Click on ok and you will see the below screen. Observe the option restore to
tl6tl7Step 5: Lets click on TIMELINE button and this is what you will see
tl8Step 6: Select the option -> Specific date and time. Once you do that you will be able to move the scroll bar to any time highlighted in green. In tis case i have moved it to 6:02:03 PM. This is the time i would like to have my DB restored to. Click on OK.
tl9Once you click on OK, you will find the below screen with the required backup only. Also observe that a tail log backup will also be taken.
tl10Step 7: Click on options tab. We will go with restore with recovery. Click on OK
tl11tl12Once you click on OK you will find the db restored with recovery to the name given above
tl13

FIRST_VALUE – New analytical function in SQL Server 2012

In this article we will be taking a look at the new analytical function FIRST_VALUE  in SQL Server 2012. Let us understand them with examples.

This function returns the first value from an ordered set of values. The syntax for the same is

FIRST_VALUE ( [scalar_expression ] )
OVER ( [ partition_by_clause ]
order_by_clause [ rows_range_clause ] )

Let us create a sample table and understand this on SQL Server 2012. The following script does the same. Lets observe the output of the script below

CREATE TABLE [dbo].[MyFirstLastVaL](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) NOT NULL,
[MarksInMaths] [int] NOT NULL )
GO
insert into MyFirstLastVaL values('Alan',99)
insert into MyFirstLastVaL values('Brent',29)
insert into MyFirstLastVaL values('Charlie',25)
insert into MyFirstLastVaL values('David',37)
insert into MyFirstLastVaL values('Ello',15)
insert into MyFirstLastVaL values('Frank',59)
insert into MyFirstLastVaL values('Grant',16)
insert into MyFirstLastVaL values('Honey',18)
insert into MyFirstLastVaL values('Irene',86)
insert into MyFirstLastVaL values('Jack',76)
insert into MyFirstLastVaL values('Kalou',66)
insert into MyFirstLastVaL values('Lisa',68)
insert into MyFirstLastVaL values('Mike',98)
insert into MyFirstLastVaL values('Lisa',28)
insert into MyFirstLastVaL values('Mike',45)
insert into MyFirstLastVaL values('Alan',19)
insert into MyFirstLastVaL values('Brent',92)
insert into MyFirstLastVaL values('Charlie',35)
insert into MyFirstLastVaL values('Irene',36)
insert into MyFirstLastVaL values('Jack',67)
insert into MyFirstLastVaL values('Kalou',96)

SELECT id,name,MarksInMaths,
FIRST_VALUE(marksinmaths) OVER (ORDER BY marksinmaths) MyFirstVal
FROM MyFirstLastVaL

fvl1Now let us use the function with partition by clause and observe the output

SELECT id,name,MarksInMaths,FIRST_VALUE(MarksInMaths)
OVER (PARTITION BY Name ORDER BY MarksInMaths) MyFirstVal
FROM MyFirstLastVaL

fvl2In the first case the query simply returns the first value of the entire data set whereas in the second case the output is based on the marksinmaths column, so the FIRST_VALUE is different but the same for each partition.

EOMONTH() – End of the month – SQL Server 2012

So how do we calculate the last date of the month which is 4 months from now?

SQL Server 2012 presents EOMONTH() function. Let us understand this function with an example.

DECLARE @MyDate datetime
SET @MyDate = GETDATE()
SELECT EOMONTH (@MyDate,4)
SELECT EOMONTH (@MyDate,-4)
as LastDayOfTheMonth

eom2
So what do we find from the 2 outputs. Well it gives me the last date of the 4th month from the current month i.e. 4 months from now would be April and the last date of April is 30th. The second output gives the last date of 4 months previous i.e August. This function would be very handy in calculations where the last date of the month is a critical factor. Previously to achieve this we would have to do a bit of manipulation.

Pagination – Using OFFSET and FETCH in SQL Server 2012

OFFSET and FETCH are two new clauses introduced in SQL Server 2012 that allows us to extract a portion of rows from the result set. When we need to display a large result set to the user, the best way of going about it is to split them .i.e use pagination. In SQL Server 2012, we can achieve pagination by using the ‘OFFSET’ and ‘FETCH’ commands. Let us understand this with an example:

USE AdventureWorks2012
GO
SELECT pp.ProductId,PP.Name,pp.ProductNumber,
pp.DaysToManufacture,pp.ListPrice
FROM Production.Product PP
ORDER BY pp.productid

OandF1
The query returns 504 rows as depicted above. So what do i do if i need to fetch only a portion of the above rows?

Enter OFFSET and FETCH. How? let us understand this with an example

USE AdventureWorks2012
GO
SELECT PP.ProductID,PP.Name,pp.ProductNumber,
pp.DaysToManufacture,pp.ListPrice
FROM Production.Product PP
ORDER BY pp.ProductID
OFFSET 100 ROWS
FETCH NEXT 10 ROWS ONLY

OandF2Here, the OFFSET tells the query to ignore the first 100 rows and then return only the following 10 rows. This is very easy to use and a quick way to return just a portion of records.
Few Limitations of the OFFSET and FETCH clause as stated in msdn:
a) ORDER BY is mandatory to use OFFSET and FETCH clause.
b) OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
c) TOP cannot be combined with OFFSET and FETCH in the same query expression.
d) The OFFSET/FETCH row count expression can be any arithmetic, constant, or parameter expression that will return an integer value. The row count expression does not support scalar sub-queries.

TRY_PARSE – Conversion function in SQL Server 2012

In this article I will discuss about the TRY_PARSE function in SQL Server 2012. Conversion functions helps us avoid errors when dealing with different data types. Let us understand TRY_PARSE with an example.

SELECT TRY_PARSE ('12-18-2013' AS datetime) AS Alpha
SELECT TRY_PARSE ('2013' AS decimal) AS Beta
SELECT TRY_PARSE ('2013.00' AS decimal) AS Gamma
SELECT TRY_PARSE ('2013.0000' AS float) AS Theta
SELECT TRY_PARSE ('Arsenal' AS float) AS Delta

The TRY_PARSE() function can convert any string value to a Numeric or Date/Time format. If the passed string value cannot be converted to Numeric or Date/Time format, it will result to a NULL.

Lets have a look at the output of the above
TP2In the above case it would not convert ‘Arsenal’ to a date time or Numeric value and hence it results in NULL output. TRY_PARSE function is not a native SQL SERVER function, instead it is a CLR dependent function.

IIF – The InLine conditional Statement in SQL Server 2012

IIF is a new inline conditional statement in SQL Server. We can pass an expression that can be evaluated to either true or false to the function and it returns one value for true and another one for false.

Let us understand this with an example:

DECLARE @MyTeam nvarchar(40) = 'Manchester United'
DECLARE @YourTeam nvarchar(40) = 'Arsenal'
SELECT IIF (@MyTeam = @YourTeam, 'True Devils',
'False Gooners') AS YouAre

The output of the above as below. In this we have passed two different values.
IIF1Now let us pass two same values and see how this works:

DECLARE @MyTeam nvarchar(40) = 'Manchester United'
DECLARE @YourTeam nvarchar(40) = 'Manchester United'
SELECT IIF (@MyTeam = @YourTeam, 'True Devils',
'False Gooners') AS YouAre

IIF2
IIF is very useful where a straightforward comparison has to be made without writing case statements.

How to use ‘WITH RESULT SETS’ in SQL Server 2012

In SQL Server 2012, the execute statement has been enhanced with an option called ‘WITH RESULT SETS‘. So let us understand what this does.

This option enables us to change the column names and data types of the returning result set from the stored procedure. Let us understand this with an example.

Create a new stored procedure

USE AdventureWorks2012
go
CREATE PROC Usp_products
AS
SELECT ProductID,Name,ProductNumber FROM Production.Product
ORDER BY ProductID

Let us now execute the above procedure and see the outcome
withresultset1Now let us use this new option and change the name of the columns and their data types while the procedure returns the output at run time.

EXEC Usp_products
WITH RESULT SETS
(
(
SerialNumber varchar(15),
Name1 varchar(30),
ProductNumber1 varchar(30)
)
)

withresultset2So this is how we can use this option to tweak the output set to different data types and column names from an stored procedure.

Format() function in SQL Server 2012

SQL Server 2012 introduces this new function called FORMAT which returns a value in the specified format and also can optionally apply a regional format. This function relies on the .NET Framework.

In this article we will explore this function and see how it can be implemented.

FORMAT() accepts 3 parameters. The first parameter is the VALUE parameter where we pass on the date value or a numeric value. The second parameter is the .NET Framework format string. The format parameter is case sensitive. The third parameter is the culture. This can be any culture supported by the .NET Framework.

Let us now explore this function with some examples:
1) Display the date using Bengali Culture

DECLARE @date DATETIME = '12/04/2013';
SELECT FORMAT ( @date, 'MMMM dddd dd yyyy', 'Bn-IN' )
 AS DateInBangla;

format12) Display the date using Tamil Culture

DECLARE @date DATETIME = '12/04/2013';
SELECT FORMAT ( @date, 'MMMM dddd dd yyyy', 'Ta-IN' ) AS
DateInTamil;

format23) Display an amount into different currency structures

SELECT FORMAT(100, 'C', 'en-GB') AS Pounds,
FORMAT(100, 'C', 'en-US') AS Dollars,
FORMAT(100, 'C', 'es-ES') AS Euro,
FORMAT(100, 'C', 'en-IN') AS Rs;
GO

format34) Display current date time in US format

SELECT FORMAT(GETDATE(), N'"Time now is "dddd MMMM dd, yyyy', 'en-US')
AS USTimeStamp;

format4For more details on this function please refer to this msdn link.

CONCAT() function in SQL Server 2012

In this article I will discuss the new function concat() in SQL Server 2012. This function performs a concatenation operation. We need to pass CONCAT() a number of string arguments and it will concatenate, or join them together and return an output string.
The basic syntax is as follows:
CONCAT ( string_value1, string_value2 [, string_valueN ] )

Let us now understand with an example how we can use this function and how it is different from the concatenation operator. Let us create a new table and insert some values in the table and then use a query that will use this function

CREATE TABLE MyConcatTable
(
FirstName varchar(20) NOT NULL,
MiddleName varchar(20) NULL,
LastName varchar(20) NOT NULL
)
INSERT INTO MyConcatTable
VALUES
('Sachin', 'Ramesh', 'Tendulkar'),
('Diego', 'Armando', 'Maradona')
,('Diego', Null, 'Maradona')
SELECT CONCAT(FirstName + ' ', MiddleName + ' ', LastName) AS
CustomerName
FROM MyConcatTable

The output of the above as shown below
concat1The concat function joins the columns and returns a single string. Now the big question is how is it different from the below

SELECT FirstName + ' ' + MiddleName + ' ' + LastName AS CustomerName
FROM MyConcatTable

Let us run the above code and see the outcome
concat2The 3rd value in the table has a null in the middle name column. Normal concatenation would not handle that thus resulting in a null output but the concat() would automatically remove the null and join the next corresponding string value.

I hope this short article was helpful in understanding the new function in SQL Server 2012 called concat().

Life is a SEQUENCE of events – Part 2

In my previous article on Sequence we went through how we can create and implement Sequence.In this short article I will demonstrate how we can achieve the following:

1) How to get the current value of the Sequence
2) How to get the next Sequence Value
3) Restarting the Sequence Value
4) Dropping a Sequence object

1) current value of the Sequence
To find the current value of a Sequence we need to query the system view sys.sequences.

USE AdventureWorks2012
go
SELECT Current_Value FROM sys.sequences
WHERE name= 'MySequence1' -- specify the name of the sequence
GO

2) get the next Sequence Value:

use Adventureworks
go
SELECT (NEXT VALUE FOR MySequence1)
AS NextSequenceValue

3) Restarting the Sequence Value:

USE [AdventureWorks2012]
GO
ALTER SEQUENCE [dbo].[MySequence1]
RESTART  WITH 100001
INCREMENT BY 10
NO MINVALUE
MAXVALUE 200000

4) Dropping a Sequence object:

use Adventureworks2012
go
DROP SEQUENCE MySequence1;
go

Life is a SEQUENCE of events

In this article I will discuss this new feature in SQL Server 2012 called Sequence and how we can use this feature.

Sequence in SQL Server 2012 is a database object and can be considered as an alternative to Identity property which we set at a table level. The primary difference between the two is that sequence is scoped to the entire database while Identity is scoped to a specific column in a specific table.

If we expand the programmability node under a specific database in SQL Server 2012 then the last object that we would find is Sequences.
seq1The primary purpose of sequence is that it can used between different tables within the database and help maintain a synced copy of seed values between multiple tables that are referencing each other as it is not limited to a single table.
Another objective where Sequence can be very handy is performance gain because using identity means that SQL has to fetch the next value from the disk whereas sequences would be residing in the memory in case it is used with the cache option which we will see later.

Let us now understand with an example how we can create and implement sequence.

Creating a Sequence:
Step 1: Right click on the Sequence object under the Programmability node
Step 2: Select ‘New Sequence’ and you will see the below dialog box
seq2Let us go through and understand each field in this dialog box before proceeding

a) Sequence name: This is where you give a name of the sequence. By default it picks up the date and time of the sequence creation.
b) Sequence schema: The schema to which you would assign the sequence.
c) Data type: The data type of the sequence. By default it is bigint.
d) Start Value: The starting value of the sequence
e) Increment By: The value by which you want to increment the series.
f) Minimum value: The least value of the series. This comes into play when you cycle the series.
g) Maximum value : The upper bound of the series post which the series will either cycle or stop.
h) Cycle : This property specifies whether the series should restart from the minimum value or throw an error when its minimum or maximum value is exceeded. The default cycle value for new sequence objects is NO CYCLE.
i) Cache: This increases performance by minimizing the number of disk IO that are required to generate sequence numbers. If a cache is set to 100 is chosen, SQL Server does not keep 100 individual values cached. It only caches the current value and the number of values left in the cache.

Step 3: Lets now fill up the dialog box and create our first sequence.
seq3The TSQL for the same is given below

USE [AdventureWorks2012]
GO
CREATE SEQUENCE [dbo].[MySequence1]
AS [int]
START WITH 1000
INCREMENT BY 1
MAXVALUE 100000
CACHE
GO

When you click on ok button or you run the code, you will find a new sequence created under the Sequences node.
seq4

Now let us implement this object and understand how it works:
Step 1: Create a new table

USE [AdventureWorks2012]
GO
CREATE TABLE mytable
( ID INT PRIMARY KEY,
Fname VARCHAR(30),
Lname VARCHAR(30),
)
go

Step 2: Now lets insert some values into the table

INSERT INTO mytable
(ID,FName,Lname)
VALUES
(NEXT VALUE FOR Mysequence1, 'Sanchayan','Pandit')
INSERT INTO mytable
(ID,FName,Lname)
VALUES
(NEXT VALUE FOR Mysequence1, 'Abhishek','Agrawal')

Lets have a look at the rows inserted
seq5As you observe now, the id field now has incremented by 1 starting with 1000.

So this is how we can implement Sequence in SQL Server 2012. I will deep dive into Sequence in my next article and we will understand how we can play around this new feature in SQL Server 2012.

 

How to find the last execution details of a stored procedure in SQL Server

In this article I will demonstrate how we can find the last execution details of a stored procedure in SQL Server 2012 or SQL Server 2008 R2.With DMV’s getting modified in these 2 editions of SQL Server, the amount of information we can obtain from the plan cache can be handy for investigation purpose.
When a stored procedure is created or recompiled, a plan for the same is created and cached in the plan cache. Whenever the same stored procedure is executed, the plan is recalled from the SQL memory for execution purpose. The details of an execution is stored internally in SQL Server which can be fetched via the dynamic management views.

A key element in this process is that the plan has to be in the plan cache for us to derive the information. By any chance if the SQL Server gets restarted or the plan cache is cleared then the information would not be available.

Let us see how we can fetch the execution details with an example. I have used the Adventureworks2012 database for demonstration purpose
SQL Server 2012:
Execute the below mentioned SP on SQL Server 2012

USE [AdventureWorks2012]
GO
EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 135

The execution produces 4 rows
ED1Now let us find the execution details of this stored procedure from the plan cache. Open another Query editor and execute the below mentioned query. The query searches the plan cache for the execution details of the stored procedure, whose name we have filtered in the last line of the query

SELECT qs.sql_handle,qs.creation_time,qs.last_execution_time,
qp.dbid,qs.execution_count,qs.last_rows,qs.last_logical_reads,
qs.last_logical_writes,qs.last_physical_reads,st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle)AS st
CROSS APPLY
sys.dm_exec_text_query_plan(qs.plan_handle,DEFAULT,DEFAULT) AS qp
WHERE st.text like '%USPGET%'---filter by name of the SP

The output would be as follows:
ED2Now if we observe the outcome we would find the following information very handy
1. Last execution Time
2. Execution Count
3. Last_rows: This depicts the number of rows as output when the SP executed last.
4. Last_Logical_Reads
5. Last_Logical_Writes
6. Last_Physical_Reads

In SQL Server 2008 R2, the column that would be missing is Last_rows. So the query that you can use in SQL Server 2008 R2 would be as below

SELECT qs.sql_handle,qs.creation_time,
qs.last_execution_time,
qp.dbid,qs.execution_count,qs.last_logical_reads,
qs.last_logical_writes,qs.last_physical_reads,st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle)AS st
CROSS APPLY
sys.dm_exec_text_query_plan(qs.plan_handle,DEFAULT,DEFAULT)AS qp
WHERE st.text like '%Name of Stored Proc%'
%d bloggers like this: