Understanding SQL 2005 PIVOT clause

http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx

SQL SELECT INTO Statement

USE AdventureWorks;
GO
-- Passing values as constants.
EXEC dbo.uspGetWhereUsedProductID 819, '20050225';
GO
-- Passing values as variables.
DECLARE @ProductID int, @CheckDate datetime;
SET @ProductID = 819;
SET @CheckDate = '20050225';
EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;
GO
-- Try to use a function as a parameter value.
-- This produces an error message.
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();
GO
-- Passing the function value as a variable.
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO



http://msdn2.microsoft.com/en-us/library/ms189915.aspx

Tuning Up SQL Server 2005 Databases

http://www.w3schools.com/sql/sql_select_into.asp

Work around for XML data type not supported in Distributed Queries

http://www.msdner.com/dev-archive/84/19-85-847101.shtm

SELECT
Cast(a.XML_Data as XML) as XML_Data
FROM OPENQUERY([LINKED SERVER NAME HERE],'
SELECT
Cast(XML_Data as Varchar) as XML_Data
FROM
[DATABASE NAME].[SCHEMA].[TABLE NAME]'
) A

Basically, the data is queried on the remote server, converts the XML data to a varchar, sends the data to the requesting server and then reconverts it back to XML.

StephenDudzic at 2007-9-3 22:09:22 >

"Re: XML data type not supported in Distributed Queries This is a limitation in SQL Server 2005. Columns of xml type or CLR type cannot be queried directly or referenced from one server to another - this means the following:
  1. You cannot use a table or view that contains xml or clr type as 4-part name in your query
  2. You need to cast the column to either nvarchar(max) or varbinary(max) or other appropriate type to use
  3. If you have a table that has xml type for example then you need to create a view that contains all columns other than xml and query it instead. Or you can issue a pass-through query using OPENQUERY with the appropriate columns only."

Indexing encrypted data

http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx