Before Applying job posting in any websites or recruiting agencies or sending resumes to job recruiters or any other employment website you need to improve your skills on general knowledge, Aptitude, Puzzles, logical reasoning, general english, database, programming as per your job profile. Job recruiters or mostly preferring job candidates who are having more stuff in those categories mentioned above. So improving more skills on your job profile always boost up your career and your performance. Even hiring companies and job recruiters who are looking for many job posting websites actually they are looking and filtering on these activities. In these Sql Server section you need to update more questions to perform well in front of job recruiters and companies. Sql Server Interview questions are most important to score in interview, Update all questions to score well in all rounds in job interview.

Sql Server interview questions and answers

26)
What is REPEATABLE READ levels in SQL Server ?

This setting disallows dirty and non-repeatable reads. However, even though the locks are held on read data, new rows can still be inserted in the table, and will subsequently be interpreted by the transaction

27)
What is SERIALIZABLE levels in SQL Server ?

This is the most restrictive setting holding shared locks on the range of data. This setting does not allow the insertion of new rows in the range that is locked; therefore, no phantoms are allowed.

Following is the syntax for setting transaction level in SQL SERVER.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

28)
What is Intent lock in SQL Server ?

The intent lock shows the future intention of SQL Server’s lock manager to acquire locks on a specific unit of data for a particular transaction. SQL Server uses intent locks to queue exclusive locks, thereby ensuring that these locks will be placed on the data elements in the order the transactions were initiated Intent locks come in three flavors: intent shared (IS) , intent exclusive (IX), and shared with intent exclusive (SIX)

IS locks indicate that the transaction will read some (but not all) resources in the table or page by placing shared locks.

IX locks indicate that the transaction will modify some (but not all) resources in the table or page by placing exclusive locks.

SIX locks indicates that the transaction will read all resources, and modify some (but not all )of them. This will be accomplished by placing the shared locks on the resources read and exclusive locks on the rows modified. Only one SIX lock is allowed per resource at one time; therefore , SIX locks prevent other connections from modifying any data in the resourse(page or table), although they do allow reading the data in the same resourse.

29)
What is Shared lock in SQL Srever ?

Shared locks (S) allow transactions to read data with SELECT statements. Other connections are allowed to read the data at the same time; however, no transactions are allowed to modify data until the shared locks are released.

30)
What is Update lock in SQL Server ?

Update locks (U) are acquired just prior to modifying the data. If a transaction modifies a row , then the update lock is escalated to an exclusive lock; otherwise, it is converted to a shared lock. Only one transaction can acquire update locks to a resource at one time . Using update locks prevents multiple connections from having a shared lock that want to eventually modify a resource using an exclusive lock. Shared locks are compatible with other shared locks , but are not compatible with Update locks.