Which index to use,if one covers another?

This is from an interview question I received.

A table has column A with two indexes.

indexA(column A) 

indexB(column A + multiple columns)

I think this will depend on how the query is written and how other indexes are defined on the table. to verify that, I did experiments.

0. Preparation

create table test(id int identity,a int, b int, c int)
go
insert into test(a,b,c)
select ROW_NUMBER() over (order by a.object_id),RANK() over (order by a.object_id),DENSE_RANK() over (order by a.object_id)
from sys.columns a cross join sys.columns b
go
create index ix_a on test(a)

create index ix_a_b on test(a,b)
go
dbcc show_statistics (test,ix_a)
dbcc show_statistics (test,ix_a_b)
go

Then check the execution plan of queries under different situations.

1. select * from test
This ends up as table scan as expected.
With clustered PK: clustered index scan because table is now the index.

2. select * from test where id<100
Since no index is created on id yet, so table scan is expected. and it is
With clustered PK: clustered index seek instead of table scnan.

3.select * from test where a<100
Since a is in both where and select list, index seek on ix_a_b is expected. it chooses wider index because the covering column b is in the select list.
With PK: same ,  but of cause now it has a key lookup instead of rid look up
4. select a from test where a<100
    select a from test where a=100
Here has an interesting observation. In this test, the optimizer chooses ix_a_b over ix_a.
With PK: same

In my another testing, when the table is very wide and no other indexes, it chose ix_a over ix_a_b.
But when a clustered PK was created, the optimizer chose ix_a_b over ix_a.

4.1 Update the statistics and recheck the plan.
update statistics test
it still chooses the ix_a_b

5 Comparison when cost is the same

5.1 with covering index, it chooses wider index.
select a from test where a=100
select a from test with(index(ix_a)) where a=100
it chooses ix_a_b over ix_a

5.2 with uncovering index, in chooses which ever created the first.
select a,c from test where a=100
(select a,c from test with(index(ix_a)) where a=100 to check the cost is the same)
drop index test.ix_a
drop index test.ix_a_b
--change the creation order
create index ix_a_b on test(a,b)
create index ix_a on test(a)
select a,c from test where a=100

6. select a,b from test where a=100
As expected, it chooses ix_a_b since this provides covering on select list.
 With PK: same

Next, Let's experiment how the indexes are used in joining.

7.select a.a from test a join test b on a.a=b.a
The optimizer is smart enough to choose ix_a for both tables

With PK: same

8.select a.a,a.b from test a join test b on a.a=b.a
The optimizer is smart enough to choose ix_a_b for a and ix_a for b.
 With PK: same

9 select a.a,a.b,a.c from test a join test b on a.a=b.a
This time, table scan for a, ix_a for b. As Expected.

With PK:
table scan becomes index scan
Next, let's take a look at how other indexes effect the optimizer's decisions.

10. build PK on id
Alter table test add constraint pk_test_id primary key clustered (id)

The go back to check the execution plans for the situations having been discussed.

Conclusions:



The optimizer choose indexes based on how it caclucate the cost. it calculate the cost
depending on how the query is written, how wide the table is, how wide the index is and
what other indexes/key are defined on the table. Basically, it depends on how the cost is
calculated and a cost is from several aspects such as disk IO and CPU usage.

In a wide table with huge number of records, if these are the only two indexes available, for
the query like “select a from test where a=100”, the optimizer will use ix_a. For a query like
“select a,other columns in ix_a_b from test where a=100”, the optimizer will use ix_a_b since
it provides more cover of the columns in the select list.

In a narrow table, if the cost is similiar, the optimizer chooses wider index over narrower index.
E.G. choose ix_a_b over ix_a on “select a from test where a=100”.

Table Valued Function and Inline Table Valued Function

Inline user defined table valued function is a subset of user defined table valued function. It can be used to achieve the functionality of parameterized views.

Simply, inline table valued function RETURNS TABLE and usually contains only one SELECT statement. Table valued function returns a table data type,   and it can contain additional statements that allow more powerful logic than is possible in views.

Example 1: Inline table valued function

CREATE FUNCTION Sales.ufn_CustomerNamesInRegion
                 ( @Region nvarchar(50) )
RETURNS table
AS
RETURN (
        SELECT DISTINCT s.Name AS Store, a.City
        FROM Sales.Store AS s
        INNER JOIN Person.BusinessEntityAddress AS bea 
            ON bea.BusinessEntityID = s.BusinessEntityID 
        INNER JOIN Person.Address AS a 
            ON a.AddressID = bea.AddressID
        INNER JOIN Person.StateProvince AS sp 
            ON sp.StateProvinceID = a.StateProvinceID
        WHERE sp.Name = @Region
       );
GO

Example 2: Table valued function

In a table-valued user-defined function:

   1.The RETURNS clause defines a local return variable name for the table returned by the function. The RETURNS clause also defines the format of the table. The scope of the local return variable name is local within the function.

   2. The Transact-SQL statements in the function body build and insert rows into the return variable defined by the RETURNS clause.

    3.When a RETURN statement is executed, the rows inserted into the variable are returned as the tabular output of the function. The RETURN statement cannot have an argument.

    4. No Transact-SQL statements in a table-valued function can return a result set directly to a user. The only information the function can return to the user is the table returned by the function.

