Glitch in SMO causes an error when you attempt to create an FTS Catalog/Table Population Schedule under very specific circumstances

If you connect from SQL Server Management Studio (SSMS) to an instance of SQL Server, whether it is named or default, and rather than using the proper machine name (or machine name followed by the instance name, separated by the backslash) you use either an alias (created using SQL Server Configuration Manager, through the corresponding…


Reducing the number of queue readers via MAX_QUEUE_READERS parameter might take long time to take effect if there are many messages in the queue

This is another issue that was kindly exposed by our colleague, John Huang, from Canada, through the MCM community distribution list. Once an activated stored procedure finishes its execution, Service Broker runtime code doesn’t verify whether the maximum number of queue readers has been exceeded. Therefore, when the number of queue readers is reduced via…


Duration of schema locks for certain DML operations might not work as expected

John Huang, a colleague from the MCM community, recently exposed to the rest of us a weird case which he decided to present as an “Interesting blocking issue”. I don’t know the exact details of what he was trying to implement, but basically, he had a main table (tBase in his simplified repro) in which…


Expected behavior of INSERT into views with Instead Of Insert Triggers

The INSTEAD OF INSERT Triggers topic in the documentation describes the following: An INSERT statement that is referencing a view that has an INSTEAD OF INSERT trigger must supply values for every view column that does not allow nulls. This includes view columns that reference columns in the base table for which input values cannot…


What’s in Enterprise only? (View Substitution Implicitly Allowed)

As with any index, SQL Server can decide to use an indexed view (materialized views) in its query plan only if the query optimizer determines it is beneficial to do so. Indexed views can be created in any edition of SQL Server. But only in the Enterprise Edition, the query optimizer automatically considers the indexed…


What’s in Enterprise only? (honor Lazy Schema Validation option in a Linked Server)

Together with the definition of each linked server goes a series of options that control different behavioral aspects of the way the linking server interacts with the linked one. Among those options there us one called “lazy schema validation” (aka deferred schema check or delayed schema check) whose default value is false. When it is…


What’s in Enterprise only? (Updatable Distributed Partitioned Views)

SQL Server can use read-only distributed partitioned views as a scale out mechanism, but any attempt to update any of these heterogeneous objects will fail with error 4451 (Views referencing tables on multiple servers are not updatable in the edition of this SQL Server instance ‘%.*ls’. See books online for more details on feature support…