SQL Server Interview Questions and Answers

    1 Votes

23) Explain Mixed authentication mode of sql server?

Mixed mode of authentication can either use SQL Server authentication or Windows authentication. If the user opt using windows authentication then the validation will happen in the operating system level and if the user opt for Sql server authentication then the password has to be set up while installing.

24) How the authentication mode can be changed?
Authentication mode can be changed using following steps –
Start -> Programs -> Microsoft SQL Server -> “SQL Enterprise Manager” and run SQL Enterprise Manager.
25) What is recursive stored procedure in Sql Server?

Recursive stored procedure is the stored procedure called as child stored procedure inside the parent or main stored procedure. This can be done easily in Sql Server by using “EXEC” keyword in a stored procedure. For example

Create Procedure SP_Test
AS
BEGIN
EXEC sp_Child @params
END

26) What is Normalization and DeNormalization in Sql Server?

  • Normalization – It’s the process of minimizing dependency and redundancy by properly organizing the fields or columns of the table.
  • DeNormalization – It is the process of accessing the data from higher to lower normalization forms.

27) List out different types of normalizations in Sql Server and explain each of them?

Below are the types of normalizations –

  • 1 NF – Removing the duplicate records from the table by assigning primary key to a table.
  • 2 NF - Meet all the requirements of 1 NF and create the relationship between the tables and segregate the data storing between multiple tables.
  • 3 NF - Meet all the requirements of 2 NF. Remove the list of columns from the table which does not meet primary key constraint.
  • 4 NF – Tables should not more than two relationships
  • 5 NF – Practical constraints on info for justifying the many-to-many relationships.

28) What you mean by Unique Index in Sql Server?

Unique Index will not allow a column to have duplicate values. Unique Index is associated with Primary key by default.

29) Explain View in Sql Server?

It is a virtual table which will have the data from multiple tables. Views can be used to retrieve / update / delete the rows. When the data in the table changes in view also it changes.

30) Explain Indexing and what are the advantages of it?

Indexing contains pointers to the data in a table. Indexes are created in a table to retrieve the data quickly. So Indexing improves the performance as the retrieval of data takes less time. Indexing will be done for columns which are being used more often while retrieving.

31) What is the significance of QUOTED_IDENTIFIER ON in Sql Server?

When “QUOTED_IDENTIFIER” set ON, all identifiers should be delimited by double quotation marks and all literals or single quotation marks or single quotation mark.

32) Why to use Cross Join in Sql Server?

Cross join gives the “cartesian product” of joined tables. In this join no need to specify the conditions while joining the tables.

33) Why to use “STUFF” keyword in Sql Server? Give an example of it.

“STUFF” keyword is used for inserting one string into other string. For example

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

Output – teHeyng

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