It's time for everyone's favorite game: What should the remote query be?

Get ready. Go!

NOTES
1. These can be solved by inspection. No knowledge of DDL, indexes or
statistics is required.

2. You must login to this site to post comments; makes adding comments difficult.


Question#1: Querying a linked server
SELECT *
FROM servertest.CMSArchiveTraining.dbo.Transactions_90
WHERE TransactionGUID = '9B2D0E34-F91C-45E8-A904-1BDA0E72802B'

Answer#1:
QUERY:(
   SELECT *
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
   WHERE TransactionGUID = '9B2D0E34-F91C-45E8-A904-1BDA0E72802B'
)



Question#2: Querying a linked server through a derived table
SELECT *
FROM (SELECT * FROM servertest.CMSArchiveTraining.dbo.Transactions_90)
CMSArchiveTransactions
WHERE TransactionGUID = '9B2D0E34-F91C-45E8-A904-1BDA0E72802B'

Answer#2:
QUERY:(
   SELECT *
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
   WHERE TransactionGUID = '9B2D0E34-F91C-45E8-A904-1BDA0E72802B'
)



Question#3: Querying a linked server through a view
CREATE VIEW CMSArchiveTransactions AS
SELECT *
FROM servertest.CMSArchiveTraining.dbo.Transactions_90

SELECT *
FROM CMSArchiveTransactions
WHERE TransactionGUID = '9B2D0E34-F91C-45E8-A904-1BDA0E72802B'

Answer#3:
QUERY:(
   SELECT *
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
   WHERE TransactionGUID = '9B2D0E34-F91C-45E8-A904-1BDA0E72802B'
)



Question#4: Joining to a linked server table
SELECT CMSArchiveTransactions.*
FROM CMSArchiveTransactions t
   INNER JOIN Customers c
   ON t.CustomerID = c.CustomerID

Answer#4:
QUERY:(
   SELECT *
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
)



Question#5: Joining to a linked server with join criteria
SELECT CMSArchiveTransactions.*
FROM CMSArchiveTransactions t
   INNER JOIN Customers c
   ON t.CustomerID = c.CustomerID
WHERE c.CustomerID = 4463

Answer#5:
QUERY:(
   SELECT *
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
   WHERE CustomerID = 4463
)



Question#6: Joining to a linked server with join criteria
SELECT CMSArchiveTransactions.*
FROM CMSArchiveTransactions t
   INNER JOIN Customers c
   ON t.CustomerID = c.CustomerID
WHERE t.TransactionAmount > 1000

Answer#6:
QUERY:(
   SELECT *
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
   WHERE TransactionAmount > 1000
)



Question#7: Joining to a linked server with join criteria
SELECT CMSArchiveTransactions.*
FROM CMSArchiveTransactions t
   INNER JOIN Customers c
   ON t.CustomerID = c.CustomerID
WHERE t.TransactionAmount > 1000
AND c.CustomerID = 4463

Answer#7:
QUERY:(
   SELECT *
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
   WHERE TransactionAmount > 1000
   AND CustomerID = 4463
)



Question#8 Joining to a linked server with join criteria
SELECT c.CustomerName, t.TransactionAmount
FROM CMSArchiveTransactions t
   INNER JOIN Customers c
   ON t.CustomerID = c.CustomerID
WHERE c.CustomerID = 4463

Answer#8:
QUERY:(
   SELECT CustomerID, TransactionAmount
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
   WHERE CustomerID = 4463
)



Question#9: Joining to a linked server with join criteria
SELECT c.CustomerName, t.TransactionAmount
FROM CMSArchiveTransactions t
   INNER JOIN Customers c
   ON t.CustomerID = c.CustomerID
WHERE c.CustomerID >= 4000
AND c.CustomerID <= 5000

Answer#9:
QUERY:(
   SELECT CustomerID, TransactionAmount
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
   WHERE CustomerID >= 4000
   AND CustomerID <= 5000
)



Question#10: Joining to a linked server with aggregates
ELECT c.CustomerID, c.CustomerName,
LastCustomerTransactions.LastTransactionDate
FROM Customers c
   LEFT JOIN (
       SELECT CustomerID, MAX(TransactionDate) AS LastTransactionDate
       FROM CMSArchiveTransactions
       GROUP BY CustomerID) AS LastCustomerTransactions
   ON c.CustomerID = LastCustomerTransactions.CustomerID

Answer#10:
QUERY:(
   SELECT CustomerID, MAX(TransactionDate) Col1042
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
)


Question#11 Joining to a linked server with aggregates and join criteria
SELECT c.CustomerID, c.CustomerName,
LastCustomerTransactions.LastTransactionDate
FROM Customers c
   LEFT JOIN (
       SELECT CustomerID, MAX(TransactionDate) AS LastTransactionDate
       FROM CMSArchiveTransactions
       GROUP BY CustomerID) AS LastCustomerTransactions
   ON c.CustomerID = LastCustomerTransactions.CustomerID
WHERE c.CustomerID = 4463

Answer#11:
QUERY:(
   SELECT CustomerID, MAX(TransactionDate) Col1042
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
   WHERE CustomerID = 4463
)



Question#12: Joining to a linked server with aggregates
SELECT c.CustomerID, c.CustomerName,
LastCustomerTransactions.LastTransactionDate
FROM Customers c
   LEFT JOIN (
       SELECT CustomerID, MAX(TransactionDate) AS LastTransactionDate
       FROM CMSArchiveTransactions
       GROUP BY CustomerID) AS LastCustomerTransactions
   ON c.CustomerID = LastCustomerTransactions.CustomerID
WHERE c.CustomerName = 'Ian Boyd'

Answer#12:
QUERY:(
   SELECT CustomerID, MAX(TransactionDate) Col1042
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
)



