Tag Archives: SQL Server 2012

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

FirstName varchar(20) NOT NULL,
MiddleName varchar(20) NULL,
LastName varchar(20) NOT NULL
('Sachin', 'Ramesh', 'Tendulkar'),
('Diego', 'Armando', 'Maradona')
,('Diego', Null, 'Maradona')
SELECT CONCAT(FirstName + ' ', MiddleName + ' ', LastName) AS
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
SELECT Current_Value FROM sys.sequences
WHERE name= 'MySequence1' -- specify the name of the sequence

2) get the next Sequence Value:

use Adventureworks
AS NextSequenceValue

3) Restarting the Sequence Value:

USE [AdventureWorks2012]
ALTER SEQUENCE [dbo].[MySequence1]

4) Dropping a Sequence object:

use Adventureworks2012

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]
CREATE SEQUENCE [dbo].[MySequence1]
AS [int]

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

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

USE [AdventureWorks2012]
Fname VARCHAR(30),
Lname VARCHAR(30),

Step 2: Now lets insert some values into the table

(NEXT VALUE FOR Mysequence1, 'Sanchayan','Pandit')
(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]
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,
FROM sys.dm_exec_query_stats AS qs
sys.dm_exec_sql_text(qs.sql_handle)AS st
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,
FROM sys.dm_exec_query_stats AS qs
sys.dm_exec_sql_text(qs.sql_handle)AS st
sys.dm_exec_text_query_plan(qs.plan_handle,DEFAULT,DEFAULT)AS qp
WHERE st.text like '%Name of Stored Proc%'

Modified DMV’s in SQL Server 2008 R2 and SQL Server 2012

In this post i will discuss about two dynamic management views that has been enhanced with new columns in SQL Server 2008 R2 and SQL Server 2012. The DMV’s are

1) sys.dm_exec_query_stats
2) sys.dm_exec_sessions

Let us begin with [sys.dm_exec_query_stats]
So what does this view reflect. It reflects performance data for stored query plans in SQL Server plan cache. The data is aggregated and is helpful for performance analysis. When a query plan is eliminated from the plan cache, the related row is also removed. In SQL Server 2008 R2 onwards, 3 new columns have been added to this DMV. They are as follows as explained on Technet.

a) last_rows: The number of rows returned by the last execution of the query.
b) min_rows: Minimum number of rows returned by the query since last recompilation of the query and execution of the plan.
c) max_rows: Maximum number of rows returned by the query over the number of times that the plan has been executed since it was last compiled.

The next dmv that was modified with new columns are [sys.dm_exec_sessions]
This view returns a row per authenticated session on SQL Server. It shows information about all the active user connections and internal tasks that are being performed. The new columns that have been added to this view are as follows:

a) Database_id: iD of the current database for each session.
b) authenticating_database_id:
i) When the source of the session is a contained database, then the value is the db id of the authenticating database.
ii) The value will be 1 if it is a server scoped principal i.e a windows or SQL login.
iii) This will be Null if the session is that of an internal task.
c) open_transaction_count: Number of open transactions per session.

SQL Server 2012 – How to enable the “Lock Pages in Memory” Option

In this article I will discuss the feature “lock pages in memory” and demonstrate how we can enable the “Lock pages in Memory” option for SQL Server 2012 on the OS. The OS decides which accounts can utilize a process to retain data in the RAM. This would not allow the system to page out data to virtual memory on the physical disk. In SQL Server 2012, the memory architecture has been simplified for the usage of locked pages across all editions including processor. In SQL 2012 we enable this option by allowing the account which runs the SQL Server service to have this right. The below illustration shows the requirements for different editions of SQL server for Locking pages in memory. LP1If you have migrated your SQL server to SQL 2012 from SQL 2005 or SQL 2008 and if you have been using this option then you can remove the trace flag 845 from the start up parameters.

We will now see how to enable this option.

        1. Click Start, click on Run. Type gpedit.msc. Click on OK and you will find the Local group policy editor opening.
          LP2         LP3
        2. On the Local Group Policy Editor, expand Computer Configuration -> expand windows Settings -> Security settings
          LP4 LP5
        3. Open Security Settings, and then Open Local Policies and then open “User Rights Assignment” folder
        4. Once you open the User Rights Assignment folder all the policies will be displayed. Locate “Lock pages in Memory”. Double Click on “Lock pages in memory”
        5. Click on “Add user or Group” and add the account that runs SQL Server service. Restart SQL server.
          We can verify whether SQL Server 2012 is using “locked pages” by the following ways:
          1) Run the following query:

