Feeds:
Posts
Comments

SQL Server – How to check weather of any location using API

April 7, 2023 by Muhammad Imran

WeatherAPI provides an API to get Weather condition of any location. In this article, we will use this API in SQL Server in order to get weather conditions of any location. 

Let me explain it step by step.

Step 1:

Please Sign Up in WeatherAPI and create an account as shown below.

Step 2:

After Sign Up, login to the account, created in Step 1, it will direct to the screen where you can find your API key as shown below, please make a note of this Key.

Step 3:

You need to run given below script to enable OLE Automation Procedures. This is a prerequisite for next step.

 
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Step 4:

Let’s create given below stored procedure which takes location as an input and provides weather condition of that location.

 
USE tempdb
GO
 
CREATE OR ALTER PROCEDURE sp_Weather_Details
@Location NVARCHAR(250)

AS
DECLARE @URL NVARCHAR(250) = 'https://api.weatherapi.com/v1/current.json';
DECLARE @key VARCHAR(250) ='xxxxxxxxxxxxxxxxxxxxxxxxx'; --Enter your own API Key generated in Step 2
DECLARE @ConsolidatedURL NVARCHAR(250) = CONCAT(@URL,'?key=',@Key,'&q=',@location,'&aqi=no'); 
DECLARE @Object INT;
DECLARE @Json TABLE(DATA NVARCHAR(MAX));
DECLARE @ResponseText NVARCHAR(MAX);
 
EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'OPEN', NULL, 'GET', @ConsolidatedURL, 'FALSE';
EXEC sp_OAMethod @Object, 'SEND';
EXEC sp_OAMethod @Object, 'RESPONSETEXT', @ResponseText OUTPUT , @Json OUTPUT;
 
INSERT INTO @Json exec sp_OAGetProperty @Object, 'RESPONSETEXT';
 
DECLARE @Data NVARCHAR(MAX) = (SELECT DATA FROM @Json);
SELECT * FROM OPENJSON(@Data)
WITH
( 
  [Country] NVARCHAR(500)  '$.location.country',
  [Region] NVARCHAR(500) '$.location.region',
  [City] NVARCHAR(500)  '$.location.name',
  [Current Temprature] NUMERIC(18,1) '$.current.temp_c',
  [Humidity] NUMERIC(18,1) '$.current.humidity',
  [Weather Condition] NVARCHAR(500) '$.current.condition.text',
  [Wind Speed] NUMERIC(18,1) '$.current.wind_kph'
);
GO

Let’s run few examples and see the weather conditions.

  • Example 1:

Location: Karachi, Pakistan

 

USE tempdb
GO

EXEC sp_Weather_Details 'Karachi';
GO

  • Example 2:

Location: Dubai, UAE

 

USE tempdb
GO

EXEC sp_Weather_Details 'Dubai';
GO

  • Example 3:

Location: London, UK

 

USE tempdb
GO

EXEC sp_Weather_Details 'London';
GO

Example 4:

Location: New York, USA

 

USE tempdb
GO

EXEC sp_Weather_Details 'New York';
GO

Conclusion:

I found this API very useful whenever I need to find the weather of any location. Do let me know your feedback.

Posted in Solutions, SQL SERVER, Uncategorized | Tagged , , , , | Leave a Comment »

SQL Server – Fix – Error – This backend version is not supported to design database diagrams or tables

April 6, 2023 by Muhammad Imran

Recently, I was working on one of the databases and I tried to insert a table through SQL Server Management Studio (SSMS) and I came across the following error.

Error:

The backend version is not supported to design database diagrams or tables.

Reproduce Error:

Let me reproduce this error step by step.

Step 1:

Let’s create a new database namely ‘TestDB’ as shown below.

 
CREATE DATABASE TestDB;
GO

Step 2:

Now, lets expand the database TestDB, then right click on the Tables option and then select New and then select Table… as shown below.

Step 3:

This will display the following error.

Solution:

This error occurs when you have the older version (18) of SSMS installed on your device. To check the version installed on your device, navigate to the Help menu and click on About menu in the menu bar as shown below.

