Feeds:
Posts
Comments

Posts Tagged ‘SQL Server 2012’

Paging became quite simpler & easy to script and manage by using OFFSET & FETCH NEXT keywords in SQL Server 2012 & above. I have written quite a detailed article earlier about it and implemented it in my most of the solutions wherever required. However, when you implement/use paging in your script, you face a big challenge, that is, to find the total number of records in that particular result-set.

Given below are the three methods which you can use to get the total row count from OFFSET / FETCH NEXT.
Before proceeding with the solutions, let me create a sample.

Sample :

USE AdventureWorks2014
GO
-- Create Sample Table Table
CREATE TABLE [dbo].[SalesOrderDetail](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[LineTotal] [numeric](38, 6) NOT NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

-- Insert bulk data into sample table
-- It may take few minutes depends upon the server performance
INSERT INTO [dbo].[SalesOrderDetail]
SELECT * FROM [SALES].[SalesOrderDetail]
GO 100

-- Verfiy the data
Select * from [dbo].[SalesOrderDetail]
GO

Method 1 : Using COUNT(*) OVER()

USE AdventureWorks2014
GO
DECLARE
  @PageSize INT = 10,
  @PageNum  INT = 1;

SELECT
  [SalesOrderID]
, [SalesOrderDetailID]
, [CarrierTrackingNumber]
, [OrderQty]
, [ProductID]
, [SpecialOfferID]
, [TotalCount]= COUNT(*) OVER()
FROM [dbo].[SalesOrderDetail]
ORDER BY SalesOrderID
  OFFSET (@PageNum-1)*@PageSize ROWS
  FETCH NEXT @PageSize ROWS ONLY;
GO
--OUTPUT

row count using Offset 1.1

Method 2 : Using Common Table Expression

USE AdventureWorks2014
GO
DECLARE
    @PageSize INT = 10,
    @PageNum  INT = 1;

;WITH Main_CTE AS(
	SELECT [SalesOrderID]
	, [SalesOrderDetailID]
	, [CarrierTrackingNumber]
	, [OrderQty]
	, [ProductID]
	, [SpecialOfferID]
	FROM [dbo].[SalesOrderDetail]
)
, Count_CTE AS (
	SELECT COUNT(*) AS [TotalCount]
	FROM Main_CTE
)
SELECT *
FROM Main_CTE, Count_CTE
ORDER BY Main_CTE.SalesOrderID
    OFFSET (@PageNum-1)*@PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY
GO
--OUTPUT

row count using Offset 1.1

Method 3 : Using Cross Apply

USE AdventureWorks2014
GO
DECLARE @PageSize INT = 10,
        @PageNum  INT = 1;

 SELECT
  [SalesOrderID]
, [SalesOrderDetailID]
, [CarrierTrackingNumber]
, [OrderQty]
, [ProductID]
, [SpecialOfferID]
, [TotalCount]
FROM [dbo].[SalesOrderDetail]

CROSS APPLY (SELECT COUNT(*) TotalCount
FROM [dbo].[SalesOrderDetail] ) [Count]
ORDER BY SalesOrderID
OFFSET (@PageNum-1)*@PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY
GO
--OUTPUT

row count using Offset 1.1

All of the above methods give you the same result-sets. Lets view their performance given below.

S.No

Method

CPU Time

Elapsed Time

1

COUNT(*) OVER()

30654 ms

40372 ms

2

Common Table Expression

11762 ms

7665 ms

3

Cross Apply

11794 ms

7373 ms

Conclusion :
On the basis of above results, I would recommend that you either use Common Table Expression or Cross Apply to get the faster results.

Note : The above queries have been tested on ~12 Million records.

Advertisements

Read Full Post »

Recently, I was developing a report for one of my customers, who migrated their database from SQL Server 2005 to SQL Server 2012 Enterprise Edition. The database belongs to a legacy system , so I found couple of conversion issues, however the major issue I faced in developing the report was the date format. The required date format was Mon-yyyy and the data available in the table was in the format of yyyymm.

Before proceeding with the solution, let me create a sample to demonstrate the problem.

USE tempdb
GO
DROP TABLE tbl_Sample
GO
CREATE TABLE tbl_Sample
(
 [ID] INT,
 [Date] varchar(6)
)
GO
INSERT INTO tbl_Sample VALUES (1,'201401')
INSERT INTO tbl_Sample VALUES (2,'201402')
INSERT INTO tbl_Sample VALUES (3,'201403')
INSERT INTO tbl_Sample VALUES (4,'201404')
INSERT INTO tbl_Sample VALUES (5,'201405')
INSERT INTO tbl_Sample VALUES (6,'201406')
INSERT INTO tbl_Sample VALUES (7,'201407')
INSERT INTO tbl_Sample VALUES (8,'201408')
INSERT INTO tbl_Sample VALUES (9,'201409')
INSERT INTO tbl_Sample VALUES (10,'201410')
INSERT INTO tbl_Sample VALUES (11,'201411')
INSERT INTO tbl_Sample VALUES (12,'201412')
GO
SELECT * FROM tbl_Sample
GO

Convert YYYYmm 1.1

Solution 1 : Traditional Method
This is the old traditional method, which is compatible with SQL Server 2005 and above. In this method, you need to use many functions (LEFT, DATENAME, CONVERT, YEAR) to convert yyyymm date format to Mon-yyyy. Given below is the script.

USE tempdb
GO
SELECT
LEFT(DATENAME(MM,CONVERT(DATE,[Date]+'01')),3)
+ '-'
+ CONVERT(VARCHAR(4),YEAR(CONVERT(DATE,[Date]+'01'))) As [Mon-Year]
FROM tbl_Sample
GO

Convert YYYYmm 1.2

Solution 2 : New Method
This is the new method, which is compatible with SQL Server 2012 and above. In this script, I will use a newly shipped (in SQL Server 2012) function namely Format to reduce the line of script in order to convert yyyymm to Mon-yyyy. Given below is the script.

USE tempdb
GO
SELECT FORMAT(CONVERT(DATE,[Date]+'01'),'MMM-yyyy') As [Mon-Year]
FROM tbl_Sample
GO

Convert YYYYmm 1.2

Read Full Post »

Conversion of Select statement result set into Insert statement is a very frequent activity that a DBA/Developer needs to create, mostly when they need to migrate small amount of data from one instance to another or from one environment to another. I recently created one of my customer’s new branch database from other branches database and came across this scenario. Fortunately, we do have a solution since SQL Server 2005 but it was very complicated specially when you need to do it for the tables as they have numerous columns. The reason I am writing this solution is that you can do it in few clicks in SQL Server 2012 and above.
Before proceeding with the solution, I would like to create a sample to demonstrate the solution.

Sample :
Given below is a select statement in which I modified the result set to demonstrate.

USE [AdventureWorks2012]
GO
SELECT [DepartmentID]
      ,[Name] + ' Department' As [Name]
      ,[GroupName]
      ,Getdate() As [ModifiedDate]
   FROM [HumanResources].[Department]
GO

Convert select.1.1

Given below are the two solutions, one of them is traditional solution and another one you can use it in SQL Server 2012 and above.

Solution 1 : Using String concatenation (Traditional Method)
In this solution, you need to concatenate the result set of the Select statement in order to convert into Insert statement (with some modifications in the data). You need to make sure that single quotes(‘) are in proper locations. In addition, if the data in the result set does not belong to string data type you must convert into string data type to concatenate. In case, the table is having identity column, you must pass the column name in the INSERT STATEMENT as well with SET IDENTITY_INSERT. The reason why I DO NOT recommend this solution is because if you have more number of columns in the table, it takes more time for the development and debug as well.

USE [AdventureWorks2012]
GO
SELECT
'INSERT INTO tbl_sample (
 [DepartmentID],[Name],[GroupName],[ModifiedDate])
