CREATE TABLE [dbo].[t_AtomVersion](
	[AtomVersionId] [bigint] IDENTITY(1,1) NOT NULL,
	[AtomId] [int] NOT NULL,
	[StructureVersion] [int] NOT NULL,
	[ContentVersion] [int] NOT NULL,
	[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_t_AtomVersion_CreatedDate]  DEFAULT (getdate()),
	[AtomVersionStatusId] [smallint] NOT NULL,
	[VersionDescription] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[AssemblySize] [bigint] NOT NULL CONSTRAINT [DF_t_AtomVersion_FileSize]  DEFAULT ((0)),
 CONSTRAINT [PK_t_AtomVersion] PRIMARY KEY CLUSTERED 
(
	[AtomVersionId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [UC_t_AtomVersion] UNIQUE NONCLUSTERED 
(
	[AtomId] ASC,
	[StructureVersion] ASC,
	[ContentVersion] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [Packaging]
GO
ALTER TABLE [dbo].[t_AtomVersion]  WITH CHECK ADD  CONSTRAINT [FK_t_AtomVersion_t_Atom] FOREIGN KEY([AtomId])
REFERENCES [dbo].[t_Atom] ([AtomId])
GO
ALTER TABLE [dbo].[t_AtomVersion]  WITH CHECK ADD  CONSTRAINT [FK_t_AtomVersion_t_AtomVersionStatus] FOREIGN KEY([AtomVersionStatusId])
REFERENCES [dbo].[t_AtomVersionStatus] ([AtomVersionStatusId])

CREATE NONCLUSTERED INDEX [IX_t_AtomVersion_CoveringIndex] ON [dbo].[t_AtomVersion] 
(
	[AtomId] ASC,
	[AtomVersionId] ASC,
	[StructureVersion] ASC,
	[AtomVersionStatusId] ASC,
	[AssemblySize] ASC
)
INCLUDE ( [ContentVersion]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
go

		select	A.AtomId, AT.AtomTypeKey, SQ.StructureVersion, max(CV.ContentVersion) as ContentVersion, SQ.AssemblySize
		from	dbo.t_AtomVersion CV with(nolock)
				inner join (
					select	AP.AtomId, SV.StructureVersion, SV.AssemblySize
					from	@atomPriority AP
							inner join
							(
								select	AtomId, AssemblySize, max(StructureVersion) as StructureVersion
								from	dbo.t_AtomVersion AV with(nolock)
										inner join dbo.t_AtomVersionStatus SVS with(nolock) on SVS.AtomVersionStatusId = AV.AtomVersionStatusId
								where	SVS.Status = 'release'
								group by AtomId, AssemblySize

							) SV on AP.AtomId = SV.AtomId
				) SQ on SQ.AtomId = CV.AtomId and SQ.StructureVersion = CV.StructureVersion
				inner join dbo.t_AtomVersionStatus CVS with(nolock) on CVS.AtomVersionStatusId = CV.AtomVersionStatusId
				inner join dbo.t_Atom A with(nolock) on A.AtomId = SQ.AtomId
				inner join dbo.t_AtomType AT with(nolock) on AT.AtomTypeId = A.AtomTypeId
		where	CVS.Status = 'release'
		group by A.AtomId, SQ.StructureVersion, AT.AtomTypeKey, SQ.AssemblySize

SELECT	AtomId,
	AssemblySize,
	tructureVersion,
	ContentVersion
FROM	(
		SELECT		av.AtomId,
				av.AssemblySize,
				a.StructureVersion,
				av.ContentVersion,
				ROW_NUMBER() OVER (PARTITION BY av.AtomID, av.AssemblySize ORDER BY av.StructureVersion DESC) AS StructureVersionRecID
				ROW_NUMBER() OVER (PARTITION BY av.AtomID, at.AtomTypeKey ORDER BY av.ContentVersion DESC) AS ContentVersionRecID
		FROM		dbo.t_AtomVersion AS av WITH (NOLOCK)
		INNER JOIN	@AtomPriority AS ap ON ap.AtomID = av.AtomID 
		INNER JOIN	dbo.t_AtomVersionStatus AS svs WITH (NOLOCK) ON svs.AtomVersionStatusId = av.AtomVersionStatusId
		INNER JOIN	dbo.t_Atom AS a WITH (NOLOCK) ON a.AtomId = av.AtomId
		INNER JOIN	dbo.t_AtomType AS at WITH (NOLOCK) ON at.AtomTypeId = a.AtomTypeId
		WHERE		svs.Status = 'Release'
				AND ap.StructureVersion = av.StructureVersion
	) AS d
WHERE	StructureVersionRecID = 1
	AND ContentVersionRecID = 1