A new window will pop up with all the version details of the SSMS.

In order to solve this error, you need to install a new version (19 or higher) of SQL Server Management Studio (SSMS). You can download it from here

Conclusion:

Make sure you have updated version of SSMS all the time to avoid such errors.

Posted in Errors, SQL SERVER, Uncategorized | Tagged , , , , | 1 Comment »

SQL Server – How to translate text from one language to another using google translate

April 5, 2023 by Muhammad Imran

Google translate provides API to translate text from one language to another. In this article, we will use this API in SQL Server and translate some text. Please sign up in google translate and create an account which provides you the Key.
Please use this Key in the given below stored procedure.

Before creating the stored procedure, you need to run given below script to enable Ole Automation Procedures. This is a prerequisite for the given below solution.

Enable OLE Automation procedures:

 
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Solution:

 

USE tempdb
GO

CREATE OR ALTER PROCEDURE sp_translate_text
@TextToBeTranslated NVARCHAR(500),
@SourceLanguage NVARCHAR(50),
@TargetLanguage NVARCHAR(50),
@Key NVARCHAR(250) = 'Please use Translate API key provided by google'  

AS

DECLARE @URL NVARCHAR(250) = 'https://translation.googleapis.com/language/translate/v2';
DECLARE @ConsolidatedURL NVARCHAR(250) = CONCAT(@URL,'?key=',@Key); 
DECLARE @Object INT;
DECLARE @Json TABLE(DATA NVARCHAR(MAX));
DECLARE @ResponseText NVARCHAR(MAX);
DECLARE @Body NVARCHAR(MAX)  = CONCAT('{','"q":','"',@TextToBeTranslated,'"',',','"target":','"',@TargetLanguage,'"',',','"source":','"',@SourceLanguage,'"','}');

EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'OPEN', NULL, 'POST', @ConsolidatedURL, 'FALSE';
EXEC sp_OAMethod @Object, 'SETREQUESTHEADER', NULL, 'CONTENT-TYPE', 'APPLICATION/JSON';
EXEC sp_OAMethod @Object, 'SEND', NULL, @Body;

EXEC sp_OAMethod @Object, 'RESPONSETEXT', @ResponseText OUTPUT , @Json OUTPUT;
INSERT INTO @Json exec sp_OAGetProperty @Object, 'RESPONSETEXT';

DECLARE @Data NVARCHAR(MAX) = (SELECT DATA FROM @Json);

SELECT @SourceLanguage AS [Source Language]
	 , @TargetLanguage AS [Target Language]
	 , TranslatedText  AS [Translated Text]
FROM OPENJSON(@Data,'$.data.translations')
WITH
(
 [TranslatedText] NVARCHAR(500) '$.translatedText'  
);
GO

  • Example 1:

Text to be translated: “How are you”

Source Language: English

Target Language: Urdu

 

USE tempdb
GO

EXEC sp_translate_text N'how are you','en','ur';
GO

  • Example 2:

Text to be translated: “آپ کیسے ہو”

Source Language: Urdu

Target Language: English

 

USE tempdb
GO

EXEC sp_translate_text N'آپ کیسے ہو','ur','en';
GO

  • Example 3:

Text to be translated: “How are you”

Source Language: English

Target Language: Arabic

 

USE tempdb
GO

EXEC sp_translate_text N'how are you','en','ar';
GO

  • Example 4:

Text to be translated: “كيف حالكم”

Source Language: Arabic

Target Language: English

 

USE tempdb
GO

EXEC sp_translate_text N'كيف حالكم','ar','en';
GO

  • Example 5:

Text to be translated: “How are you”

Source Language: English

Target Language: Hindi

 

USE tempdb
GO

EXEC sp_translate_text N'how are you','en','hi';
GO

  • Example 6:

Text to be translated: “आप कैसे हैं”

Source Language: Hindi

Target Language: English

 

USE tempdb
GO

EXEC sp_translate_text N'आप कैसे हैं','hi','en';
GO

Conclusion:

