I have personally implemented the solution suggested of resolving contention about creating an index with OPTIMIZE_FOR_SEQUENTIAL_KEY at my client’s place.
Today’s goal is to understand what is the Last Page Insert PAGELATCH_EX Contention Due to Primary Key. Let us start understanding it together.
Recently, one of my clients showed me a very cool trick to check Primary Key for a single table with the help of the system stored procedure sp_pkeys.
Question: What is Trusted Constraint in SQL Server?
Question: How to Create Primary Key Without ANY Index?
Unique key on a column is used to identify a particular row easily. It is created either by explicitly mentioning it either part of the definition or when defined as Primary key (By default it is unique and not null). Let us learn about special stored procedure sp_special_columns customers today.
Question: How to Allow Only Alphabets in Column?
Answer: This was a real world scenario and I have mixed feeling about this kind of question being asked in interviews. However, let us accept this question as it is and let us see how to solve this problem.