Here, I have provided an article showing you how to utilize the two new logical functions Choose and IIF in SQL Server. The Choose function works like an array kind of thing and the IIF function is used to check a condition. In this article we will see both functions with examples. These functions are also called new logical functions in SQL Server 2012. So let's take a look at a practical example of how to use the Choose and IIF functions in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

These are the two logical functions:
    IIF() Function
    Choose() Function

IIF() Function
The IIF function is used to check a condition. Suppose X>Y. In this condition a is the first expression and b is the second expression. If the first expression evaluates to TRUE then the first value is displayed, if not the second value is displayed.

Syntax
IIF ( boolean_expression, true_value, false_value )

Example
DECLARE @X INT;
SET @X=50;
DECLARE @Y INT;
SET @Y=60;
Select iif(@X>@Y, 50, 60) As IIFResult

In this example X=50 and Y=60; in other words the condition is false.  Select iif(@X>@Y, 50, 60) As IIFResult. It returns false value that is 60.

Output

Choose() Function
This function is used to return the value out of a list based on its index number. You can think of it as an array kind of thing. The Index number here starts from 1.

Syntax
CHOOSE ( index, value1, value2.... [, valueN ] )
CHOOSE() Function excepts two parameters,

Index: Index is an integer expression that represents an index into the list of the items. The list index always starts at 1.

Value: List of values of any data type.

Now some facts related to the Choose Function

1. Item index starts from 1
DECLARE @ShowIndex INT;
SET @ShowIndex =5;
Select Choose(@ShowIndex, 'M','N','H','P','T','L','S','H') As ChooseResult

In the preceding example we take index=5. It will start at 1. Choose() returns T as output since T is present at @Index location 5.

Output

2.  When passed a set of types to the function it returns the data type with the highest precedence; see:
DECLARE @ShowIndex INT;
SET @ShowIndex =5;
Select Choose(@ShowIndex ,35,42,12.6,14,15,18.7)  As CooseResult

In this example we use index=5. It will start at 1. Choose() returns 15.0 as output since 15 is present at @ShowIndex location 5 because in the item list, fractional numbers have higher precedence than integers.

3. If an index value exceeds the bound of the array it returns NULL
DECLARE @ShowIndex INT;
SET @ShowIndex =9;

Select Choose(@ShowIndex , 'M','N','H','P','T','L','S','H')  As CooseResult

In this example we take index=9. It will start at 1. Choose() returns Null as output because in the item list the index value exceeds the bounds of the array; the last Index=8.

Output

4. If the index value is negative then that exceeds the bounds of the array therefore it returns NULL; see:
DECLARE @ShowIndex INT;

SET @ShowIndex =-1;

Select Choose(@ShowIndex, 'M','N','H','P','T','L','S','H')  As CooseResult

In this example we take index= -1. It will start at 1. Choose() returns Null as output because in the item list the index value exceeds the bounds of the array.

Output

5. If the provided index value has a float data type other than int, then the value is implicitly converted to an integer; see:
DECLARE @ShowIndex  INT;

SET @ShowIndex =4.5;

Select Choose(@ShowIndex ,35,42,12.6,13,15,20) As CooseResult

In this example we take index= 4.5. It will start at 1.  If the specified index value has a float data type other than int, then the value is implicitly converted to an integer. It returns the 13.0 as output since 15 is present at @ShowIndex=4.5 which means index is 4.

Output

Have you ever come across a situation where you need to check first if a table exists? Or have you come across a scenario where you need to check if the table's column exists?  Then alter the table by adding your desired column. If you have answered yes to any of these two, you came to the right place.

This post will explore the INFORMATION_Schema views, learn what it is, and show you some of its common usages.

By the way, I'll be using SQL Server 2016, but it can also be applied with other older and later versions of SQL Server.
What is INFORMATION_SCHEMA Database?

The INFORMATION_SCHEMA stores other databases' details on the server.

