These two operators EXCEPT and INTERSECT have been introduced since SQL Server 2005 and these two returns distinct or unique values by comparing the results of two queries.

Moreover, these operators do have rules for combining result sets just like other operators like UNION and we’ll also discuss it.

That’s why in this article we’ll be showing how we can use these two operators and understand their differences.

What is INTERSECT Operator?
Fundamentally, INTERSECT operator combines two or more queries and returns unique rows or records that are the outcomes of both left and right queries.

Then it removes duplicate records as the final result set.

INTERSECT Syntax
--INTERSECT Syntax
query_expression
INTERSECT
query_expression

It is similar to the UNION operator, that’s why both queries must have the same number and order of columns, and the column data type should be the same.

Furthermore, if these rules aren’t followed an error will occur like failing conversion of data and an equal number of expressions error.

INTERSECT Operator Example
Before we can see examples, let’s try to create a table first with some data in it.
DECLARE @TBLDOGS TABLE (Id int, DogBreed nvarchar(50))
DECLARE @TBLDOG_OWNERS TABLE (Id int, OwnersName nvarchar(50),
DogBreed nvarchar(50))

INSERT INTO @TBLDOGS (Id, DogBreed)
VALUES
(1, 'German Shepherd'),
(2, 'Labrador Retriever'),
(3, 'Golden Retriever'),
(4, 'Poodle'),
(5, 'French Bulldog')

INSERT INTO @TBLDOG_OWNERS (Id, OwnersName, DogBreed)
VALUES
(1, 'Bruce Wayne', 'German Shepherd'),
(2, 'Lex Luthor', 'Golden Retriever'),
(3, 'Vandal Savage', 'Dobermann'),
(4, 'Maxwell Lord', 'Dachshund'),
(5, 'Simon Stagg ', 'Poodle'),
(6, 'Elon Musk', 'German Shepherd')

SELECT * FROM @TBLDOGS
SELECT * FROM @TBLDOG_OWNERS

Output

OK, so everything has been set up. Let’s show how we can use the INTERSECT operator.
SELECT DogBreed FROM @TBLDOGS
INTERSECT
SELECT DogBreed FROM @TBLDOG_OWNERS

Output

As you can see, in our example we have two tables @TBLDOGS and @TBLDOG_OWNERS. Going to the data of the two tables, @TBLDOGS have German Shepherd, Labrador Retriever, Golden Retriever, Poodle, and French Bulldog.

While @TBLDOG_OWNERS have German Shepherd, Golden Retriever, Dobermann, Dachshund, Poddle, and German Shepherd.

The intersection of the two tables resulted in a German Shepherd, Golden Retriever, and Poodle.

What is EXCEPT Operator?
Fundamentally, EXCEPT operator subtracts a query result set from another query result set.

In other words, it returns unique rows or records from the left query that doesn’t appear or are not found on the right query.

EXCEPT Syntax
--EXCEPT Syntax
query_expression
EXCEPT
query_expression

Moreover, both queries must have the same number and order of columns, and the column data type should be the same.

Furthermore, if these rules aren’t followed an error will occur like failing conversion of data and an equal number of expressions error.

EXCEPT Operator Example

Data about dogs from the previous example, we’re still going to use here.

To lessen confusion I’ll be posting again the data sample of dogs.
DECLARE @TBLDOGS TABLE (Id int, DogBreed nvarchar(50))
DECLARE @TBLDOG_OWNERS TABLE (Id int, OwnersName nvarchar(50),
DogBreed nvarchar(50))

INSERT INTO @TBLDOGS (Id, DogBreed)
VALUES
(1, 'German Shepherd'),
(2, 'Labrador Retriever'),
(3, 'Golden Retriever'),
(4, 'Poodle'),
(5, 'French Bulldog')

INSERT INTO @TBLDOG_OWNERS (Id, OwnersName, DogBreed)
VALUES
(1, 'Bruce Wayne', 'German Shepherd'),
(2, 'Lex Luthor', 'Golden Retriever'),
(3, 'Vandal Savage', 'Dobermann'),
(4, 'Maxwell Lord', 'Dachshund'),
(5, 'Simon Stagg ', 'Poodle'),
(6, 'Elon Musk', 'German Shepherd')

