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.
Start -> Programs -> Microsoft SQL Server -> “SQL Enterprise Manager” and run SQL Enterprise Manager.
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
EXEC sp_Child @params
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