Feeds:
Posts
Comments

Archive for the ‘SQL SERVER’ 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 »

In SQL Server 2022, a new column namely last_valid_restore_time has been added to the table backupset as a part of the enhancements. It contains the information regarding the last valid restore time, and it became very handy when something happens to your database and you need to restore data from the backup and you want to know the exact last valid restore time. Actually, it is the time when you post your last transaction in your database before taking backup. By the way, last transaction can be way before you take your backup.

One important point is that it shows the last_valid_restore_time in Transaction Log Backup ONLY. In Full Backup & differential back, it returns NULL value and which makes sense.

Let me create a sample database, a table and insert a record to demonstrate the behaviour of last_valid_restore_time as shown below.

Sample:

USE master
GO
  
CREATE DATABASE TESTDB;
GO
  
USE TESTDB
GO
  
CREATE TABLE Customers
(
 CustomerID   INT IDENTITY,
 CustomerName VARCHAR(100),
 CreatedAt    DATETIME,
 ModifiedAt   DATETIME
);
GO
  
INSERT INTO Customers(CustomerName, CreatedAt)
VALUES('Customer-1', GETDATE());
GO
 
SELECT * FROM Customers;
GO
--OUTPUT

Step 1: Full Backup

In this step, we will take full backup of the database.

--Taking full backup
BACKUP DATABASE TESTDB 
TO DISK ='D:\Backup\TESTDBfullbackup.BAK';
GO 
--OUTPUT

Step 2: Differential Backup

In this step, first we will insert a new record then will take differential backup.

USE TESTDB
GO

INSERT INTO Customers(CustomerName, CreatedAt)
VALUES('Customer-2', GETDATE());
GO
   
--Taking differential backup
BACKUP DATABASE TESTDB 
TO DISK ='D:\Backup\TESTDBdifferentialbackup.BAK'
WITH DIFFERENTIAL;
GO

SELECT * FROM Customers;
GO
--OUTPUT

Step 3: Log backup

In this step, we will insert and update few records and then take log backup.

USE TESTDB
GO
 
INSERT INTO Customers(CustomerName, CreatedAt)
VALUES('Customer-3', GETDATE());
GO
 
UPDATE Customers
SET    CustomerName = 'Customer22'
     , ModifiedAt   =  GETDATE()
FROM   Customers 
WHERE  CustomerID = 2;
GO
   
--Taking log backup
BACKUP LOG TESTDB 
TO DISK ='D:\Backup\TESTDBlogbackup1.TRN' ;
GO
 
SELECT * FROM Customers;
GO
--OUTPUT

Step 4: Last Valid Restore Time

Now, we have successfully created the sample along with backups. Let me run the query and add this new column namely last_valid_restore_time as shown below. As we can see, the last_valid_restore_time & the last record modified date & time is same which is 2022-12-03 18:37:18.

SELECT bs.database_name, backuptype = CASE
        WHEN bs.type = 'D' AND bs.is_copy_only = 0 THEN 'Full Database'
        WHEN bs.type = 'D' AND bs.is_copy_only = 1 THEN 'Full Copy-Only Database'
        WHEN bs.type = 'I' THEN 'Differential database backup'
        WHEN bs.type = 'L' THEN 'Transaction Log'
        WHEN bs.type = 'F' THEN 'File or filegroup'
        WHEN bs.type = 'G' THEN 'Differential file'
        WHEN bs.type = 'P' THEN 'Partial'
        WHEN bs.type = 'Q' THEN 'Differential partial'
        when bs.type = 'i' THEN 'last valid restore value' END + ' Backup',
    CASE bf.device_type
        WHEN 2 THEN 'Disk' WHEN 5 THEN 'Tape' WHEN 7 THEN 'Virtual device'
        WHEN 9 THEN 'Azure Storage' WHEN 105 THEN 'A permanent backup device'
        ELSE 'Other Device' END AS DeviceType,
    bms.software_name AS backup_software,
    /**New column addition in SQL Server 2022**/
    bs.last_valid_restore_time,
    /**************************************/
    BackupStartDate = bs.Backup_Start_Date,
    BackupFinishDate = bs.Backup_Finish_Date,
    LatestBackupLocation = bf.physical_device_name,
    backup_size_mb = CONVERT(DECIMAL(10, 2), bs.backup_size / 1024. / 1024.),
    compressed_backup_size_mb = CONVERT(DECIMAL(10, 2), bs.compressed_backup_size / 1024. / 1024.),
    database_backup_lsn, -- For tlog and differential backups, this is the checkpoint_lsn of the FULL backup it is based on.
    checkpoint_lsn,
    begins_log_chain,
    bms.is_password_protected
FROM msdb.dbo.backupset bs
LEFT JOIN msdb.dbo.backupmediafamily bf
    ON bs.[media_set_id] = bf.[media_set_id]
INNER JOIN msdb.dbo.backupmediaset bms
    ON bs.[media_set_id] = bms.[media_set_id]
WHERE bs.backup_start_date > DATEADD(MONTH, - 2, sysdatetime()) --only look at last two months
ORDER BY bs.database_name ASC,
    bs.Backup_Start_Date DESC;
--OUTPUT

Step 5: Restore

Now, in order to get data before the execution of update statement, we are going to check the last_valid_restore_time and restore the database before the last_valid_restore_time which will return the data before update of the table as shown below.

USE [master]
RESTORE DATABASE [TESTDB] 
FROM  DISK = N'D:\Backup\TESTDBfullbackup.BAK' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
RESTORE DATABASE [TESTDB] 
FROM  DISK = N'D:\Backup\TESTDBdifferentialbackup.BAK' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [TESTDB] 
FROM  DISK = N'D:\Backup\TESTDBlogbackup1.TRN' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 5,  STOPAT = N'2022-12-03T18:37:15'
GO

--OUTPUT

Conclusion:

Last_valid_restore_time is very handy information when we want to know the last transaction date in the specified backup & would like to restore based on specific time. Do let me know, how were you tracking this time in earlier version of SQL Server 2022.

Read Full Post »

Taking backup is the core responsibility of Database Administrator, either he takes the backup manually or he automates it but it does not matter. What actually matters is the database size because if it is very big then obviously it will take too much time in taking backup & occupy lots of disk space as well.

In SQL Server 2022, two new database backup compression algorithms were introduced as follows:

  1. MS_XPRESS
  2. QAT_DEFLATE

In this article, we are going to discuss one of the compression algorithms namely QAT_DEFLATE, which compresses backup size & reduce backup time drastically. We can use this compression algorithm for any types of database backup which are Full, Differential & Log backup. This algorithm will take less disk space and will reduce the backup time as well.

Let me demonstrate, how to implement this algorithm step by step.

Step 1:

Let’s check the database file size by using sys.database_files and as you can see the database size is ~10 GB.

USE SampleDB;
GO

SELECT DB_NAME()  AS DatabaseName
     , name       AS FileName
	 , size/128.0 AS CurrentSizeInMBs
FROM sys.database_files; 
GO

Step 2:

Let’s take full backup of SampleDB to see how much disk space & time it takes:

BACKUP DATABASE [SampleDB] 
TO  DISK = 'D:\Backup\FullBackupSampleDB.BAK';
GO

Step 3:

Let’s check the backup details especially the backup size and time. As we can see below, it took ~50 seconds to take Full backup and a disk space of ~10 GB

SELECT bs.database_name,
    backuptype = CASE 
        WHEN bs.type = 'D' AND bs.is_copy_only = 0 THEN 'Full Database'
        WHEN bs.type = 'D' AND bs.is_copy_only = 1 THEN 'Full Copy-Only Database'
        WHEN bs.type = 'I' THEN 'Differential database backup'
        WHEN bs.type = 'L' THEN 'Transaction Log'
        WHEN bs.type = 'F' THEN 'File or filegroup'
        WHEN bs.type = 'G' THEN 'Differential file'
        WHEN bs.type = 'P' THEN 'Partial'
        WHEN bs.type = 'Q' THEN 'Differential partial'
        END + ' Backup',
	BS.compression_algorithm,
    backup_size_mb = CONVERT(DECIMAL(10, 2), bs.backup_size / 1024. / 1024.),
    compressed_backup_size_mb = CONVERT(DECIMAL(10, 2), bs.compressed_backup_size / 1024. / 1024.),
	bs.backup_start_date ,
	bs.backup_finish_date,
	DATEDIFF(second, backup_start_date, backup_finish_date) AS time_in_seconds