It gives you the chance to retrieve views/information about the tables, views, columns, and procedures within a database.

The views are stored in the master database under Views->System Views and will be called from any database you choose.

Let's see a screenshot below,

Benefits
As a database developer or administrator, understanding schema and what tables are in a specific database gives us a good idea what's the underlying structure, which can help you write SQL queries. It also allows us to check if everything is expected or on the database. Moreover, it also helps us avoid running queries multiple times to see if the schema name or column name is correct.

Common Usages
Before we start, we'll be using the Northwind database for all our examples. You can download it from here. Now, here are the steps we're going to take. First, get all the databases on our local SQL Server instance, get all the tables, third, let's search for a column, fourth search for constraints, and the last query some views.
Showing All Databases

Let's try to show first all of the databases on a current server instance that we're in.

Note that my result will differ from yours, but you'll see all the databases on your SQL Server instance once you run the query below.
SELECT * FROM sys.databases;

EXEC sp_databases;

Output

The syntax on how we were able to show the database isn't directly related to INFORMATION_Schema.

However, it is an excellent start for us as we go through from database to tables to columns to keys.
Showing All Tables

Now that we have an idea of getting the database on a SQL Server instance.

In this section, we will try to get all of the possible tables of the Northwind database.
USE [Northwind];
--show all table
SELECT * FROM INFORMATION_SCHEMA.Tables;

Querying Column of a Specific Table
In this example, we'll explore how to check a table and then a column if it exists.

Then add a new column to the categories table. Then let's set Tags as its column name and set its data type as NVARCHAR(MAX).

Let's try to see a sample query below.
USE [Northwind];

/*
 * Let's try to declare some variables here that we can use later when searching for them.
 */
DECLARE @TABLE_NAME NVARCHAR(50);
SET @TABLE_NAME = 'Categories';

DECLARE @COLUMN_NAME NVARCHAR(50);
SET @COLUMN_NAME = 'Tags';

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE
                    TABLE_NAME = @TABLE_NAME)
    BEGIN

        PRINT CONCAT('1. Categories table does exists.',
                    CHAR(13), '1.1 Let''s now create the Tags column.');

        IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE TABLE_NAME = @TABLE_NAME
                    AND COLUMN_NAME = @COLUMN_NAME)
            BEGIN
                PRINT '2. Dropping the Tags column of the Categories table.';
                ALTER TABLE [Categories]
                DROP COLUMN [Tags];
            END

        BEGIN
            PRINT '3. Creating the Tags column of the Categories table.';

            ALTER TABLE [Categories]
            ADD [Tags] NVARCHAR(MAX);

            DECLARE @ADDED_COLUMNS NVARCHAR(MAX);

            SET @ADDED_COLUMNS = CONCAT('4. Categories table columns: ',
                                        (SELECT STRING_AGG(COLUMN_NAME, ',')
                                        FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLE_NAME));

            PRINT @ADDED_COLUMNS;
        END
    END
ELSE
    BEGIN
        PRINT 'CATEGORY TABLE DOESN''T EXISTS';
    END

Going to the example above, as you can see, we have to check if the Categories-table exists. Then from that, we did check if the Tags column does exist. If it does, we need to drop the existing column. And after that, we have re-created the Tags column. Lastly, we have shown all the Categories-table columns by a comma-separated list.

Just a note, the STRING_AGG function is a function that can be applied to SQL Server 2017 and later.

Output

Find Foreign Keys, Primary Key, and Check Constraint in a Table
Let's see how we can query the primary key, foreign key and check the constraint of a specific table.

In this case, we're just going to use the [Order Details] table and show the constraints such as primary key, foreign key, and check constraint.

Let's see the query below.
USE [Northwind];

SELECT CONSTRAINT_TYPE, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE IN ('PRIMARY KEY', 'FOREIGN KEY', 'CHECK')
AND TABLE_NAME = 'Order Details';

Querying Views
In this last section, we'll make a simple query that will show the Views inside the Northwind database.