I found Google translate API very useful whenever I need to convert any text from one language to another. It works very smoothly. Do let me know if you use it and how did you find it.

Posted in Solutions, SQL SERVER | Tagged , , , , , , , | 3 Comments »

SQL Server – How to compare files in SQL Server Management Studio (SSMS)

April 4, 2023 by Muhammad Imran

Comparing files is one of the frequent activities, when we want to see the difference between two files. SQL developers face this problem on a day to day basis when they have two versions of the same file and they want to know where they made changes.

Fortunately, SQL Server Management Studio (SSMS) has this toolbar, which you can use to compare two files very easily.

In order to explain it, let me create two scripts as shown below.

Sample:

The below scripts will be saved as Version1.sql & Version2.sql.

  • Version1:

USE Northwind
GO
 
CREATE OR ALTER VIEW [dbo].[OrdersQry] 
AS
   SELECT Orders.OrderID
        , Orders.ShipName AS ShipName
   FROM Customers 
   INNER JOIN Orders 
   ON Customers.CustomerID = Orders.CustomerID;
GO

  • Version2:

USE Northwind
GO
  
CREATE OR ALTER VIEW [dbo].[OrdersQry] 
AS
   SELECT COUNT(Orders.OrderID) AS TotalOrders
        , Orders.ShipName AS ShipName
        , Orders.ShipAddress AS ShipAddress
   FROM Customers 
   INNER JOIN Orders 
   ON Customers.CustomerID = Orders.CustomerID
   GROUP BY Orders.ShipName
          , Orders.ShipAddress;
GO

Now, let’s compare above mentioned both scripts (Version1.sql, Version2.sql) in SQL Server Management Studio (SSMS) as shown in below steps.

Step 1:

Let’s activate Compare Files toolbar by clicking on View menu then click on Toolbars, and in Toolbars, click on Compare Files option as shown below.

The Compare Files toolbar will appear as shown below.

Step 2:

In the toolbar, there is menu button on extreme left hand side which shows the mode of compare. We have 4 types of mode as described below.

  • Side-by-side mode: It compares both files side by side. This is selected by default as shown below.
  • Inline mode: It compares combined scripts.
  • Left file only: It compares the first script.
  • Right file only: It compares the second file only.

Step 3:

Open File Version1 and Version2 in SQL Server Management Studio (SSMS) as shown below.

Step 4:

Open Command Widow by clicking on View menu then click on Other Windows then select Command Widow Or use shortcut(Ctrl+Alt+A).

Step 5:

In the Command Window, type Command Tools.DiffFiles and give the file name (Version1.sql, Version2.sql) which needs to be compared and press Enter as shown below.

Tools.DiffFiles Version1.sql Version2.sql

Step 6:

Both files will be compared in side by side mode since it is selected by default and the output will be as shown below.

  • Additional scripts in the second file (Version2.sql) are represented in GREEN color.
  • Modified or deleted scripts from first file (Version1.sql) are represented in RED color.
  • Scripts which are NOT changed are represented in NO Background color.

Please note that in case of any changes which are made to these files need to be saved again, we need to run the DiffFiles command again.

Conclusion:

Compare Files toolbar is one of the handy toolbars in SQL Server Management Studio (SSMS). Do let me know if you use it and found it helpful.

Posted in SQL SERVER, Tools | Tagged , , , , , , , , | 3 Comments »

SQL Server – How to truncate database log file

April 3, 2023 by Muhammad Imran

Sometimes, we come across a situation where the transaction log file grows unexpectedly, and it needs to be managed properly. One of the options is to reduce the size of database transaction log by shrinking the database transaction log file. Please be careful while implementing this solution on production databases.

Let me create a sample and then expalin it step by step as shown below.

Sample:

 
CREATE DATABASE SampleDB;
GO

Use SampleDB
GO

CREATE TABLE [Customers]
(
 [CustomerID]         INT IDENTITY(1,1),
 [CustomerName]       VARCHAR(250),
 [RegistrationDate]   DATE,
 [EmailAddress]       VARCHAR(100),
 [Address]            VARCHAR(500),
 [PhoneNumber]        VARCHAR(100)
);
GO

