USE [DBArchive]
GO

/****** Object:  Table [dbo].[DirectoryUserAction_Archive_2_Partition]    Script Date: 12.07.2017 20:33:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DirectoryUserAction_Archive_2_Partition](
	[EventId] [bigint] NOT NULL,
	[NodeId] [int] NOT NULL,
	[EventDateUtc] [datetime] NOT NULL,
	[EventTypeId] [tinyint] NOT NULL,
	[DirId] [bigint] NULL,
	[FileName] [nvarchar](900) NULL,
	[FileId] [bigint] NULL,
	[FileLength] [bigint] NULL,
	[ParentId] [bigint] NULL,
	[OldParentId] [bigint] NULL,
	[OldName] [nvarchar](1800) NULL,
 CONSTRAINT [PK_DirectoryUserAction_Archive_2_Partition] PRIMARY KEY NONCLUSTERED 
(
	[EventId] ASC,
	[EventDateUtc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON [schmUserActionMonthlyPartition]([EventDateUtc])

GO

/****** Object:  Index [IX_DirectoryUserAction_Archive_2_Partition_EventDate]    Script Date: 12.07.2017 20:33:18 ******/
CREATE CLUSTERED INDEX [IX_DirectoryUserAction_Archive_2_Partition_EventDate] ON [dbo].[DirectoryUserAction_Archive_2_Partition]
(
	[NodeId] ASC,
	[EventDateUtc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [schmUserActionMonthlyPartition]([EventDateUtc])
GO

/****** Object:  Index [IX_DirectoryUserAction_Archive_2_Partition_NodeId_EventId]    Script Date: 12.07.2017 20:33:18 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_DirectoryUserAction_Archive_2_Partition_NodeId_EventId] ON [dbo].[DirectoryUserAction_Archive_2_Partition]
(
	[NodeId] ASC,
	[EventId] ASC,
	[EventDateUtc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [schmUserActionMonthlyPartition]([EventDateUtc])

GO

/****** Object:  Index [IX_DirectoryUserAction_Archive_2_Partition_Type_EventDate]    Script Date: 12.07.2017 20:33:18 ******/
CREATE NONCLUSTERED INDEX [IX_DirectoryUserAction_Archive_2_Partition_Type_EventDate] ON [dbo].[DirectoryUserAction_Archive_2_Partition]
(
	[NodeId] ASC,
	[EventTypeId] ASC,
	[EventDateUtc] ASC
)
INCLUDE ( 	[DataFileId],
	[DirId],
	[EventId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [schmUserActionMonthlyPartition]([EventDateUtc])
GO


CREATE TABLE [dbo].[DirectoryUserActionEvent_Archive_2_Partition](
	[DirectoryId] [int] NOT NULL,
	[EventId] [bigint] NOT NULL,
	[EventDateUtc] [datetime] NOT NULL,
	[DirectoryEventTypeId] [tinyint] NULL,
 CONSTRAINT [PK_DirectoryUserActionEvent_Archive_2_Partition] PRIMARY KEY CLUSTERED 
(
	[DirectoryId] ASC,
	[EventId] ASC,
	[EventDateUtc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)  ON [schmUserActionMonthlyPartition]([EventDateUtc])

GO

/****** Object:  Index [IX_DirectoryUserActionEvent_Archive_2_Partition_EventId]    Script Date: 12.07.2017 20:34:22 ******/
CREATE NONCLUSTERED INDEX [IX_DirectoryUserActionEvent_Archive_2_Partition_EventId] ON [dbo].[DirectoryUserActionEvent_Archive_2_Partition]
(
	[EventId] ASC,
	[EventDateUtc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [schmUserActionMonthlyPartition]([EventDateUtc])


CREATE VIEW [dbo].[vwDirectoryUserActionArchive]
AS 
SELECT *, 1 AS serverId
FROM dbo.DirectoryUserAction_Archive_1_Partition
UNION ALL 
SELECT *, 2 AS serverId
FROM dbo.DirectoryUserAction_Archive_2_Partition
UNION ALL 
SELECT *, 3 AS serverId
FROM dbo.DirectoryUserAction_Archive_3_Partition
GO

CREATE VIEW [dbo].[vwDirectoryUserActionEventArchive]
AS 
SELECT *, 1 AS serverId
FROM dbo.DirectoryUserActionEvent_Archive_1_Partition
UNION ALL 
SELECT *, 2 AS serverId
FROM dbo.DirectoryUserActionEvent_Archive_2_Partition
UNION ALL 
SELECT *, 3 AS serverId
FROM dbo.DirectoryUserActionEvent_Archive_3_Partition
GO

 DECLARE 
            
            @ldateBeginUtc DATETIME,
            @ldateEndUtc DATETIME,
			@lserverId INT,
			@UserDirectoryList VARCHAR(MAX)

set @ldateBeginUtc = '20160401'
set	@ldateEndUtc = '20160701'
set @lserverId = 2
			
set	@UserDirectoryList  = '<S DirectoryId="3503336" NodeId="3503336"/><S DirectoryId="3503337" NodeId="3503337"/><S DirectoryId="3503341" NodeId="3503341"/>'

DECLARE @UserDirectoryListXml XML = CAST(@UserDirectoryList AS XML);            
                
CREATE TABLE #tmp_UserDirectorys_sd
			(
				DirectoryId INT, 
				[NodeId] INT
			);

INSERT INTO #tmp_UserDirectorys_sd
(DirectoryId,
[NodeId])
SELECT t.S.value('@DirectoryId','INT'),
	t.S.value('@NodeId','INT')
FROM @UserDirectoryListXml.nodes('/S') AS t(S);

select *
from #tmp_UserDirectorys_sd

SELECT NF.EventId,
        NF.SourceUserId,
        NE.DirectoryId,
        NF.DataFileId,
        NF.EventDateUtc,                      
		NF.[NodeId],						
		NF.[FileName],						
		NF.ParentId,
		NF.OldParentId
FROM    [dbo].[vwDirectoryUserActionArchive] NF WITH (NOLOCK)
		INNER JOIN #tmp_UserDirectorys_sd VF ON (VF.[NodeId] = NF.[NodeId]) 
		INNER JOIN dbo.vwDirectoryUserActionEventArchive NE WITH (NOLOCK) ON 
			(NE.EventId = NF.EventId 
			AND NE.EventDateUtc = NF.EventDateUtc
			AND NE.DirectoryId = VF.DirectoryId)
WHERE   NF.EventDateUtc BETWEEN @ldateBeginUtc AND @ldateEndUtc
		AND NF.serverId = @lserverId
		AND NE.serverId = @lserverId
OPTION (RECOMPILE)


drop table #tmp_UserDirectorys_sd

cкачать

CREATE TABLE #tmp_UserDirectorys_sd
			(
				DirectoryId INT, 
				[NodeId] INT
				primary key clustered(NodeId, DirectoryId)
			);