error while loading shared libraries: /opt/cubrid/lib/libclntsh.so.llxxxx: cannot restore segment prot after reloc: Permission denied

Yesterday I installed Oralce in my VMWare workstation, after installation I encountered the below error while running sqlplus command to connect to SQL

error while loading shared libraries: /opt/cubrid/lib/libclntsh.so.llxxxx: cannot restore segment prot after reloc: Permission denied


I am able to fix the below issue by using the below command.

Why do we get the error?

If the linux distribution has SEX Linux enabled, (Security-enhanced Linux) then we may get the error.

To disable the feature temporarily

/usr/sbin/setenforce 0  (Execute from root user) -- Zero to disable, One to enable
/usr/sbin/setenforce 1 -- Enables the feature

After disable the feature temporarily i am able to login into sqlplus.

To disable the feature permanently.

vi /etc/selinux/config

Include comment infront of SELINUX=enforcing line and include the below line
SELINUX=disabled

Reboot the server to get this feature on.

What is virtual log file ?

SQL Server internally divides the transactional log file into smaller chunks, called virtual log files.

What is LSN ( Log Sequence Number) ?
Each log record written into log file is stamped with a number called LSN.

SET FMTONLY Explanation

SET FMTONLY -- Returns only Column Information to client, not the actual result set.

- It helps to view the format how result is going to display without executing the query.

SET FMTONLY ON;
GO
SELECT * FROM KalyanDB.Employee;
GO
SET FMTONLY OFF;
GO
 
 

Editions of SQL Server 2012

SQL Server 2012 Editions

1. Standard Edition

2. Business Intelligence

3. Enterprise Edition

Specialized Editions

Developer -- The developer edition includes all the features which are available in enterprise edition, but it is only meant for development, testing and for demonstration purpose. 

Web    -- It is mainly focused on hosting internert facing web applications. Like Express editions it doesn't have database size restrictions, and it supports upto 64 GB RAM. It is a ideal platform for websites and web applications

Express -- This is a free edition, which can integrate with independent software vendors. It is limited to one processor and 1 GB memory and it is integrated with visual studio environment.

Logshipping - FAQ

What recovery models will support Logshipping
FULL & BULK-LOGGED

What will happen if we change the recovery model of a logshipping database?
If we change it from Full to Bulk Logged, nothing will happen. The T-log files will generate as usual.
If we change it back from Bulk Logged to Full also no issues.
If we change it from Full to Simple and simple to full then it wont generate logbackups from that point onwards.

Example Scenario:
backup log mydatabase to disk='c:\temp\mydatabase_log1.trn' log mydatabase to disk='c:\temp\mydatabase_log1.trn'
alter database mydatabase Set recovery bulk_logged
select name, recovery_model from sys.databases where name ='mydatabase'
backup log mydatabase to disk='c:\temp\mydatabase_log2.trn'
alter database mydatabase Set recovery Full
select name, recovery_model from sys.databases where name ='mydatabase'
backup log mydatabase to disk='c:\temp\mydatabase_log3.trn'
alter database mydatabase Set recovery Simple
select name, recovery_model from sys.databases where name ='mydatabase'
alter database mydatabase Set recovery Full
select name, recovery_model from sys.databases where name ='mydatabase'
backup log mydatabase to disk='c:\temp\mydatabase_log4.trn'

Then we will get the below error message

Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

To Restart the logshipping what needs to be done ?

1) Need to generate full backup to generate logs  -- It will take huge time based ont the size of the database.
2) Simple way is generate a differential backup -- Which allows to generate logs again, no need to generate full backup.

backup database mydatabase to disk='c:\temp\mydatabase_differential1.dif' with differential database mydatabase to disk='c:\temp\mydatabase_differential1.dif' with differential

backup log mydatabase to disk='c:\temp\mydatabase_log5.trn'

Adding File To Logshipping Database -- Error: Could not apply log backup file

Sometimes due to disk space issue or with some other issue, we may need to add additional file to logshipping primary database, in that case the logshipping will fail from that point onwards. We will receive the below error message.

* Error: Could not apply log backup file 'C:\Temp\PrimaryDBLogs\PrimaryDB_20120419115948.trn' to secondary database 'SecondaryDB'.(Microsoft.SqlServer.Management.LogShipping) ***


Error: The file 'C:\Appl\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Secondary_File.ndf' cannot be overwritten. It is being used by database 'PrimaryDB'.

File 'Secondary_File' cannot be restored to 'C:\Appl\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Secondary_File.ndf'. Use WITH MOVE to identify a valid location for the file.
 
Steps
1)  I created two databases called PrimaryDB and Secondary DB
2)  Configured Logshipping between primarydb and secondarydb (Secondary in Readonly Mode)
3)  Ensure Logshipping is configured and running smooth between the servers
4)  Stop the logshipping and disable logshipping jobs
5)  Add data file (ndf) to primaryDB
6)  Run the LSBackup manually
7)  Run the copy job manually (ensure the file copied to target server)
8)  Run the below command to verify whether this trn file consists additional data file or not
         Restore filelistonly from disk='C:\temp\PrimaryDBLogs\PrimaryDB_20120419115948.trn'
9)  Run the below restore command to add the data file to secondaryDb on target server
Use Master

RESTORE log [SecondaryDB] FROM Disk='C:\temp\PrimaryDBLogs\PrimaryDB_20120419115948.trn'
WITH MOVE 'Secondary_File'
TO 'C:\Appl\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Secondary_File2.ndf',
STANDBY = N'C:\Appl\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_SecondaryDB.BAK'
10) Verify file added or not using the below command
select * from secondaryDb..sysfiles
11) Enable the backup, copy and restore jobs.

Changing SQL Server Instance Name - Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44

If you change name of the computer where SQL Server is running, new name will automatically recorded in sys.servers metadata table. Below is the procedure to change name of the SQL Server Instance if it is running in a stand-alone server.

sp_dropserver     -- Removes a server from the list.

sp_addserver       -- Defines a remote server or the name local server.

sp_dropserver

sp_addserver

sp_dropserver kalyan

sp_addserver kalyan\SQL2005

After running the above command we need to restart the SQL Instance.

Some Considerations while renaming the SQL Instance.
1) If the computer has remote remotes it may generate an error.
Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44
There are still remote logins for the server 'kalyan'.

We can drop remote logins using below command

sp_dropremotelogin

sp_dropremotelogin

sp_dropserver [old_server_name], 'droplogins';

2) We need to manually re-configure linked server instance names.

sp_delete_backuphistory

MSDB database keeps track of all backup and restore information, when the backup was generated, who took the backup, what time backup generated, backup file list, backup media information and restore information etc., Over a period of time the database size becomes huge. So it is recommended to to run the sp_delete_backuphistory SP periodically to clean up and reduce size of the database.

sp_delete_backuphistory

It deletes records from the MSDB database older than the oldest date

Example

sp_delete_backuphistory '01/01/2012' --> Removes all oldest information from the database.

Insert Images into database using T-SQL

The following are the rowset functions available with us to query values from different types of sources.

1. OPENQUERY
2. OPENROWSET
3. OPENDATASOURCE
4. OPENXML

OPENROWSET function support bulk insert operations, Using this we can insert BLOB objects into the database.

CREATE TABLE [dbo].[TblImage](

    [Slno] [int] NOT NULL,

    [Image_Data] [image] NULL,

     [Image_FileName] Varchar(128),

     [Image_Path] Varchar(128))

INSERT INTO TblImage(Slno, Image_FileName, Image_Path, Image_Data)

SELECT '1', 'Jan2012.jpg', 'C:\users\kalyan\desktop',

* FROM OPENROWSET(BULK N'C:\Users\Kalyan\Desktop\Jan2012.jpg', SINGLE_BLOB) as tempImg

INSERT INTO TblImage(Slno, Image_FileName, Image_Path, Image_Data)

SELECT '2', 'Feb2012.jpg', 'C:\users\kalyan\desktop',

* FROM OPENROWSET(BULK N'C:\Users\Kalyan\Desktop\Feb2012.jpg', SINGLE_BLOB) as tempImg

INSERT INTO TblImage(Slno, Image_FileName, Image_Path, Image_Data)

SELECT '2', 'March2012.jpg', 'C:\users\kalyan\desktop', Null