FROM msdb.dbo.backupset bs
LEFT JOIN msdb.dbo.backupmediafamily bf
    ON bs.[media_set_id] = bf.[media_set_id]
INNER JOIN msdb.dbo.backupmediaset bms
    ON bs.[media_set_id] = bms.[media_set_id]
WHERE bs.backup_start_date > DATEADD(MONTH, - 2, sysdatetime()) --only look at last two months
ORDER BY bs.database_name ASC,
    bs.Backup_Start_Date DESC;

Step 4:

We MUST install the Intel® QuickAssist Technology Driver in order to use QAT_DEFLATE algorithm. Recently I have written an article on Intel® QuickAssist Technology Driver installation that can be accessed from here.

Step 5:

Let’s configure QAT_DEFLATE compression algorithm as a default in SQL Server as shown below.

EXEC sp_configure 'backup compression algorithm', 2;   
RECONFIGURE; 
GO

Step 6:

Let’s check the configuration of backup compression by using sys.configuration. As we can see below, the value is 2 which means QAT_DEFLATE is successfully setup.

SELECT configuration_id
     , name
     , description
     , value   
FROM sys.configurations   
WHERE name = 'backup compression algorithm' ;  
GO

Step 7:

let’s configure the hardware_offload option which is also mandatory for QAT_DEFLATE algorithm as shown below.

EXEC sp_configure 'show advanced options', 1;
GO

RECONFIGURE
GO

EXEC sp_configure 'hardware offload enabled', 1;
GO

RECONFIGURE
GO

ALTER SERVER CONFIGURATION SET HARDWARE_OFFLOAD = ON(ACCELERATOR = QAT); 
GO

Step 8:

Please make SURE to RESTART the SQL Server in this step.

Step 9:

Let’s take the full backup of our database with compression but since we setup the default compression algorithm as QAT_DEFLATE in earlier step then by default it takes full back with QAT_DEFLATE backup compression algorithm, so NO need to write the name of the backup compression algorithm in the script as shown below.

BACKUP DATABASE [SampleDB] 
TO  DISK = 'D:\BackupFullBackupSampleDBWithCompression.BAK'
WITH COMPRESSION;
GO

Step 10:

Once the backup is taken, let’s check the backup details by using given below query. As we can see below, the backup compression algorithm is QAT_DEFLATE, the backup compression size is ~1.3 GB & it took 24 seconds to take back up. It is almost reducing the size by ~87% and time by ~52% as compared to original backup and which is a big achievement.

SELECT bs.database_name,
    backuptype = CASE 
        WHEN bs.type = 'D' AND bs.is_copy_only = 0 THEN 'Full Database'
        WHEN bs.type = 'D' AND bs.is_copy_only = 1 THEN 'Full Copy-Only Database'
        WHEN bs.type = 'I' THEN 'Differential database backup'
        WHEN bs.type = 'L' THEN 'Transaction Log'
        WHEN bs.type = 'F' THEN 'File or filegroup'
        WHEN bs.type = 'G' THEN 'Differential file'
        WHEN bs.type = 'P' THEN 'Partial'
        WHEN bs.type = 'Q' THEN 'Differential partial'
        END + ' Backup',
    CASE bf.device_type
        WHEN 2 THEN 'Disk'
        WHEN 5 THEN 'Tape'
        WHEN 7 THEN 'Virtual device'
        WHEN 9 THEN 'Azure Storage'
        WHEN 105 THEN 'A permanent backup device'
        ELSE 'Other Device'
        END AS DeviceType,

	BS.compression_algorithm,
    backup_size_mb = CONVERT(DECIMAL(10, 2), bs.backup_size / 1024. / 1024.),
    compressed_backup_size_mb = CONVERT(DECIMAL(10, 2), bs.compressed_backup_size / 1024. / 1024.),
	bs.backup_start_date ,
	bs.backup_finish_date
FROM msdb.dbo.backupset bs
LEFT JOIN msdb.dbo.backupmediafamily bf
    ON bs.[media_set_id] = bf.[media_set_id]
INNER JOIN msdb.dbo.backupmediaset bms
    ON bs.[media_set_id] = bms.[media_set_id]
