Feeds:
Posts
Comments

Archive for the ‘Solutions’ Category

Policy Based Management (PBM) is one of the greatest features shipped with SQL Server 2008. It made DBA’s life easier when it comes to implementation, compliance as well as changing any database/ server properties, especially when you need to implement /check some database / server settings across the databases/ servers. Recently, we have implemented Delayed durability database setting to Forced in one of our clients’ databases using Policy Based Management across the databases. Delayed durability is actually one of the best features shipped with SQL Server 2014. What made it best actually is its handling vs performance. You can just enable with a single statement and you get a huge performance boast.

 Note: Delayed durability is disabled by default. 

In this article, I will show you step by step, how to implement any database / server settings across the databases / servers using few clicks with the help of Policy Based Management.

Step 1:
Open SQL Server Management Studio (SSMS) and Select Management >>Policy Management >>Conditions and right click on it to Select New Condition… as shown below.

PBM - Delayed Durability 1.0

Step 2:
Now, we are in condition building screen, so lets build the 1st condition and that is to check delayed durability in all user databases which is online ONLY. Given below is the configuration. Do not forget to give a proper name to this condition.

PBM - Delayed Durability 1.1

Step 3:
Lets repeat Step 1 and open the condition creation screen. This time we will create the condition to check database setting which is delayed durability=FORCED. Given below is the configuration.

PBM - Delayed Durability 1.2

Step 4:
Now we are done with the conditions, so the next step is to build the Policy. In order to build the policy you need to Select Management >>Policy Management >> Policies and right click on it to select New Policy… as shown below.

PBM - Delayed Durability 1.4

Step 5:
Now, we are in policy creation screen, here we need to give a proper Policy Name and then select the conditions created in Step 2 and Step 3 shown below in red and blue color border respectively.  In  other words, we are trying to make policy which checks the delayed durability is forced or NOT in all user databases which is online.

PBM - Delayed Durability 1.6

Usually I choose the Evaluation Mode to “On Demand” showing above because it is the easiest way to evaluate and fix any discrepancy across the databases / servers, however, you can select On Schedule as well and Press OK.

Step 6:
Now, the policy has been created and you can see it under policies as shown below. Just right click on and select Evaluate to evaluate the policy as shown below.

PBM - Delayed Durability 1.7

Step 7:
You are in evaluation screen and you can see all the user databases shows in red color because none of them is having database settings to delayed durability= force.

PBM - Delayed Durability 1.8

Step 8:
Lets click on View Details to see the difference in database settings as shown below. You can see that Policy is expecting the Delayed durability = force but the actual value is disabled.

PBM - Delayed Durability 1.9

Step 9:
Lets select the databases where you want to change the database settings of delayed durability to FORCE and press Apply button. It gives the given below messages. Press YES button.

PBM - Delayed Durability 1.10

Step 10:
Once the policy has been implemented, policy based management will automatically evaluate the policy again and shows as green color icon as shown below.

PBM - Delayed Durability 1.11

Let me know if you have implemented any databases / servers settings using Policy Based Management.

Read Full Post »

Sometimes, we come across a case where we have given one date and we need to calculate multiple dates based on this date. This can be achieved in the earlier versions of the SQL Server (earlier than 2022), but we had to use multiple functions like CONVERT(), DATEADD(), DATEDIFF() etc with lots of complexity.

Fortunately, a new function shipped in SQL Server 2022 namely DATETRUNC() which helps us to achieve this scenario easily.

Compatibility Level:

Your database compatibility level MUST be 160 or higher to use this new function.

 ALTER DATABASE tempdb SET COMPATIBILITY_LEVEL = 160 

Prerequisite:

In order to set the first day of the week as Monday, we need to run the below script.


SET DATEFIRST 1;
GO

First Date of Previous Week

  • Old Approach

In the old approach, we have to extract first date of the previous week using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							 AS [Current Date]
	  , CONVERT(DATE
	  , DATEADD(WEEK
	  , DATEDIFF(WEEK, 0, @Date) -1, 0)) AS [First Date Of Previous Week];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the first date of the previous week using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date Date;
SET @Date = GETDATE();

SELECT  @Date					  AS [Current Date]
	  , DATEADD(WEEK, -1
	  , DATETRUNC(WEEK, @Date))   AS [First Date Of Previous Week];
