SQL Joins Tricky Interview Questions

SQL Joins Tricky Interview Questions

Introduction

SQL Joins is always an important topic of SQL Server. In most of the SQL interviews, questions are asked about SQL Joins irrespective of the number of years experience one have. If the candidate has knowledge about SQL Joins concepts and join columns contains unique values, these SQL joins questions are not difficult to answer. But in case of some interviews, interviewers may make it trickier by asking SQL joins puzzles (queries) where these join columns may contains either duplicate values or NULL values which makes the result interesting and interview candidate confuse about outcome of these queries. Here in this article, I am going to share these SQL Joins tricky interview questions.

In this article, we are going to cover the SQL Join interview questions with answer where columns with SQL Joins condition may contain:

  1. Unique values
  2. Duplicate values
  3. Null values

Before proceeding further, I assume that you have the basic knowledge of SQL Joins concepts. I am also going to explain these SQL Joins concepts with the help of  interview questions which we are going to discuss in this article but it is better to have some prior basic knowledge of SQL Join. This below article on SQL Joins may help you in getting more information about SQL Joins concepts.

Sql Joins- Inner Joins, Self Joins, Outer Joins, Cross Joins

Tables to be use

In this article we are going to use 2 tables tbl_samplejoin1  & tbl_samplejoin2. Each of these 2 tables contains one column on which join condition has been defined. Table tbl_samplejoin1 contains the column Col1 and table tbl_samplejoin2 has the column Col2.

Interview Questions on SQL Joins

In this article, we are going to explain the impact of having the unique records or duplicate records or Null value on the outcome on the Inner Join, Left Outer Join, Right Outer Join and the Full Outer Join. We are going to use the below mentioned 4 SQL Joins queries for better understanding in each scenario of data.

a) What will the outcome of the following inner join query?

    SELECT a.Col1, b.Col2 
    FROM tbl_samplejoin1 a INNER JOIN tbl_samplejoin2 b 
    ON a.Col1= b.Col2

b) What will the outcome of the following left outer join query?

    SELECT a.Col1, b.Col2
    FROM tbl_samplejoin1 a LEFT OUTER JOIN tbl_samplejoin2 b 
    ON a.Col1= b.Col2

c) What will the outcome of the following right outer join query?

    SELECT a.Col1, b.Col2
    FROM tbl_samplejoin1 a RIGHT OUTER  JOIN tbl_samplejoin2 b 
    ON a.Col1= b.Col2

d) What will the outcome of the following full outer join query?

    SELECT a.Col1, b.Col2
    FROM tbl_samplejoin1 a FULL OUTER JOIN tbl_samplejoin2 b 
    ON a.Col1= b.Col2

Interview Scenario 1: Join Columns having Unique Values

This is the most basic and common interview scenario where interviewer asked the outcome of query containing either Inner SQL Join or Left Outer Join or Right Outer Join or the Full Outer Join using the table containing the unique records in the columns on which join is define. These questions are the basic interview questions to check interview candidate basic concepts and practical knowledge on SQL joins.

Now suppose both tables contains the unique records as shown in the below screenshot.

Lets explains the outcomes of these 4 SQL joins queries one by one

Answers with explanation

a) Answer of the first interview question containing the Inner Join

If we look the first interview question, SQL query is a Inner Join between the 2 tables. Before we going to discuss the outcome of the inner join query which is shown in the below screenshot, let refresh the concept of Inner Join.

Inner Join:- This join returns all the rows from both tables where there is a match. In other words you can say that it gives all the records of the left table which have the matching records from the right table

Now if we look at the records of both tables, there are only 2 matching records with value 1 & 2. Therefore, when we execute the inner query mentioned in the screenshot, it gives only 2 records.

b) Answer of the 2nd interview question containing the Left Outer Join

SQL query in the 2nd interview question talks about the Left outer join. let first discuss about Left outer join before we proceed towards its answer.

Left Outer Join:- This join returns all records from the left table irrespective of whether right table contains the matching records or not. For all matching records, it returns the matched records from the right table and for not matching records, it return with NULL value.

So if look at both tables data, we found that two records (1 & 2) of the table tbl_samplejoin1 has the matching records in the second table tbl_samplejoin2. But the remaining two records (3 & 5) don’t have the matching record in second table. So result-set returns records 1 & 2 of  table tbl_samplejoin1 with matching records 1 & 2 from right table and for records 3 & 5, it return with NULL value.

c) Answer of the 3rd interview question containing the Right Outer Join

Right Outer Join:- This join returns all records from the Right table irrespective of whether Left table contains the matching records or not. For all matching records from the right table, it returns the matched records from the Left table and for not matching records, it return with NULL value.

So if we look into the outcome of the query mentioned above, we see 3 rows. It because since the table on the right tbl_samplejoin2 contains 3 rows with values 1, 2 & 4. We have matching values for 1 & 2 in left table but not for 4. So first two rows of the outcome contains the matching records of value 1& 2 but for third row it has NULL from left table and 4 from Right table

d) Answer of the 4th  interview question containing the FULL Outer Join

Full Outer Join:- This Join will return all the records from the left table as well as from the right table. It return matching records from both tables. For non matching records of the left table, it return NULL value as the right table records. Similarly for non matching records of the right table, it return NULL value as the left table records.

Again in case of FULL outer join, we got 5 rows. First & second rows contains the matching records of both tables. Since there is no record in right table with values 3 & 5 so we have 3rd & 4th rows with values 3 & 5 fro left table and NULL value from right table. Now left table tbl_samplejoin1 contains no record with value 4 so we have 5th row in the outcome where we have value 4 from right table and NULL value from left table.

Interview Scenario 2: Join columns having duplicate values

Let insert value 1 into the table tbl_samplejoin2 so that this tables contains the duplicate values. So after inserting the duplicate record in table 2, both tables contains the following data .

Now if the interviewer asked any of the 4 SQL joins queries again then, duplicate record in the 2nd table may put confusion in the candidate mind. Let check how this duplicate record impact the outcomes of these queries.

a) Answer of the first question will be

With duplicate values in the Join column, we found one extra row has been added to the result-set. This is because we have now 2 matching rows in table tbl_samplejoin2 containg value “1” for the value “1” of the table tbl_samplejoin1.

b) Answer of the 2nd question will be

Again  if look at both tables data, we found that two records (1 & 2) of the table tbl_samplejoin1 has the matching records in the second table tbl_samplejoin2. But the remaining two records (3 & 5) don’t have the matching record in second table. So result-set returns values of 1 & 2 from  table tbl_samplejoin1 with matching values 1 & 2 from second table and for records 3 & 5, it return with NULL value.

c) Answer of the 3rd question will be

So if we look into the outcome of the query mentioned above, we see 4 rows. It is because the table on the right tbl_samplejoin2 contains 4  rows with values 1,1, 2 & 4. We have matching values for 1 & 2 in left table but not for 4. So first three rows of the outcome contains the matching records of value 1& 2 but for forth row it has NULL from left table and 4 from Right table

d) Answer of the 4th question will be

Now with duplicate records, in case of FULL outer join, we got 6 rows. First, second, & third rows contains the matching records of both tables. Since there is no record in right table with values 3 & 5 so we have 4th & 5thth rows with values 3 & 5 fro left table and NULL value from right table. Now left table tbl_samplejoin1 contains no record with value 4 so we have 6th row in the outcome where we have value 4 from right table and NULL value from left table.

Interview Scenario 3: One Join table contains Null Value

After adding the duplicate values, make it more interesting by adding the Null values and see its impact on the inner join and outer joins. Lets add NULL value in the 2nd table tbl_samplejoin2 also. So the data in 2nd table become

Again let see how our answers for the above 4 SQL Joins queries are going to change after one of the tables contains the  NULL value in the Id column

a) Impact on the Inner query

Here we see that there is no impact of adding NULL value in the 2nd table and we got the same result-set as we got in 2nd Interview scenario.

b) Impact on the Left Outer Join query

Since in case of Left outer join, we got all records of the left table with corresponding matching records from right table in  case of match or NULL value from right table in case of no match. So there is no impact of adding NULL value in the 2nd table tbl_samplejoin2.

c) Impact on the Right Outer join query

If we compare the result-set with the result-set we got for Right Outer Join from our second interview scenario, we found one extra row. This is because we have added one extra record of NULL value in the 2nd table tbl_samplejoin2 and there is no matching value of NULL in the 1st table. So we have 5th row with one NULL value from right table tbl_samplejoin2 and and another NULL from left table as there is no matching record of NULL value in left table.

d) Impact on the Full Outer join query

If we again compare the result-set with the result-set we got for FULL Outer Join from our second interview scenario, we found one extra row. This 7th row contains one NULL value from right table tbl_samplejoin2 and and another NULL from left table as there is no matching record of NULL value in left table.

Interview Scenario 4: Both Tables containing Null Values.

Now add NULL value in the 1st table also so that both tables contains the Null value. Now it become more interesting to know when Null value of one table try to match with another Null value of the 2nd table how it is going to affect the outcome of the same four interview join questions.

a) Impact on the Inner join

If we look at the resultset of the Inner Join query after adding Null Value in both the tables, we found that Null value has no impact on the Inner Join and we got the same 3 rows which we got earlier too.

b) Impact on the Left outer join query outcome

In case of Left outer join, we can see the impact of adding NULL value on the result-set. One extra row in the record-set with NULL value in both columns. Since NULL is treated as unknown. So it cant be compared with any value. So we have NULL value from left table and since there can’t be done any matching because of NULL, we have NULL on the column Col2.

c) Impact on the Right outer join query outcome

We can see the similar effect of adding Null value in both table as we got one extra row of NULL values in the result-set. Here we have NULL value from right table and since there can’t be done any matching because of NULL which is consider as unknown value, we have NULL on the column Col1.

d) Impact on the Full outer join query outcome

This outcome is also self explanatory as first 3 rows of the result-set are matching values. For the rows 4 & 5, since the table tbl_samplejoin2 doesn’t have the values 3 & 5 in the col2 so we have NULL value against the values 3 & 5.

For 6th row, Since table tbl_samplejoin1 contain NULL which is a unknown value which can’t be matched so we have NULL value against it in col2.

In 7th Row, we have NULL in Col1 since table tbl_samplejoin2’s col2 value 4 doesn’t find any matching value in table tbl_samplejoin1.

For 8th row, Since table tbl_samplejoin2 contain NULL which is a unknown value which can’t be matched so we have another NULL value against it in col1.

Interview Scenario 5: Join tables containing Multiple Null Values

After adding duplicate values and one Null value,  what will happen if interviewer add more null values into into the tables. To know the change in the outcome of the queries, let add one more Null value in the first table. Now both the tables contains the data as shown in the below screenshot

a) Impact on the inner join query outcome

Since there is no impact of NULL value on the Inner join query so we have no change in the result as we have in the interview scenario 2.

b) Impact on the left join query outcome

Here we have 2 NULL values in the left table tbl_samplejoin1.

So we have first 3 rows of matching values with matching values in both tables..

4 & 5 rows has values from left table but has NULL in col2 since there is no corresponding matching values in the right table.

6 & 7 rows have NULL values from left table but NULL in col2 as NULL is consider as unknown so can’t have the matching value from right table.

c) Impact on the Right join query outcome

Since we have no extra value added to the right table tbl_samplejoin2 so we have the same result-set which we have in interview scenario 4.

d) Impact on the Full outer join query outcome

Again if we see at the result-set, we find one additional row as compared to the result-set we got in Interview scenario 4. This extra row (9th) contains NULL value in both columns. NULL in the Col1 is the NULL which we inserted into the table tbl_samplejoin1. Since NULL value is considered as unknown so we have NULL value in the Right table column col2.

Summary:-

In this article, I have tried to explain the SQL Join concepts with the help of the Interview questions which interview candidate faces regularly. In this article we started interview question scenario using the table with column having join condition contains the unique values. After that we see the impact of having duplicate values in the Join columns. In the end we see the impact of  having Null values in the join columns on Inner Join, Left Outer Join, Right Outer Join and the Full Outer Join.

Posted in SQL Server | 32 Comments

Happy New Year 2024

Posted in Festive Greetings, SQL Server | Leave a comment

Point In Time Restore for Azure SQL Database

Point In Time Restore for Azure SQL Database

Contents

  • Introduction
  • Automatic Backups
  • Backups Retention Period
  • Point In Time Database Restoration
  • Step by Step process for Point In Time Database Restoration
  • Summary
  • Read More

Introduction

In this article we are doing to discuss the point in time restore for azure SQL database and its backup capabilities. Azure SQL Database is a database as a service offered inside the Microsoft Azure. By database as a service,

  1. Azure is responsible for taking care of database software, automatic backups, high availability, operating system & its hardware. User only need to take care of database itself.
  2. User need to pay according to service usage on hourly basis. They can make payment monthly or yearly but can’t forced to pay any amount upfront unless user itself want to pay.
  3. Based on usage demand, user can scale up/down or create/destroy the environment without the provider intervention.

Automatic Backups

As we mentioned above in Point 1, Azure is responsible for the automatic backups. Full backup happens once a week, differential backups happens daily and transnational backup happens every 5 mins. Full backup started automatically after the database creation and after that other backups schedule automatically. This automatic backup service is free of cost.

Backups Retention Period

Retention time period for these database backups is automatically decided on the basis of service tier selected for the database. Retention Time period according to three service tiers are given below

  1. Basic Service Tier- 7 days

  2. Standard Service Tier -35 days

  3. Premium Service Tier -35 days

Point In Time Database Restoration

Point in time restoration is a self-service capabilities. It enable user to restore a basic, standard or premium database to any time within its service tier retention period. When we go for point in time database restoration, it always create a new database on the logical SQL Server. Automatic database backup service is free. But database restoration process is not free as it involve creation of new database. This new database is charge at normal database rates. Thus point in time database restoration is a very low-cost, simple and zero admin process which protect the database from accidental database corruption or accidental data deletion.

Step by Step process for Point In Time Database Restoration

In this section, I will explain the step by step process of Point in Time database restoration in Azure.

To explain this process, I will use the reference of the my azure SQL database “AzureDBRestoration” as shown in the below screenshot.

List of Azure SQL Databases

List of Azure SQL Databases

Click on the database name link and it will open the property page for the selected database as shown below

Azure SQL Database Properties Page

Azure SQL Database Properties Page

To restore the database we need to click on the Restore link from the top Menu. It will bring up the Restore page for the selected database as shown in the below screenshot.

Azure SQL Database Restoration Page

Azure SQL Database Restoration Page

Database restoration process always create a new database with a different name as shown in the above screenshot. Here default name for the new database as suggested by the Azure is “AzureDBRestoration_2019-01-07T17-13Z”. We can change it as per our requirement. In this case, I have changed the name to “AzureDBRestoration_Restore” as shown in the below screenshot.

Database Restoration Page- Changing Restored Database Name

Database Restoration Page- Changing Restored Database Name

In the above screenshot, suggested time for Restoration point is 5:13:00 PM on 2019-01-07. Time mentioned in the screen is always in UTC. We can change this restoration time as per our need. Now suppose, we need to restore the database using the restore point time of 3 hrs back. So we will change the restore point time to 2019-01-07 2:13:00 PM (UTC) as shown in below screenshot.

