There’s a topic in SQL Server’s documentation whose title is Configuring Parallel Index Operations , which mentions that the Database Engine can reduce the degree of parallelism if the leading key column of a non-partitioned index has a limited number...

For those of you who relied on that command, typically used in data recovery learning labs, just wanted to warn you, if you haven’t noticed already, that it has been removed from the product starting with SQL Server 2012 RTM. The supported way to do what...

A colleague from PFE recently asked the following: ****************************** Hi Folks, I’m trying to find out when the Auto Update Stats event is generated assuming Auto Update Stats and Auto Create Stats db options are enabled. As far as I know...

Starting with SQL Server 2012, the query engine folks introduced an extended event, inaccurate_cardinality_estimate, you can use to identify those running queries for which any of its iterators outputs significantly more rows than those estimated by the...

Under normal circumstances, once there is no one referencing any object from a database whose AUTO_CLOSE option is enabled, the database manager would attempt to close it (with the exception of master, tempdb and model system databases). But if you want...

Starting with version 2008, SQL Server introduced a feature called Change Data Capture which provides information about DML changes on a table and a database. Change Data Capture eliminates the need to use expensive techniques such as user triggers, timestamp...

Starting with version 2008, SQL Server solves the problem of protecting data at rest, encrypting databases both on the hard drive and consequently on backup media. To benefit from this out of the box functionality though, you must be running the Enterprise...

This week I was in a customer measuring the effects it would have, in one particular environment where they were using SQL Server 2008 R2 Enterprise, if they would compress their backups. To start with, we ran a few tests using BACKUP DATABASE & BACKUP...

If you require one or more read-only, static views of a database, which is transactionally consistent with the source database at the moment the static view was created, beginning with SQL Server 2005 you can take advantage of Database Snapshots , which...

Starting with version 2008, SQL Server supports both row and page compression for both tables and indexes . Also SQL Server 2008 R2 continues to support, although it has been deprecated, the vardecimal storage format that was introduced with SQL Server...

A recurring question I receive from colleagues at work, or from friends I know from elsewhere is, where to go in Madrid to enjoy a vegan/vegetarian meal either for themselves or to take someone who is paying them a visit. Following is a compiled list...

Each edition of SQL Server supports a specified number of processor sockets multiplied by the number of logical CPUs in each socket. The Enterprise Edition of SQL Server supports up to 8 CPU sockets, while the Standard only supports up to 4. All this...

Notice that when I wrote this repro and captured the screenshots available below, I didn’t think they will be rendered in this blog. Unfortunately, you will have to click on each image to be able to read the descriptions of every relevant chunk of data...

A colleague who is a consultant in Chile, reported a weird scenario he was observing in one customer and for which he couldn’t articulate an explanation. I saw his question posted in one of our internal distribution lists but couldn’t put...

Fuzzy Lookup is a component which, when used as part of a data flow in an SSIS (SQL Server Integration Services) package, enables you to locate matching records in a reference table just like the standard Lookup transformation does, with the added value...

Starting with SQL Server 2008 the product supports the ability to start using or stop using CPUs that are dynamically added or removed to a running system, either physically, logically (by online hardware partitioning), or virtually through a virtualization...

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...

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...

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...

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...

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...

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”...

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...

Starting with SQL Server 2005 you can create, rebuild, or drop indexes while concurrent users read or modify the underlying table or clustered index data and any associated nonclustered indexes during these index operations. This mode of performing index...

Starting with version 2005, SQL Server introduces the concept of partitioned tables and indexes. Partitioning can make large tables and indexes more manageable and scalable. By using partitioning, an operation such as loading data from an OLTP to an OLAP...