GO
--OUTPUT

Last Date of Previous Week

  • Old Approach

In the old approach, we have to extract last date of the previous week using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							  AS [Current Date]
      , CONVERT(DATE
	  , DATEADD(WEEK
	  , DATEDIFF(WEEK, 0, @Date) +0, -1)) AS [Last Date Of Previous Week];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the last date of the previous week using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date					  AS [Current Date]
	  , DATEADD(DAY, -1
	  , DATEADD(WEEK, 0
	  , DATETRUNC(WEEK, @Date)))  AS [Last Date Of Previous Week];
GO
--OUTPUT

First Date of Current Week

  • Old Approach

In the old approach, we have to extract first date of the current week using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							 AS [Current Date]
	  , CONVERT(DATE
	  , DATEADD(WEEK
	  , DATEDIFF(WEEK, 0, @Date) +0, 0)) AS [First Date Of Current Week];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the first date of the current week using DATETRUNC() function as shown below.

DECLARE @Date Date;
SET @Date = GETDATE();

SELECT  @Date				   AS [Current Date]
	  , DATETRUNC(WEEK, @Date) AS [First Date of Current Week];
GO
--OUTPUT

Last Date of Current Week

  • Old Approach

In the old approach, we have to extract last date of the current week using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							  AS [Current Date]
      , CONVERT(DATE
	  , DATEADD(WEEK
	  , DATEDIFF(WEEK, 0, @Date) +1, -1)) AS [Last Date Of Current Week];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the last date of the current week using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE()

SELECT  @Date					 AS [Current Date]
      , DATEADD(DAY, -1
	  , DATEADD(WEEK, 1
	  , DATETRUNC(WEEK, @Date))) AS [Last Date Of Current Week];
GO
--OUTPUT

First Date of Next Week

  • Old Approach

In the old approach, we have to extract first date of the next week using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							 AS [Current Date]
	  , CONVERT(DATE
	  , DATEADD(WEEK
	  , DATEDIFF(WEEK, 0, @Date) +1, 0)) AS [First Date Of Next Week];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the first date of the next week using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date					 AS [Current Date]
	  , DATEADD(WEEK, 1
	  , DATETRUNC(WEEK, @Date))  AS [First Date Of Next Week];
GO

--OUTPUT

Last Date of Next Week

  • Old Approach

In the old approach, we have to extract last date of the next week using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							  AS [Current Date]
      , CONVERT(DATE
	  , DATEADD(WEEK
	  , DATEDIFF(WEEK, 0, @Date) +2, -1)) AS [Last Date Of Next Week];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the last date of the next week using DATETRUNC() & DATEADD() function as shown below.


DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date					 AS [Current Date]
      , DATEADD(DAY, -1
	  , DATEADD(WEEK, 2
	  , DATETRUNC(WEEK, @Date))) AS [Last Date Of Next Week];
GO
--OUTPUT

Conclusion:

We used DATETRUNC() function to achieve it and found it easier & simpler as compared to earlier version of SQL Server.

Read Full Post »

Sometimes, we come across a case where we have given one date and we need to calculate multiple dates based on this date. This can be achieved in the earlier versions of the SQL Server (earlier than 2022), but we had to use multiple functions like CONVERT(), DATEADD(), DATEDIFF() etc with lots of complexity.

Fortunately, a new function shipped in SQL Server 2022 namely DATETRUNC() which helps us to achieve this scenario easily.

Compatibility Level:

Your database compatibility level MUST be 160 or higher to use this new function.

 ALTER DATABASE tempdb SET COMPATIBILITY_LEVEL = 160 

First Date of Previous Month

  • Old Approach

In the old approach, we have to extract first date of the previous month using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							  AS [Current Date]
	  , CONVERT(DATE
	  , DATEADD(MONTH
	  , DATEDIFF(MONTH, 0, @Date) -1, 0)) AS [First Date Of Previous Month];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the first date of the previous month using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date					 AS [Current Date]
	  , DATEADD(MONTH, -1
	  , DATETRUNC(MONTH, @Date)) AS [First Date Of Previous Month];
GO
--OUTPUT

Last Date of Previous Month

  • Old Approach

