SQL Server

How to group based on multiple columns?
example : number of orders each employee has taken for customers with CustomerIDs between A and AO
SELECT CustomerID, EmployeeID, COUNT(*) FROM Orders WHERE CustomerID BETWEEN 'A' AND 'AO' GROUP BY CustomerID, EmployeeID

What happens if we use aggregate functions with out using group by?
Aggregates the entire result -- only one result is returned

Difference between these 2 queries:
SELECT COUNT(*) FROM Customers   -- gives total row count
SELECT COUNT(Fax) FROM Customers    -- ignores null values for Fax column

Difference between coalsce() and isnull
Select Avg(salary) from Employee  ---  What is the result of this query if some rows contains null for salary?
Difference between where and having clause
Discuss for xml, for xml raw, for xml auto
What is identity column? How to set the start value and increment value?
Example of insert into using select statement
Compare Delete and truncate statements
Is it possible to delete rows from multiple tables using delete statement?
Explain inner join, left, right and full outer joins
Explain null value comparsion when query uses join?
Output of this query:
IF (NULL= NULL)
PRINT 'It Does'
ELSE
PRINT 'It Doesn''t'

Explain this query:
SELECT DISTINCT c.CustomerID, c.CompanyName FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID

Union vs Union All
Union vs Inner Join

Does foreign key allows null values? Can we have foreign key constaint referring to a unique key in parent table?
Do we have cascade update / cascade delete in SQL Server? Where is it specified? In child tables only
Example of self referencing columns  like - employee having manager

Primary key vs unique key (alternate key) vs rowguid column vs identity column
How many null values a unique constraint column allows? - only one
Default values  -- they are applied only for insert -- not applicable for update / delete
Disabling constraints -- how to disable a constraint? Is it possible to disable primary or unique constraint?
How to add a constraint where data exists, and existing data should not be enforced by constraint ? Use NoCheck option
How to create a rule?
rule vs constraint  -- rule is exist to provide backward compatibility only
constraint vs default vs trigger
join vs sub query
sub query and correlated sub query
Use of exists, any, some, all, not exists operators
Exists vs IN  -- when writing a sub query?
Whether IN returns duplicate rows?
Exists Vs inner join -
CAST vs CONVERT
sub query vs join  and correlated query vs join?
explain Normalization
Why de normalizatin is requied?
How to enforce one to one relationship in SQL Server? It is not possible as such ( you can achieve this by taking care of insert thru stored procedures etc.)
Extent and data page
How many rows a data page can contain?
What is a page split?
Explain B-Trees and how SQL Server finds records in tables?
What if the clustered index is not unique?
Explain SysIndexes? What it contains and  in which database is it stored?
Covered Queries - Index include option
What are xml indexes? (new in sql server 2005)
Usage of DBCC -- Database Consistency Checker
DATEADD function
View can have triggers? What type?
Can views have relationships?
How to get last error, last inserted identity column value? No. of rows returned? -- @@ERROR,  @@IDENTITY, @@ROWCOUNT
Explain SQL CMD?
How to execute dynamic sql ? Exec or Execute
Stored Procedures Vs User Defined Functions?
Give an example of using CASE
How to handle erros? How to raise an error manually?
RAISEERROR and sp_addmessage
Describe Extended Stored Procedures?
What are lockable resources? Database, table, extent, page, row
Locks - shared , exclusive locks, Update Locks
Discuss different isolation levels:  READ COMMITTED (the default), READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE
What is deadlock? what is your aprroach to avoid deadlocks?
What is trigger? How many types of triggers are there? Explain in detail
Instead Of ,  For / After triggers
How many instead of triggers a table can have and how many after triggers a table can have? Explain
Is it possible for an instead of insert trigger to have insert statement?
Use of inserted and deleted tables in  after triggers
When a transaction is committed? Before firing after triggers or after firing 'after triggers'?   --- after firing 'after triggers'
Trigger timing and check constraint checking order --   check constraint are validated only after instead of triggers
Explain Recursive triggers?
Specifying trigger orders
How to find out whether a column is updated or not? -- UPDATE() Function
For XML RAW, AUTO , EXPLICIT
OpenXML