Database Restoration Page - Changing Point In Time Restore Timing

Database Restoration Page – Changing Point In Time Restore Timing

After changing the Restored database name and the restore point time, if required, we can also change the Elastic Pool and Pricing tier.  Now click OK. It will start the restoration process which you can see under the notifications as shown in below screenshot.

Azure SQL Database Restoration In Progress

Azure SQL Database Restoration In Progress

Once the database restoration process completed, you will got the success message as shown in below screenshot.

Successful Restoration of Azure SQL Database

Successful Restoration of Azure SQL Database

You can also see the newly created Azure SQL database “AzureDBRestoration_Restore” in the database list by clicking on the SQL Databases link present at the left side as shown in below screenshot.

Newly Created Database on Azure SQL Database List

Newly Created Database on Azure SQL Database List

Summary

This article try to explain the automatic backups services provided by Azure, Backup retention periods and how to take the point in time database restoration. This database restoration process help in protecting the database from corruption and more importantly from accidental deletion of data.

I hope this article will help you in Azure database restoration. Please don’t forget to gives your valuable comments for the article or you can send me the direct queries to my Email id askvivekjohari@gmail.com. If you want you can also join our Facebook community here or follow me on Twitter

Read More

Migrating SQL Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool

How to migrate SQL Database to Azure SQL Database using SSMS Export/Import

Migrating SQL Database to Azure SQL Database using SSMS Deploy

Understanding Azure SQL Database- Introduction

Azure Pricing Models and Service Tiers

Azure – Creating an Azure SQL Database

How to connect with Azure SQL Database

Read more from Tutorials

SQL Tutorial

Azure Tutorial

YouTube Channel

Technology With Vivek Johari

Posted in Azure, Azure Database Restoration | Tagged , , , | 39 Comments

Azure – Creating an Azure SQL Database

Azure SQL Database Creation

Creating an Azure SQL Database

Contents

Introduction

In my previous articles on Azure, I give introduction about Azure SQL & its pricing model & various Service Tiers. In this article, I will tell you the steps of creating an Azure SQL Database as PaaS (Platform As A Service ). Azure SQL Database has some difference with the On premises SQL Server. I will going to explain details of many of these difference in my later articles of this series . Lets start the process of creating an Azure SQL Database

As a prerequisite, you must have your Azure account. If you don’t have your account, then you can create you free Azure subscription for a month. After the completion of your first month you can either cancel your subscription or continue with it. The beauty of Azure is that you will pay for what you use. Billing is done on the hourly basis which means you don’t be charged for the hours in which you don’t use any azure resources. You can create your free account for a month using the below Microsoft link. I will recommend the subscription Pay-As-You-Go for those you are learning Azure or want to do some R&D for self learning. Once you login into your Azure account you will see the following screen:-

Azure Dashborad Screen

Azure Dashboard

In this Azure Dashboard, click on the link named “SQL databases” on the left hand side. It will open the page where list of all databases will be shown. Since no database has been created yet, there is no list of databases in the page as shown in the below screenshot.

List of databases

List of databases page

Creation of Azure SQL Database

We can create a new SQL Database by either click on the “+Add” button on the top of the page or “Create SQL database” link  as shown in the above screenshot. On clicking either of these link, below screen comes:

Screen for creating the Azure database.

Azure SQL Database Creation Screen

Step 1: Select Database Name

On the screen shown above, in order to create a new database, we need to select the database name, Resource group, Database source, SQL server and many other things which I will discuss during this article. Database name must be unique around all the existing Azure SQL databases in worldwide azure environment. It means no Azure SQL database with the same name should be existed already. But you doesn’t need to worry about it as Azure itself will check it when you type the database name in the text box. If any database with the same name already existed it will give the red signal. If not then it will give the green signal.

Step 2: Select Azure Subscription

The next text box will show the list of Azure subscriptions you have. If the user have multiple subscription then a drop down box is appear and user can choose any one subscription from the drop down list.

Step 3: Choose Resource Group

After that user needs to choose the name of the resource group which will contain the Azure SQL database. If user has multiple resource groups already, then user can select any of the resource group from the drop down list. If no resource group has been created  yet, user can create the new resource group by clicking on the link Create new as shown in the below screenshot:

Resource Group creation screen

Resource Group creation screen

In the above screen, user need to mentioned the name of the new Resource group in the text box “Name“. For example, if we want to create the resource group with name as “AzureResourceGroup“, then user need to mentioned this name as shown in below screenshot:

Creating new resource group screen

Creating new resource group screen

Step 4: Choose Database Source

After selecting the resource group, user need to select the database source from the 3 options mentioned in the drop down box.

  • Blank Database: – To create a blank database
  • Sample (AdventureWorksLT): – To create the AdventureWorksLT database
  • Backup: – To create database using a backup file of a database

We will select the Blank database as database source for this article.

Database Source selection

Database Source selection screen

Step 5: Choose SQL Server

After selecting the database source, user needs to select the SQL Server. Again if user has access to multiple SQL Servers, then user can choose any of the these SQL Servers. But if user doesn’t have access to any of the SQL Server, then user need to create a new SQL Server. For this, user needs to click on the Configure required settings under the section Server as shown in below screenshot.

Selecting/Creating SQL Server

Selecting/Creating SQL Server

Here, user needs to choose the Server Name which should be unique throughout the Azure World wide environment similar to the way we choose the new database name. Also user needs to mentioned the SQL Server Admin login/ username and its password. After that user need to choose the location from available locations list from the drop down on which he wants his database to reside as shown in below screenshot. Here I choose West India as location.

Mentioning SQL Server details

Mentioning SQL Server details

Step 6: Choose Elastic Pool Option

Once the SQL Server is chosen, we need to decide whether we want to use the SQL elastic pool feature of Azure . I will tell about the SQL elastic pool in the later articles. For now, we will select the No option.

Step 7: Choose Service Tier for database

After that we need to choose the Service tiers like Basic, Standard or the Premium based on our database requirement like memory, No. of CPU & Disk IO etc.  Base on the chosen Service tier, Azure decide its pricing. You can read more about these various service tiers and Azure pricing from the article Azure Pricing Models and Service Tiers. By default, Standard Service tier is mentioned. But we can change it according to our requirement as shown in below screenshots.

Azure Database configuration settings

Click on the right arrow in Pricing tier to change it

Choosing Basic Service Tier

Choosing Basic Service Tier

Choose Basic Service tier and click Apply. After that user can choose the collation for its database if required. I will go with the default collation.

Validation of SQL Database Configuration settings

Validation of SQL Database Configuration settings

Step 8: Deployment of Azure SQL Database

Once the Validation is successful, click on the Create button. Once Create button is clicked below page comes showing the deployment process. This deployment process usually takes few minutes time to complete deployment.

Deploying Azure SQL database.

Deploying Azure SQL database.

Once the SQL database is deployed on the Azure, user can Pin that database on its dashboard as shown in below screenshot.

Pin SQL Database on Azure Dashboard

Pin SQL Database on Azure Dashboard

Azure SQL Database Overview Page

Now we have created and deployed the Azure SQL Database on the cloud, we can see its properties by two ways

  1. Clicking on the pinned Azure SQL Database from the dashboard
  2. By first clicking on the SQL Databases on the left side link of dashboard and then click on the Database name from the list of available Azure SQL databases.

It will open the SQL Database Overview page as shown in below screenshots.

"<yoastmark

"<yoastmark

"<yoastmark

If you see, all above 3 screenshots are taken from the same overview screen. In the overview page, there are many links provided in the top like restore for restoration of database to a particular time, delete for deleting the database, setting SQL Server Firewall etc. Also this page gives many useful information like Database status, Location, Elastic Pool, Connection String, SQL Server Name, Resource Name, user subscription. It also gives information about the security features currently configured on it. For example, from the above screenshot, user can see that Transparent Data Encryption (TDE) is configured on it.

