This question attempts to verify your database design skills. Which of the following are correct for "Foreign Keys"?

One of the greatest features of PowerShell is the Pipeline, where results from one cmdlet get passed to the next operation via the ‘|’ operator. What’s the key difference between Unix shell scripting languages (csh, ksh, bash, etc.) and PowerShell in how the pipeline works?

Where would you use a stored procedure resolver over a business logic resolver and vice versa?

Explain how should connections be handled in SQL Server Compact

Explain what happens when a Database Mirror Server goes offline and comes back online after some time.

Sparse columns are a feature that was introduced with SQL Server 2008. Designating a column as SPARSE saves you space when your column is sparsely populated (NULLs take up no space), but any populated values in the column take up more space. What uses have you found for sparse columns in your databases?

A server side trace using TSQL tuning template produces that 3 stored procedures are used during those reports execution, a SHOWPLAN event activity reveals that there are multiple scan operations such as index scan, hash-join, index seek and table scan on the above 3 tables. Your job is to provide a descriptive answer about each of the above operation (hah-join/scan/seek) and feedback which operation will be fastest (why) and will be slowest (why) with valid reasons.

what is your favorite XML processing function? When will you use OPENXML() and when XQuery? Why do you prefer one over the other?

Which is best suited for updating data from subscriber (bi-directional) and why? Transactional Replication with updatable subscription or Merge replication?

What is the maximum amount of memory that the SQL Server instance could use in the specified server configuration?