1

As part of processing an Invoice, our 3rd party ERP creates, queries, then drops a view on one of the ERP Database tables.

We have separate concerns about this, but I'm genuinely curious: do either of the CREATE or DROP statements require a Schema Modification Lock on the Database or the tables that are involved in the view?

If not, are there any other lock types involved in the process that we should be concerned about?

2
  • 1
    Performing DDL as part of everyday processing is a "bad idea" (TM). This smells "off". Not sure about the ins and outs of SQL Server - contact your vendor and get them to stop this!
    – Vérace
    Dec 15, 2017 at 1:31
  • @Vérace there's plenty of stuff they do that smells off... unfortunately, even if we asked them to fix it we wouldn't get any benefits without updating and that's not without risks either Dec 15, 2017 at 3:12

1 Answer 1

6

This is easy enough to test on your own with a dummy table. It doesn't have to be anything fancy.

CREATE TABLE dbo.t1 (id INT)
INSERT dbo.t1 ( id )
VALUES ( 1 )

After the table is created, start a blocking transaction.

BEGIN TRAN
UPDATE dbo.t1 SET id = 2

--ROLLBACK 

Over in another SSMS window, create a view.

CREATE VIEW dbo.YourMom
--WITH SCHEMABINDING /*this makes no difference*/
AS 
SELECT *
FROM dbo.t1

This completes successfully, immediately, but trying to query the view or table will be blocked.

SELECT *
FROM dbo.YourMom

SELECT *
FROM dbo.t1

It's worth noting that views don't have any special properties. They're just a query.

I'm not sure why your vendor creates and drops them as part of a process, unless the definition needs to be dynamically generated because you have custom tables or something.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.