SQL Server Interview Questions and Answers

    1 Votes

12) Why to use Stored Procedures in Sql Server?

Stored Procedures are mainly used for reusability and security for data. Stored Procedures are pre-compiled files so whenever the application want to use run the stored procedure it will not be compiled again.

13) List out some advantages and disadvantages of stored procedure in Sql Server?
Advantages
  • Testing
  • Maintainability
  • Speed
  • Optimization
  • Security etc.
Disadvantages
  • Portability
  • Limited Coding functionality
14) List out some differences between DELETE and TRUNCATE?
Below are the following differences between DELETE and TRUNCATE –
  • DELETE can be rolled back but TRUNCATE cannot be rolled back.
  • When executing the query DELETE will keep lock on row unlike TRUNCATE which keeps lock over whole table.
  • TRUNCATE will reset the value of identity column whereas DELETE will not do that.
  • Trigger will be fired in case of DELETE but in case of TRUNCATE it will not.
15) Explain COLLATE keyword in Sql Server?
COLLATE keyword can be applied to either column definitions or database definitions. For example
SELECT EmpId FROM Employee ORDER BY EmpId COLLATE Latin1_General_CS_AS_KS_WS ASC;
16) List out the differences between Global and Local temp tables in Sql Server?

Global temp tables can be created with – “##” and it will be visible to all active sessions and this temp table will be deleted when all active sessions are abandoned or disconnected. Local temp table will be visible to only to the user who created and users of other session will not be able to see this. And this will be deleted once the table creator session is abandoned or disconnected.

17) List out the different types of locks available in Sql Server?

Below are the list of locks available in Sql Server –

  • Update Locks
  • Shared Locks
  • Exclusive Locks

18) What are the differences between Left join and Inner join in Sql Server?

  • Left join will return all the rows from the left table and matching rows from right table. “Left Join” and “Left Outer Join” are used interchangeably because records which are returned will be the same with either of these.
  • Inner join matches the common records in two tables joined. In Inner join each record of table A Is matched with each record of Table B and the matched records are then be displayed in the resultant table.

19) List out the differences between Clustered Index and Non Clustered Index in Sql Server?

  • Clustered Index – Clustered Index will reorder the number of records in a table and by default the reordering will be done on the basis of primary key as it default acts like a clustered index.
  • Non Clustered Index – Non Clustered Index depends on clustered index internally. Leaf nodes will not be data pages as in clustered index instead it will have index rows, which acts like a pointer to point to root node of clustered index.

20) List the different types of collation sensitivities in Sql Server?

Below are the list of collation sensitivities in sql server –

  • Case sensitivity
  • Accent sensitivity
  • Kana Sensitivity
  • Width sensitivity

21) Why to use UPDATE_STATISTICS command in Sql Server?

This command is used to update the index of the table whenever there is a bulk insertion or updation or deletion in the table.

22) Explain Sql server authentication modes?

Below are the two authentication modes of sql server –

  • Mixed Mode 
  • Windows Mode
Page 2 of 6

Popular Videos

communication

How to improve your Interview, Salary Negotiation, Communication & Presentation Skills.

Got a tip or Question?
Let us know

Related Articles

SQL Queries Interview Questions and Answers
Database & SQL Interview Questions
Oracle Interview Questions and Answers on SQL Queries and Database Theory
Advanced SQL Interview Questions and Answers
MongoDB interview questions & answers