SELECT * FROM @TBLDOGS
SELECT * FROM @TBLDOG_OWNERS

Output

OK, so everything has been set up. Let’s show how we can use the EXCEPT operator.

SELECT DogBreed FROM @TBLDOGS
EXCEPT
SELECT DogBreed FROM @TBLDOG_OWNERS

SELECT DogBreed FROM @TBLDOG_OWNERS
EXCEPT
SELECT DogBreed FROM @TBLDOGS

Output

Going to the data of the two tables, the EXCEPT operator resulted differently and it makes a significant difference because of what is on the left table.
Moreover, as we change the left table we have different results.
Moreover, from the example above the first result, the left query is the @TBLDOGS while the right query is the @TBLDOG_OWNERS.
While the second result, the left query is the @TBLDOG_OWNERS while the right query is the @TBLDOGS.
Let’s see the difference between EXCEPT and INTERSECT operators in the next section.

Difference Between INTERSECT and EXCEPT Operators

Remember that the Intersect operator uses an intersection that takes rows from both result sets that are common in both.

While the Except operator takes rows from the left result set but excludes the right result set that isn’t found.

Conclusion
In this article, we have learned how to use the SQL Server’s INTERSECT and EXCEPT operators and have seen some examples to understand these operators.
Moreover, we didn’t forget to show the difference between the two.
I hope you have enjoyed this article, as I enjoyed it while writing.
Till next time, Happy programming! Cheers! And Thank you!

HostForLIFEASP.NET SQL Server 2019 Hosting

Enable configuration in SQL
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Sample API link
-- Sample API Link
http://sbankapi7.somee.com:80/api/Member/GetDataFromServerByMobile

Sample JSON Request
@Mobile varchar(max),
@Flag varchar(max)

{
  "CID": 0,
  "CName": "",
  "AC_Balance": "",
  "AC_No": "",
  "AC_ID": "",
  "CDOB": {},
  "CEmail": "",
  "ReciverEmail": "",
  "CMObile": "'+@Mobile+'",
  "CGender": "",
  "CPan": "",
  "CAdhaar": "",
  "CNationality": "",
  "CAddress": "",
  "City": "",
  "State": "",
  "Country": "",
  "PinCode": "",
  "Cisdelete": 0,
  "CreatedBy": 0,
  "CreatedDate": {},
  "ModifiedBy": 0,
  "ModifiedDate": {},
  "UID": 0,
  "CustImgPath": "",
  "CustAdaarPath": "",
  "CustPanPath": "",
  "Flag": "'+@Flag+'",
  "OpMsg": "",
  "Pass": ""
} '

Content Type
application/json

Create Store Procedure for Calling Web API.
--//========================
    -- if you learn more please visit my blog

  -- https://saipathrikar.blogspot.com/
--//========================
--================ execute this 1st for 1st time use only

Create Proc Proc_CallApiFromSQL
(
@Mobile varchar(max),
@Flag varchar(max)
)
as
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
 declare @json table (Json_Table nvarchar(max))
 declare @body varchar(max)
 declare @Apilink varchar(max)
 set @Apilink='http://sbankapi7.somee.com:80/api/Member/GetDataFromServerByMobile';
 set @body='

 {
  "CID": 0,
  "CName": "",
  "AC_Balance": "",
  "AC_No": "",
  "AC_ID": "",
  "CDOB": {},
  "CEmail": "",
  "ReciverEmail": "",
  "CMObile": "'+@Mobile+'",
  "CGender": "",
  "CPan": "",
  "CAdhaar": "",
  "CNationality": "",
  "CAddress": "",
  "City": "",
  "State": "",
  "Country": "",
  "PinCode": "",
  "Cisdelete": 0,
  "CreatedBy": 0,
  "CreatedDate": {},
  "ModifiedBy": 0,
  "ModifiedDate": {},
  "UID": 0,
  "CustImgPath": "",
  "CustAdaarPath": "",
  "CustPanPath": "",
  "Flag": "'+@Flag+'",
  "OpMsg": "",
  "Pass": ""
} '

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'post',@Apilink,'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

  INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'
