En uno de los foros encontre la siguiente pregunta (la pregunta original habia sido "traducionada" a un ejemplo tecnico y yo le traduzco a un ejemplo mas comprensible): dado el codigo de un objeto en la base de datos, hay que recuperar todos los objetos del mismo esquema y del mismo tipo de la tabla de sistema sys.objects.
Por ejemplo- si 1234 es el numero de una tabla del esquema 'sales' – hay que recuperar los detalles de todas las tablas del esquema 'sales'.
Primero que nada crearemos copia de sys.objects – asi que nuestros experimentros habran hecho con una tabla (definida por el usuario) en lugar de objetos del sistema:

Use tempdb;

Go

If Object_ID('T_Objects','U') Is Not Null Drop Table T_Objects;

Select  *

Into    T_Objects

From    AdventureWorks.sys.objects;

Select * From T_Objects;

La solución que el propuso y que coincidió con la manera como el problema había introducido incluyó dos accesos al la tabla en la cláusula Where y una en la From, asi que resumió en tres accesos.
Alternativamente se puede acceder dos veces a la tabla con el operador Exists que comprueba las ambas condiciones simultáneamente y por lo tanto reducirá el numero de los accesos.
Finalmente- las funciones de ventana nos habilita acceder solamente una vez para encontrar las deseadas filas: dividiremos las filas en grupos (=ventanas) segun los numeros del objeto y del esquema, y filtraremos asi que quedaran solas las filas del grupo del objeto dado.

Select  *

From    T_Objects

Where   schema_id=(Select schema_id From T_Objects Where object_id=18099105)

        And type_desc=(Select type_desc From T_Objects Where object_id=18099105);

Select  *

From    T_Objects T1

Where   Exists (Select  1

                From    T_Objects T2

                Where   T2.object_id=18099105

                        And T2.schema_id=T1.schema_id

                        And T2.type_desc=T1.type_desc);

With T As

(Select Max(Case When Object_id=18099105 Then 1 End) Over(Partition By schema_id,type_desc) MaxID,

        *

From    T_Objects)

Select  *

From    T

Where   MaxID=1;

Descargar
Aparentemente la primera recuperación que accede tres veces a la misma tabla es la mas eficaz (se puede descargar el archivo del plan de ejecución pulsando la impresión de la pantalla), porque el uso del Exists en la segunda implica la utilización de Hash Match muy ineficaz (en esta fase todavia no usamos indices), y en la tercera – hay que ordernar los datos en operación muy cara (en terminos de uso de recursos del servidor).
Hay dos opciones como indexar la tabla: empezar con object_id que sirve para filterar la recuperación secundaria (Sub Select), o con schema_id & type_desc que sirven para filterar la recuperación primaria.

Primer intento:

If (Select Count(1) From sys.indexes Where name='IDX_T')>0 Drop Index T_Objects.IDX_T;

Create Clustered Index IDX_T On T_Objects(object_id,schema_id,type_desc);

Descargar
La primera recuperación es la mas eficaz en este caso, su costo absoluto resuma en 0.0220846, los dos accesos en el Where son Index Seek eficaz, y solamente el acceso en el Select obliga Table Scan complete.
La segunda recuperación es menos eficaz- aunque hay solo un Index Seek y un Table Scan – entre los hay Hash Match que es peor que el Nested Loops de la primera.
La tercera recuperación- es la peor aqui y totalmente, especialment porque el índice no la apoya, y hay que dividir primero las filas en grupos; y por tanto hay que ordenar las filas en operacion muy costosa.

Segundo intento:

If (Select Count(1) From sys.indexes Where name='IDX_T')>0 Drop Index T_Objects.IDX_T;

Create Clustered Index IDX_T On T_Objects(schema_id,type_desc,object_id);

Descargar
La primera recuperación es la peor en este caso y ejecuta dos Table Scan (en otras palabras- el índice es inutil) y un Index Seek.
La segunda recuperación es la mejor aqui y en total, ejecuta solamente un Table Scan para encontrar la fila en la recuperación secundaria, e Index Seek por las otras filas en la primaria.
La tercera recuperación es mejor que la primera y tambien de la tercera con el primer índice, principalmente porque el índice le ahorra ordenar antes la division en grupos.

Comparemos los costos absolutos (Estimated Subtree Cost) de las distintas recuperaciones:

Se puede ver last res mejores recuperaciones segun su orden, y las conclusions:
1. A distintas recuperaciones se debe adecuar distintos indices, y viceversa.
2. Las funciones de ventana son muy utiles generalmente, pero en este caso es menos util a pesar la solución original.
3. Estos resultados son apropriados a esta tabla especifica, y en distintas circunstancias los resultados pueden ser distintos.