Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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.
