SQL Puzzles - Interesting Ones

Question#1: When a composite index is created on a table. Lets see the scenarios of Select clauses with the columns and see whether index is being used
Table TestTable
Columns - A,B,C,D
Index - IX_ABC
Lets see three SELECT queries based on indexed columns and check whether index is being used

STEP 1
IF OBJECT_ID ('TestTable1') IS NOT NULL DROP TABLE TestTable1
CREATE TABLE TestTable1
(
    Number INT IDENTITY(1,1) PRIMARY KEY,
    A VARCHAR(20) NOT NULL,
    B VARCHAR(20) NOT NULL,
    C VARCHAR(20) NOT NULL,
    D VARCHAR(20) NOT NULL
)
STEP 2
DECLARE @I INT
SET @I = 100
WHILE 1 = 1
    BEGIN
       SET @I = @I + 1
       INSERT INTO TestTable1(A,B,C,D)
       VALUES ((CONVERT(CHAR(5),@I)+'A'),(CONVERT(CHAR(5),@I)+'B'),
       (CONVERT(CHAR    (5),@I)+'C'),(CONVERT(CHAR(5),@I)+'D'))
       IF @I=10000
            BREAK;
       END

STEP 3
CREATE INDEX IX_ABC ON TestTable1(A,B,C)

Queries and Index Used
SELECT Number FROM TestTable1 WHERE A = '1000 A' AND B = '1000 B' AND C = '1000 C'
INDEX SEEK

SELECT Number FROM TestTable1 WHERE C = '1000 C'
INDEX SCAN

SELECT Number FROM TestTable1 WHERE C = '1000 C' AND B = '1000 B'
INDEX SCAN

SELECT Number FROM TestTable1 WHERE A = '1000 A' AND C = '1000 C'
INDEX SEEK

SELECT Number FROM TestTable1 WHERE A = '1000 A' AND B = '1000 B'
INDEX SEEK

SELECT Number FROM TestTable1 WHERE A = '1000 A'
INDEX SEEK

Hint: As long as your search arguement has the index columns and the same order A,B,C or A,B or A it will use the Index IX_ABC
Question#2 - What is the output for below query
Query
SELECT top 10 'abc' from sys.objects
WHERE 1>2
HAVING 1<2;
Answer abc
Reference - Please refer link

Query
SELECT 'No rows'
WHERE 1>2
HAVING 1<2;
Answer No Rows

Question#3 - What is output for below query
use tempdb
CREATE TABLE A(ID INT IDENTITY(1,1), Name VARCHAR(10))
CREATE TABLE B(ID INT IDENTITY(1,1), Name VARCHAR(10))

INSERT INTO A(Name)
VALUES('1')
INSERT INTO B(Name)
VALUES('1'),('_1'),('%1'),('[%]1'),('[_]1')

Select * from A JOIN B
ON 'x'+A.Name LIKE B.Name

Have fun...Happy Reading....