How to identify duplicate indexes along with columns in SQL Server?

To get the key column list from indexes that are duplicates in SQL Server, you can use the following query:

use databasename
go

WITH DuplicateIndexes AS (
    SELECT 
        i.OBJECT_ID,
        i.index_id
    FROM 
        sys.index_columns ic
    JOIN 
        sys.indexes i ON i.OBJECT_ID = ic.OBJECT_ID 
                     AND i.index_id = ic.index_id
    WHERE 
        i.type_desc <> 'HEAP' 
		AND OBJECT_NAME(i.OBJECT_ID) NOT LIKE '%sys%' --excluding system tables
    GROUP BY 
        i.OBJECT_ID, i.index_id
    HAVING 
        COUNT(*) > 1 -- to check duplicates 
)

SELECT 
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    OBJECT_NAME(ic.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS IndexedColumns
FROM 
    sys.index_columns ic
JOIN 
    sys.indexes i ON i.OBJECT_ID = ic.OBJECT_ID 
                 AND i.index_id = ic.index_id
JOIN 
    sys.objects o ON o.OBJECT_ID = ic.OBJECT_ID
JOIN 
    sys.columns c ON ic.OBJECT_ID = c.OBJECT_ID 
                 AND ic.column_id = c.column_id
JOIN 
    DuplicateIndexes di ON di.OBJECT_ID = ic.OBJECT_ID 
                        AND di.index_id = ic.index_id
GROUP BY 
    o.schema_id, ic.OBJECT_ID, i.name;
  

This query first identifies the indexes that are duplicates, and then retrieves the table name, index name, and the key column list for each duplicate index.

Execute this query in your SQL Server management tool to get the key column list from indexes that are duplicates in your database.

How to Review transaction order and lock acquisition in SQL Server

In SQL Server, you can review the transaction order and lock acquisition by analysing the queries and transactions that are being executed against the database. Here are some approaches to review transaction order and lock acquisition:

1. Transaction isolation levels:

   • Review the transaction isolation levels used in your database transactions. Isolation levels such as Read Uncommitted, Read Committed, Repeatable Read, and Serializable can impact the order of lock acquisition and the behaviour of concurrent transactions.

2. Query execution plans:

   • Use SQL Server Management Studio (SSMS) or other database management tools to analyse the query execution plans for your transactions.
   • The execution plans can provide insights into the order in which data is accessed and the types of locks acquired during query execution.

3. Locking and blocking:

   • Monitor and analyse the locking and blocking behaviour of concurrent transactions using tools like SQL Server Profiler, Extended Events, or dynamic management views (DMVs) such as sys.dm_tran_locks and sys.dm_os_waiting_tasks.
   • Identify instances of blocking and analyse the lock types and resources involved to understand the order of lock acquisition.

4. Transaction log and history:

   • Review the transaction log and history to understand the sequence of transactions and their impact on lock acquisition.
   • SQL Server's transaction log and history can provide valuable information about the order in which transactions are executed and their associated locks.

By using these approaches, you can gain insights into the transaction order and lock acquisition behaviour in SQL Server, which can help in identifying potential issues related to deadlocks, blocking, and overall transaction concurrency.

What is deadlock priority and how to address in SQL Server

In SQL Server, deadlock priority is a mechanism that allows you to influence the selection of the transaction that will be chosen as the deadlock victim when a deadlock occurs. You can use deadlock priority to specify the importance of individual transactions in the event of a deadlock.

To address deadlock priority in SQL Server, you can consider the following:

1. Setting deadlock priority:
   • You can use the SET DEADLOCK_PRIORITY statement to specify the priority of a session or transaction.
   • The priority levels range from -10 to 10, where -10 is the lowest priority and 10 is the highest.
   • By setting the deadlock priority, you can influence the selection of the victim transaction when a deadlock occurs.

Here's an example of how to set the deadlock priority for a session:

SET DEADLOCK_PRIORITY LOW; -- Set the deadlock priority to low
  

2. Adjusting transaction logic:

   • Design your transaction logic to handle the potential impact of being chosen as the deadlock victim based on the assigned deadlock priority.
   • Consider implementing retry logic for transactions with lower deadlock priority after being chosen as the deadlock victim.

3. Analyzing and tuning deadlock priority:

   • Evaluate the impact of deadlock priority settings on your application's transactions and overall performance.
   • Tune the deadlock priority based on the specific requirements and characteristics of your application to effectively manage deadlocks.

It's important to carefully consider the implications of deadlock priority settings in SQL Server and design your transaction logic to handle deadlock situations appropriately. Understanding the behavior of deadlock priority in SQL Server is crucial for effectively addressing and managing deadlocks.

Understanding In-Memory Caching in .NET Core with IMemoryCache Interface

1. In-Memory Caching in .NET Core:
- In-Memory Caching is used to provide faster response to incoming requests by retrieving data from cache rather than the original source.
- Data Caching allows retrieval of data from cache as long as it doesn't expire.

2. Terms Related to Caching:
- Cache Hit refers to the requested data being in the cache, while Cache Miss refers to the data not being in the cache.
- In the case of Cache Miss, data is fetched from the data source and written back into the cache.

3. In-Memory Cache in .NET Core:
- In .NET Core, data can be written to cache, read, or deleted using the IMemoryCache interface in the Microsoft.Extensions.Caching.Memory library.
- Various options such as AbsoluteExpiration, ExpirationTokens, Priority, Size, and SlidingExpiration can be used to manage the cache.

4. Usage in a Project:
- Memory Cache is enabled in the ConfigureServices method in the startup.cs class by adding services.AddMemoryCache().
- The IMemoryCache interface is injected in the related controller to use 'In-Memory Cache'.

Convert String to Title case using Javascript

Here is the function to convert string to title case, which can handle spaces and underscores. Below function will remove underscores from the string.

// Import the function
function convertToTitleCase(input) {
  return input.toLowerCase().replace(/_/g, ' ').replace(/\b\w/g, function(match) {
    return match.toUpperCase();
  });
}

You can call the convertToTitleCase function in HTML by including a script tag with the function definition, and then using JavaScript to call the function and display the result.

Here's an example of how you can call the convertToTitleCase function in HTML:

<!DOCTYPE html>
<html>
<head>
  <title>Convert to Title Case</title>
</head>
<body>

<p id="output"></p>

<script>
// Function definition
function convertToTitleCase(input) {
  return input.toLowerCase().replace(/_/g, ' ').replace(/\b\w/g, function(match) {
    return match.toUpperCase();
  });
}

  // Call the function and display the result
  let input = "Nagasai_Srinivas_Mudara";
  let convertedString = convertToTitleCase(input);
  document.getElementById("output").innerHTML = convertedString;
</script>

</body>
</html>

In this JavaScript function, the replace method is used with a regular expression to match the underscores and lowercase letters and convert the lowercase letters to uppercase when preceded by an underscore or at the beginning of the string.

You can use the convertToTitleCase function to convert any input string to title case in a generic and reusable way.