In the old approach, we have to extract last date of the previous month using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							   AS [Current Date]
      , CONVERT(DATE
	  , DATEADD(MONTH
	  , DATEDIFF(MONTH, 0, @Date) +0, -1)) AS [Last Date Of Previous Month];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the last date of the previous month using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date					 AS [Current Date]
      , DATEADD(DAY, -1
	  , DATETRUNC(MONTH, @Date)) AS [Last Date Of Previous Month];
GO
--OUTPUT

First Date of Current Month

  • Old Approach

In the old approach, we have to extract first date of the current month using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							  AS [Current Date]
	  , CONVERT(DATE
	  , DATEADD(MONTH
	  , DATEDIFF(MONTH, 0, @Date) +0, 0)) AS [First Date Of Current Month];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the first date of the current month using DATETRUNC() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date					AS [Current Date]
	  , DATETRUNC(MONTH, @Date) AS [First Date of Current Month];
GO
--OUTPUT

Last Date of Current Month

  • Old Approach

In the old approach, we have to extract last date of the current month using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							   AS [Current Date]
      , CONVERT(DATE
	  , DATEADD(MONTH
	  , DATEDIFF(MONTH, 0, @Date) +1, -1)) AS [Last Date Of Current Month];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the last date of the current month using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date					  AS [Current Date]
      , DATEADD(DAY, -1
	  , DATEADD(MONTH, 1
	  , DATETRUNC(MONTH, @Date))) AS [Last Date Of Current Month];
GO
--OUTPUT

First Date of Next Month

  • Old Approach

In the old approach, we have to extract first date of the next month using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							  AS [Current Date]
	  , CONVERT(DATE
	  , DATEADD(MONTH
	  , DATEDIFF(MONTH, 0, @Date) +1, 0)) AS [First Date Of Next Month];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the first date of the next month using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date					 AS [Current Date]
	  , DATEADD(MONTH, 1
	  , DATETRUNC(MONTH, @Date)) AS [First Date of Next Month];
GO
--OUTPUT

Last Date of Next Month

  • Old Approach

In the old approach, we have to extract last date of the next month using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							   AS [Current Date]
      , CONVERT(DATE
	  , DATEADD(MONTH
	  , DATEDIFF(MONTH, 0, @Date) +2, -1)) AS [Last Date Of Next Month];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the last date of the next month using DATETRUNC() & DATEADD() function as shown below.


DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date					  AS [Current Date]
      , DATEADD(DAY, -1
	  , DATEADD(MONTH, 2
	  , DATETRUNC(MONTH, @Date))) AS [Last Date Of Next Month];
GO
--OUTPUT

Conclusion:

We used DATETRUNC() function to achieve it and found it easier & simpler as compared to earlier version of SQL Server.

Read Full Post »

Sometimes, we come across a case where we have given one date and we need to calculate multiple dates based on this date. This can be achieved in the earlier versions of the SQL Server (earlier than 2022), but we had to use multiple functions like CONVERT(), DATEADD(), DATEDIFF() etc with lots of complexity.

Fortunately, a new function shipped in SQL Server 2022 namely DATETRUNC() which helps us to achieve this scenario easily.

Compatibility Level:

Your database compatibility level MUST be 160 or higher to use this new function.

 ALTER DATABASE tempdb SET COMPATIBILITY_LEVEL = 160 

First Date of Previous Year

  • Old Approach

In the old approach, we had to extract first date of the previous year using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.


DECLARE @Date DATE;
SET @Date = GETDATE();
 
SELECT @Date                            AS [Current Date]
     , CONVERT(DATE
     , DATEADD(Year
     , DATEDIFF(Year, 0, @Date) -1, 0)) AS [First Date Of Previous Year];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the first date of the previous year using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();
   
SELECT @Date                   AS [Current Date]
     , DATEADD(YEAR, -1 
     , DATETRUNC(YEAR, @Date)) AS [First Date Of Previous Year]; 
GO
--OUTPUT

Last Date of Previous Year

  • Old Approach

In the old approach, we had to extract last date of the previous year using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();
 
SELECT @Date                             AS [Current Date]
     , CONVERT(DATE
     , DATEADD(YEAR
     , DATEDIFF(YEAR, 0, @Date) +0, -1)) AS [Last Date Of Previous Year];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the last date of the previous year using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();
 
SELECT @Date                    AS [Current Date]
     , DATEADD(DAY, -1
     , DATETRUNC(YEAR, @Date))  AS [Last Date Of Previous Year];
GO
--OUTPUT

First Date of Current Year

  • Old Approach

In the old approach, we had to extract first date of the current year using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();
  
SELECT @Date                         AS [Current Date]
     , CONVERT(DATE
     , DATEADD(YEAR
     , DATEDIFF(YEAR, 0, @Date), 0)) AS [First Date of Current Year];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the first date of current year using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE; 
SET @Date = GETDATE();
 
SELECT @Date                  AS [Current Date]
     , DATETRUNC(YEAR, @Date) AS [First Date of Current Year];
GO
--OUTPUT

Last Date of Current Year

  • Old Approach

In the old approach, we had to extract last date of the current year using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT @Date                             AS [Current Date]
     , CONVERT(DATE
	 , DATEADD(YEAR
	 , DATEDIFF(YEAR, 0, @Date) +1, -1)) AS [Last Date Of Current Year];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the last date of current year using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE
SET @Date = GETDATE()
   
SELECT @Date                    AS [Current Date]
     , DATEADD(DAY, -1
     , DATEADD(YEAR, 1
     , DATETRUNC(YEAR, @Date))) AS [Last Date of Current Year];
GO
--OUTPUT

First Date of Next Year

  • Old Approach

In the old approach, we had to extract first date of the next year using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();
  
SELECT @Date                            AS [Current Date]
     , CONVERT(DATE
     , DATEADD(Year
     , DATEDIFF(Year, 0, @Date) +1, 0)) AS [First Date Of Next Year];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the first date of the next year using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();
  
SELECT @Date				   AS [Current Date]
	 , DATEADD(YEAR, 1 
	 , DATETRUNC(YEAR, @Date)) AS [First Date Of Next Year]; 
GO
--OUTPUT

Last Date of Next Year

  • Old Approach

In the old approach, we had to extract last date of the next year using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();
   
SELECT @Date                             AS [Current Date]
     , CONVERT(DATE
     , DATEADD(Year
     , DATEDIFF(Year, 0, @Date) +2, -1)) AS [Last Date Of Next Year];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the last date of the next year using DATETRUNC() & DATEADD() function as shown below.


DECLARE @Date DATE
SET @Date = GETDATE();

SELECT @Date                    AS [Current Date]
     , DATEADD(DAY, -1
	 , DATEADD(YEAR, 2
	 , DATETRUNC(YEAR, @Date))) AS [Last Date Of Next Year];
GO
--OUTPUT

Conclusion:

We used DATETRUNC() function to achieve above mentioned dates and found it easier and simpler as compared to earlier version of SQL Server.

Read Full Post »

To test any functionality in SQL Server, we need sample databases to test different scenarios. Few of the sample databases are already available as shown below and you can download and use for FREE.

  1. AdventureWorks
  2. WideWorldImporters
  3. Northwind
  4. Pubs

Sometimes, we need a customized sample table to test one of the SQL Server features. In such cases, above mentioned sample databases are not enough. So we need to create our own test table with some data too. Since there was no proper function available to generate a sample table in earlier version of SQL Server (before 2022), we used CAST(), RAND() and many other functions to achieve it somehow.

In SQL Server 2022, a new function came called GENERATE_SERIES(), which was a long awaited function. One of the usages of this function is that it can easily generate sample table having as much data as we want.

Compatibility Level:

Your database compatibility level MUST be 160 or higher to use this function. Given below is the query to change your database compatibility level.

ALTER DATABASE tempdb SET COMPATIBILITY_LEVEL = 160

Let me show you in the below example, how can we use GENERATE_SERIES() function to generate sample table in SQL Server 2022.

Example :

USE tempdb
GO

CREATE TABLE [Customers]
(
 [CustomerID]         INT,
 [CustomerName]       VARCHAR(250),
 [RegistrationDate]   DATE,
 [EmailAddress]       VARCHAR(100),
 [Address]            VARCHAR(500),
 [PhoneNumber]        VARCHAR(100),
 [DiscountPercentage] NUMERIC(18,6)
);
GO