INSERT INTO TblImage(Slno, Image_FileName, Image_Path, Image_Data)

SELECT '4', 'March2012.jpg', 'C:\users\kalyan\desktop',

* FROM OPENROWSET(BULK N'C:\Users\Kalyan\Desktop\April2012.jpg', SINGLE_BLOB) as tempImg

Shrink TempDB Data File Without SQL Restart

We can shrink the tempdb data file without restarting the SQL Server using the following steps.

First verify the space usage information from MDF file, If you find there is enough space to shrink then ensure there wont be any open transactions running on the tempdb and execute the below steps to shrink the TempDB Data File.


DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
go
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
DBCC SHRINKFILE (TEMPDEV,1024)
GO

Try Catch In SQL Server

Try /  Catch introduced from SQL Server 2005 onwards, which helps to track the error messages occured in the program block.

Syntax
TRY
     Set of T-SQL Statements
END TRY
BEGIN CATCH
     Set of T-SQL Statements
END CATCH

The catch block will execute statements only if error occured on the TRY Block.

Simple Example

If you execute the below command
select 1/0
It gives the error message  Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.
To handle any kind of errors we can use Try / Catch Construct



Begin Try
Select 1/0
End Try
Begin Catch
Select 'Error Occured'Select 'Error Occured'
End Catch

BEGIN TRY
select 1/0
End Try
Begin Catch
Select ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(),
ERROR_STATE()
RAISERROR ('NUMERIC ERROR',11,1) WITH LOG
-- Logged into Event Viewer as a informational message
RAISERROR ('NUMERIC ERROR',16,1) WITH LOG
-- Logged into Event Viewer as a Error Message .
End Catch

Cluster Log -- SQL 2008

In windows 2003 failover clustering, cluster service will maintain a cluster log file on all individual nodes. These files are located in "%systemroot%\Cluster folder, the name of file is cluster.log.

From Windows 2008 onwards, cluster log  mechanism has been changed and all the cluster events are handled by windows event tracing (ETW). The cluster.log file no longer exists on window 2008 servers.
All cluster log files are in .etl format, to read those files we need to run the below commands to generate text files. The etl files are stored under this directory C:\Windows\System32\winevt\Logs

cluster [clustername] log /gen /copy:"path"

cluster [clustername] log /gen /node="node name"

cluster [] log /gen /copy:"c:\temp\cluster.log" -- It generates logs from both the nodes, if you want generate log from specific node, then you can specify /node switch.

Default Trace SQL Server

SQL Server runs a trace in background which is known as default trace, It helps administrator to troubleshoot few issues. It is enabled by default, we can disable if required, but it is not a overhead for the server because it is light weight.

To View default trace file location

SELECT * FROM fn_trace_getinfo(default);
SELECT * FROM sys.configurations WHERE name = 'default trace enabled'

To disable the default trace file

EXEC master.dbo.sp_configure 'allow updates', 1;

GO

EXEC master.dbo.sp_configure 'show advanced options', 1;

GO

EXEC master.dbo.sp_configure 'default trace enabled', 0;

GO

RECONFIGURE WITH OVERRIDE;

  

To Read Trace file from T-SQL

Select * FROM sys.fn_trace_gettable('FileName', DEFAULT ) * FROM sys.fn_trace_gettable('FileName', DEFAULT )

What is Slipstreaming How to create slipstream drop

Slipstreaming is creating a single installation file which will install SQL Server along with service pack or cumulative updates.

If you want to upgrade SQL Server from 2005 to 2008 along with latest Service Pack, as a first step we need to upgrade with SQL Server 2008 and after that we need to install latest service pack on top of it.  The upgradation and service pack / cumulative update can be done with one setup file, that is know as split streaming.

Steps to creates SlipStream Package

1.       Install .NET Framework 3.5

2.       Windows Installer 4.5

1) Create a folder C:\SQLSlipDVD and copy all the SQL 2008 RTM files into it.

1) Download the service pack which suits your environment from the Microsoft Site

2) Copy into C:\ServicePacks Folder 

3) Extract the service pack with the below syntax

4) SQLServer2008SP1-KB968369-x86-ENU/x C:\servicePacks\SQL2008SP1

5) After extracting copy the setup.exe and setup.rll to C:\SQLSlipDVD and overwrite it

6) Delete the Microsoft.SQL.Chainer.PackageData.dll file from C:\servicePacks\SQL2008SP1 Extracted folder

7) Create a folder with name pcu inside C:\SQLSlipDVD

8) Create a sub folder with version inside pcu (C:\SQLSlipDVD\PCU\x86)

7) Copy all files from c:\servicePacks\SQL2008SP1 to C:\SQLSplidDVD\PCU\x86\ with overwrite

ex:  copy c:\servicePacks\SQL2008SP1\*.*  C:\SQLSplidDVD\PCU\x86\ y

8) Update the DefaultSetup.Ini as below

;SQL Server 2008 Configuration File

[OPTIONS]

PID="xxxxx-xxxxx-xxxxx-xxxxx-xxxxx"

PCUSOURCE=".\PCU"

9) Run the setup file as usual, It will install RTM and SP1 together

Alternatively in other products, we can follow the below approach to install service packs or cumulative updates along with the installation.

1)      After copying binaries  you will find an empty folder called updates

2)      Copy all the CU’s or Service packs into that folder

3)      Run the setup as below from the command prompt

4)      Setup.exe /Action=Install /UpdateSource=".\Updates"

How to make database as suspect database

How to make database as suspect database
1. Run the transaction from one query window (don’t commit or rollback)
2. Open another query window and run SHUTDOWN WITH NOWAIT
3. Open the LDF file in hex editor and overwrite the second row with some values (for example type all 8’s in the second row and save the file)
4. Start the SQL Service
5. Now you can see the current database is in Suspect Mode.

SELECT DATABASEPROPERTYEX ('Test', 'STATUS') AS 'Status';

It shows the result as SUSPECT.

**Make sure you are working with your test database, This may help you to bring suspect database to online.

SQL Server Isolation Levels

Isolation levels are for control the behaviour of Transactions inside SQL Server. The below are the Isolation levels.

1. READ_COMMITTED (Default Isolation Level)

2. READ_UNCOMMITTED

3. REPEATABLE READ

4. SERIALIZABLE

5. SNAPSHOT

what is Dirty Read ?

Reading Uncommited Data is known as dirty read

what is Phantom Read ?

In simple words, the consequent reads will fetch different results in the same transactions. That means if the first select retuns 5 rows, if you run the same select it may return other value. This will not happen only in Serializable Read.

Isolation Level                                  Dirty Read           Non-Repeatable            Read Phantom Read

Read Committed                                   Yes                           Yes                                Yes

Read Uncommitted                                No                            Yes                                Yes

Repeatable Read                                   No                            No                                 Yes

Serializable                                            No                            No                                  No

Syntax :

SET TRANSACTION ISOLATION LEVEL

READ UNCOMMITTED  -- The lowest isolation level, it causes dirty reads. It doesn't cause shared locks. It allows other transactions to modify data while reading. But there is no guarantee of consistency while reading data using this isolation.

READ COMMITTED  -- This is the default isolation level, which reads only committed data. In this isolation level select statements will issue shared locks, if the other transaction is exclusively locked then the current transaction needs to wait until ther other transaction got released. It is good in concurrency and data consistency. But locking and blocking happend.

REPEATABLE READ  -- This is very similar to Read Commited, but it gives guarantee that if the same select statment issued multiple times it provides the same result all the times, because it provides shared lock on the transaction until the end of transaction.

SERIALIZABLE  -- In all the three isolation levels there is a chance of gettting phantom rows (that is newly inserted data) with in the transaction if you query select more than one time, but in this Isolation level it gives guarantee that it wont fetch phantom rows. It applies a range locks or table level lock to acheive this.

SNAPSHOT   -- In this isolation level readers wont block writers, and writers wont block readers. It maintains a seperate copy of transactional information in tempdb and writer will write into it and reader will directly read the data, so there wont be chance of blocking and locking. This Isolation level is a overhead for the tempdb because all operations will be done in tempdb and copy will be loaded into table when user commits the data.

Syntax  :

ALTER DATABASE IsolationTests SET ALLOW_SNAPSHOT_ISOLATION ON