Summary

In this article, we have learned that how to create an Azure SQL Database as PAAS. It also tells the step by step process of Azure database creation . We also use many screenshots which help in better understanding of the this process. During our journey of database creation, we also try to give you the information of various fields which user need to choose or mentioned. It also gives you some details about creating a SQL Server .

Please don’t forget to gives your valuable comments for the article or you can send me the direct queries to my Email id askvivekjohari@gmail.com.

In the next article, we will tell about how to connect this database using the SQL Server Management Studio (SSMS).

Read More

Understanding Azure SQL Database- Introduction

Azure Pricing Models and Service Tiers

Posted in Azure | Tagged , , | 29 Comments

Merry Christmas

Wish you all a Merry Christmas and may this festival bring abundant joy and happiness in your life!

Merry Christmas

Posted in Festive Greetings | Tagged | 32 Comments

SQL Triggers – An Introduction

SQL Triggers – An Introduction

Contents

  • Introduction
    • Magic Tables
  • Difference between Stored Procedure and Trigger
  • DML Triggers
    • After Triggers
      • Syntax of the After trigger
      • Example of After Trigger for Insert
      • Example of After Trigger for Delete
      • Example of After Trigger for Update
    • Instead of Triggers
  • DDL Triggers
    • DDL Trigger for Create Table
    • DDL Trigger for Alter Table
    • DDL Trigger for Drop Table
  • Nested Triggers
  • Recursive triggers
  • How to find the Triggers in a database
  • How to Disable a trigger
  • How to enable a trigger
  • How to drop a trigger
  • Real life Example
  • Advantages of SQL Triggers
  • Disadvantages of Triggers
  • Summary

Introduction

Triggers can be defined as the database objects which perform some action for automatic execution whenever users try to do execute data modification commands (INSERT, DELETE and UPDATE) on the specified tables. Triggers are bound to specific tables. As per MSDN, triggers can be defined as the special kind of stored procedures. This article “SQL Triggers – An Introduction” will give you the detailed knowledge about the SQL Triggers which can be very helpful in your work. Before describing the types of triggers, we should first understand the Magic tables which are referenced in triggers and used for reuse.

Magic Tables

There are two tables Inserted and deleted in the SQL Server, which are popularly known as the Magic tables. These are not the physical tables but the SQL Server internal tables usually used with the triggers to retrieve the inserted, deleted or updated rows. These tables contain the information about inserted rows, deleted rows and the updated rows. This information can be summarized as follows:

Action              Inserted                 Deleted
Insert Table contains all the inserted rows Table contains no row
Delete Table contains no rows Table contains all the deleted rows
Update Table contains rows after update Table contains all the rows before update

Difference between Stored Procedure and Trigger

  1. We can execute a stored procedure whenever we want with the help of the exec command, but a trigger can only be executed whenever an event (insert, delete and update) is fired on the table on which the trigger is defined.
  2. We can call a stored procedure from inside another stored procedure but we can’t directly call another trigger within a trigger. We can only achieve nesting of triggers in which action (insert, delete and update) defined within a trigger can initiate the execution of another trigger defined on the same table or different table.
  3. Stored procedures can be scheduled through a job to execute on a predefined time, but we can’t schedule a trigger.
  4. Stored procedure can take the input parameters, but we can’t pass the parameters as an input to a trigger.
  5. Stored procedures can return values but a trigger cannot return a value.
  6. We can use the Print commands inside the stored procedure to debug purpose but we can’t use the print command inside a trigger.
  7. We can use the transaction statements like begin transaction, commit transaction and rollback inside a stored procedure but we can’t use the transaction statements inside a trigger.
  8. We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.) but we can’t call a trigger from these files.

DML Triggers

Types of trigger

In SQL Server, there are two types of triggers which are given below:-

  1.    After Triggers
  2.    Instead of Triggers

In this article, we will use three tables named customer, customerTransaction and Custmail whose structure is given below:-

Create table customer (customerid int identity (1, 1) primary key,Custnumber nvarchar(100), custFname nvarchar(100), CustEnamn nvarchar(100), email nvarchar(100), Amount int, regdate datetime) Create table customerTransaction(Transactionid int identity(1,1)primary key,custid int,Transactionamt int, mode nvarchar, trandate datetime) Create table Custmail (Custmailid int identity (1, 1) primary key, custid int, Amt int, Mailreason nvarchar(1000))

After Triggers

“After Triggers” are executed after the data modification action (Insert, Delete, Update) is executed on the respective tables. A table can have multiple triggers defined on it.

Syntax of the After trigger

Create Trigger trigger_name
On Table name
For Insert/Delete/update
As
Begin //SQL Statements End

Example of After Trigger for Insert

Suppose we have a requirement that whenever a new customer is added then automatically its corresponding value must be inserted into the table Custmail so that an email can be send to the customer and an authorized person in the Bank. To solve this problem we can create an After Insert trigger on the table customer whose syntax is given below:-

Create Trigger trig_custadd on Customer
For Insert
As
Begin
Declare @Custnumber as nvarchar(100)
Declare @amount as int
Declare @custid as int Select @Custnumber=Custnumber, @amount=Amount From inserted Select @custid=customerid From customer Where Custnumber =@Custnumber Insert Into Custmail (custid,Amt,Mailreason) Values (@custid,@amount,'New Customer') End

This trigger will be fired, whenever a new Customer is added to the bank and the corresponding entry is inserted into the table Custmail. The mail functionality will use the entries from the table custmail to send the mail to the Customer.

Example of After Trigger for Delete

Suppose, there is an another requirement that whenever a customer is deleted from the system, mail is sent to the customer containing the notification about deletion.To sends the mail, we need to insert an entry of the customer in the table custmail, whenever a customer is deleted from the master table customer. To achieve this we will use the after trigger for deletion. In the example given below, we will use the magic table Deleted.

Create trigger trig_custdelete on customer
For Delete
As begin
 Declare @Custnumber as nvarchar(100)
 Declare @custid as int
Select @Custnumber=Custnumber from deleted
Select @custid=customerid from customer where Custnumber =@Custnumber
Delete from customerTransaction where custid=@custid
Insert into Custmail
Values(@custid,0,'Customer delete')
end

Example of After Trigger for Update

Suppose, we have also a requirement that whenever a client credit his account or updated his name (first name as well as last name), a mail should be send to the customer containing this information. In this case, we can use the After trigger for update. In this example,we are going to use the Magic table Inserted.

create trigger trig_Custupdate
on customer
for update
as
begin
  declare @Custnumber as nvarchar(100)
  declare @amount as int
  Declare @custid as int
  if update(amount)
    begin
        select @Custnumber=Custnumber, @amount=Amount from inserted
        select @custid=customerid from customer where Custnumber =@Custnumber
        insert into Custmail 
        values(@custid,@amount,'Customer Amount Update')
    end
  if update(custFname)or update(CustEnamn)
    begin
   insert into Custmail 
   values(@custid,0,'Customer Name Update')
    end
end

In the above example, we used the Update function on the columns amount, custfname and custEname which initiates the update trigger on modification of these columns.

Instead of Triggers

Instead of trigger is used when we want to perform another action instead of the action which causes the trigger to fire. Instead of trigger can be defined in case of Insert, Delete and Update. For example, suppose we have a condition that in a single transaction a user could not be able to debit more than $15000. We can use the Instead of trigger, to implement this constraint. If the user try to debit more than $15000 from his account at a time then error is raised with the message “Cannot Withdraw more than 15000 at a time”. In this example we use the magic table Inserted.