--Lets's insert 3,000,000 records in customer table
INSERT INTO [Customers]
(CustomerName,RegistrationDate,EmailAddress,Address,PhoneNumber)
VALUES('John','01-13-2023','John@raresql.com','AB Street','0134018201');
GO 3000000

Step 1:

Let’s check the size of the database files as shown below.

 
Use SampleDB
GO

SELECT name           AS [DB File Name]
     , size           AS [Size in KBs]
     , size * 8/1024  AS [Size in MBs]
     , Max_Size       AS [Max Size]
FROM sys.master_files
WHERE DB_NAME(database_id) = 'SampleDB';
GO

Step 2:

Now, let’s take the full backup of the database by using the following query.

 
--Backup database
BACKUP DATABASE [SampleDB] TO  DISK = N'D:\SampleDB_Backup.bak';
GO

Step 3:

Let’s shrink database log file by using the following script.

 
Use SampleDB
GO

--Let's change recovery model to simple
ALTER DATABASE SampleDB 
SET RECOVERY SIMPLE;
GO

--Let's shrink the database log file
DBCC SHRINKFILE (SampleDB_log, 1, TRUNCATEONLY);
GO

--Let's change recovery model to full
ALTER DATABASE SampleDB 
SET RECOVERY FULL;
GO

Step 4:

Let’s check the database log file size again. As we can see below the database_log file size has been reduced.

 
Use SampleDB
GO

SELECT name           AS [DB File Name]
     , size           AS [Size in KBs]
     , size * 8/1024  AS [Size in MBs]
     , Max_Size       AS [Max Size]
FROM sys.master_files
WHERE DB_NAME(database_id) = 'SampleDB';
GO

Conclusion:

This is one of easiest ways to shrink the database transation log. Do let me know, which method you use and how effective it is.

Posted in Solutions, SQL SERVER | Tagged , , , , , | Leave a Comment »

SQL Server – How to use common table expression (CTE) in user defined function

March 31, 2023 by Muhammad Imran

All of us are familiar with Common Table Expression (CTE) because of its frequent use. We use it in our day-to-day operation quite frequently. Recently, I came across a case where I had to use CTE in a user defined function.

Let me create a sample user defined function (UDF) in Northwind database with the help of CTE to use in my demonstration as shown below.

USE Northwind
GO

CREATE FUNCTION [dbo].[udf_Most_Expensive_Product]
(
	@SupplierID INT
)
RETURNS NVARCHAR(50)
AS
BEGIN
	DECLARE @ProductName NVARCHAR(50)

	;WITH CTE AS
	(
		SELECT ROW_NUMBER() OVER (ORDER BY UnitPrice DESC) AS SNo  
			 , ProductName 
			 , UnitPrice FROM Products 
		WHERE SupplierID = @SupplierID
	)

	SELECT 
			@ProductName = ProductName
	FROM CTE 
	WHERE SNo = 1

	RETURN @ProductName;
END

GO

Let me call the above created user defined function(UDF) and see the results.

USE Northwind
GO

SELECT [dbo].[udf_Most_Expensive_Product](2) AS [ProductName];
GO
--OUTPUT

Conclusion:

It is quite simple to use Common Table Expression (CTE) in user defined function (UDF).

Posted in SQL SERVER | Tagged , , , | 1 Comment »

SQL SERVER 2022 – Error – Fix – Invalid length parameter passed to the RTRIM() function

March 30, 2023 by Muhammad Imran

In SQL Server 2022, an enhancement came in RTRIM()  function, which was a long awaited functionality. This enhancement helps us to remove last character from the string. I have written an article about it in detail.

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

Error:

Invalid length parameter passed to the rtrim function.

Sample:

 
USE tempdb
GO

CREATE TABLE #Employees
(
 EmployeeID   INT IDENTITY(1,1),
 EmployeeName VARCHAR(250),
 EmailAddress VARCHAR(MAX)
);
GO

INSERT INTO #Employees
VALUES ('Alex','alex114@gmail.com;')
     , ('Sarina','sarina152@gmail.com;')
     , ('Sophia','sophiaa123@gmail.com;');
GO

SELECT * FROM #Employees;
GO

Example:

In the given below example, I have written a simple script using RTRIM() function and I declared separator as variable and kept its data type & length as VARCHAR(MAX). This script generates an error as shown below.

USE tempdb
GO

DECLARE @separator VARCHAR(MAX);
SET @separator = ';';

SELECT EmployeeID
	 , EmployeeName
	 , EmailAddress
	 , RTRIM(EmailAddress, @separator) AS EmailAfterRTrim
FROM #Employees;
GO

Solution:

The solution to this problem is that while declaring datatype and length of a separator variable, we should never use MAX as its length since it does not support. We should always give length in numbers as shown below.

USE tempdb
GO

DECLARE @separator VARCHAR(1);
SET @separator = ';';

SELECT EmployeeID
	 , EmployeeName
	 , EmailAddress
	 , RTRIM(EmailAddress, @separator) AS EmailAfterRTrim
FROM #Employees;
GO

Conclusion:

Whenever you use RTRIM() function by using a separator variable, you should always define the length of the variable. Please don’t use MAX as a length to avoid such error.

Posted in Errors, SQL SERVER | Tagged , , , | Leave a Comment »

SQL SERVER 2022 – Error – Fix – Invalid length parameter passed to the LTRIM() function

March 29, 2023 by Muhammad Imran

In SQL Server 2022, an enhancement came in LTRIM() function, which was a long awaited enhancement. This enhancement helps us to remove first character from the string. I have written an article about it in detail.

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

Error:

Invalid length parameter passed to the ltrim function.

Sample:

 
USE tempdb
GO

CREATE TABLE #Employees
(
EmployeeID   INT IDENTITY(1,1),
EmployeeName VARCHAR(250),
EmailAddress VARCHAR(MAX)
);
GO

INSERT INTO #Employees
VALUES ('Alex','alex114@gmail.com;')
     , ('Sarina','sarina152@gmail.com;')
     , ('Sophia','sophiaa123@gmail.com;');
GO

SELECT * FROM #Employees;
GO

Example:

In the given below example, I have written a simple script using LTRIM() function and I declared separator as variable and kept its data type & length as VARCHAR(MAX). This script generates an error as shown below.

USE tempdb
GO

DECLARE @separator VARCHAR(MAX);
SET @separator = ';';

SELECT EmployeeID
	 , EmployeeName
	 , EmailAddress
	 , LTRIM(EmailAddress, @separator) AS EmailAfterLTrim
FROM #Employees;
GO

Solution:

The solution to this problem is that while declaring datatype and length of a separator variable, we should never use MAX as its length since it does not support. We should always give length in numbers as shown below.

USE tempdb
GO

DECLARE @separator VARCHAR(1);
SET @separator = ';';

SELECT EmployeeID
	 , EmployeeName
	 , EmailAddress
	 , LTRIM(EmailAddress, @separator) AS EmailAfterLTrim
FROM #Employees;
GO

Conclusion:

Whenever you use LTRIM() function by using a separator variable, you should always define the length of the variable. Please don’t use MAX as a length to avoid such error.

Posted in Errors, SQL SERVER | Tagged , , , | Leave a Comment »

SQL SERVER 2022 – Error – Fix – Invalid length parameter passed to the TRIM() Function

March 28, 2023 by Muhammad Imran

In SQL Server 2022, an enhancement came in TRIM() function which was a long awaited functionality. This enhancement helps us in removing first or last character from the string. I have written an article about it in detail.

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

Error:

Invalid length parameter passed to the trim function.

Sample:

 
USE tempdb
GO

CREATE TABLE #Employees
(
 EmployeeID   INT IDENTITY(1,1),
 EmployeeName VARCHAR(250),
 EmailAddress VARCHAR(MAX)
);
GO

INSERT INTO #Employees
VALUES ('Alex','alex114@gmail.com;')
     , ('Sarina','sarina152@gmail.com;')
     , ('Sophia','sophiaa123@gmail.com;');
GO

SELECT * FROM #Employees;
GO

Example:

In the given below example, I have written a simple script using TRIM() function and I declared separator as variable and kept its data type & length as VARCHAR(MAX). This script generates an error as shown below.

DECLARE @separator VARCHAR(MAX);
SET @separator = ';';

SELECT EmployeeID
	 , EmployeeName
	 , EmailAddress
	 , TRIM(@separator FROM EmailAddress) AS EmailAfterTrim
FROM #Employees;
GO

Solution:

The solution to this problem is that while declaring datatype and length of a separator variable, we should never use MAX as its length since it does not support. We should always give length in numbers as shown below.

DECLARE @separator VARCHAR(1);
SET @separator = ';';

SELECT EmployeeID
	 , EmployeeName
	 , EmailAddress
	 , TRIM(@separator FROM EmailAddress) AS EmailAfterTrim
FROM #Employees;
GO

Conclusion:

Whenever you use TRIM() function by using a separator variable, you should always define the length of the variable. Please don’t use MAX as a length in order to avoid such error.

Posted in Errors, SQL SERVER | Tagged , , | Leave a Comment »

SQL Server 2022 – TSQL – IS [NOT] DISTINCT FROM

March 27, 2023 by Muhammad Imran

SQL Server 2022 brought some exciting features which will help us in optimising the SQL scripts. IS [NOT] DISTINCT FROM is one of the new features. It helps us in code optimisation and reduce the code complexity.

Compatibility Level:

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

 ALTER DATABASE tempdb SET COMPATIBILITY_LEVEL = 160

Sample:

USE tempdb
GO
 
CREATE TABLE [#Customer](
[CustomerID] INT IDENTITY(1,1),
[CustomerName] VARCHAR(100),
[PhoneNumber] VARCHAR(100),
[AlternatePhoneNumber] VARCHAR(100));
GO
 
