SQL Server Interview Questions and Answers

    1 Votes

45) List all types of constraints in Sql Server?

Below are the list of constraints in Sql Server –
  • NOT NULL
  • DEFAULT
  • CHECK
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
46) Why to use IDENTITY in Sql Server?

IDENTITY is used for a column to auto increment the value of the column in a table and it is mainly used with Primary Key.

47) What are the differences between Union, Interact and Minus operators?
  • Union operator is used to combine all the results or records of the table and it removes the duplicate values.
  • Interact operator is used to return the common list of records between two result sets.
  • Minus operator is used to get the list of records from the first result set and which is not there in second result set.
48) List out difference between Union and UnionAll in Sql Server?

Union is used to combine all result sets and it removes the duplicate records from the final result set obtained unlike UnionAll which returns all the rows irrespective of whether rows are being duplicated or not.

Union checks the number of columns given in the SELECT statement should be equal or not and the datatypes are also should be same and same applied to UnionAll.

49) Explain “ROW_NUMBER()” in Sql Server with an example.

“ROW_NUMBER()” is used to return a sequential number of each row within a given partition. “1” will be the first position. “Partition By” and “Order By” can be used along with “ROW_NUMBER()”. Below is the example for the same

SELECT ROW_NUMBER() OVER(ORDER BY EmpSalary DESC) AS Row FROM Employees WHERE EmpNameName IS NOT NULL

50) What are the differences between “ROW_NUMBER()”, “RANK()” and “DENSE_RANK()”?

  • “ROW_NUMBER” - Used to return a sequential number of each row within a given partition.
  • “RANK” - Used to returns a new row number for each distinct row in the result set and it will leave a number gap in case of duplicates.
  • “DENSE_RANK” - Used to returns a new row number for each distinct row in the result set and it will not leave any number gap in case of duplicates.

51) Explain about Link Server in Sql Server?

Linked Server is used to enable execution of OLEDB data sources in remote servers. With Linked servers we can create easy SQL statements which will allow remote data to be joined, combined and retrieved with data in local.

52) What are the advantages of user defined functions over stored procedures in Sql Server?

User Defined functions can be used in SELECT/WHERE/HAVING clauses whereas stored procedure cannot be called. In case of table valued functions, the returned table cam be used for joining with other tables.

53) What is Snapshot Isolation and how it can turned ON and OFF in Sql Server?

Updated versions of the rows for a transaction is maintained in “TempDB” and once the transaction begins it will ignore all the rows updated or inserted in a table. Below Sql Statement can be used for turning ON snapshot isolation –

SET ALLOW_SNAPSHOT_ISOLATION ON

54) Why to use “OUTPUT” clause in Sql Server?

OUTPUT clause can be used for determining the rows which are affected due to operations like – INSERT/DELETE/UPDATE. For Insert statements we have a table called “INSERTED”, for delete we have “DELETED” table for tracking the rows which are inserted and deleted respectively.

55) List out the differences between “REPLACE” and “STUFF” functions in Sql Server?

“REPLACE” function used for replace the characters in a string with the given character. For example

SELECT REPLACE(‘my Name’, ‘m’, ‘h’)

Output : hy nahe

“STUFF” function is used replace the part of one string to another. For example

SELECT STUFF('testing', 3, 3, 'Hello');

Output – teHellong

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