Transactions and Concurrency control in SQL Server

Mostly from Guide to Migrating Oracle to SQL Server. Credit to MS.

A transaction is closed by COMMIT, ROLLBACK, how is that started?

Choosing a Transaction Management Model

In Oracle, a transaction automatically starts when an insert, update, or delete operation is performed. An application must issue a COMMIT command to save changes to the database. If a COMMIT is not performed, all changes are rolled back or undone automatically.This is known as implicit transaction control.

By default, SQL Server 2005 automatically performs a COMMIT statement after every insert, update, or delete operation. Because the data is automatically saved, you cannot roll back any changes.This is called autocommit transaction control.

You can start transactions in SQL Server 2005 as autocommit, implicit, or explicit transactions. Autocommit is the default behavior; you can use implicit or explicit transaction modes to change the default behavior.

Autocommit Transactions

Autocommit transactions are the default mode for SQL Server 2005. Each individual Transact-SQL statement is committed when it completes. You do not have to specify any statements to control transactions.

Implicit Transactions

As in Oracle, an implicit transaction starts whenever an INSERT, UPDATE, DELETE, or other data manipulating function is performed. In SQL Server, to allow implicit transactions, use the SET IMPLICIT_TRANSACTIONS ON statement.

If this option is ON and there are no outstanding transactions, every SQL statement automatically starts a transaction. If there is an open transaction, no new transaction will start. The user must explicitly commit the open transaction with the COMMIT TRANSACTION statement for the changes to take effect and for all locks to be released.

Oracle by default is implicit transaction.

Explicit Transactions

An explicit transaction is a grouping of SQL statements surrounded by BEGIN TRAN/WORK and COMMIT or ROLLBACK commands. 

Therefore, for the complete emulation of the Oracle transaction behavior, use a SET IMPLICIT_TRANSACTIONS ON statement.

Choosing a Concurrency Model

This is regarding to how the database engine handles the situation when multiple users update same resource at same time. There are two models for updating data in a database:Pessimistic and Optimistic.

Isolation levels are described in terms of which concurrency side-effects, such as dirty reads or phantom reads, are allowed.

Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions.

Lower level of isolation will boost concurrency, but with a harm of data integrity/consistency. Higher level of isolation have better guaranty on data consistence but with a cost of resource overhead and performance reduction. Choosing the appropriate isolation level depends on balancing the data integrity requirements of the application against the overhead of each isolation level.

(Maybe we should not category the concurrency to be pessimistic and optimistic because he definition to them are always ambiguous. They are more meaningful when they are used to describe cursor behaviors. I might be wrong on the following definitions on pessimistic and optimistic definitions.)

 Pessimistic concurrency involves locking the data at the database when you read it so that other user can't modify them during your reading process. You exclusively lock the database record and don't allow anyone to touch it until you are done modifying and saving it back to the database. You have 100 percent assurance that nobody will modify the record while you have it checked out. Another person must wait until you have made your changes(SQL Server exclusively locks the data when it updates them no matter which isolation level that is within.). 

Pessimistic concurrency complies with ANSI-standard isolation levels as defined in the SQL-99 standard. Microsoft SQL Server 2005 has three pessimistic isolation levels:

·         READ COMMITTED

·         REPEATABLE READ

·         SERIALIZABLE

Optimistic concurrency means that you read the database record but don't lock it. Anyone can read and modify the record at any time, so the record might be modified by someone else before you modify and save it. If data is modified before you save it, a collision occurs. Optimistic concurrency is based on retaining a view of the data as it is at the start of a transaction. SQL Server has three optimistic isolation levels, which does not lock data while reading:
(Read operations require only SCH-S table level locks and no page or row locks.)
. READ UNCOMMITTED

. READ_COMMITTED_SNAPSHOT
. SNAPSHOT


This model is embodied in Oracle. The transaction isolation level that implements an optimistic form of database concurrency is called a row versioning-based isolation level.

Since SQL Server 2005 has completely controllable isolation-level models, you can choose the most appropriate isolation level. To control a row-versioning isolation level, use the SET TRANSACTION ISOLATION LEVEL command. SNAPSHOT is the isolation level that is similar to Oracle and does optimistic escalations.

Simulating Oracle Autonomous Transactions

This section describes how SSMA Oracle 3.0 handles autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION). These autonomous transactions do not have direct equivalents in Microsoft SQL Server 2005.

When you define a PL/SQL block (anonymous block, procedure, function, packaged procedure, packaged function, database trigger) as an autonomous transaction, you isolate the DML in that block from the caller's transaction context. The block becomes an independent transaction started by another transaction, referred to as the main transaction.

To mark a PL/SQL block as an autonomous transaction, you simply include the following statement in your declaration section:

PRAGMA AUTONOMOUS_TRANSACTION;

SQL Server 2005 does not support autonomous transactions. The only way to isolate a Transact-SQL block from a transaction context is to open a new connection.

To convert a procedure, function, or trigger with an AUTONOMOUS_TRANSACTION flag, you split it into two objects. The first object is a stored procedure containing the body of the converted object. It looks like it was converted without a PRAGMA AUTONOMOUS_TRANSACTION flag and is implemented as a stored procedure. The second object is a wrapper that opens a new connection where it invokes the first object. It is implemented via an original object type (procedure, function, or trigger).