Create trigger trigg_insteadofdelete
on customerTransaction
instead of insert
as
begin
declare @Custnumber as nvarchar(100)
declare @amount as int
Declare @custid as int
Declare @mode as nvarchar(10)
select @custid=custid , @amount=Transactionamt,@mode=mode from
inserted
if @mode='c'
begin
update customer set amount=amount+@amount where
customerid=@custid
insert into Custmail
values(@custid,@amount,'Customer Amount Update')
end
if @mode='d'
begin
if @amount<=15000
begin
update customer set amount=amount-@amount where
customerid=@custid
insert into Custmail
values(@custid,@amount,'Customer Amount Update')
end
else
begin
Raiserror ('Cannot Withdraw more than 15000 at a time',16,1)
rollback;
end
end
end

DDL Triggers

DDL Triggers has the similar behavior as the DML triggers to have except that they are fired in response to a DDL type event like Alter command, Drop command and Create commands. In other words, it will fire in response to the events which try to change the schema of the database. Therefore, these triggers are not created for a particular table, but they are applicable to all the tables on the database. Also DDL triggers can be fired only after the commands which make them fire is executed. They can be used for the following purposes:

1) To prevent any changes to the database Schema
2) If we want to store the records of all the events, which change the database schema.

For example, suppose we want to create a table command_log which will store all the user commands for creating tables (Create table) and commands which alter the tables. Also we don’t want any table to be dropped. Therefore if any drop table command is fired, a DDL trigger will rollback the command with a message that “You can’t drop a table”.

The script for the table  command_log will be given below:

CREATE TABLE Command_log(id INT identity(1,1), Commandtext NVARCHAR(1000), Commandpurpose nvarchar(50))

DDL Trigger for Create Table

For storing the create table command in the table command_log , we first need to create a trigger which will be fired in response to the execution of the Create table command.
CREATE TRIGGER DDL_Createtable
ON database
FOR CREATE_Table
AS
Begin
PRINT 'Table has been successfully created.'
insert into command_log ()
Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/ CommandText ) [1] ','nvarchar(1000)')

End

This trigger will be fired whenever any command for the table creation is fired and will insert the command into the table command_log and also print the message that “Table has been successfully created”.

Note:  Eventdata() is a functions which returns information about the server or database events.It returns value of XML type. Read more about Eventdata()

DDL Trigger for Alter Table

Suppose if we want to store the alter table commands also in the table command_log, we need to make a trigger for Alter_table command.

Create Trigger DDL_Altertable
On Database
for Alter_table
as
begin
declare @coomand as nvarchar(max)
print 'Table has been altered successfully'
insert into command_log(commandtext)
Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/ CommandText)[1]','nvarchar(1000)')

end

This trigger will be fired whenever any alter table command is fired on the database and will print the message “Table has been altered successfully.”

DDL Trigger for Drop Table

To stop the user from dropping any table in the database, we need to create a trigger for drop table command.

Create TRIGGER DDL_DropTable
ON database
FOR Drop_table
AS
Begin
PRINT 'Table cannot be dropped.'
INSERT into command_log(commandtext)
Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/ CommandText)[1]','nvarchar(1000)')
Rollback;
end

This trigger will not allow any table to be dropped and also print the message the “Table cannot be dropped.”

Nested Triggers

Nested Trigger: – In Sql Server, triggers are said to be nested when the action of one trigger initiates another trigger that may be on the same table or on the different table.
For example, suppose there is a trigger t1 defined on the table tbl1 and there is another trigger t2 defined on the table tbl2, if the action of the trigger t1 initiates the trigger t2 then both the triggers are said to be nested. In SQL Server, triggers can be nested up to 32 levels. If the action of nested triggers results in an infinite loop, then after the 32 level, the trigger terminates.
Since the triggers are executed within a transaction, therefore failure at any level of within nested triggers can cancel the entire transaction, and it result in total rollback.

We can also stop the execution of nested triggers through the following SQL Command:

sp_CONFIGURE 'nested_triggers',0
GO RECONFIGURE GO

Recursive triggers

In SQL Server, we can have the recursive triggers where the action of a trigger can initiate itself again. In SQL Server, we have two types of recursion.

  1.   Direct recursion
  2.   Indirect recursion

In Direct recursion, action of a trigger initiates the trigger itself again which results in trigger calling itself recursively.
In Indirect recursion, action on a trigger initiates another trigger and the execution of that trigger again calls the original trigger, and this happen recursively. Both the triggers can be on the same table or created on the different tables.

Please note: Recursive trigger can only be possible when the recursive trigger option is set.

Recursive trigger option can be set using the following SQL Command:

ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF


How to find the Triggers in a database

1)  Finding all the triggers defined on whole the database
Suppose we want to get the list of all the triggers and their respective tables name then we can use the following SQL Statement.

select o1.name, o2.name from sys.objects o1 inner join sys.objects o2 on  o1.parent_object_id=o2.object_id and o1.type_desc='sql_trigger'

2) Finding all the triggers defined on a particular table

For example if we want to find out all the triggers created on the table Customer then we can use the following SQL Statement:-

sp_helptrigger Tablename
example:-
sp_helptrigger 'Customer'

3)  Finding the definition of a trigger

Suppose if we want to find out the definition of the trigger, we can use the following SQL Statement:-

sp_helptext triggername
For example:-
sp_helptext 'trig_custadd'

Result

How to Disable a trigger

DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]

Disabling a DML trigger on a table

DISABLE TRIGGER 'trig_custadd' ON Customer;

Disabling a DDL trigger

 DISABLE TRIGGER 'DDL_Createtable' ON DATABASE;

Disabling all triggers that were defined with the same scope

DISABLE Trigger ALL ON ALL SERVER;

How to enable a trigger

Enabling a DML trigger on a table

ENABLE Trigger 'trig_custadd'  ON Customer;

Enabling a DDL trigger

ENABLE TRIGGER 'DDL_Createtable' ON DATABASE;

Enabling all triggers that were defined with the same scope

ENABLE Trigger ALL ON ALL SERVER;

How to drop a trigger

Dropping a DML trigger :-

DROP TRIGGER trig_custadd ;

Dropping a DDL trigger

DROP TRIGGER DDL_Createtable ON DATABASE;

Real life Example

Some weeks ago one of my friends gets a task which needs to be completed on a very old written code. The task includes that a mail should be sent to the user in the following cases:

1. The user is added to the system.
2. Whenever any information regarding the user is updated or deleted or added.
3. A user is deleted.

The challenges in this task include:-

1. The code is very old and unstructured. Therefore, it has many inline queries written on the various .aspx pages.
2. Queries for the insert, delete and update is also written in many stored procedures.
So the code doesn’t have a common library function or a standard stored procedure which is used throughout the application which can be used to insert, update and delete a user, which is not a good practice. But it happen sometimes with the old code. The required queries are written on many .aspx pages and stored procedures.

Possible solutions:

To complete this task, we need to insert an entry into the table tblmail with proper flags indicating the insert, delete and update. A scheduled application built in .net application will read the rows from the table tblmail and send the mails.

Two approaches to insert the rows:

1. Find all the places in the .aspx files and the stored procedures where the queries for the insert, delete and update and after these queries, add the insert query for the table tblmail.
2. Instead of finding these queries in all the .axps files and stored procedures, create after (insert, update and delete) trigger on the user master table will insert the date in the table tblmail after the execution of the insert, update and delete statement.
We used the second approach because of the following 4 reasons:
1) It is very difficult to search so many .aspx files and stored procedures to find the required queries.
2) It has the risk that a new developer may not know about this requirement of sending mail and forget to add the code for inserting the values in the table tblmail.
3) If we need to change anything in the requirement, it has to be changed in all these files and stored procedures.
4) With the second approach, we only need to create triggers on the table and the developer, and it will also minimize the risk mention in the three 3 points mention above.