Question#13: Joining to a linked server with aggregates
SELECT c.CustomerName, SUM(CMSArchiveTransactions.TransactionAmount)
FROM CMSArchiveTransactions t
   INNER JOIN Customers c
   ON t.CustomerID = c.CustomerID
GROUP BY c.CustomerName

Answer#13:
QUERY:(
   SELECT CustomerID, SUM(TransactionAmount) Col1042
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
   GROUP BY CustomerID
)



Question#14: Joining to a linked server with aggregates
SELECT c.CustomerName, SUM(CMSArchiveTransactions.TransactionAmount)
FROM CMSArchiveTransactions t
   INNER JOIN Customers c
   ON t.CustomerID = c.CustomerID
WHERE CustomerName = 'Ian Boyd'
GROUP BY c.CustomerName

Answer#14:
QUERY:(
   SELECT CustomerID, SUM(TransactionAmount) Col1042
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
   GROUP BY CustomerID
)



Question#15: Joining to a linked server with aggregates
SELECT c.CustomerName, SUM(CMSArchiveTransactions.TransactionAmount)
FROM CMSArchiveTransactions t
   INNER JOIN Customers c
   ON t.CustomerID = c.CustomerID
WHERE c.CustomerName LIKE '%ian%'
GROUP BY c.CustomerName

Answer#15:
QUERY:(
   SELECT CustomerID, SUM(TransactionAmount) Col1042
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
   GROUP BY CustomerID
)



Question#16: Joining to a linked server with aggregates
SELECT c.CustomerName, SUM(CMSArchiveTransactions.TransactionAmount)
FROM CMSArchiveTransactions t
   INNER JOIN Customers c
   ON t.CustomerID = c.CustomerID
WHERE CustomerName LIKE '%ian%'
AND c.CustomerID >= 4000
AND c.CustomerID <= 5000
GROUP BY c.CustomerName

Answer#16:
QUERY:(
   SELECT CustomerID, SUM(TransactionAmount) Col1042
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
   WHERE CustomerID >= 4000
   AND CustomerID <= 5000
   GROUP BY CustomerID
)



Question#17: Right join to linked table
SELECT *
FROM Customers c
   RIGHT OUTER JOIN CMSArchiveTransactions t
   ON c.CustomerID = t.CustomerID

Answer#17:
QUERY: (
   SELECT *
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
)

Question#18: Right join to linked table with left table filtering
SELECT *
FROM Customers c
   RIGHT OUTER JOIN CMSArchiveTransactions t
   ON c.CustomerID = t.CustomerID
WHERE c.CustomerID = 4463

Answer#18:
QUERY: (
   SELECT *
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
)

Question#19: Right join to linked table with right table filtering
SELECT *
FROM Customers c
   RIGHT OUTER JOIN CMSArchiveTransactions t
   ON c.CustomerID = t.CustomerID
WHERE t.TransactionAmount >= 1000

Answer#19:
QUERY: (
   SELECT *
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
   WHERE TransactionAmount >= 1000
)


Question#20: Right join to linked table with both table filtering
SELECT *
FROM Customers c
   RIGHT OUTER JOIN CMSArchiveTransactions t
   ON c.CustomerID = t.CustomerID
WHERE c.CustomerID = 4463
AND t.TransactionAmount >= 1000

Answer#20:
QUERY: (
   SELECT *
   FROM "CMSArchiveTraining"."dbo"."Transactions_90"
   WHERE TransactionAmount >= 1000
)

Extra credit questions.


Question#21:
CREATE VIEW AllTransactions AS
   SELECT t.*, 'local' AS SourceTable
   FROM LocalTransactions t

   UNION ALL    

   SELECT t.*, 'remote' AS SourceTable
   FROM servertest.CMSArchiveTraining.dbo.Transactions_90

SELECT *
FROM AllTransactions
WHERE Source = 'local'

Answer#21
no remote query


Question#22
SELECT *
FROM AllTransactions
WHERE Source <> 'remote'

Answer#22
no remote query


Question#23
SELECT MAX(TranasctionDate)
FROM AllTransactions

Answer#23
QUERY(
   SELECT MAX(TransactionDate) Col1402
   FROM CMSArchiveTraining.dbo.Transactions_90)

Bonus points:
   AGGREGATE
       CONCAT
           LOCALQUERY(
               SELECT MAX(TransactionDate) Col1402
               FROM LocalTransactions)
           REMOTEQUERY(
               SELECT MAX(TransactionDate) Col1402
               FROM CMSArchiveTraining.dbo.Transactions_90)


Question#24
SELECT c.CustomerID, MAX(TransactionDate)
FROM Customers c
   INNER JOIN AllTransactions t
GROUP BY c.CustomerID

Answer#24
QUERY(
   SELECT CustomerID, MAX(TranasctionDate)
   FROM CMSArchiveTraining.dbo.Transactions_90
   GROUP BY CustomerID)

Bonus points:

INNER JOIN
   Customers
   AGGREGATE
       CONCAT
           REMOTEQUERY(
               SELECT CustomerID, MAX(TranasctionDate)
               FROM CMSArchiveTraining.dbo.Transactions_90
               GROUP BY CustomerID)
           LOCALQUERY(
               SELECT CustomerID, MAX(TransactionDate)
               FROM LocalTransactions
           GROUP BY CustomerID)

PingBack from http://chaespot.com/mssql/2006/09/30/ssw-sql-auditor-optimize-your-sql-server-performance-2/

PingBack from http://chaespot.com/mssql/2007/04/26/tips-tricks-and-advice-from-the-sql-server-query-optimization-team-3/

PingBack from http://websitescripts.247blogging.info/tips-tricks-and-advice-from-the-sql-server-query-optimization-team/

PingBack from http://fixmycrediteasily.info/story.php?id=6275