WHERE bs.backup_start_date > DATEADD(MONTH, - 2, sysdatetime()) --only look at last two months
ORDER BY bs.database_name ASC,
    bs.Backup_Start_Date DESC;

Conclusion:

I used QAT_DEFLATE backup compression algorithm & its results are quite good. Do let me know, which backup compression algorithm are you using and what is the outcome.

Read Full Post »

In SQL Server 2022, a new backup compression algorithm was introduced known as QAT_DEFLATE. This article demonstrates the installation of the Intel® Quick Assist Technology Driver, which is required to use this algorithm.

Let me demonstrate the installation step by step.

Installation:

Step 1:

The first step is to download the driver, which can be downloaded from here. Currently, QAT 1.7 version is available for download. You need to download whatever latest version is available. It will be downloaded as a zip file.

Step 2:

Once it is downloaded, you just need to extract it and you will get below files in the respective folder.

Step 3:

You need to run QatSetup file in order to install it, a warning message as shown below will appear which is fine, since you are installing it on a server which does not have any Intel QAT supported hardware, so you just need to click Yes to proceed.

Step :4

Click on accept the terms and then click Next button to proceed.

Step 5:

Now, it’s ready to be installed, click on Install button to proceed the installation.

Step 6:

Once the installation is completed, “install wizard completed” will appear and QuickAssist Technology is ready to be used.

Step 7:

Once you click on Finish button, installation summary will pop up, which shows the status of the installed components. Some of the components are not installed, since you don’t have any QAT supported hardware on this server as mentioned earlier.

Conclusion:

I found the installation pretty simple & easy; do let me, how did you find it.

Read Full Post »

Taking backup is the core responsibility of Database Administrator, either he takes the backup manually or he automates it but it does not matter. What actually matters is the database size because if it is very big then obviously it will take too much time in taking backup & occupy lot’s of disk space as well.

In SQL Server 2022, two new database backup compression algorithms were introduced as follows:

  1. MS_XPRESS
  2. QAT_DEFLATE

In this article, we are going to discuss one of the compression algorithms namely MS_XPRESS, which compresses backup size & reduce backup time drastically. We can use this compression algorithm for any types of database backup which are Full, Differential & Log backup. This algorithm will take less disk space and will reduce the backup time as well.

Let me demonstrate, how to implement this algorithm step by step.

Step 1:

Let’s check the database file size by using sys.database_files and as you can see the database size is ~10 GB.

USE SampleDB;
GO

SELECT DB_NAME()  AS DatabaseName
     , name       AS FileName
	 , size/128.0 AS CurrentSizeInMBs
FROM sys.database_files; 
GO

Step 2:

Let’s take full backup of SampleDB to see how much disk space & time it takes:

BACKUP DATABASE [SampleDB] 
TO  DISK = 'D:\Backup\FullBackupSampleDB.BAK';
GO

Step 3:

Let’s check the backup details especially the backup size and time. As we can see below, it took ~50 seconds to take Full backup and a disk space of ~10 GB.

SELECT bs.database_name,
    backuptype = CASE 
        WHEN bs.type = 'D' AND bs.is_copy_only = 0 THEN 'Full Database'
        WHEN bs.type = 'D' AND bs.is_copy_only = 1 THEN 'Full Copy-Only Database'
        WHEN bs.type = 'I' THEN 'Differential database backup'
        WHEN bs.type = 'L' THEN 'Transaction Log'
        WHEN bs.type = 'F' THEN 'File or filegroup'
        WHEN bs.type = 'G' THEN 'Differential file'
        WHEN bs.type = 'P' THEN 'Partial'
        WHEN bs.type = 'Q' THEN 'Differential partial'
        END + ' Backup',
	BS.compression_algorithm,
    backup_size_mb = CONVERT(DECIMAL(10, 2), bs.backup_size / 1024. / 1024.),
    compressed_backup_size_mb = CONVERT(DECIMAL(10, 2), bs.compressed_backup_size / 1024. / 1024.),
	bs.backup_start_date ,
	bs.backup_finish_date,
	DATEDIFF(second, backup_start_date, backup_finish_date) AS time_in_seconds
FROM msdb.dbo.backupset bs
LEFT JOIN msdb.dbo.backupmediafamily bf
    ON bs.[media_set_id] = bf.[media_set_id]