DECLARE @Start INT = 1;    --Starting point of the record
DECLARE @Stop  INT = 1000; --Ending point of the record

INSERT INTO [Customers]
(
 [CustomerID],
 [CustomerName],
 [RegistrationDate],
 [EmailAddress],
 [Address],
 [PhoneNumber],
 [DiscountPercentage]
)

SELECT  
  value                                           AS [CustomerID]
, CONCAT('John',' - ',value)                      AS [CustomerName]
, DATEADD(day,value,'1753-01-01')                 AS [RegistrationDate]
, CONCAT('John',value,'@raresql.com')             AS [EmailAddress]
, CONCAT(value,' N. 10th Street')                 AS [Address]
, '1 (11) '+ RIGHT(CONCAT('0000000000',value),11) AS [PhoneNumber]
, CONVERT(NUMERIC(18,6),value)/@Stop              AS [DiscountPercentage]

FROM GENERATE_SERIES(@Start,@Stop);
GO

SELECT * FROM [Customers];
GO
--OUTPUT

Clean Up:

In order to clean up the table, we need to drop it as shown below.

USE DATABASE tempdb
GO

DROP Table [Customers];
GO

Conclusion:

I used GENERATE_SERIES() function to generate the sample table and I noticed that it has reduced a lot of complexity in creating sample table, compared to earlier approach. Do let me know if you have generated sample table and what was the approach.

Read Full Post »

Quite often we need to generate a calendar table in our database for reporting purposes with a specific date range. We may generate it for a month, a quarter or a year depending upon our requirements. Since there was no proper function available to generate calendar table in earlier version of SQL Server (Earlier than 2022), we used DATEADD(), DATEDIFF() to achieve it somehow.

In SQL Server 2022, a new function came called GENERATE_SERIES(), which was a long awaited function. One of the usages of this function is that it can easily generate calendar table between two specific dates without using any additional functions.

Let me show you in the below example, how we can use GENERATE_SERIES() function to generate calendar table in SQL Server 2022.

Compatibility Level:

Your database compatibility level MUST be 160 or higher to use this function. Given below is the query to change your database compatibility level.

ALTER DATABASE tempdb SET COMPATIBILITY_LEVEL = 160

Example :


CREATE TABLE tbl_Calendar
(
  [ID]             INT IDENTITY
, [Date]           DATE
, [Day]            INT
, [DayShortName]   VARCHAR(100)
, [DayName]        VARCHAR(100)
, [Week]           INT
, [MonthShortName] VARCHAR(100)
, [Month]          INT
, [MonthName]      VARCHAR(100)
, [Quarter]        INT
, [QuarterName]    VARCHAR(100)
, [Year]           INT
);
GO

DECLARE @SetStartDate DATE, @Start INT, @Stop INT, @Step INT;

SET @SetStartDate = '2022-01-01' --Start date of the calender;
SET @Start   = 0;
SET @Stop    = 365 -- No of days(one year) ;
SET @Step    = 1;

INSERT INTO tbl_Calendar
( 
  [Date]
, [Day]
, [DayShortName]
, [DayName]
, [Week]
, [MonthShortName]
, [Month]
, [MonthName]
, [Quarter]
, [QuarterName] 
, [Year]
)
SELECT DATEADD(DAY,value,@SetStartDate) AS [Date]
     , DATEPART(DAY,DATEADD(DAY,value,@SetStartDate)) AS [Day]
	 , FORMAT(DATEADD(DAY,value, @SetStartDate) ,'ddd') AS [DayShortName]
	 , DATENAME(WEEKDAY,DATEADD(WEEKDAY,value,@SetStartDate)) AS [DayName]
     , DATEPART(WEEK,DATEADD(DAY,value,@SetStartDate)) AS [Week]
	 , FORMAT(DATEADD(DAY,value, @SetStartDate) ,'MMM') AS [MonthShortName]
	 , MONTH(DATEADD(DAY,value,@SetStartDate)) AS [Month]
     , DATENAME(MONTH,DATEADD(DAY,value,@SetStartDate)) AS [MonthName]
	 , DATEPART(QUARTER,DATEADD(DAY,value,(@SetStartDate))) AS [Quarter]
	 , CONCAT('Quarter - ',DATEPART(QUARTER,DATEADD(DAY,value,(@SetStartDate)))) AS [QuarterName]
     , YEAR(DATEADD(DAY,value,@SetStartDate)) AS [Year]