Advantages of SQL Triggers

1) It helps in maintaining the integrity constraints in the database tables, especially when the primary key and foreign key constrain are not defined.

2) It sometimes also helps in keeping the SQL codes short and simple as I show in the real-life example.

3) It helps in maintaining the track of all the changes (update, deletion and insertion) occurs in the tables through inserting the changes values in the audits tables.

4) Sometimes if the code is not well-managed, then it can help in maintaining the database constraints defined on the tables on which the trigger is defined. For example, suppose if have a situation that there is an online learning system in which a user can register in the multiple course.

Suppose the organization wants to define a constraint is defined that a user cannot be deleted until he/she passed all the course in which he is registered or the user has to first himself from all the incomplete or failed courses.

Since the code is not well-managed and the code to delete the user is defined as the inline query in many .net pages and multiple stored procedures (this is not a good thing, but it happens), one has to write the code for enforcing this constraint in to all these .net files and stored procedures, which take so much time and if the new developer does not this constraint and forgets to include the constrain enforcing code which corrupt the database. In this case, we can defines an instead of trigger on the table which checks every time a user is deleted and if the condition of the above constraint is not met, display the error message instead of  deleting user.

Disadvantages of Triggers

1) Hard to maintain since this may be a possibility that the new developer doesn’t able to know about the trigger defined in the database and wonder how data is inserted, deleted or updated automatically.
2) They are hard to debug since they are difficult to view as compared to stored procedures, views, functions, etc.
3) Excessive or over use of triggers can slow down the performance of the application since if we defined the triggers in many tables then they kept automatically executing every time data is inserted, deleted or updated in the tables (based on the trigger’s definition) and it makes the processing very slow.
4) If complex code is written in the triggers, then it will slow down the performance of the applications.
5) The cost of creation of triggers can be more on the tables on which frequency of DML (insert, delete and update)  operation like bulk insert is high.

Summary

Trigger is bad or good depends upon its use and its proper documentation. It can be very useful when it is used to maintain the integrity constraints in the database tables in the absence of primary key and foreign key, or it is very useful for the auditing purpose in tracking all the changes. But, if it is used extensively, it can reduce the performance. Also to maintain it and making debugging simple, proper documentation of the Triggers is necessary, which records the trigger name, table name on which it is created, its definition and its purpose.

Read More

SQL Joins

SQL Stored Procedure

SQL Constraints – Primary Key / Foreign Key / Unique Key / Null Constraint / Check Constraint

SQL Views

SQL Cursor

SQL Functions

Pivot and Unpivot table in SQL SERVER

Exception handling in SQL Server

Best practices for Database Programming

Read more from Tutorials

SQL Tutorial

Azure Tutorial

DMCA.com

Posted in Most Imp SQL Concepts, SQL Advanced, SQL Server, SQL Triggers | Tagged , , , , | 33 Comments

Migrating SQL Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool

Migrating SQL Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool

Contents

  • Introduction
  • Microsoft Database Migration Assistant (DMA) tool
  • Migrate SQL Server database To Azure SQL Instance using DMA tool
    • Detecting the compatibility issues using Assessment Type project
    • Migrating the database using the Migration project type
    • Verifying the migrated database on Azure SQL Instance
  • Summary

Introduction

In my previous articles on migrating On premise SQL Database to Azure SQL Database, I have already discussed two migration methods using SSMS Deploy & SSMS Export/Import Methods and their step by step process. Now, we will discuss the 3rd method of migrating the On Premise SQL Database to Azure SQL Database. In this article we learn migrating SQL Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool.

Microsoft Database Migration Assistant (DMA) tool

Microsoft Database Migration Assistant (DMA) tool helps in detecting compatibility issues & features which are not supported or partially supported by the Target server (Azure SQL Instance). This issues can impact database functionality on the target server. DMA has 2 project types. First is Assessment Project type. This section helps in getting the report on compatibility issues and the SQL Server features which are not supported or partially supported on the target server ( Azure ). The second project type is Migration which migrates the database from On premise SQL database to Azure SQL database. It not only allows migrating the data & Schema but also uncontained SQL objects from Source  Server (On premise SQL Server) to the target server (Azure SQL database).

Download Microsoft Database Migration Assistant (DMA) tool

You can download the  from the below mentioned Microsoft link

https://www.microsoft.com/en-us/download/details.aspx?id=53595

You can learn more about DMA tool from here 

let start discussing the step by step process of database migration to Azure SQL Instance using (DMA) tool

Migrate SQL Server database To Azure SQL Instance using DMA tool

After downloading and installing the DMA tool, when you open it, you will get the following screen.

Figure-1 Welcome to Data Migration Assistant

Figure-1 Welcome to Data Migration Assistant

In this screen click the “+”to start the new project. It will bring the below screen. By default Assessment Project type is selected.

Figure-2 Choose the project type

Figure-2 Choose the project type

Detecting the compatibility issues using Assessment Type project

Figure-3 Define Project Type, Project Name, Source Server & Target Server

Figure-3 Define Project Type, Project Name, Source Server &  Target Server

In the above screen, for detecting the compatibility issues, choose the Radio button Assessment (If not already selected). After choosing the project type, define the project name, source server name and the target server name. After that click the Create button. It will open the below screen:

Figure-4 Select the report options.

Figure-4 Select the report options.

In the above screen we need to select the report type options. Since I want to check the compatibility issues as well as features which are either not supported or partially supported, I will go with the first option Check database compatibility as well as second option Check feature parity.

Once you select the report options click on the Next button. It will bring the below screen

Figure-5 Connect to the Source SQL Server

Figure-5 Connect to the Source SQL Server

In the above screen, enter the server name & the required login information for connecting it. Then click on the Connect button to connect to the source SQL Server. It will take us to the below screen.

Figure-6 Select the SQL database for migration

Figure-6 Select the SQL database for migration

This screen contains all the database from the source SQL Server. Here we need to select the database which we want to migrate. For this article, we choose the database AdventureWorks2012. Once we choose the database, click on the Add button which will bring up the following screen.

Figure-7  Start Assessment

Figure-7  Start Assessment

In the above screen, you can see the selected SQL database, its compatibility level & its size.  Now click on the Start Assessment button. It will start the assessment process as show in the below mentioned screenshot.

Figure-8 Assessment in process...

Figure-8 Assessment in process…

After completing the assessment process, it will bring the below screen. By default SQL Server feature parity radio option is selected. This screen show the features which are either not supported by the Azure SQL database or features which are partially supported by Azure SQL Database.

Figure-9 Review result  for Unsupported & Partially Supported issues

Figure-9 Review result  for Unsupported & Partially Supported issues

Now, if you chose the radio button option Compatibility issues, it will the below screen. This screen will show all the compatibility issues with their details which may impact the migration process.

Figure-10 Review result for Compatibility Issues

Figure-10 Review result for Compatibility Issues

Now click on the Export Report button and it will asked you to save the report. For example, in this case the report will be look like this

Figure-11 Assessment Report

Figure-11 Assessment Report

Now once you click on the icon below “+”, you will get the below screen where you can see all the assessments.

Figure-12 All Assessments list

Figure-12 All Assessments list

With this, the assessment part is complete. In the next section we will going to discuss how we can do the actual migration using the Microsoft Database Migration Assistant tool ‘s Migration project type..

Migrating the database using the Migration project type

Click on the “+” sign to start new project.

Figure-13 Select Migration Project Type

Figure-13 Select Migration Project Type

Now select the Migration Project type and defined the project name. Also select the Source Server Type, Target Server type and the scope of the migration. In this article, I have chosen the Schema & data as migration scope.

After that click the Create button. It will bring up the “Connect to source server” screen.

Figure-14 Connect to the Source Server