Let's see the query below.
USE [Northwind];

SELECT TABLE_CATALOG AS 'Database Name',
       TABLE_NAME AS 'View Name',
       View_Definition as 'Query'
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME  LIKE 'Products%'

I hope you have enjoyed this article. I know that there are many ways you can use INFORMATION_Schema.

Moreover, this article has given you a jump start by showing you how to get all the databases, get all the tables, search for a column, search for constraints and search for views using the INFORMATION_Schema.

Once again, I hope you have enjoyed this article/tutorial 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!

In this article, you will learn how to take backup of database using SQL queries in a compressed condition and in uncompressed condition.

Prerequisites
    Install SQL Server
    Install SQL Server Management Studio (SSMS)

For above prerequisites, you can follow the article that is How to Install SQL Server and SQL Server Management Studio (SSMS)
Backup of Database in compressed condition

Use the below query to take the backup of database in compressed condition.
BACKUP DATABASE Database_Name TO
DISK = 'BackupPath\BackupFileName.bak'
WITH COMPRESSION
GO

Backup of Database in uncompressed condition
Use the below query to take the backup of database in uncompressed condition.
BACKUP DATABASE Database_Name TO
DISK = 'BackupPath\BackupFileName.bak'
WITH NO_COMPRESSION
GO

Restore the database using backup file with SQL query
RESTORE DATABASE Database_Name
FROM DISK = 'BackupPath\BackupFileName.bak'
WITH RECOVERY
GO

RESTORE DATABASE Database_Name
FROM DISK = 'BackupPath\BackupFileName.bak'
WITH RECOVERY
GO

Now I will demonstrate the same with the help of an example.
Before we start we need to know where do we define the following items in the SQL query as shown in figure-1.
    Database name
    Backup folder location
    Backup file name
    Backup file extension

As I have a database named as "Company" now I am taking backup of this database in compressed condition as shown in figure-2.

Now I can see the Backup file is created successfully and saved at the defined location as shown in picture below

now I am going to delete the Database using Drop query as shown in picture below

Now restore the database using backup file as shown in a picture below:

Now I am going to create a backup of the same database "Company" in uncompressed condition as shown in a picture below:

Now I am going to create a backup of the same database "Company" in uncompressed condition

Now follow the same step as shown in figure-4 to delete/drop the database, then follow the steps as shown in figure-5 to restore the database using compressed backup file.

Difference between Compressed Backup and Uncompressed Backup

HostForLIFEASP.NET SQL Server 2021 Hosting

In this article, we learn how to import CSV files into SQL server using SQL server management Studio
    Log in to your database using SQL Server Management Studio.
    Right-click the database and select Tasks -> Import Data.

Then click the Next button.

From Data Source, select Flat File Source and then use the Browse button to select the CSV file.

Here is my sample CSV file that has some dummy data.

Then select Advanced, and as per your CSV data size is required to set column width.

Then click the Next button.

Destination select as SQL Server Native Client and Enter the Server name;
Enter SQL Server Authentication data and click the Next button.

Then click the Next button.

Then click the Next button.

Then click the Finish button.

CSV file data imported successfully in SQL database table. The table is created as per the CSV file name. Select the database and execute a query.

XML acronym for eXtensible Markup Language. This markup language is much similar to HTML. It is designed to store and transport data. Moreover, the XML tag is not predefined, it is designed to be self-descriptive.

For XML Clause

For XML clause is used to convert SQL result set into XML format. It is a very much helpful clause when we need XML data from the SQL result set. The FOR XML clause can be used in top-level queries and in subqueries. The top-level FOR XML clause can be used only in the SELECT statement. In subqueries, FOR XML can be used in the INSERT, UPDATE, and DELETE statements. 

There are four modes of For XML Clause. Like

• Raw 
• Auto
• Path
• Explicit

Elaboration of Modes
 
Raw
Each row is turned into an XML node. That is called row by default, but you change the node name at any time by your own name. Every column will convert as an attribute.

