Sql-server – Can a Selective XML index help when using query()

This answer assumes you have a master database/table of clients and their associated database names, or some way to figure out which databases in a given instance of SQL Server have relevant databases that you want to query.

Since this is for reporting purposes, you may want to implement some form of snapshotting technique to ensure the data sets consolidate to the same point in time, on at least a per-client basis. As a nice side effect, these techniques usually also alleviate locking/blocking effects of running these types of queries directly against the production data.

Pure Dynamic SQL
Using the master list, build up a SQL statement that uses 3-part names like you're doing now, but instead, inject the database name dynamically. It's unclear from the question what format the data is going to end up in, but it sounds like you want to UNION ALL everything together, which is easily accomplished.

This may be advantageous for you now because it will require very little up-front work.

The downside is that this approach is not very flexible and quickly becomes complicated if you need to run the query when the individual database revisions are different (i.e., the query was tied to a given database revision, and when a database update was deployed, it didn't succeed for all clients). It also may have problems or complications depending on the security model you're using for these databases.

Database-Based Object(s) + Dynamic SQL
This solution creates permanent objects within every database which you can query in an external process. Usually this means a view, stored procedure, or table-valued function. Dynamic SQL will be used to inject the database names into the query as above, but this time all that's needed is to select from, or execute, the database object instead of the raw query directly.

This method gives all the advantages of protecting your code behind an interface just like it would by using views, stored procs, etc., in application code. This also solves the security issues much more easily.

The disadvantage, of course, is that this needs a little more up-front work and management, and that the queried object(s) can only be deployed in database updates, instead of independently.

My personal opinion is that the latter approach is a much better long-term solution, so in this type of scenario, that's the one I would opt for. For ad-hoc types of queries, using the pure dynamic SQL approach is fine.

You could carve off the data you are interested in into a temp table and work with it there. I've used FOR XML PATH in this example as it's easier to work with:

USE tempdb
GO

IF OBJECT_ID('[dbo].[AUDIT]') IS NOT NULL DROP TABLE [dbo].[AUDIT]
GO

CREATE TABLE [dbo].[AUDIT](

    [UNIQUE_ID] [int], --IDENTITY(1,1) NOT NULL,
    [TABLE_NAME] [nvarchar](50) NOT NULL,
    [TABLE_UNIQUE_ID] [int] NOT NULL,
    [TABLE_USER_ID] [int] NOT NULL,
    [XML] [nvarchar](MAX) NULL,
    [DESCRIPTION] [nvarchar](MAX) NULL,
    [TIMESTAMP] [datetime] NOT NULL,
    [EXPLANATION] [nvarchar](MAX) NULL,
    [DATE_INDEX] [datetime] NOT NULL CONSTRAINT [DF_SYS_ADUIT_DATE_INDEX] DEFAULT ('01/01/1900')

PRIMARY KEY CLUSTERED 
(
    [UNIQUE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO



INSERT INTO [dbo].[AUDIT] ( UNIQUE_ID, TABLE_NAME, TABLE_UNIQUE_ID, TABLE_USER_ID, XML, DESCRIPTION, TIMESTAMP, EXPLANATION, DATE_INDEX )
VALUES
    ( 848098, 'MAIN', 500187, 317, '<Record><Data>Original record data and other xml goes here, not important right now</Data></Record>', 'Record was created', '8 Aug 2011 9:45', NULL, '1 Jan 1900' ),
    ( 849901, 'MAIN', 500187, 317, '<Record/>', 'Record locked', '8 Aug 2011 10:01', NULL, '1 Jan 1900' ),
    ( 854269, 'MAIN', 500187, 317, '<Record/>', 'Record unlocked', '8 Aug 2011 10:51', NULL, '3 Aug 2011' ),
    ( 911382, 'MAIN', 500187, 317, '<Record><Data>Change record data and other xml goes here, not important right now</Data></Record>', 'Record changes:', '8 Aug 2011 10:52', NULL, '8 Aug 2011' ),
    ( 927795, 'MAIN', 500187, 317, '<Record/>', 'Record locked', '15 Aug 2011 13:11', NULL, '1 Jan 1900' ),
    ( 931013, 'MAIN', 500187, 317, '<Record><Data>Current record data and other xml goes here, not important right now</Data></Record>', 'Record was exported', '15 Aug 2011 14:13', NULL, '1 Jan 1900' )
GO


IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

DECLARE @from DATETIME = '8 Aug 2011 00:00', @through DATETIME = '8 Aug 2011 23:59:59:999', @currentRecord int

-- Get all records for that id and date range into a temp table
SELECT
    UNIQUE_ID, 
    TABLE_USER_ID, 
    TABLE_UNIQUE_ID,
    [TIMESTAMP],
    DATE_INDEX,
    [XML],
    [DESCRIPTION]
INTO #tmp
FROM dbo.[AUDIT]
WHERE DATE_INDEX >= @from 
AND DATE_INDEX <= @through
--AND presumably some other criteria to limit the records ...
AND TABLE_UNIQUE_ID = 500187
AND TABLE_USER_ID = 317

-- Add unique index and RowNum column
ALTER TABLE #tmp ADD PRIMARY KEY ( UNIQUE_ID )
ALTER TABLE #tmp ADD RowNum INT

-- Get any associated records (date criteria not used), which don't already exist
INSERT INTO #tmp ( UNIQUE_ID, TABLE_USER_ID, TABLE_UNIQUE_ID, TIMESTAMP, DATE_INDEX, XML, DESCRIPTION )
SELECT
    UNIQUE_ID, 
    TABLE_USER_ID, 
    TABLE_UNIQUE_ID,
    [TIMESTAMP],
    DATE_INDEX,
    [XML],
    [DESCRIPTION]
FROM dbo.[AUDIT] a
WHERE EXISTS ( SELECT * FROM #tmp t WHERE a.TABLE_UNIQUE_ID = t.TABLE_UNIQUE_ID AND a.TABLE_USER_ID = t.TABLE_USER_ID )
  AND NOT EXISTS ( SELECT * FROM #tmp u WHERE a.UNIQUE_ID = u.UNIQUE_ID )


-- Add the row number
;WITH cte AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY TABLE_USER_ID, TABLE_UNIQUE_ID ORDER BY UNIQUE_ID ) x, *  
FROM #tmp
)
UPDATE cte
SET RowNum = x


-- Have a look at the data before we create the xml
SELECT 't' s, * FROM #tmp
GO


SELECT
    (
    SELECT
        UNIQUE_ID AS AUDITID,
        TABLE_USER_ID AS [UID],
        TABLE_UNIQUE_ID AS UNIQUEID,
        CAST( [XML] AS XML ) AS "CURRENTDATA/*"
    FROM #tmp
    WHERE RowNum = ( SELECT MAX(RowNum) FROM #tmp )
    FOR XML PATH(''), TYPE
    ),
    (
    SELECT
        [TIMESTAMP] AS [TIMESTAMP],
        [DESCRIPTION] AS [DESCRIPTION],
        '' AS EXPLANATION
    FROM #tmp
    WHERE RowNum > 1
      --AND RowNum = ( SELECT MAX(RowNum) FROM #tmp )   -- ??
    FOR XML PATH('HISTORY'), TYPE
    ),
    (
    SELECT
        CAST( [XML] AS XML ) AS "DATA/*"
    FROM #tmp
    WHERE RowNum = 1
    FOR XML PATH('ORIGINAL'), TYPE
    )

FOR XML PATH('RECORD'), TYPE
GO