VALUES(' + CONVERT(VARCHAR(50),[DepartmentID])
+ ',''' + [Name] + ' Department'' ,'
+ ''''+ [GroupName] + ''','
+ ''''+ CONVERT(VARCHAR(50),GETDATE(),120) + ''')'
  FROM [HumanResources].[Department]
GO

Convert select.1.2

Solution 2 : Using Generate Script (New Method)
This method is applicable to SQL Server 2012 and above and you will find it quite simple. Let me explain this method using two simple steps.

Step 1 :
First of all, you need to develop a select statement like I did it in the sample based on your requirements and INSERT INTO A TABLE as shown below.

 USE [AdventureWorks2012]
GO
SELECT [DepartmentID]
      ,[Name] + ' Department' As [Name]
      ,[GroupName]
      ,Getdate() As [ModifiedDate]
  INTO [tbl_Department_Sample]  -- Result set inserted in a table
  FROM [HumanResources].[Department]
GO

Convert select.1.3

Step 2 :
Your select statement result set has been inserted into the table([tbl_Department_Sample]). Now, you just need to generate the script (data only) of the table ([tbl_Department_Sample]) using Generate Script feature in SQL Server 2012 and above.

Let me know if you come across these scenarios and their solutions.

Read Full Post »

Change Data Capture (CDC) is one of the frequently used features in SQL Server 2008 and above, it records any (CDC enabled) table’s changes and stores in audit tables. Recently, I upgraded one of my client’s database from SQL Server 2005 database to SQL Server 2012 and one of the key reasons to upgrade is to utilize the new features in the upgraded version. Once I started enabling CDC feature in few tables of the database it gave me given below error.

