45) List all types of constraints in Sql Server?
- NOT NULL
- PRIMARY KEY
- FOREIGN KEY
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.
- 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.
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