INNER JOIN msdb.dbo.backupmediaset bms
    ON bs.[media_set_id] = bms.[media_set_id]
WHERE bs.backup_start_date > DATEADD(MONTH, - 2, sysdatetime()) --only look at last two months
ORDER BY bs.database_name ASC,
    bs.Backup_Start_Date DESC;

Step 4:

Let’s configure MS_XPRESS compression algorithm as a default in SQL Server as shown below.

EXEC sp_configure 'backup compression algorithm', 1;   
RECONFIGURE; 
GO

Step 5:

Let’s check the configuration of backup compression by using sys.configuration. As we can see below, the value is 1 which means MS_XPRESS is successfully setup

SELECT configuration_id
     , name
     , description
     , value   
FROM sys.configurations   
WHERE name = 'backup compression algorithm' ;  
GO

Step 6:

Let’s take the full backup of our database with compression but since we setup the default compression algorithm as MS_XPRESS in earlier step then by default it takes full back with MS_XPRESS backup compression algorithm, so NO need to write the name of the backup compression algorithm in the script as shown below.


BACKUP DATABASE [SampleDB] 
TO  DISK = 'D:\Backup\FullBackupSampleDBWithCompression.BAK'
WITH COMPRESSION;
GO

Step 7:

Once the backup is taken, let’s check the backup details by using given below query. As we can see below, the backup compression algorithm is MS_XPRESS, the backup compression size is 1.3 GB & it took 27 seconds to take back up only. It is almost reducing the size by ~87% and time by ~46% as compared to original backup and which is a big achievement.

SELECT bs.database_name,
    backuptype = CASE 
        WHEN bs.type = 'D' AND bs.is_copy_only = 0 THEN 'Full Database'
        WHEN bs.type = 'D' AND bs.is_copy_only = 1 THEN 'Full Copy-Only Database'
        WHEN bs.type = 'I' THEN 'Differential database backup'
        WHEN bs.type = 'L' THEN 'Transaction Log'
        WHEN bs.type = 'F' THEN 'File or filegroup'
        WHEN bs.type = 'G' THEN 'Differential file'
        WHEN bs.type = 'P' THEN 'Partial'
        WHEN bs.type = 'Q' THEN 'Differential partial'
        END + ' Backup',
    CASE bf.device_type
        WHEN 2 THEN 'Disk'
        WHEN 5 THEN 'Tape'
        WHEN 7 THEN 'Virtual device'
        WHEN 9 THEN 'Azure Storage'
        WHEN 105 THEN 'A permanent backup device'
        ELSE 'Other Device'
        END AS DeviceType,

	BS.compression_algorithm,
    backup_size_mb = CONVERT(DECIMAL(10, 2), bs.backup_size / 1024. / 1024.),
    compressed_backup_size_mb = CONVERT(DECIMAL(10, 2), bs.compressed_backup_size / 1024. / 1024.),
	bs.backup_start_date ,
	bs.backup_finish_date
FROM msdb.dbo.backupset bs
LEFT JOIN msdb.dbo.backupmediafamily bf
    ON bs.[media_set_id] = bf.[media_set_id]
INNER JOIN msdb.dbo.backupmediaset bms
    ON bs.[media_set_id] = bms.[media_set_id]
WHERE bs.backup_start_date > DATEADD(MONTH, - 2, sysdatetime()) --only look at last two months
ORDER BY bs.database_name ASC,
    bs.Backup_Start_Date DESC;

Conclusion:

I used MS_XPRESS backup compression algorithm & its results are quite good. Do let me know, which backup compression algorithm are you using and what is the outcome.

Read Full Post »

In SQL Server 2022, an enhancement came in STRING_SPLIT() function, which was a long awaited functionality. This enhancement provides a new column name ordinal. I have written an article about it in detail.

Recently, I was using STRING_SPLIT() function and came across an error as mentioned below.

Error:

Invalid column name ‘ordinal’.

Example:

In the given below example, I wrote a simple script by using STRING_SPLIT() function and placed an ordinal column in the select statement. This ordinal column provides the row number for each split string which is a very handy functionality but I got an error as shown below.

USE Northwind
GO