Message Number: 22939

Severity : 16

Error Message: The parameter @supports_net_changes is set to 1, but the source table does not have a primary key defined and no alternate unique index has been specified.

Error Generation:
I presume that CDC has been enabled on this particular database. Let me create a sample table to demonstrate this error.

USE AdventureWorks2012
GO
--Create Sample Table
CREATE TABLE tbl_Sample
(
 [ID] INT NOT NULL,
 [NAME] VARCHAR(50)
)
GO

-- Enable CDC feature on this table with net changes support parameter.
 USE AdventureWorks2012
 GO
 EXEC sys.sp_cdc_enable_table
 @source_schema = N'dbo',
 @source_name = N'tbl_sample',
 @role_name = NULL,
 @supports_net_changes = 1
GO
--OUTPUT

Error Message 22939.1

Msg 22939, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 194
The parameter @supports_net_changes is set to 1, but the source table does not have a primary key defined and no alternate unique index has been specified.

Ooopps…… I am NOT able to enable CDC on sample table.

Resolution:
The reason behind this error is that you do not have either primary key or unique index on the sample table and you want to enable net changes support in CDC. Before proceeding with the resolution, you should check whether you can create a Primary Key or Unique Index on the table to avoid such error.
Given below is the simple script to add Primary Key OR Unique Index in the sample table.

--Create Primary Key
USE AdventureWorks2012
GO
ALTER TABLE dbo.tbl_Sample ADD PRIMARY KEY (ID)
GO

-- Create Unique Index
USE AdventureWorks2012
GO
ALTER TABLE tbl_Sample
ADD CONSTRAINT UX_Constraint UNIQUE (ID)
GO
--OUTPUT

You can execute any one of the above scripts and can create Primary Key or Unique Index in the sample table.

Once you executed the above script, you can easily enabled the CDC with supports_net_changes as shown below.

 USE AdventureWorks2012
 GO
 EXEC sys.sp_cdc_enable_table
 @source_schema = N'dbo',
 @source_name = N'tbl_sample',
 @role_name = NULL,
 @supports_net_changes = 1
 GO
--OUTPUT

Command(s) completed successfully.

Error Message 22939.2

Conclusion :
Remember, whenever you need to enable CDC with supports_net_changes, make sure that the particular table HAS either Primary Key or Unique Index in order to avoid this error.