INSERT INTO [#Customer]
      ([CustomerName],[PhoneNumber],[AlternatePhoneNumber])
VALUES
      ('Maria','+92-300074321','+92-300074321'),
      ('Trujillo',null,null),
      ('Antonio','+96-562108460',null),
      ('Thomas',null,'+96-0515552282'),
      ('Christina','+92-059675428','+92-05676753425');
GO

SELECT * FROM [#Customer];
GO
--OUTPUT

Example 1:

  • IS DISTINCT FROM
Old Approach:(Using Complex WHERE Clause)

Whenever we need to search some records having NULL, the search criteria will look like this. 


USE tempdb
GO
  
SELECT [CustomerID]
     , [CustomerName]
     , [PhoneNumber]
     , [AlternatePhoneNumber]
FROM   [#Customer]
WHERE
     (([PhoneNumber] <> [AlternatePhoneNumber] 
   OR  [PhoneNumber] IS NULL
   OR  [AlternatePhoneNumber] IS NULL)
   AND NOT ([PhoneNumber] IS NULL
   AND [AlternatePhoneNumber] IS NULL));
GO
--OUTPUT

New Approach:(Using IS DISTINCT FROM)

The complexity and length of where clause has been reduced by the new enhancement which comes with the name IS DISTINCT FROM which takes all unique records of two columns as well as return single null values and drop where both columns are null as shown below:

USE tempdb
GO

SELECT [CustomerID]
     , [CustomerName]
	 , [PhoneNumber]
	 , [AlternatePhoneNumber]
FROM   [#Customer] 
WHERE  [PhoneNumber] IS DISTINCT FROM [AlternatePhoneNumber];
GO
--OUTPUT

Example 2:

  • IS NOT DISTINCT FROM

Old Approach:(Using Complex WHERE Clause)

In old methodology if we had to take same values from two columns and also where both columns have null values, then the query would be as shown below:

USE tempdb
GO

SELECT [CustomerID]
     , [CustomerName]
	 , [PhoneNumber]
	 , [AlternatePhoneNumber]
FROM   [#Customer]
WHERE 
 (NOT ([PhoneNumber] <> [AlternatePhoneNumber] 
  OR   [PhoneNumber] IS NULL 
  OR   [AlternatePhoneNumber] IS NULL)
  OR  ([PhoneNumber] IS NULL 
  AND  [AlternatePhoneNumber] IS NULL));
GO
--OUTPUT

New Approach:(Using IS NOT DISTINCT FROM)

In new Approach, the complexity has been reduced to just single clause named IS [NOT] DISTINCT FROM & rest all conditions are applied by default as shown in the picture below:

USE tempdb
GO

SELECT [CustomerID]
     , [CustomerName]
	 , [PhoneNumber]
	 , [AlternatePhoneNumber]
FROM   [#Customer] 
WHERE  [PhoneNumber] IS NOT DISTINCT FROM [AlternatePhoneNumber];
GO
--OUTPUT

Example 3:

  • IS DISTINCT FROM

Old Approach:(Using Complex HAVING Clause)

In old methodology if we had to take unique values using HAVING Clause from two columns and also single null values and drop where both columns are null, then the query would be as shown below:

USE tempdb
GO
  
SELECT [CustomerID]
     , [CustomerName]
     , [PhoneNumber]
     , [AlternatePhoneNumber]
FROM   [#Customer]
GROUP BY
       [CustomerID]
     , [CustomerName]
     , [PhoneNumber]
     , [AlternatePhoneNumber]
HAVING
     (([PhoneNumber] <> [AlternatePhoneNumber] 
   OR  [PhoneNumber] IS NULL
   OR  [AlternatePhoneNumber] IS NULL)
   AND NOT ([PhoneNumber] IS NULL
   AND [AlternatePhoneNumber] IS NULL));
GO
--OUTPUT

New Approach:(Using IS DISTINCT FROM)

The complexity is being reduced by New Approach where we need to write only one clause and rest is handled by itself as shown in the example below.

USE tempdb
GO
 
SELECT [CustomerID]
     , [CustomerName]
     , [PhoneNumber]
     , [AlternatePhoneNumber] 
FROM   [#Customer]
GROUP BY
       [CustomerID]
     , [CustomerName]
     , [PhoneNumber]
     , [AlternatePhoneNumber]
HAVING
       [PhoneNumber] IS DISTINCT FROM [AlternatePhoneNumber];
GO
--OUTPUT

Example 4:

  • IS NOT DISTINCT FROM

Old Approach:(Using Complex HAVING Clause):

In old methodology if we had to take same values using HAVING from two columns and also where both columns have null values then the query would be as shown below.


USE tempdb
GO
  
SELECT [CustomerID]
     , [CustomerName]
     , [PhoneNumber]
     , [AlternatePhoneNumber]
FROM   [#Customer]
GROUP BY
       [CustomerID]
     , [CustomerName]
     , [PhoneNumber]
     , [AlternatePhoneNumber]
HAVING
(NOT  ([PhoneNumber]  [AlternatePhoneNumber] 
 OR    [PhoneNumber] IS NULL
 OR    [AlternatePhoneNumber] IS NULL)
 OR   ([PhoneNumber] IS NULL
 AND   [AlternatePhoneNumber] IS NULL));
GO
--OUTPUT

New Approach:(Using IS NOT DISTINCT FROM)

The complexity is being reduced by New Approach where we need to write only one clause and rest is handled by itself as shown in the example below.

USE tempdb
GO

SELECT [CustomerID]
     , [CustomerName]
	 , [PhoneNumber]
	 , [AlternatePhoneNumber]
FROM   [#Customer] 
GROUP BY
       [CustomerID]
     , [CustomerName]
	 , [PhoneNumber]
	 , [AlternatePhoneNumber]
HAVING [PhoneNumber] IS DISTINCT FROM [AlternatePhoneNumber];
GO
--OUTPUT

Conclusion:

The IS [NOT] DISTINCT Clause is a life saver where writing a query with multiple conditions just reduced to a single clause. It also reduces the complexity of the query. Do let me know if you used IS [NOT] DISTINCT Clause and found it useful.

Posted in Features, SQL SERVER | Tagged , , , , | Leave a Comment »

Older Posts »