Figure-14 Select Source Server

In this screen, fill the required connection information of the source server as shown in the above screen and click on the Connect button. It will take us to the database selection screen as shown below

Figure-15 Select the source database

Figure-15 Select the source database

For this article, I have chosen the database AdventureWorks2012. Click Next to continue.. It will bring us to the target server selection screen.

Figure-16 Choose the Target Azure SQL Server

Figure-16 Choose the Target Azure SQL Server

In this screen choose the Target Azure SQL Instance and fill the required credential information for connecting to the Azure SQL Instance. After filling the required credential information, click the Connect button. In the next screen, we need to select the database from the list of databases in the target Azure SQL Instance. Since there is no database available in the Azure SQL Instance. So we need to create it.

We can create new Azure SQL Database through multiple ways like through Azure Portal, using SQL Server Management Studio or by clicking the link Create a new Azure SQL Database from the above screen. In this article I will create the new Azure SQL database using the SQL Server Management Studio.

For creating a new azure SQL database, first connect to the Azure SQL instance using SQL Server Management studio. If you don’t know how to connect with Azure SQL Instance using SSMS, this article can help you. Once connected, right-click on the Databases node and select the option New database. It will bring the below mentioned screen

 

Figure-17 Create the new database

Figure-17 Create the new database

 

In the Database name field, define the new database name. In this article, I have define the name as AdventureWorks2012. Now click on the options tab. It will bring the below mentioned screen

Figure-18 Select setting for new Azure SQL database

Figure-18 Select setting for new Azure SQL database

If you notice in the above screen, S2 Service tier is selected by default. We can change the service tier according to our database performance & size requirements. If you want to read more about Azure service tiers, you can refer to the article ” Azure Pricing Models and Service Tiers “. In this article I have change the Service tier to S0 as shown in the below screenshot.

Figure-19 Change the Service tier for the new database

Figure-19 Change the Service tier for the new database

Click OK. This will create the new blank SQL database in the Azure SQL Instance. Now if we again return to the Screen where we have made the connection with the Azure SQL Instance (Figure-16) and click Connect, it will show the newly created database AdventureWorks2012. Since it is the only database in the Azure SQL Instance, it is by default selected.

Figure-20 Select the target database on Azure SQL Instance

Figure-20 Select the target database on Azure SQL Instance

Click on the Next button. It will bring the below screen. In this screen we need to select the schema objects which we want to migrate from source database.

Figure-21 Select the Schema objects for migration

Figure-21 Select the Schema objects for migration

Once the schema objects got selected, click on the Generate SQL Script. It will bring the screen Script & deploy Schema. In this screen you can see the SQL script generated for the Schema Objects.

Figure-22 Generating SQL scripts for selected schema objects

Figure-22 Generating SQL scripts for selected schema objects

Once the SQL Script for Schema objects generated, click on the Deploy Schema button. It will start the process of deploying the selected Schema objects on the target Azure SQL Instance.

Figure-23 Deploying selected schema objects on target Azure SQL database

Figure-23 Deploying selected schema objects on target Azure SQL database

Once this process got completed, click on the Migrate data button. It will bring the Select tables screen. In this screen, select the tables which we wish to migrate to the Azure SQL Instance.

Figure-24 Select the tables for migration

Figure-24 Select the tables for migration

After selecting the tables, click on the Start data migration button. It will take to the Migrate data screen. Here we can see the tables data migration process as shown in the below screenshot.

Figure-25 Selected tables data migration in progress

Figure-25 Selected tables data migration in progress

Once this data migration process got completed successfully, you can see the final  summary & total time taken by it to complete the data migration process as shown in the below screenshot.

Figure-26 Completion of table data migration process

Figure-26 Completion of table data migration process

Verifying the migrated database on Azure SQL Instance

To check the data migration happened successfully or not, you can connect to the Azure SQL Instance using SQL Server management studio (SSMS) and expand the node Databases. It will show the database AdventureWorks2012. Now expands it. There you can see all the tables & other schema objects which shows the success of the migration process.

Figure-27 Tables in the migrated database in Azure SQL Instance

Figure-27 Tables in the migrated database in Azure SQL Instance

If you want to check the migrated data within the tables, you can execute the Select command to see data from any of the table as shown in the below screenshot. It will verify the success of the table data migration.

Figure-28 Verifying table data by executing Select command

Figure-28 Verifying table data by executing Select command

 

Summary

In continuation on the series of articles on migrating On premise SQL Database to Azure SQL Database, this article discussed about migrating SQL Server Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool. The link of the other 2 methods has been mentioned under the Read More section. Microsoft provides this migration tool and have two project types. Assessment & Migration.

Using Assessment project type we can know the compatibility issues and the features which are either not supported or partially supported by the Azure SQL Instance before starting the actual migration process. Since these issues can  have an impact during the migration process, we have the opportunity to resolve these issues and then start the actual migration process.

Migration project type is use for the actual migration process. Here we can choose the schema objects & tables, which we need to migrate and monitor the migration process.

I hope this article will help you in migrating the SQL Server database to the Azure SQL Instance. Please don’t forget to gives your valuable comments for the article or you can send me the direct queries to my Email id askvivekjohari@gmail.com. If you want you can also join our Facebook community here or follow me on Twitter

Read More

How to migrate SQL Database to Azure SQL Database using SSMS Export/Import

Migrating SQL Database to Azure SQL Database using SSMS Deploy

Understanding Azure SQL Database- Introduction

Azure Pricing Models and Service Tiers

Azure – Creating an Azure SQL Database

How to connect with Azure SQL Database

Read more from Tutorials

SQL Tutorial

Azure Tutorial

 

Posted in Azure, Migration to Azure SQL Database | Tagged , , , , | 33 Comments

How to migrate SQL Database to Azure SQL Database using SSMS Export/Import

How to migrate SQL Database to Azure SQL Database using SSMS Export/Import

How to migrate SQL Database to Azure SQL Database using SSMS Export/Import

Migrating SQL Database to Azure SQL Database using SSMS Export/Import

Contents

  • Introduction
  • Creating the .bacpac file using SSMS Export
  • Importing the .bacpac file using SSMS Import
  • Summary

Introduction

There are many ways of migrating the On premise SQL Database to Azure SQL Database. In the previous article on migrating SQL Database to Azure SQL Database, we discuss the SSMS deploy method as a step by step process. This article will tell how to migrate SQL Database to Azure SQL Database using SSMS Export/Import data as a step by step process. It is a multi steps process where in first step we need to export the data into a BACPAC (.bacpac ) and then in next step, import that BACPAC file. It means even if the import data step is failed, we doesn’t need to repeat the data export process again. As we have have the exported BACPAC file, we just need to restart the Import process. This process require longer downtime. So this process is suitable for larger databases if the downtime duration is acceptable.

Migrating SQL Database to Azure SQL Database using SSMS Export data

Creating the .bacpac file using SSMS Export

As a first step, we need to create the .bacpac file of the On premise SQL database which we want to migrate to the Azure SQL Server. So connect to the On premise database server and select the SQL database to migrate. Right click on it and select the option Export Data-tier Application.. (as shown in the below screenshot)

Select the database for migrating to Azure

Select the database for migrating to Azure

Once the option Export Data-tier Application.. clicked, it will open the Export Data-Tier Application page as shown in the below screenshot.

Introduction Screen for Data export

Introduction Screen for Data export

Click on Next button to reach the Export Setting screen. In this screen, we need to select the location for storing the .bacpac file.

Select the .bacpac file location

Select the export (.bacpac) file location

Click on the Next button to reach the Summary Page. In this screen, we need to recheck the setting which we have specified.

Verify the specified settings

Verify the specified settings

After verifying the specified settings, click on the Finish button. It will start the process of .bacpac file creation as shown below.