Read Full Post »

How to delete recent connection from Connect to Server window in SSMS is a very common issue and it becomes frustrated if you connect many servers on daily basis and SQL Server Management Studio (SSMS) pile up all the new server names in the list of Connect to Server window as shown below.

list of connection.1.1

I have been facing this common problem since SQL Server 2005 and it has been reported on Connect as well. Fortunately we do have a solution explained in this article that demonstrate step by step how to delete the recent connection list using mru.dat and SqlStudio.bin in SQL Server 2005  and SQL Server 2008 respectively. However, this is NOT smart solutions because it deletes all your recent connections including the active ones as well.

In SQL Server 2012, a proper solution came in the picture and I believe, this is much better than the earlier solution. Let me demonstrate it step by step.

Step 1 :
In order to open “Connect to Server” window, first of all you need to select file menu and click on connect object explorer.. in SSMS as shown below.

list of connection.1.3

Step 2 :
Once you open the Connect to Server window, you will find all recent connections in the server name as shown below but the drawback is you cannot determine what belongs to SQL Authentication and what to Windows.

list of connection.1.1

Step 3 :
Now, you need to eliminate the connection. In order to do it, you need to select the particular connection and press DELETE button from the keyboard. Once you press delete button, it will delete that particular connection but it will remain as selected in the server name dropdown list as shown below. If you need to delete multiple connections, you need to select server one by one and press delete button to delete  it,  as multiple selection is NOT allowed.

list of connection.1.2

Let me know if you come across this scenario and how did you resolve it.

Read Full Post »

In my earlier article, I have explained that creating a directory / sub directory in any file table is same like creating a directory / sub directory in windows itself. However, sometimes you need to create these directories and sub directories via T-SQL. I came across a case recently where I had to create a series of sub directories inside a directly using T-SQL.

Prerequisite :
I strongly recommend that you read the given below articles to have a clear understanding about FileTables.

  1. FileTables – Prerequisites
  2. FileTables – Data Definition Language (DDL)
  3. FileTables – Data Manipulation Language (DML)

Let me explain the solution step by step.

Step 1 :
First of all you need to create a sequence (A new object shipped with SQL Server 2012) to generate a series of IDs. However you can use any other techniques to create these series.

--This script is compatible with SQL Server 2012 and above.
--DROP SEQUENCE [dbo].[NewID]
--GO
CREATE SEQUENCE [dbo].[NewID]
 AS [bigint]
 START WITH 100000000
 INCREMENT BY 1
 CACHE
GO

Step 2 :
In this step, you need to create the given below procedure that can generate a new ID for your sub directory. Remember that filetable maintains directory and sub directory IDs in a hierarchy ID datatype. So you must get the parent folder ID (directory hierarchy ID) in order to create a child folder (sub directory). You can get further detail about hierarchy ID here. This stored procedure is self explanatory.

--This script is compatible with SQL Server 2012 and above.
--DROP PROCEDURE dbo.GetNewPathLocator
--GO
CREATE PROCEDURE dbo.GetNewPathLocator
@MainFolderID HIERARCHYID,
@SubDirectoryPath VARCHAR(MAX) OUTPUT

AS
BEGIN       

	DECLARE @FirstSeqNum sql_variant,
	        @LastSeqNum  sql_variant

     EXEC sys.sp_sequence_get_range
     @sequence_name = N'dbo.NewID'
   , @range_size = 3
   , @range_first_value = @FirstSeqNum OUTPUT
   , @range_last_value = @LastSeqNum OUTPUT 

    SELECT @SubDirectoryPath = CONCAT(COALESCE(@MainFolderID.ToString(),'/'),
	CONVERT(VARCHAR(20),@FirstSeqNum) ,'.',
	CONVERT(VARCHAR(20),Convert(BIGINT,@FirstSeqNum)+1) ,'.',
	CONVERT(VARCHAR(20),@LastSeqNum) ,'/')

END
GO

