12) Why to use Stored Procedures in Sql Server?
Stored Procedures are mainly used for reusability and security for data. Stored Procedures are pre-compiled files so whenever the application want to use run the stored procedure it will not be compiled again.
- Security etc.
- Limited Coding functionality
- DELETE can be rolled back but TRUNCATE cannot be rolled back.
- When executing the query DELETE will keep lock on row unlike TRUNCATE which keeps lock over whole table.
- TRUNCATE will reset the value of identity column whereas DELETE will not do that.
- Trigger will be fired in case of DELETE but in case of TRUNCATE it will not.
SELECT EmpId FROM Employee ORDER BY EmpId COLLATE Latin1_General_CS_AS_KS_WS ASC;
Global temp tables can be created with – “##” and it will be visible to all active sessions and this temp table will be deleted when all active sessions are abandoned or disconnected. Local temp table will be visible to only to the user who created and users of other session will not be able to see this. And this will be deleted once the table creator session is abandoned or disconnected.
17) List out the different types of locks available in Sql Server?
Below are the list of locks available in Sql Server –
- Update Locks
- Shared Locks
- Exclusive Locks
18) What are the differences between Left join and Inner join in Sql Server?
- Left join will return all the rows from the left table and matching rows from right table. “Left Join” and “Left Outer Join” are used interchangeably because records which are returned will be the same with either of these.
- Inner join matches the common records in two tables joined. In Inner join each record of table A Is matched with each record of Table B and the matched records are then be displayed in the resultant table.
19) List out the differences between Clustered Index and Non Clustered Index in Sql Server?
- Clustered Index – Clustered Index will reorder the number of records in a table and by default the reordering will be done on the basis of primary key as it default acts like a clustered index.
- Non Clustered Index – Non Clustered Index depends on clustered index internally. Leaf nodes will not be data pages as in clustered index instead it will have index rows, which acts like a pointer to point to root node of clustered index.
20) List the different types of collation sensitivities in Sql Server?
Below are the list of collation sensitivities in sql server –
- Case sensitivity
- Accent sensitivity
- Kana Sensitivity
- Width sensitivity
21) Why to use UPDATE_STATISTICS command in Sql Server?
This command is used to update the index of the table whenever there is a bulk insertion or updation or deletion in the table.
22) Explain Sql server authentication modes?
Below are the two authentication modes of sql server –
- Mixed Mode
- Windows Mode