Use of NOWAIT in SQL server ?

If the requested table is locked by any other transaction then you can make use of NOWAIT in SQL server.

SYNTAX

SELECT * FROM TABLE_NAME WITH (NOWAIT)

EXAMPLE

SELECT * FROM StudentMarks WITH (NOWAIT)

If the requested table is locked by any other resources then the following error message will appear

Msg 1222, Level 16, State 45, Line 2

Lock request time out period exceeded.

From the above message we can identify that the table is locked by other resources, So that you can wait for sometime to read the data.

How to Rebuild the Index online in SQL server ?

The following option in SQL server provides you to rebuild the index online.

SYNTAX

ALTER INDEX INDEX_NAME

ON TABLE_NAME REBUILD WITH (ONLINE = ON)

EXAMPLE

ALTER INDEX IX_NAME

ON StudentMarks REBUILD WITH (ONLINE = ON)

Use of NOLOCK in SQL Server ?

To Read the Not committed data in SQL server we can use the NOLOCK option.

Sometimes if you are trying to read the data from the table you can notice that the query is executing for a long time, because the table is locked by other transaction.

Once the transaction is completed the lock will be released from the table then you can the see the data in the table.

To avoid this we can use the NOLOCK option to read the uncommitted data in the table.Following example explain how to use the NOLOCK option in SQL query.

Example

Select * from StudentDetail (NOLOCK)

You can also use the With Keyword as shown below.

Select * from StudentDetail WITH (NOLOCK)

If you want to read the uncommitted data you can go with this option else you need to wait for the transaction to commit.

How to Fix 'int' is not a recognized CURSOR option Error ?

The following example simulate the  'int' is not a recognized CURSOR option error.

We declare the int variable without using '@' Symbol. For all variable declaration we need to use the @ symbol as prefix to avoid the above error.

In above example we declared int variable so that the error is showing as int is not recognized. In case if you declare float variable the error will be
'float' is not a recognized CURSOR option