select Id, Name, CatId from Product for xml raw

Now replace the default row node name with own custom name.

select Id, Name, CatId from Product for xml raw('product')

It is also possible to set a root element in this XML structure. Here is the same as before. The default root element name is root. But you can change the root element name.

select Id, Name, CatId from Product for xml raw('product'), root

Moreover, you can also set elements instead of attribute.

select Id, Name, CatId from Product for xml raw('product'), root, elements

Auto
Similar to raw mode, but here uses table name as default node name instead of row. Here everything is possible as in raw mode. Like the root, elements can be used.

select Id, Name, CatId from Product for xml auto

But in this mode, you can’t set a custom node name instead of the default table name.

Use of root and elements in auto mode.

select Id, Name, CatId from Product for xml auto, root('products'), elements

Difference between raw and auto mode
When you will retrieve data from multiple tables by joining then you will see the big difference between the two modes.

Raw mode query
select Category.Name as Title, Product.Name from Product
inner join Category on Product.CatId = Category.CatId
for xml raw, root

Auto mode query
select Category.Name as Title, Product.Name from Product
inner join Category on Product.CatId = Category.CatId
for xml auto, root

Path
This mode will convert each record as a parent element and every column as a nested element. There is no default attribute, but you can set custom attributes. In this mode, you can also use row, root, and elements. This mode is the best and better.

select Category.Name as Title, Product.Name from Product
inner join Category on Product.CatId = Category.CatId
for xml path

Use of custom parent path and root element in path mode.

select Category.Name as Title, Product.Name from Product
inner join Category on Product.CatId = Category.CatId
for xml path('Category'), Root('Products')

You can set attributes in this path mode very easily. But keep in mind that to assign attribute must be use @sign with the column name alias.

select Category.Name as [@Title], Product.Name from Product
inner join Category on Product.CatId = Category.CatId
for xml path('Category'), Root('Products')

Explicit
This mode is used to generate own custom XML structured format. So, you can choose it to generate own XML structure.

<ELEMENT>: The name of the element to which values will be assigned.
<TAG>: The tag number represents the level in hierarchy or depth.
<ATTRIBUTE>: The name of the attribute to which a particular column’s value will be assigned.
<DIRECTIVE>: This is optional and used to provide additional information for XML creation. We will look at one of its options "ELEMENT".

The first two columns are Tag and Parent and are Meta columns. These values determine the hierarchy. Moreover, this two-column name must be Tag and Parent. This name is required.

SELECT        1 AS TAG,
              NULL AS PARENT,
              c.Name AS [Category!1!Name],
              NULL AS [Sales!2!SaleID],
              p.Name AS [Sales!2!Product!ELEMENT],
              NULL AS    [Sales!2!Quantity!ELEMENT],
              NULL AS [Sales!2!Date!ELEMENT]
FROM          [Product] p
              INNER JOIN Category c ON p.CatId = c.CatId
              WHERE p.Id in (SELECT ProductId FROM Sales)
              UNION ALL
              SELECT        2 AS TAG,
              1 AS PARENT,
              c.Name AS [Category!1!Name],
              s.SaleId AS [Sales!2!SaleID],
              p.Name AS [Sales!2!Product!ELEMENT],
              s.Quantity AS    [Sales!2!Quantity!ELEMENT],
              s.Date AS [Sales!2!Date!ELEMENT]
FROM          [Product] p
              INNER JOIN Category c ON p.CatId = c.CatId
              INNER JOIN Sales s ON s.ProductId = p.Id
              WHERE p.Id = s.ProductId
              ORDER BY  [Category!1!Name], [Sales!2!Product!ELEMENT], [Sales!2!SaleID]
              FOR XML EXPLICIT

Hope this article would have helped you to understand about SQL XML clause. Here I have tried to explain very simply all terms of this clause. Happy coding and thanks for reading my article!

HostForLIFEASP.NET SQL Server 2021 Hosting