Step 3 :
Now, it is time to create a sub directory in any directory using T-SQL. I already created a directory inside a filetable as shown below.

creating sub directory in filetable

Let us create a sub directory inside that directory.

--This script is compatible with SQL Server 2012 and above.
DECLARE @MainFolderPath AS HIERARCHYID
--You must have the given below file table (dbo.DataBank)
--and directory (IT) inside file table in the existing database.
SELECT @MainFolderPath=path_locator FROM dbo.DataBank
WHERE [name]='IT'

DECLARE @SubDirectoryPath varchar(max)
EXEC dbo.GetNewPathLocator
  @MainFolderID=@MainFolderPath
, @SubDirectoryPath = @SubDirectoryPath OUTPUT 

--SELECT @SubDirectoryPath

INSERT INTO dbo.DataBank (name,path_locator,is_directory,is_archive)
VALUES ('sub directory', @SubDirectoryPath, 1, 0);
GO

Given below is the new sub directory created inside IT folder via T-SQL.

creating sub directory in filetable 2

Conclusion :
Remember, filetable keeps directory and sub directory IDs in Hierarchy ID datatype. So you must go through this concept. In addition, the whole process is self explanatory.

Let me know if you came across this situation and how you handled it.

Read Full Post »

In my earlier articles, I discussed & demonstrated all basic activities in Change Data Capture (CDC) in any SQL Server database. However, I did not discuss about the retention period of CDC data in the database. In most cases, we need to modify the retention period due to our business requirements. I came across this query many times, if we can modify retention period, if Yes, how ?

BY DEFAULT, CDC configure the data retention period of 3 days. In other words, CDC keeps all the data changes history for 3 days ONLY and the rest will be cleaned (deleted). In most of my clients, I configured CDC for few tables with the retention period of 10 days and few of them leave it as a default (3 days).

Let me explain how to view / modify the retention period of CDC from in few easy steps.

Step 1 – View the existing RETENTION period in CDC:
First of all, you should check the existing retention period of CDC. It is also important to know that CDC keeps the retention period in minutes. Given below script will show the retention period in minutes as well as days by using dbo.cdc_jobs (a change data capture system table).

--This script is compatible with SQL Server 2008 and above.
USE msdb
GO
SELECT [retention] As [Retention period in minutes]
,[retention]/60/24 As [Retention period in days]
FROM
dbo.cdc_jobs
WHERE job_type ='cleanup'
GO
--OUTPUT

Change data capture.4.4_part1

Step 2 – Modify the RETENTION period in CDC :
As you can see in the above result that the retention period is 3 days (4320 minutes). Lets modify it to 10 days by using sys.sp_cdc_change_job (a system stored procedure of CDC).

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO
DECLARE @New_retention_period_in_minutes AS SMALLINT
DECLARE @New_retention_period_in_days AS TINYINT

--Set the retention period for 10 days
SET @New_retention_period_in_days = 10

--Convert 10 days into minutes
SET @New_retention_period_in_minutes= @New_retention_period_in_days*60*24

--Select the total number of minutes in 10 days to check.
SELECT @New_retention_period_in_minutes
As [Retention period in minutes]

--Update minutes in the CDC job
EXECUTE sys.sp_cdc_change_job
    @job_type = N'cleanup',
    @retention = @New_retention_period_in_minutes;
GO
--OUTPUT

Change data capture.4.4_part2

Step 3 – Verify the RETENTION period in CDC:
You need to execute the same script as Step 1 but the output would be different this time, as we have successfully updated the retention period to 10 days in Step 2.

--This script is compatible with SQL Server 2008 and above.
USE msdb
GO
SELECT [retention] As [Retention period in minutes]
,[retention]/60/24 As [Retention period in days]
FROM
dbo.cdc_jobs
WHERE job_type ='cleanup'
GO
--OUTPUT

Change data capture.4.4_part33

An update of my blog is available at my twitter or you can like my Facebook page or subscribe via email by mentioning your email address in the ‘follow blog’ section.

Read Full Post »

Older Posts »