**You as a DBA have a challenge to provide a descriptive answer about each of the above operation (hash-join/scan/seek) and feedback which operation will be fastest (why) and will be slowest (why) with valid reasons. You are at liberty to define an inde...

**You can process XML documents in SQL Server 2005 or 2008 either using OPENXML() or using XQuery. Both can be used to shred an XML document into a result set. Each of them have specific strengths and weaknesses. what is your favorite XML processing fun...

**Which is best suited for updating data from subscriber (bi-directional) and why? Transactional Replication with updatable subscription or Merge replication?** In short, Transactional Replication sends transactional changes from Publisher to Subscri...

**"What is the maximum amount of memory that the following SQL Server configuration could use?** **Microsoft SQL Server 2008 (SP1) - 10.0.2757.0 (INTEL X86) Jan 8 2010 19:55:08 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windo...

**How can scalar functions cause performance problems when used in the select list, join expression, or where clause?** A scalar function is any function that operates on a single value and returns a single value. Many are provided within SQL Server...

**There is a need to design a table structure with following requirements.** - There should be sequence number and it should not have gaps. - We should get the system datetime for each sequence number which they were inserted. **What is the tabl...

**Why is there a performance gap between AdHoc and Parameterized queries?** In simplistic terms, we would often see differences in performance between ad hoc and parameterized queries due to the ability of parameterized queries to reuse query plans. ...

**A common practice to move or transfer database files to change their location or to send it to another team dev per example is to shutdown the SQL Server instance. But this practice is not very secure and in many few cases it generates a problem to re...

**The example given below has two queries, one using UNION and the other using UNION ALL. Both queries mis-spells "UNION" AS "UNOIN". However, SQL Server generates an error only for the second query** When the query optimizer parses a query, or batch...

**My company uses SQL Server Reporting Services to send daily sales reports to all managers. I want to change the report that gets sent to each manager to only include their territory data, but I am told I cannot do this in Standard Edition. What can I...