select od.node_id, os.memory_node_id, od.node_state_desc, os.locked_page_allocations_kb
from sys.dm_os_memory_nodes os
inner join sys.dm_os_nodes od on
os.memory_node_id = od.memory_node_id
where od.node_state_desc <> 'ONLINE DAC'

The locked_page_allocations_Kb will display a non-zero value.

LP10              2) DBCC MEMORYSTATUS will show the following:
LP11 I hope this article was helpful in understanding how we can enable this option for SQL Server 2012.

How to create a report using SQL Server 2012 reporting services

In this article i would like to give a demo as to how to create a report using SQL Server 2012 reporting services. I have been developing reports using this tool for some time and this attempt is to share my knowledge for the same. In this article i am assuming that SQL Server Reporting services is installed and is in a running state.

Step 1: Open the Report Manager Home page
R1Step 2: Click on the Report Builder tab.
R3Step 3: Once the Report Builder opens, you will see the following screen -> Click on “New Report” -> “Blank Report”
R4Step 4: Once click on “Blank Report” you will find the below scree. This is the report builder where we will develop our first report. The while blank area is the body of the report where we will insert a matrix. On the left hand panel are the data properties based on which the report will function. Let us go through each of the terms that we see there.
a) Built-in-Fields: Built in fields help us render in built functionality on our report page. For example if we want page number on each of our pages in a report we can use the “Page Number” field from here. We will be using them in our demo.

b) Parameters : Parameters help us to build reports based on a certain input. For e.g if we want to extract some information based on 2 dates, then we need to define parameters based on which the report will pull data and display.

c) Images: This will help us add images to the report.

d) Data Sources: very important aspect of Report Builder(RB). Data source help us define and maintain the source from which we will pull data for the report. I will explain Data source with more clarity once we start developing the report.

e) Datasets: A dataset specifies a query, query parameters, filters, and a field collection. It might be a query, table or stored procedure.
R5Let start to build our report now.

Step 5: Right click on Data Sources -> Add Data Source
R6Step 6: You will see the below screen once you click on Data Source properties. Choose the below depicted options and click on the build button
R7Step 7: Once you click on the build button, the “Connection properties” dialog box will come up. In the server name field, type in the name of the SQL server from where you want to pull data. Once you feed the server name, select the name of the database from where you want to pull data. In our example we have used the [AdventureWorksDW_WroxSSRS2012] database. Click on “Test Connection” button to verify the connection. Click on OK.
R8R9Step 8: On you click on OK you will see the “Data Source Properties” window with the connection string embedded. Click on OK. Now your data source is ready
R10R11Step 9: Now we need to create a new dataset for our report to access.Right Click on “Datasets” -> The Data set properties dialog box pops up -> Under the name type in a name of the dataset. Choose “Use a dataset embedded in my report” option and choose Datasource1 from the dropdown. Select query type as “text” and paste the below query in the text box
SELECT  vResellerSalesProdTerrDate.CalendarYear  ,vResellerSalesProdTerrDate.CalendarQuarter
  ,vResellerSalesProdTerrDate.MonthNumberOfYear  ,vResellerSalesProdTerrDate.MonthName
  ,vResellerSalesProdTerrDate.Category  ,vResellerSalesProdTerrDate.Subcategory
  ,vResellerSalesProdTerrDate.ProductName  ,vResellerSalesProdTerrDate.SalesTerritoryGroup
  ,vResellerSalesProdTerrDate.SalesTerritoryRegion  ,vResellerSalesProdTerrDate.SalesTerritoryCountry
  ,vResellerSalesProdTerrDate.SalesAmt  ,vResellerSalesProdTerrDate.OrderQty
  ,vResellerSalesProdTerrDate.Frieght FROM

Click on the fields tab below the query tab and you find the fields populated. Click on Ok and you will find your dataset ready. Below images for illustration.
Step 10: Now Click on the “Insert” tab -> Matrix Wizard
R16Step 11: Once you click on the Matrix Wizard you will find the dataset that you had created few steps back selected. Click on that dataset, click on next
R17Step 12 : Drag and drop the fields as shown in the below illustration. Click Next
R18R19Step 13: Select the Corporate Style in the Styles Tab -> Click Finish
R20Step 14: When you click on finish you will find a template which will have the fields you had selected.Give a name to your report.  Expand the Built-in Fields option and drag the “Page Number” property below the template. Save the report.
R21Now your report is ready. Click on the Run button and your report will open up in IE.
R22I hope this article was helpful in building a basic report from scratch. In my next post i will discuss how to build a report based on parameters.

%d bloggers like this: