Creating a unique Clustered Index on a view with NOLOCK option.

Question

Post reply

Creating a unique Clustered Index on a view with NOLOCK option.

dhanasekar.palani

SSC Eights!

Points: 841
More actions
December 19, 2008 at 8:43 am

#197340

I have created a view with NOLOCK option in the table. when i am trying to create a index it says. "Cannot create index on view 'dev13d1.dbo.viewname' because the view contains a table hint. Consider removing the hint." When I am removed the NOLOCK condition it works fine But I need the index to be created with the Nolock on the view is there a option to create it?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Why do you need the nolock?? That data is persisted so the nolock shouldn't really matter, or does it?

dhanasekar.palani SSC Eights! Points: 841 More actions · Answer 2

As per ours DBA instruction's we were told to use NOLOCk on the query.

Is there any problem when we give NOLOCK in the View.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

The answer is that you cannot do this. If you need to index the view, you must remove the hint.

I would go back and question the use of NOLOCK. This is a hint that is almost always mis-used. It should really be avoided.

Make sure that you, the other developers, and the DBA understand what it will result in. The NOLOCK query hint will not only read uncommitted data, but it will cause the possibility of missing committed data or even reading committed data multiple times. It is important to understand and properly handle these situations.

ps. One Orange Chip Points: 29422 More actions · Answer 4

I completely agree with Michael. Worst part is you'll present inconsistent data to your users. You got to understand the dangerous part of it. Users tend to take help of NOLOCK hint to avoid delays due to possible locks.

Pradeep Singh

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 5

I'd also suggest getting your DBA into the discussion here. They might have a good reason for using NOLOCK, or, more likely, they need a better understanding of what it is and what it does.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ESAT ERKEC SSCertifiable Points: 5661 More actions · Answer 6

In my first times I used to NOLOCK hint but it reads uncommited transactions and it is problem when you want to take stock amount or sales amount but in your table there is a lot of transactions you can use because you don t wait the transactions. But I prefer to use READPAST only read commited transactions

ESAT ERKEÇ