SELECT CategoryID
     , CategoryName
	 , Value AS Description
     , Ordinal
FROM   Categories
CROSS APPLY STRING_SPLIT(CONVERT(VARCHAR(100),Description), ',');
GO
--OUTPUT

Solution:

Remember, whenever you place ordinal column in select statement, you MUST pass “1” as third argument in STRING_SPLIT() function. By default, it takes third argument as “0” which was the case in the above example and in such cases, this function does NOT enable ordinal column in the select statement and resulted in error.

Let’s place ordinal column in the select statement and the MOST important thing, we MUST pass “1” in the third argument of STRING_SPLIT() function. This time, it will run sucessfully as shown below.

USE Northwind
GO

SELECT CategoryID 
     , CategoryName
     , Value AS Description
     , Ordinal
FROM   Categories
CROSS APPLY STRING_SPLIT(CONVERT(VARCHAR(100),Description), ',', 1);
GO
--OUTPUT

Conclusion:

Whenever you place ordinal column in select statement, you MUST pass “1” as third argument in STRING_SPLIT() in order to avoid this error.

Read Full Post »

In SQL Server 2022, an enhancement came in STRING_SPLIT() function, which was a long awaited functionality. This enhancement provides a new column name ordinal, which has row number for each string split by this function.

Let me create a sample to demonstrate the functionality of new ordinal column in STRING_SPLIT() function as shown below.

Sample:

USE tempdb
GO
   
CREATE TABLE #Employee
(
 EmployeeID     INT IDENTITY(1,1),
 EmployeeName   VARCHAR(500),
 EmailAddresses VARCHAR(500)
);
GO
   
INSERT INTO #Employee(EmployeeName, EmailAddresses)
VALUES
('John', 'John_1@gmail.com;John_2@gmail.com;John_3@hotmail.com'),
('Sarah', 'Sarah_1@gmail.com;Sarah_2@hotmail.com;Sarah_3@gmail.com'),
('Aaron', 'Aaron_1@gmail@com;Aaron_2@hotmail.com'),
('Ackerman', 'Ackerman_1@gmail.com;Ackerman_2@hotmail.com');
GO

SELECT * FROM #Employee;
GO
--OUTPUT

Example 1:

In the below example, I will split the string in email addresses column based on a separator as usual. Also, I will pass the third argument (which came in SQL 2022 as an ehancement) as 1 in STRING_SPLIT() function which means that STRING_SPLIT() will not only split the string but also provide a serial number (ordinal column) against each split string as shown below.

USE tempdb
GO

SELECT EmployeeID
     , EmployeeName
 , value AS EmailAddress
 , Ordinal
FROM   #Employee
CROSS APPLY STRING_SPLIT(EmailAddresses, ';', 1);
GO
--OUTPUT

Example 2:

In this example, I will show you how important is this new column ordinal and how we used to struggle in earlier version of SQL Server (Earlier than 2022) to mimic the same functionality with the help of common table expression & other functions.

Let me grab the first two email addresses for each employee using old and new approach. Both approaches will return the same result but new approach is simple & efficient.

  • Old Approach: (Using ROW_NUMBER() & common table expression)


USE tempdb
GO
 
;WITH CTE AS
(SELECT EmployeeID
      , EmployeeName
      , value AS EmailAddress
      , ROW_NUMBER() OVER(PARTITION BY EmployeeID 
						  ORDER BY EmployeeID ASC) AS Ordinal
FROM    #Employee
CROSS APPLY STRING_SPLIT(EmailAddresses, ';'))
SELECT * FROM CTE WHERE Ordinal<3;
GO
--OUTPUT

  • New Approach: (Using ordinal column)

USE tempdb
GO
 
SELECT EmployeeID
     , EmployeeName
     , value AS EmailAddress
	 , Ordinal
FROM   #Employee
CROSS APPLY STRING_SPLIT (EmailAddresses, ';', 1)
WHERE ordinal<3;
GO
--OUTPUT

Conclusion:

I found the new enhancement of STRING_SPLIT() function very useful, earlier we used common table expression & ROW_NUMBER() functions to find the row number of each split string but now we can easily achieve with the help of STRING_SPLIT() ordinal column. Do let me know if you use ordinal column and how did you find it.

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 »

Older Posts »