SQL Server interview question Part 2

1. Ques: What is the difference between Varchar and Nvarchar types?
Answer:
Varchar :-

• Varchar variable type doesn't store Unicode characters.
• It takes 1 byte per character.
• Accepts only English character 
• Doesn't supports other language symbols 
• Runs faster than NVARCHAR as consumes less memory 
• Use this when you develop the application for only local purpose 

Nvarchar :-

• Nvarchar variable type store Unicode characters.
• It takes 2 byte per character.
• Accepts both English character and non-English symbols 
• it allows you to store multiple languages to store in database.
• Runs slower than VARCHAR as consumes less memory 
• Use this when you use your application globally

2. Ques: What is a Stored Procedure? How many types of Types of Stored Procedure and what are the advantage of Stored Procedure.
Answer:
Stored procedures are precompiled group of sql statements and stored in the database.it compile only first time and saved in database.

There are four types of Stored Procedure in Sql Servre:
System Stored Procedure
User Defined Stored Procedure.
Extended Procedure.
CLR Stored Procedure.

Advantage of Stored Procedure are given bellow as

• Stored procedure will reduce network traffic and increase the performance.
• Stored procedures provide better security to your data.
• Stored procedure allows faster execution.
• Stored procedure prevent SQL injection attacks.
• Stored procedures increase scalability by isolating application.it can be used with any number of applications.
• Reduced development cost and increased reliability.

3. Ques: What is Joins? How many types of Types of Joins in sql server.

Answer:

Joins are used to get data from two or more tables based on relationship.

Types of Joins are 

Inner Join : Inner join returns only those records that match in both the tables.

Outer Join : It is three types 

• Left Outer Join:In this join returns all records from left table and from right table returns only matched records.
• Right Outer Join:In this join returns all records from Right table and from left table returns only matched records.
• Full Outer Join: In this join returns all records from both the tables.

Self Join : Self join is used to join a table to itself.

Cross Join : Cross join selects all the rows from first table and all the rows from second table.In Cross join result set which is the number of rows in the first table multiplied by the number of rows in the second table.it is Cartesian product of both the tables. In cross join can not apply where clause.


4. Ques: What is difference between Triggers and Stored procedures ?
 Answer:
The differences are:

• Triggers fire automatically but they need events for that.(Ex: create, alter, drop, insert, delete, update) 
• Procedures have to be explicitly called and then executed.They do not need create events for that, alter, drop, insert, delete, update. we can also execute procedures automatically using the sp_procoption. 

• we cannot pass parameters inside the triggers, 
• but we can pass parameters inside stored procedures 

• using a trigger: we need some DDL/DML Statement 
• using a procedure: NO DDL/DML is needed

5. Ques: What is Replication in Sql Server? What are the types of Replication?

Answer:

Replication is the process of distributing data from one database to another on the same server or servers connected through LAN or the internet. Replication is used to synchronize data between databases that may be at remote location. Using this, you can maintain copies of database at more than one locations.

There are three type of Replication in Sql Server 

• Snapshot Replication 
• Merge Replication 
• Transactional Replication 

Snapshot Replication

• In this kind, snapshot of entire data is copied from publisher to the subscriber's database on regular interval. 
• This kind of replication is used for replicating data that doesn’t change frequently. 
• This replication is considered when amount of data to be replicated is small.

Merge Replication 

• This allows both publisher and subscriber to work independently, online or offline and merges the changes later. 
• Here changes are track on both publisher and subscriber and then merged.

Transactional Replication 

• This kind is used when changes are frequent. 
• The replication agent monitors the changes at the publisher and distributes the changes to the subscribers.
• Transactional Replication is required where up to date data is required.

5. Ques: What is a SubQuery? What are the Properties of SubQuery ? What are the Types of Subquery?
Answer:

• Subquery or Inner query or Nested query is a query in a query.
• A Subquery is usually added in the WHERE Clause of the sql statement.
• Used when we know how to search a value using SELECT statement, but don’t know the exact value.
• Subqueries are an alternate way of returning data from multiple tables.
• Subqueries can be used with the following sql statements along with the comparison operators like =, <, >, >=, <= etc. SELECT, INSERT, UPDATE, DELETE

Properties of Sub query given bellow

• It must be enclosed in the parenthesis.
• It must be put in the right hand of the comparison operator.
• It cannot contain an ORDER-BY clause.
• A query can contain more than one sub-query.

Types of Subquery

• Single-row sub query, where the sub query returns only one row.
• Multiple-row sub query, where the Subquery returns multiple rows.
• Multiple column sub query, where the sub query returns multiple columns.