SQL Server Interview Questions and Answers

    1 Votes

56) Why to use “No Lock” in Sql Server?

“No Lock” is used for unlocking the rows which are locked by some other transaction. Once after the rows are committed or rolled back no need to use No Lock. For example

SELECT * from Employees WITH(NOLOCK)
57) What are the significance of master, tempdb and model databases?
  • master - This database will have data and catalog of all the databases of SQL Server instance. 
  • tempdb - tempdb database will have temporary objects like local and global temporary tables and stored procedures as well.
  • model - model database is mainly used for creating new user databases.
58) Explain about unique identifier datatype in Sql Server?

Unique Identifier datatype mainly used for primary key columns of the tables or any other columns which need to have unique Ids. “NEWID()” function can be used for generating unique identifier for the column. Unique Identifiers are also named as GUIDs.

59) Why to use “PIVOT” in Sql Server?

Pivot table automatically count, sort and total the data in a table or spreadsheet and used to create a separate table for displaying summarized data.

60) Explain Alternate key, Candidate Key and Composite Key in Sql Server?

  • Alternate Key – To identity a row uniquely we can have multiple keys one of them is called primary key and rest of them are called alternate keys.
  • Candidate Key – Set of fields or columns which are uniquely identified in a row and they constitute candidate keys. 
  • Composite Key – One key formed by combining at least two or more columns or fields.

61) How to use “BETWEEN” operator in Sql Server?

“BETWEEN” operator is used for selecting the range of values. For example

SELECT * FROM Employees WHERE EmpSalary BETWEEN 10000 AND 20000

62) How to use “DROP” keyword in Sql Server and Give an example.

“DROP” keyword is used to drop either Index or database or table. Below are list of Sql statements using Drop keyword.

  • Dropping Index
  • DROP INDEX my_index
  • Dropping Database
  • DROP DATABASE my_database
  • Dropping Table
  • DROP TABLE my_table

63) List out all Null functions in Sql Server?

Below are the list of Null functions in Sql –

  • ISNULL()
  • NVAL()
  • IFNULL()
  • COALESCE()

64) What are the differences between ISNULL() and COALESCE() in Sql Server?

ISNULL() – This function is used to replace the given value in case of NULL value. For example

SELECT ISNULL(@myvar, ‘replacetext’)

COALESCE() – This function will return the first non-null expression given in the list of expressions. Advantage of this function over “ISNULL()” is it takes more than two inputs for checking non null expression unlike ISNULL() which takes only two inputs. For example

SELECT COALESCE(@firvar, @secondvar, @thirdvar);

65) Give list of Scalar and Aggregate functions of Sql Server?

Scalar Functions

  • MID() - Extracting characters from a string.
  • LEN() – Get the length of string.
  • ROUND() – Rounding the number.
  • UCASE() - Change it to upper case.
  • LCASE() - Change it to lower case etc.

Aggregate Functions

  • AVG() – Returns the average value. 
  • MAX() – Returns maximum value.
  • MIN() - Returns minimum value.
  • COUNT() - Returns count of rows.
  • SUM() – Returns sum value etc.

66) Give list of Date functions of Sql Server?

Date Functions

  • GETDATE() - Get current data and time.
  • DATEDIFF() – Get time between two dates. 
  • DATEPART() – Get single part of Datetime.
  • DATEADD() – Adding time interval from Date.
  • CONVERT() – Display date in different formats.
Page 6 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