-- select the JSON string
--select * from @json
-- Parse the JSON string
SELECT * FROM OPENJSON((select * from @json))
WITH (
CID bigint,CName varchar(max),AC_Balance varchar(max),AC_No varchar(max),AC_ID bigint,CDOB datetime,
CEmail varchar(max),ReciverEmail varchar(max),CMObile varchar(max),CGender varchar(max),CPan varchar(max),
CAdhaar varchar(max),CNationality varchar(max),CAddress varchar(max),City varchar(max),State varchar(max),
Country  varchar(max),PinCode varchar(max),Cisdelete bit,CreatedBy varchar(max),CreatedDate datetime,ModifiedBy varchar(max),
ModifiedDate datetime,UID bigint,CustImgPath varchar(max),CustAdaarPath varchar(max),CustPanPath varchar(max),
Flag varchar(max),OpMsg varchar(max),Pass varchar(max)
)
return

Execute Stored Procedure
EXEC  Proc_CallApiFromSQL '8541254874','SE'

Output

HostForLIFEASP.NET SQL Server 2019 Hosting

Create Sequence in SQL
CREATE SEQUENCE Seq_AccountNumber As BigInt
Start with 100001
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999999999999999
CYCLE
CACHE;

Use Sequence in Procedure for generating numbers Like "Sbank_10001".
CREATE Proc Proc_GenerateAccountNumber
as
Declare @BnkName varchar(100)='Sbank_';
Declare @AccountNumber varchar(max);

SELECT @AccountNumber =CONCAT(@BnkName ,
 next value for dbo.Seq_AccountNumber
)
SELECT @AccountNumber as AccountNumber
Return

Now execute procedure "Proc_GenerateAccountNumber" & check output

If you want to restart Sequence Alter Sequence,
alter sequence dbo.Seq_AccountNumber Restart with 10001

HostForLIFEASP.NET SQL Server 2019 Hosting

Today we will look at a very common debate that occurs while developing a data access layer. This debate is to decide if we want to use stored procedures and keep our logic in the database, or if we want to use an ORM like Entity Framework and keep all logic in our middle tier C# code, using the database only as a data store. I will not be detailing the different types of ORMs and samples on using them as this information is widely available on the web. I will also not detail how to write and use stored procedures, as this information is also widely available for almost all types of databases. I will simply try to explain the pros and cons of using each and which and why I think it is a better solution.

What are Stored Procedures?
Let us begin by identifying what stored procedures are. Stored procedures are commonly used artifacts of relational databases, like SQL Server, Oracle, etc. These can include code to manipulate the data and are efficient in selecting, processing, and sending back data to the front-end application. These are written using SQL language, which is easy to understand and learn. Some of the major advantages of using stored procedures are that these are compiled, optimized, and run by the database engine and so can perform very quickly. For this reason, I have seen some architects strongly push the use of stored procedures.

What is an ORM?
An ORM means object relational mapping. This method allows us to create classes to represent our database tables, and then use a context or communication class to join these classes to our database tables. Then, we can use the full power of the middle-tier programming language, like C#, to write queries to select and update the data in our tables. Of course, an ORM does not end there, and we can also create other artifacts for stored procedures, etc. We can also merge classes from multiple tables and break a single table into multiple classes. However, the main idea is to use the database mainly as a store and keep all processing logic in the middle-tier code.

What to use today? Stored Procedures vs ORM
These days we have a much-advanced landscape for both stored procedures in various databases, and also great ORMs. For example, Entity Framework Core gives us many options and is easy to understand and implement. So, what should we use? The answer is that it depends upon the particular scenario. However, I would prefer to use an ORM, as in this way we can better abstract all business and processing logic into proper classes inside our middle-tier, and use the power of all libraries, NuGet packages, etc. available at this level. This also saves us from being tied to a particular database, and in the future, we can move to another data store quite easily. However, if we need to do some heavy programming at a database level and want to fully utilize the power of the database engine, using stored procedures would be the way to go. However, in this case, we might need to commit to a particular database for a longer period, especially if we are using a SQL dialect for that particular database to write our stored procedures.

Summary
In this article we looked at what to choose to build, our data access or data process layer. The answer depends on the coder's needs, and different people will have different opinions on it. However, from my personal point of view, I prefer to keep all logic in the middle-tier. This way the logic is better abstracted, and we can use the vast number of libraries available to process the data. Also, we can retrieve data in chunks and process it using parallel programming techniques and utilize the extremely powerful hardware we have these days. This also does not tie us to any particular database.

HostForLIFEASP.NET SQL Server 2019 Hosting

T-SQL supports different types of table expressions,
    Views
    Functions
    Derived Tables (DT)
    Common Table Expressions (CTE)

We will have separate articles on functions and views. But today’s post is related to Derived Table and Common Table Expression and their differences. By the way, what is table expression?

Table expression - is a special type of named query which allows us to build relational-oriented queries. You create a special query, rename it, and can use it in multiple types ( at least as a view and a function)

You can wrap your query into views and functions, store them as an object in the database and call them as a user-defined table which can have an argument (function) or without it.

But unfortunately, the above sentence is not correct for DT and CTE. T-SQL provides your to wrap your query into Derived table and Common Table Expressions but storing them directly as a separate object is not possible.
Why do we need to use Derived Table and Common Table Expression?

When working with SELECT, we usually apply special searching/filtering to our queries. But in a few cases, it is not possible to do it.
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum, *
FROM Sales.SalesOrderDetail AS SOD

Let’s try to filter numbers that are less than 200.
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum, *
FROM Sales.SalesOrderDetail AS SOD
WHERE rownum < 200

As we know from this article, every statement has its logical execution number and Where executes before SELECT and defined alias in SELECT can’t be detected in WHERE. How about using expression directly:
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum, *
FROM Sales.SalesOrderDetail AS SOD
WHERE Row_number() over(order by SalesOrderDetailId) < 200

It is also not possible because Windowed functions can only appear in the SELECT and ORDER clauses. (read error message)

So, how we can filter numbers?

Except for using views and functions there are 2 more options,
    Derived Tables
    Common Table Expressions

2 types of writing Derived tables,
SELECT * FROM
(
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum,
SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail AS SOD) AS DT
WHERE DT.rownum < 200

--second version of writing derived tables

SELECT * FROM
(
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum,
SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail AS SOD) AS DT(RowNumber, SalesOrderID, CTrackingNumber)
WHERE DT.RowNumber < 200

Now, it is possible to wrap and filters our query. When comparing with Subqueries, Derived tables allow us to wrap the entire query but in subqueries, the inner query “lived” in the WHERE clause.

There are 2 “but”s when using derived tables:

It is really hard to refer from one derived table to another when building complex queries:
SELECT ...
FROM (SELECT
    FROM (SELECT ...
        FROM T1
            WHERE ...) AS MyDT1
         WHERE ....) AS MyDT2
    WHERE ....

If you want to combine multiple instances of the same derived table, it will not be possible.
SELECT * FROM
 (
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum,
SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail AS SOD)
AS DT(RowNumber, SalesOrderID, CTrackingNumber)
INNER JOIN DT as DT2
ON DT.RowNumber = DT2.RowNumber

The problem here is related to the same-time execution of expression in the T-SQL lifetime.

To write it correctly, you need to type DT twice.
SELECT * FROM
 (
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum,
SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail AS SOD)
AS DT(RowNumber, SalesOrderID, CTrackingNumber)
INNER JOIN (SELECT
Row_number() over(order by SalesOrderDetailId) as rownum,
SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail AS SOD)
AS DT2(RowNumber, SalesOrderID, CTrackingNumber)
ON DT.RowNumber = DT2.RowNumber

rom the usage point of view, CTE does the same thing DT does. We can wrap and filter, join, and order our queries like in DT. But you can think about it as version 2.0 of Derived Tables.
WITH CTE
AS(
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum, *
FROM Sales.SalesOrderDetail AS SOD)
SELECT * FROM CTE
WHERE rownum < 200;

What are the advantages of using Common Table Expressions?

It is really easy to refer from one CTE to another,
WITH CTE
AS(
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum, *
FROM Sales.SalesOrderDetail AS SOD)
,
CTE2 AS --from CTE2 we referred to CTE
(SELECT * FROM CTE)
SELECT * FROM CTE2;

If you want to combine multiple instances of the same expressions, it is also easy to do it with CTE.
WITH CTE
AS(
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum, *
FROM Sales.SalesOrderDetail AS SOD)
SELECT * FROM CTE
INNER JOIN CTE as CTE2
ON CTE.rownum = CTE2.rownum

Writing recursive queries with CTE:
CREATE TABLE [dbo].[RecursiveTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Level] [int] NULL,
    [SubLevel] [int] NULL,
    [Name] [nvarchar](30) NOT NULL,
    [Position] [varchar](30) NULL
) ON [PRIMARY]
GO

WITH CTE
AS
(SELECT Id, Level, SubLevel, Name, Position
FROM Rekursiya WHERE Id = 1
UNION ALL
SELECT R.Id, R.Level, R.SubLevel, R.Name, R.Position
FROM CTE
INNER JOIN RecursiveTable AS R
ON R.SubLevel = CTE.Level)
SELECT * FROM CTE;

Working with PIVOT and Unpivot ( I’ll write a separate article about it )

HostForLIFEASP.NET SQL Server 2019 Hosting

We always have chance to use connection strings, such as in an application. When we need it, the first idea is to acquire it from SQL Server Managerment Studio. However, it seems we cannot.

This article will introduce several ways that we can get connection strings easily. The article will cover the following:
    Introduction
    A - Get Connection string by SSMS (SQL Server Management Studio) --- no way
    B - Get Connection string from online reference or generator
    C - Get Connection String by Visual Studio, Server Explorer
    D - Get Conenction String by Microsoft Universal Data Link file (xxx.udl)

A - Get Connection String by SSMS
Although the first expection is to get Connection String from SSMS, but actually, we cannot. Open SSMS, right click a Database Connection => Properties.

Open the Server Properties Window. This is most possible place where I might get the database Connection String. However, we have all parameters available, but we cannot see the whole connection string.

Click the link "View Connection Properties" on the left panel of this Window:

As before, I can get all parameters, but not the connection string.
B - Get Connection String by online Reference or Generator

online Reference:
From the Web, we can check and get the connection string we need, such as in:

ConnectionStrings References - ConnectionStrings.com --- in general
    SQL Server connection strings - ConnectionStrings.com --- Specific for SQL Server

We can get connection string like this:

However, we do not have chance to test the connection string.

Online Generator:

We can use an online Connection String generator, such as:

    SQL Server Connection String Generator | Aireforge®

To generate a Connection String by your parameters:

You can get a real and ready connection string using your specifc parameters, but stil cannot make a test.

C - Get Connection String by Visual Studio's Server Explorer
In Visual Studio IDE, Click View => Server Explorer:

In the Server Explorer, click the Connect to Database icon:

To add a connection:

In this way, we can test if the connection is working.

Finally, click the Advanced Button. Then we can get the connection string at the bottom of the Advanced Properties Window.

Otherwise, after creating the connection, right click => Properties

In Properties Window, we will see the ConnectionString there too:

D - Get Connection String by Microsoft Universal Data Link file (xxx.udl)
We can create a Data.udl file in the computer:

Double click the Data.udl file, and set up the connection:

Click the Test Connection button:

Close the created connection, and open the Data.udl file by another editor, such as VS Code:

We will see the connection string:

HostForLIFEASP.NET SQL Server 2019 Hosting

This article demonstrates how we can easily rename various database objects like Tables, Columns, Constraints, Indexes, SP in SQL Server. You may have faced a scenario where sometimes we need to rename database objects to specify correct business significance and meaning to the system on production code. The database objects which were originally designed may not match the current business objects. To solve this problem, you may need to rename existing database objects like table name, column name, store procedure name, etc. The best and easiest way is to use SP_RENAME, a build-in stored procedure to rename these objects. This approach is recommended because we can run pre-deployment scripts in the environment before deploying these changes. SP_RENAME takes below arguments. Reference has been taken from Microsoft documentation.

To demonstrates this, I am creating a table with Primary key, check constraint, non-clustered index and putting some data into this table. We will compare before and after snapshots.

CREATE TABLE OrderInfo
(
    Id INT IDENTITY(1, 1) NOT NULL,
    OrderMode VARCHAR(20) CONSTRAINT [CK_OrderInfo_OrderMode] CHECK (OrderMode IN ('ONLINE','OFFLINE')) NOT NULL,
    OrderName VARCHAR(100) NOT NULL,
    OrderDate DATETIME CONSTRAINT [DF_OrderInfo_OrderDate] DEFAULT (GETDATE()) NOT NULL,
    CONSTRAINT PK_OrderInfo_Id PRIMARY KEY NONCLUSTERED (Id ASC)
)

CREATE NONCLUSTERED INDEX IX_OrderInfo_OrderMode ON dbo.OrderInfo (OrderMode)

INSERT INTO  OrderInfo
VALUES
    ( 'ONLINE', 'Notebook', GETDATE()),
    ( 'ONLINE', 'PC', GETDATE()),
    ( 'OFFLINE', 'Printer', GETDATE())
GO

Before snapshot of table, constraints, and index.

One thing to keep in mind is that when we rename these objects, we need to make changes in dependencies of these objects. For example – if you are renaming a table and that table is being used in multiple SPs then we also to modify those SPs as well. But that is a manual activity to find and fix. This warning represents the same thing.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this post, we’ll explore the IIF SQL functionality. IIF (If and only if) function, and we’ll use the AdventureWorks database for our testing purposes. Let’s get started.
What’s IIF Function?

Introduced in SQL Server 2012
This function returns one of two values depending on the boolean expression [also known as condition] that evaluates true or false.
IIF is composed of the logical statement, the boolean expression known as the condition, followed by the “true” and the “false” expressions.
This function can be compared to CASE expressions and a shorthand way of writing a CASE expression.

IIF Syntax
-- Syntax
    IIF(boolean_expressions, true, false)
-- OR
    IIF(condition, true, false)

    Boolean expression or condition – this is required, and it is because of the value that needs to be tested.
    True - this is optional, but the value is returned if the condition is true.
    False - this is optional, but the value is returned if the condition is false.

Examples
1. Compare Numbers
-- Let's declare and initialize two numbers
DECLARE @NUM1 INT = 20;
DECLARE @NUM2 INT = 25

--OUTPUT: 25 is greater than 20
SELECT IIF( (@NUM2 > @NUM1), FORMATMESSAGE('%i is greater than %i', @NUM2, @NUM1),
                             FORMATMESSAGE('%i is greater than %i', @NUM1, @NUM2))
                             AS [ComparingNumbers];
--OUTPUT: TRUE
SELECT IIF(@NUM2 > @NUM1, 'TRUE', 'FALSE')  AS [ComparingNumbers];

SET @NUM1  = 120;
SET @NUM2  = 25;

--OUTPUT: 120 is greater than 25
SELECT IIF((@NUM1 > @NUM2), FORMATMESSAGE('%i is greater than %i', @NUM1, @NUM2),
                            FORMATMESSAGE('%i is greater than %i', @NUM2, @NUM1))
                             AS [ComparingNumbers];
--OUTPUT: TRUE
SELECT IIF(@NUM2 > @NUM1, 'FALSE', 'TRUE')  AS [ComparingNumbers];

As you can see from the example we have shown how we can easily compare numbers using the IIF function.

Output

2. Compare numbers within a table column
This section will try to explore some examples using the AdventureWorks database. Checking the Person Person table of the AdventureWorks database, you’ll see a NameStyle column. This column uses bit as its datatype. When it is 0, FirstName and LastName are stored in Western-style (first name, last name). Otherwise, when it is 1, it is stored in an Eastern-style (Lastname, first name) order.

Let’s try to use the IIF function here.
USE [AdventureWorks2019]
/** Let’s try to format the person’s names using either western-style or eastern-style by using IIF.*/
SELECT IIF(NameStyle = 0,
    CONCAT(FirstName, ', ' , LastName),
    CONCAT(LastName, ', ', FirstName))
FROM
[Person].[Person]

Output

3. Compare strings within a table column
In this example, we’ll try to get the Employee’s marital status by using the IIF function and joining the two tables [HumanResources].[Employee] and [Person].[Person] inside the AdventureWorks database. The column MaritalStatus uses the nchar(1) datatype, and M stands for married, while S stands for single.

Let’s see an example below.
USE [AdventureWorks2019]

SELECT TOP 10 IIF(MaritalStatus = 'M', 'Married', 'Single') as [Marital Status],
    FirstName,
    LastName
FROM [HumanResources].[Employee] E
INNER JOIN [Person].[Person] P
ON E.[BusinessEntityID] = P.[BusinessEntityID]

Output

4. Nested SQL IIF Statement
In this example, we’ll try to get the Person’s type by using a nested IIF function and checking the PersonType column, which uses nchar(2) as its datatype. Has many meanings.

Let’s see the list below.
    SC = Store Contact
    IN = Individual
    SP = Sales Person
    EM = Employee
    VC = Vendor Contact
    GC = General Contact

USE [AdventureWorks2019]
-- Randomly select rows in this table (10 percent)
SELECT TOP 10 PERCENT
       FirstName,
       LastName,
       IIF(PersonType = 'EM', 'Employee',
       IIF(PersonType = 'SC', 'Store Contact',
       IIF(PersonType = 'IN', 'Individual',
       IIF(PersonType = 'SP', 'Sales Person',
       IIF(PersonType=  'VC', 'Vendor Contact',
       IIF(PersonType = 'GC', 'General Contact', 'n/a')))))) AS [Type of Person]
FROM [Person].[Person]
ORDER BY NEWID()

Just a reminder, as you can see, the query sample is randomly selecting records on the [Person].[Person] table to see different results every time we execute the query. So your results will be further from the output shown below.

Output

5. SQL IIF and NULL Value
When dealing with the IIF function and passing a NULL value within both the true and false expressions/parameters, it will throw an exception. But before we see an example, we’ll be using the [Production].[Product] table as our example and let’s try to see the number of days to manufacture a certain product.
USE [AdventureWorks2019]
SELECT MIN(DaysToManufacture), Max (DaysToManufacture) FROM [Production].[Product]

If you will execute the query above, this will show us that the minimum number of days to manufacture a product is 0, and the maximum number of days to manufacture a product is 4 days.

Now, let’s try to use the IIF function and check if the [DaysOfManufacture] is zero and pass the value NULL to both parameters.
USE [AdventureWorks2019]
SELECT IIF(DaysToManufacture = 0, NULL, NULL) FROM [Production].[Product]

Output

It’s clearly saying that we need to have at least one true or false argument and be mindful next time not to encounter this exception.

Let’s see an example below.
USE [AdventureWorks2019]
SELECT DaysToManufacture,
         IIF(DaysToManufacture = 0, 'Fast to manufacture.', NULL) FROM [Production].[Product]

Output

Summary
In this article, we have discussed the following:
    What’s IIF Function
    IIF Syntax
    Examples
        Compare numbers
        Compare numbers within a table column
        Compare strings within a table column
        Nested SQL IIF Statement
        SQL IIF and NULL Value

Once again, I hope you have enjoyed reading this article/tutorial as much as I have enjoyed writing it.

Stay tuned for more. Until next time, happy programming!

Please don't forget to bookmark, like, and comment. Cheers! And Thank you!

HostForLIFEASP.NET SQL Server 2021 Hosting

In this article, I will guide you in how to take SQL Server Database backup to a local folder. There are two ways to take a database backup.

Method 1
Open SQL Server Management Studio (SSMS) and follow the below steps
Select the Database that you want to take backup.
For example, here I am using the EmployeeDB database.
Select Database. Right click on database -> select Task -> Back Up.

Once we click on Back up, a pop-up window will open.
This window will show the Database name from which we are taking a backup. Select the backup type as Full, and back up to Disk.

Now click on the remove button and then click on Add. This will open one more popup window, which will allow us to choose our specific path. Now once we navigate to our path, we need to provide the database file name. In my case, I have given EmployeeDB.bak. and click on OK.

Note: ".bak" is an extension for backup.

Once we click Ok, our backup path will be set. Now click Ok.

Once we click on OK our database backup will be created to our provided path.

Now we can verify whether our database backup is created or not.

Method 2
Using SQL Query.
declare @backuppath as nvarchar(max)
set @backuppath  = N'C:\WorkingProjects\Practice\DataBase\Employee\EmployeeDB_'
+ CONVERT(nvarchar,YEAR(getdate()))
+ CONVERT(nvarchar,Month(getdate())) +
+ CONVERT(nvarchar,DAY(getdate())) + '.bak'
Backup Database [EmployeeDB] to DISK = @backuppath WITH NOFORMAT, NOINIT, NAME=N'EmployeeDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Now execute the query

Once the query is executed, it will create a database backup to the specified path.
Eg. In my case path is "C:\WorkingProjects\Practice\DataBase\Employee".

Now, we can verify whether our database backup is created or not.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, I will guide you on how to delete old backup files on an SQL Server Database. Though we have moved to the cloud, there are still some projects that have their own server machines, on which we do deployment, SQL Server Database backups, and other jobs.

There are two ways we can delete old database backup files:
    Using C# Code (delete files from location)
    Using SQL Script (master.sys.xp_delete_file)

Delete SQL Server Database backup files Using C# Code
Set a path in App.config file.
<appSettings>
        <add key="path" value="C:\WorkingProjects\Practice\DataBase\Employee\DBBackup"/>
</appSettings>

In Program.cs file.
In my case, I have set it to delete database backup files that are older than one week.

Note
    I have used CreationTime, which will return the date and time when the database backup was created.
    .bak is an extension of SQL Server backup file.

var directorypath = ConfigurationManager.AppSettings["path"].ToString();
var files = Directory.GetFiles(directorypath, "*.bak");

foreach (var file in files)
 {
   var _file = new FileInfo(file);
   if (_file.CreationTime < DateTime.Now.AddDays(-7)) // weekly
        _file.Delete(); // Delete File
 }

SQL Server DB backups which we want to delete.

Before Code execution (Before Old DB backup Delete)

After Code execution (After Old DB backup Delete).
Now, we can verify whether our old database backup is deleted or not.

Delete SQL Server Database backup files Using SQL Query (xp_delete_file)
DECLARE @name NVARCHAR(500); -- Database name
DECLARE @path NVARCHAR(500); -- Path for backup files
DECLARE @fileName VARCHAR(500); -- Filename for backup
DECLARE @fileDate VARCHAR(20) = CONVERT(VARCHAR(20),GETDATE(),112); -- Used for file name
DECLARE @FileExtension nvarchar(4) = N'.BAK' -- file extension
DECLARE @DeleteDate DATETIME = DATEADD(wk,-1,GETDATE()); -- last week date

-- Path to backups.
SET @path = 'C:\WorkingProjects\Practice\DataBase\Employee\DBBackup'

-- Dynamically get each database on the server.
DECLARE db_cursor CURSOR FOR

SELECT NAME
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB');

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @name;

-- Loop through the list to backup each database.
    WHILE @@FETCH_STATUS = 0
    BEGIN
          -- Build the path and file name.
          SET @fileName = @path + @name + '_' + @fileDate + @FileExtension;
          --select @fileName

          -- Loop to the next database.
          FETCH NEXT FROM db_cursor INTO @name;
    END

--Delete backup files
EXEC master.sys.xp_delete_file 0,@path,'BAK',@DeleteDate,0;

CLOSE db_cursor;
DEALLOCATE db_cursor;
GO

xp_delete_file takes five parameters:
    File Type - 0 for backup files or 1 for report files.
    Folder Path - The folder to delete files.
    File Extension - This could be ‘BAK’.
    Date - Cutoff date for what files need to be deleted.
    Subfolder - 0 to ignore subfolders, 1 to delete files in subfolders.

Before Query execution (Before Old DB backup Delete)

After Query execution (After Old DB backup Delete).
Now we can verify whether our old database backup has been deleted or not.

Note
We can add our .exe file in the task scheduler and script in the SQL Job, which we can automate to execute as per our choice (weekly/monthly).