Loading...

Follow Technology with Vivek Johari on Feedspot

Continue with Google
Continue with Facebook
or

Valid
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

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Point In Time Restore for Azure SQL Database

Point In Time Azure Database Restoration - YouTube

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

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

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

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

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

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

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

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

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

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Azure SQL Database Creation

Creating an Azure SQL Database

Azure Database Creation - YouTube

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 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 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:

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

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

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 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

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.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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

Merry Christmas

The post Merry Christmas appeared first on Technology with Vivek Johari.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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..

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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

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

Detecting the compatibility issues using Assessment Type project

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.

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

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

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

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…

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

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.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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

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

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 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

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

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

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..

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

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

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

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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

The post Difference between Stored Procedure and Trigger appeared first on Technology with Vivek Johari.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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

The post Advantages of SQL Stored Procedure appeared first on Technology with Vivek Johari.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Advantages & Disadvantages of SQL Trigger Introduction

Triggers are database objects which are needed to perform some predefined action for automatic execution whenever users try to do execute data modification commands (INSERT, DELETE and UPDATE) on the specified tables. Triggers are helpful since it can help in maintaining the integrity of the data in database tables but they also hard to maintain as are difficult to find. Since they executed on every Insert/delete/update command on the table, they can slow down the performance. This article will discuss the advantages & disadvantages of SQL Trigger as it will help you in deciding whether to define the SQL Trigger or not.

If you want to study more about SQL Triggers you can read the article SQL Triggers – An Introduction

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.

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

This article tells about the various advantages and disadvantages of SQL Trigger. If you want more about SQL trigger , its various type & real world example of SQL trigger, you can read my article  SQL Triggers – An Introduction.

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

Difference between Stored Procedure and Trigger

The post Advantages & Disadvantages of SQL Trigger appeared first on Technology with Vivek Johari.

Read Full Article

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview