Allow Intellisense to use Read Uncommitted Isolation levels - by akelly

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


6
0
Sign in
to vote
ID 776354 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 1/11/2013 10:43:06 AM
Access Restriction Public

Description

Currenlty in SQL Server Management Studio 2012 the queries used by Intelisense get blocked under certain conditions such as when another user is running a SELECT INTO query like the one listed below. This is due to the locking on the meta data tables. This results in many hung sessions for no real benefit.

SELECT
tr.name AS [Name],
tr.object_id AS [ID],
CASE WHEN tr.type = N'TR' THEN 1 WHEN tr.type = N'TA' THEN 2 ELSE 1 END AS [ImplementationType],
CAST(
                tr.is_ms_shipped
            AS bit) AS [IsSystemObject],
CAST(CASE WHEN ISNULL(smtr.definition, ssmtr.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted]
FROM
sys.triggers AS tr
LEFT OUTER JOIN sys.sql_modules AS smtr ON smtr.object_id = tr.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmtr ON ssmtr.object_id = tr.object_id
WHERE
(tr.parent_class = 0)
ORDER BY
[Name] ASC
Sign in to post a comment.
Posted by Microsoft on 1/25/2013 at 12:25 PM
Thanks for the input however we are not making investments in this area.
Posted by AaronBertrand on 1/11/2013 at 11:21 AM
Agreed, or snapshot if it is determined the target database has it enabled. But with read uncommitted - since 99.99% of the time I am not looking up metadata for objects that are being changed at the exact same time - I'm perfectly ok with not taking locks to get the list of column names. And even if I get the old column name right now while it is being changed underneath me, that's something I'll find out at runtime, and not worth waiting for IMHO.