Processing the creation of .bacpac file

Processing the creation of .bacpac file

Once this operation got completed, below screen comes. Click on the Close button. This will complete the database export part of this migration process.

Operation Completion Screen

Operation Completion Screen

Importing the .bacpac file using SSMS Import

After creating the .bacpac file by exporting the data, we need to import it into the Azure SQL Database. For this, first connect with the Azure SQL Server through SSMS. Then right-click on the Databases node and choose Import Data-Tier Application..

Choose Import Data-Tier Application

Choose Import Data-Tier Application..

It will open the below Import Data-Tier Application.. page as shown below.

Import Data-Tier Application..

Import Data-Tier Application..

Click Next button. It will open the Import Settings page. In this page we need to specify the .bacpac file location for import.

Specify the BACPAC file for import

Specify the BACPAC file for import

Click Next to reach database setting page. Here we need to specify the Azure SQL Server Name, New Azure SQL Database name & the service tier based on the database size and other requirement as shown in the below screenshot

Database setting page

Database setting page

We can change the default Microsoft Azure SQL Database settings as per your requirement. For example, I have changed the Edition of Microsoft Azure SQL Database, Maximum database size (GB) and Service Objective for my database as shown in the below screenshot.

Change Azure SQL Database default settings

Change Azure SQL Database default settings

Once all database settings defined, click on the Next button. It will bring up the Summary page as shown below.

Summary Page for verify settings

Summary Page for verify settings

Once settings verified, click on the Finish Button. It will start the Import process as shown in below screenshot.

Importing Database...

Importing Database…

Once the Importing database process completed successfully, it will bring up the Result page. Click on the Close button.

Result Page - Importing Database process successfully completed

Result Page – Importing Database process successfully completed

Database is successfully imported to the Azure SQL Database. We can it verify by connecting the Azure SQL Database using the SQL Server Management Studio and then expanding the Database node. Database will be visible and it is available for SQL queries as shown in the below screenshot.

Executing SQL Query against the imported database

Executing SQL Query against the imported database

Summary

This article is the part of the series Migrating SQL databases to Azure SQL Database. In the last article in this series, we discussed  SSMS deploy method for migrating the SQL Database to Azure. In this article we discussed how to migrate SQL Database to Azure SQL Database using SSMS Export/Import data as a step by step process. Unlike SSMS deploy method, which is a single step process and suitable for smaller database, it is a multi step process and suitable for large database but with longer downtime required. In the next article, we will going to discuss the migration of SQL database to Azure SQL Database using the Microsoft database migration assistant (DMA) tool.

Please don’t forget to gives your valuable comments for the article or you can send me the direct queries to my Email id askvivekjohari@gmail.com. If you want you can also join our Facebook community here or follow me on Twitter

Read More

Migrating SQL Database to Azure SQL Database using SSMS Deploy

Understanding Azure SQL Database- Introduction

Azure Pricing Models and Service Tiers

Azure – Creating an Azure SQL Database

How to connect with Azure SQL Database

Read more from Tutorials

SQL Tutorial

Azure Tutorial

Posted in Azure, Migration to Azure SQL Database | Tagged , , , | 27 Comments

Difference between Stored Procedure and Trigger

Difference between Stored Procedure and Trigger

 

 

SQL Stored Procedure & SQL Triggers, both are the important objects of SQL. SQL stored procedure are the set of logically group of SQL statements which are a group to perform a specific task. Trigger are also similar to SQL Stored procedure as they can also be a set of logically group SQL Statement  but they are needed to perform some predefined action for automatic execution whenever users try to execute data modification commands (INSERT, DELETE and UPDATE) on the specified tables. Triggers are defined on Tables and whenever any Insert /delete /update command executed on these tables, SQL statements defined in the SQL trigger got executed. This article will going to cover the difference between Stored Procedure and Trigger.

Stored Procedure Vs Trigger

  1. We can execute a stored procedure whenever we want with the help of the exec command, but a trigger can only be executed whenever an event (insert, delete and update) is fired on the table on which the trigger is defined.
  2. We can call a stored procedure from inside another stored procedure but we can’t directly call another trigger within a trigger. We can only achieve nesting of triggers in which action (insert, delete and update) defined within a trigger can initiate the execution of another trigger defined on the same table or different table.
  3. Stored procedures can be scheduled through a job to execute on a predefined time, but we can’t schedule a trigger.
  4. Stored procedure can take the input parameters, but we can’t pass the parameters as an input to a trigger.
  5. Stored procedures can return values but a trigger cannot return a value.
  6. We can use the Print commands inside the stored procedure to debug purpose but we can’t use the print command inside a trigger.
  7. We can use the transaction statements like begin transaction, commit transaction and rollback inside a stored procedure but we can’t use the transaction statements inside a trigger.
  8. We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.) but we can’t call a trigger from these files.

 

 

SQL Stored Procedure and SQL Trigger are one of the important objects of SQL Server. This article tells about the difference between Stored Procedure and Trigger.

If you want to read more SQL Concepts, you can find the list of all SQL articles in page SQL Tutorial

If you want to read more Azure SQL Concepts, you can find the list of all Azure SQL articles in page Azure Tutorial

Please don’t forget to gives your valuable comments for the article or you can send me the direct queries to my Email id askvivekjohari@gmail.com.

Read More

Sql Server – How to write a Stored procedure in Sql server

SQL Triggers – An Introduction

SQL Script to find all the triggers defined on a database or on a single table

Advantages of SQL Stored Procedure

SQL Trigger – Advantages & Disadvantages

 

 

Posted in Imp SQL Difference, SQL Server, SQL Stored Procedure | Tagged , , , | 15 Comments

Advantages of SQL Stored Procedure

SQL Stored procedure are the set of logically group of SQL statement which are grouped to perform a specific task. Stored procedure are better than the inline SQL queries as they help in securing the database from SQL Injection. It also help in increasing the performance of the Database. There are many other Advantages of SQL Stored Procedure which we are going to discuss in this article.

Advantages of SQL Stored Procedure

1) One of the main benefit of using the Stored procedure is that it  reduces the amount of information sent to the database server. It can become more important benefit when the bandwidth of the network is less. Since if we send the SQL query (statement)which is executing in a loop to the server through network and the network get disconnected then the execution of the SQL statement don’t returns the expected results, if the SQL query is not  used between Transaction statement and rollback statement is not used.
2) Compilation step is required only once when the stored procedure is created. Then after it  does not required recompilation before executing unless it is modified and re utilizes the same execution plan whereas the SQL statements needs to be compiled every time whenever it is sent for execution even if we send the same SQL statement every time.
3) It helps in re usability of the SQL code because it can be used by multiple users and by multiple client since we needs to just call the stored procedure instead of writing the same SQL statement every time. It helps in reduces the development time.
4) Stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving the permission on the tables used in the Stored procedure.
5) Sometime it is useful to use the database for storing the business logic in the form of stored procedure since it make it secure and if any change is needed in the business logic then we may only need to make changes in the stored procedure and not in the files contained on the web server.

 

 

SQL stored procedure is one of the most important concept of SQL Server. You can read more about the SQL Stored procedure and its various type from the article Sql Server – How to write a Stored procedure in Sql server. In this article, we discuss many advantages of SQL Stored Procedure.

If you want to read more SQL Concepts, you can find the list of all SQL articles in page SQL Tutorial

If you want to read more Azure SQL Concepts, you can find the list of all Azure SQL articles in page Azure Tutorial

Please don’t forget to gives your valuable comments for the article or you can send me the direct queries to my Email id askvivekjohari@gmail.com.

Read More

Sql Server – How to write a Stored procedure in Sql server

Posted in SQL Server, SQL Stored Procedure | Tagged , , | 32 Comments