0

Is it permitted to alter view with nolock? If so, how should I use it if I want to? 

ALTER VIEW dbo.xx_view 
AS
    SELECT * 
    FROM dbo.yy

My current issue is that I have to wait for others finish using dbo.xx_view, then the view can be altered. Is there a way that I can alter the view forcibly, even when others are making queries on it.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
dragon.warrior.nyc
  • 41
  • 5
  • 3
    NOLOCK has nothing to do with "locking" the view definition. It's "query hint" that happens to be one of the more pervasive and persistent bad habits in the SQL world. Here are a couple articles to get you up to speed... https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/ ... https://sqlstudies.com/2015/03/18/why-not-nolock/ – Jason A. Long Aug 26 '17 at 03:18
  • It's also helpful to say that HINTS are actually disabling the optimizer from options. Microsoft painstakingly highlights throughout their doc pages that they are used as a last resort by professional DBAs. [HINTS - Microsoft Docs](https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql) – clifton_h Aug 26 '17 at 06:42
  • Also, understand that unless this is a materialized view, VIEWS are hard-coded **queries**. It makes perfect sense you will not be able to modify them until after a user finishes because that would require invalidating the sql plan the query is using as it is executing. – clifton_h Aug 26 '17 at 06:45

1 Answers1

2

You can specify it just like any other select statement:

ALTER VIEW dbo.xx_view AS
SELECT * FROM dbo.yy WITH (NOLOCK)

Or, you could provide the NOLOCK hint while querying on the view as suggested here:

 SELECT * FROM dbo.xx_view WITH (NOLOCK)

In the latter case, your query inside the view need not provide the NOLOCK hint.

Nisarg Shah
  • 14,151
  • 6
  • 34
  • 55
  • Hi Nisarg, thank you for your reply! I am thinking whether I can alter view dbo.xx_view even when somebody else is using dbo.xx_view. The current issue I have is that I have to wait until they finish using dbo.xx_view, then the view can be altered. I am thinking something more or less as ```ALTER VIEW dbo.xx_view WITH (NOLOCK) AS SELECT * FROM dbo.yy``` which is not working... – dragon.warrior.nyc Aug 26 '17 at 03:33
  • I have never ran into such a problem. Are you getting any errors if you try to alter it? – Nisarg Shah Aug 26 '17 at 03:51
  • No, there is no error. The process is just suspended to wait for another one to complete. – dragon.warrior.nyc Aug 26 '17 at 14:48