Loop Through Worksheets Using VBA

Sub loopThruSheetNames()


Dim ws As Worksheet
Dim i As Integer
i = 1
MsgBox "Total Sheets in current workbook are " & ThisWorkbook.Worksheets.Count
For Each ws In ThisWorkbook.Sheets
MsgBox "Name of Sheet " & i & " is " & ws.Name
i = i + 1
Next

End Sub

Working with worksheets using VBA

Sub WorkSheetsDemo()

Sheets.Add 'It adds a new worksheet before the active sheet
'To add worksheet at last please use the below syntax
'Sheets.Add After:=Worksheets(Sheets.Count)
'To add a worksheet at required position, we can specify name of the worksheet
Sheets.Add After:=Worksheets("Sheet2")

'To Rename a worksheet
Sheets("sheet9").Name = "MySampleSheet"

End Sub

Script to view clustered drives using TSQL

SELECT * FROM fn_servershareddrives()     -- Displays list of attached shared drives

SELECT * FROM sys.dm_io_cluster_shared_drives  -- DMV to display clustered shared drives

Find and Fix Orphan Users

Sp_change_users_login – This SP helps us to maps the existing database user to SQL login and creates a login if required


Below are the parameters of SP

1. Report -- Reports the orphan users
2. Auto_Fix -- Creates SQL Login for the corresponding database user
3. Update_one -- Maps the database user with corresponding SQL login if SIDS are different.

How To Find Orphan Users in the database?

Use Kalyandb
exec sp_change_users_login 'Report'
                           (OR)
select name from sysusers where issqluser = 1 and uid >4
and name not in (select name from master..syslogins)

Auto_Fix option helps us to fix the orphan user by creating SQL login with the given password. It takes three parameters a) database_user b) SQL Login (we can pass Null or SQL Login name) c) password

USE Kalyandb
EXEC sp_change_users_login 'Auto_Fix', 'test', null, 'password@123'

Output:
Barring a conflict, the row for user 'test' will be fixed by updating its link to a new login.
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 1.

Update_One – It maps the Database user with appropriate SQL Login. Sometimes we face login issues eventhough database user and SQL Login exists on the server. It might be because of their SIDs are different. The sp_change_users_login ‘report’ helps to identify that one also.

Sp_change_user_login ‘Report’
                                (OR)
select uid, sl.sid, su.sid name from sysusers su, master..syslogins sl
where su.name = sl.name and issqluser = 1 and uid > 4 and su.sid <> sl.sid

Update_one helps to correct the SIDs between dbuser and syslogin

EXEC sp_change_users_login 'update_one','test','test'

Sample select statement which will generate Update_One commands for all database users which has SID mismatches with SQL Logins

select 'EXEC sp_change_users_login ' + '''' + 'update_one' + '''' + ',' + '''' + name + '''' + ',' + '''' + name + '''' + char(13) from sysusers where issqluser = 1 and uid >4

Example to Read Cell Values using VBA

Sub getCellValues() 'Example to Read Cell Values
Dim value As Integer
Worksheets("sheet1").Range("N6").Activate
value = ActiveCell.value
MsgBox value
Worksheets("sheet1").Range("P4").Activate
value = ActiveCell.value
MsgBox value
End Sub

Script To Generate Backup of All Databases

DECLARE @db_name VARCHAR(50)

DECLARE @file_path VARCHAR(150)
DECLARE @file_Name VARCHAR(150)

SET @file_path = 'C:\Temp\'

IF left(REVERSE(@file_path ),1) <> '\'
            SET @file_path = @file_path + '\'

