Difference between Read Uncommitted and Nolock - SQL server

The read-uncommitted isolation level is the least restrictive isolation level within SQL Server, which is also what makes it popular for developers when looking to reduce blocking.

The nolock table hint behind the scenes performs the exact same action as running under the read-uncommitted isolation level.

The only difference between the two is that the read-uncommitted isolation level determines the locking mechanism for the entire connection and the nolock table hint determines the locking mechanism for the table that you give the hint to.

Sample:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT *
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.ID = T2.id

It is functionally equivalent to:

SELECT *
FROM Table1 T1 WITH(NOLOCK)
INNER JOIN Table2 T2 WITH(NOLOCK) ON T1.ID = T2.ID

Query to search on specific string in database procedures

select name,OBJECT_DEFINITION(object_id) from sys.procedures
where OBJECT_DEFINITION(object_id) like '%With(Index%'

SQl Query to find out the unused Indexs in SQl server


SELECT object_name(i.object_id) AS TableName, i.name AS [Unused Index]
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s ON s.object_id = i.object_id
      AND i.index_id = s.index_id
      AND s.database_id = db_id()
WHERE objectproperty(i.object_id, 'IsIndexable') = 1
AND objectproperty(i.object_id, 'IsIndexed') = 1
AND s.index_id is null
OR (s.user_updates > 0 and s.user_seeks = 0 and s.user_scans = 0 and s.user_lookups = 0)
ORDER BY object_name(i.object_id) ASC