FROM GENERATE_SERIES(@Start,@Stop,@Step) 
GO

SELECT * FROM tbl_Calendar;
GO

--OUTPUT

Conclusion:

I used GENERATE_SERIES() function to create calendar table and I noticed that it has reduced a lot of complexity in creating the calendar table, if we compare it with earlier approach. Do let me know, if you have generated calendar table and what was the approach.

Read Full Post »

Resource Governor is one of the best features shipped with SQL Server 2008. We use this feature to handle SQL Server workload and resource allocation. However, if you go into further details of this feature, you will be shocked to see the level of control it gives you to control over CPU, memory on the basis of incoming requests from the application/user etc. But I have been hearing a very common QUESTION since SQL Server 2008 and that is, can we control physical IO using resource governor and the ANSWER is YES, you can do it in SQL SERVER 2014 & Above. But How ?

In SQL Server 2014, we have an additional control in resource governor namely CONTROL PHYSICAL IO. In other words, if you would like to restrict a user/application to use limited physical IO, you can restrict it with this additional control. You can implement this additional control in resource governor by just setting up two options which is MIN_IOPS_PER_VOLUME & MAX_IOPS_PER_VOLUME.

Let me demonstrate how to control physical IO in SQL Server 2014 step by step.

Step 1:
First of all, lets create a pool as usual and restrict its MAX_IOPS_PER_VOLUME limit to 50 ONLY, which means that whatever set of users / applications will be used, this pool cannot exceed 50 Physical IO.

USE master
GO
--DROP RESOURCE POOL Sample_Pool_Restrict_IO;
--GO
CREATE RESOURCE POOL Sample_Pool_Restrict_IO WITH
(
MAX_IOPS_PER_VOLUME = 50,
MIN_IOPS_PER_VOLUME = 1
);
GO

Step 2:
Once we created the pool, lets create the workload group. This is also a usual step while configuring resource governor.

USE master
GO
--DROP WORKLOAD GROUP Sample_Workload_Group_Restrict_IO
--GO
CREATE WORKLOAD GROUP Sample_Workload_Group_Restrict_IO
USING Sample_Pool_Restrict_IO;
GO

Step 3:
Let’s, create a test user, later on I will demonstrate how resource governor will restrict physical IO for this test user.

USE master
GO
CREATE LOGIN dba WITH PASSWORD = 'imran.1234@';
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [dba]
GO

Step 4:
Create a classifier function and define if the above created user is the current user. Then classifier will assign the restricted IO workload group to this user. Then each time this user tries to use physical IO, it  CAN’T go beyond 50. SQL Server will restrict this user up to 50.

USE MASTER;
GO
--DROP FUNCTION dbo.fnIOClassifier
GO
CREATE FUNCTION dbo.fnIOClassifier()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
DECLARE @GroupName SYSNAME
IF SUSER_NAME() = 'dba'
BEGIN
SET @GroupName = 'Sample_Workload_Group_Restrict_IO'
END
ELSE
BEGIN
SET @GroupName = 'default'
END
RETURN @GroupName;
END
GO

Step 5:
Let’s assign the classifier function (created in the above step) to resource governor and reconfigure the resource governor to implement new settings.

USE master;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fnIOClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Step 6:
Now, we are ready to test the new feature (Control Physical IO). To test the feature, let’s open performance monitor, add counter and then select resource pool stats object, further select Disk read IO/sec and add default and custom Pool (Sample_Pool_Restrict_IO) created in step 1 and press ADD BUTTON as shown below.

Resource Governor 1.0

Step 7:
Now, we setup performance monitor to test the physical IO control feature via resource governor. Lets login with any user except the one created in step 3 and run the given below script and view the results in performance monitor. In my case, I logged in with sa and executed the script.

USE AdventureWorks2014
GO
DBCC DROPCLEANBUFFERS
GO
EXEC sp_MSforeachtable 'SELECT * FROM ?'
GO

Resource Governor 1.1

Note: In the above results, you can easily view that the DISK READ IO/SEC reached the maximum of 122 and it may go far from it as well because we did not restrict DISK READ IO/SEC for sa user.

Step 8:
This time lets login with ‘dba’ user which we created in step 3 and execute the same script which we executed in the above step.

USE AdventureWorks2014
GO
DBCC DROPCLEANBUFFERS
GO
EXEC sp_MSforeachtable 'SELECT * FROM ?'
GO

Resource Governor 1.2

Note: In the above results, you can easily view that the DISK READ IO/SEC reached the maximum of 50 and why is it not going beyond 50 is because dba user has been restricted by resource governor using physical IO control feature.

Conclusion:
As you can see above that this feature really gives DBA’s more control on physical IO and it would be very handy for the DBA’s where they have serious problems with I/O which can be from users / applications.

Read Full Post »

Earlier, I have written a blog post about how to split a single row data into multiple rows using XQuery. Today, I came across a situation where I had to split a single column data into multiple columns using delimiter.

Lets me create a sample to demonstrate the solution.

Sample :

USE TEMPDB
GO
DROP TABLE [dbo].[tbl_Employee]
GO
CREATE TABLE [dbo].[tbl_Employee](
[Employee ID] INT IDENTITY(1,1) ,
[Employee Name] VARCHAR (100) NOT NULL)
GO
INSERT INTO dbo.[tbl_Employee] ([Employee Name])
VALUES ('Andreas Berglund T')
GO
INSERT INTO dbo.[tbl_Employee] ([Employee Name])
VALUES ('Sootha Charncherngkha T')
GO
INSERT INTO dbo.[tbl_Employee] ([Employee Name])
VALUES ('Peng Wu')
GO

--Browse the data
SELECT * FROM dbo.[tbl_Employee]
GO

Split single column into multiple columns.1.0

Solution :
Given below is the solution, where we need to convert the column into xml and then split it into multiple columns using delimiter. You can use any delimiter in the given below solution. In my sample, I used a blank space as a delimiter to split column into multiple columns.

USE TEMPDB
GO

DECLARE @delimiter VARCHAR(50)
SET @delimiter=' '  -- <=== Here, you can change the delimiter.
;WITH CTE AS
(
SELECT
[Employee ID],
[Employee Name],
CAST('' + REPLACE([Employee Name], @delimiter , '') + '' AS XML)
AS [Employee Name XML]
FROM  [tbl_Employee]
)
SELECT
[Employee ID],
[Employee Name],
[Employee Name XML].value('/M[1]', 'varchar(50)') As [First Name],
[Employee Name XML].value('/M[2]', 'varchar(50)') As [Last Name],
[Employee Name XML].value('/M[3]', 'varchar(50)') As [Middle Name]

FROM CTE
GO

Split single column into multiple columns.1.1

Let me know if you come across this situation and its solution.

Read Full Post »

Earlier this week, my team & I were working on financial reports and we developed some giant scripts in order to generate report data. Once we were done with the report, we came to know that we did not implement dynamic sorting in the report. Ooopssss !!!. Now do we need to re-write the query and convert into dynamic query ? 😦 Of course NOT.
What you can actually do is, write a conditional case and make it dynamic sort but it will increase the size of your query depending upon how many conditions you have.

In today’s post I will implement the dynamic sorting using CHOOSE function & CASE Statement. First of all, I will find the column number using CASE Statement and then pass the Column order number to CHOOSE function to actually sort the column. It will actually reduce the size of your query.

Given below is the script which will dynamically sort the query.

Use AdventureWorks2014
GO

DECLARE @SortCoulmnName VARCHAR(50) = 'OrderDate';
DECLARE @SortColumnNumber AS INT

SET @SortColumnNumber = CASE
When @SortCoulmnName='SalesOrderID' THEN 0
WHEN @SortCoulmnName='OrderDate' THEN 1
WHEN @SortCoulmnName='DueDate' THEN 2
WHEN @SortCoulmnName='ShipDate' THEN 3
ELSE 0
END
-- By Default, it will sort on first column

SELECT SalesOrderID
,OrderDate
,DueDate
,ShipDate
,Status
FROM
Sales.SalesOrderHeader
ORDER BY
CHOOSE(@SortColumnNumber,SalesOrderID,OrderDate,DueDate,ShipDate) DESC
GO

dynamic order by 1.1

Let me know if you came across this issue and its solution as well.

Read Full Post »

Older Posts »