DECLARE db_cursor CURSOR FOR
         SELECT name FROM master..sysdatabases WHERE name NOT IN ('tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
             SET @file_Name = @file_path + @db_name + '_' + replace(CONVERT(varchar, getdate(), 
                               101),'/','') + '_Full.BAK'
             BACKUP DATABASE @db_name TO DISK = @file_Name
             FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor

DBCC ShrinkFile Empty File

DBCC ShrinkFile Empty File Option


Shrinks the data file or log file by specified percentage or empties the file by moving data into other file in the same file group.

DBCC Shrinkfile (Database_LogicalName, %of spaceto shrink)

DBCC Shrinkfile(kalyandb_log2, emptyfile) – Which will move the data into log2 file into other file and empties the current specified file. This file can be deleted by using Alter database remove file option.

Alter database database_name remove file kalyandb_log2 -- Removes file (But the file must be empty)

Select * from sys.database_files – which proivdes the complete information of the files which are associated with the current database.

What is Collation fn_helpcollations()

The physical storage of information in SQL Server is handled or controlled by Collations. A collation is a set of rules that manage bit representation of characters in the computer.

If we want to store multilingual data in the SQL Server we may need to change the collation settings of the table.

To view the current collation setting

SELECT SERVERPROPERTY('Collation') – Returns SQL_Latin1_General_CP1_CI_AS

SELECT * FROM fn_helpcollations() – List all the collations that current version supports

How to find the Database Collation?

SELECT DATABASEPROPERTYEX('Database_Name', 'Collation')

To view the collation of all the databases

SELECT name, collation_name FROM sys.databases

Msg 515, Level 16, State 2, Procedure sp_helpdb -- Error while running sp_helpdb

The issue may occur because one of the database owner might be null.
SELECT name, suser_sname(sid), dbid, cmptlevel FROM master.dbo.sysdatabases

To get around the issue we need to figure out which database do not have a valid owner by using the above command. If owner is null the sp_helpdb throws error.

USE KalyanDB

EXEC sp_changedbowner 'sa';

How to Place a value in required Cell using VBA

Sub printCellValues() 'How to Place a value in Required Cell in WorkSheet using VBA

Worksheets("sheet1").Range("E7").value = "kalyan"
End Sub

Worksheets("SheetName").Range("CellAddress").Value  = Required Value

How to create 32bit ODBC DSN in 64bit Operating System

We have seen this request from one of our user, they required a 32-bit ODBC driver to run their application. Below are the steps to create 32-Bit DSN in 64-Bit Server.
We can create this DSN using a folder which is located at C:\windows\SysWow64 folder wherein WoW64 stands for “Windows on 64bit Windows” and this folder contains all the 32-bit binary files, which run on the top of the 64-bit windows. It looks like a double copy of everything in system-32 (which actually is 64-bit binaries).
In WoW64 folder there is an executable file, odbcad32.exe which helps us to create 32-bit DSN on 64-bit operating system.

sp_spaceused & dbcc updateusage

sp_spaceused -- Returns the row count, space used, space reserved and space used by indexes.

Example :
sp_spaceused 'HumanResources.Department'  -- Returns the row count from Department Table, and data size, Index size, Object Size of that table.
sp_spaceussed -- Returns the size of the whole database.

Some time sp_spaceused doesn't report the accurate result, we can query it, In that situation we can use dbcc updateusage command to make the counts accurate.

dbcc updateusage -- It corrects the inaccuracies in row counts, disk space etc.,

Usage & Examples:
dbcc updateusage (0) -- zero indicates current database name.

dbcc updateusage (DatabaseName, "HumanResources.Department")

dbcc updateusage (DatabaseName, TableName, IndexName)

NOLOCK AND READPAST

 NOLOCK -- If the table is exclusively lock eventhen we can retrieve the rows by using NOLOCK hint along with SQL Server. But there is no guarantee of data consistency.

READPAST It skips the rows which are there inside the transaction and returns the remaining rows from the table.

Example using  Adventureworks database

Select count(*) from Employee -- Returns 290 Rows
Select count(*) from Employee where Title = 'Design Engineer' -- Returns 3 rows

Begin Transaction
Update Employee Set ContactID = ContactID + 1 where Title = 'Design Engineer' -- It will place an exclusive lock placed.

Open other query window, If you are trying to retrieve rows from Employee table, it wont allow you to verify

Select COUNT(*) from Employee -- We need to wait to see the result of this query.

Whereas by using NoLock / ReadPast we can retrieve the data from the Employee Table

Select COUNT(*) from Employee with (READPAST) -- Returns 287 Records, where as it will skip the exclusive locked rows and returns the remaining rows.

Select COUNT(*) from Employees with (NOLOCK) -- Returns all 290 records, but there might be inconsitency at data level.