PRINT N'Dropping [dbo].[Asset_SMBios_MemoryDevice_Device_FK]...';


GO
ALTER TABLE [dbo].[Asset_SMBios_MemoryDevice] DROP CONSTRAINT [Asset_SMBios_MemoryDevice_Device_FK];


GO
PRINT N'Dropping [dbo].[Asset_SMBios_MemoryDevice]...';


GO
DROP TABLE [dbo].[Asset_SMBios_MemoryDevice];


GO
PRINT N'Creating [dbo].[MwusFileIDList_DTT]...';


GO
CREATE TYPE [dbo].[MwusFileIDList_DTT] AS TABLE (
    [FileID] UNIQUEIDENTIFIER NOT NULL,
    PRIMARY KEY CLUSTERED ([FileID] ASC));


GO
PRINT N'Creating [dbo].[Asset_Device]...';


GO
CREATE TABLE [dbo].[Asset_Device] (
    [DeviceGuid]        UNIQUEIDENTIFIER NOT NULL,
    [TimeLastAssetScan] DATETIME         NOT NULL,
    [DateCreated]       DATETIME         NOT NULL,
    [DateModified]      DATETIME         NOT NULL,
    [OMRowVersion]      ROWVERSION       NOT NULL,
    CONSTRAINT [Asset_Device_DeviceGuid_PK] PRIMARY KEY CLUSTERED ([DeviceGuid] ASC)
);


GO
PRINT N'Creating [dbo].[Asset_Device_DeviceGuid_FK]...';


GO
ALTER TABLE [dbo].[Asset_Device] WITH NOCHECK
    ADD CONSTRAINT [Asset_Device_DeviceGuid_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [dbo].[Device] ([DeviceGuid]);


GO
PRINT N'Creating [dbo].[Mwus_GetFilesByFileID]...';


GO

CREATE PROCEDURE [dbo].[Mwus_GetFilesByFileID]
  @fileIDs [MwusFileIDList_DTT] READONLY
AS
BEGIN
  SET NOCOUNT ON;

  -- OPTION (RECOMPILE) is used, so the optimizer can make a correct cardinality estimation 
  -- for @fileIDs table variable when creating the execution plan
  SELECT RevisionID, MwusFileGuid, PatchType 
  FROM dbo.MwusFileForRevision  
  WHERE MwusFileGuid IN (SELECT FileID 
                         FROM @fileIDs)
  OPTION (RECOMPILE)

END -- Mwus_GetFilesByFileID
GO
PRINT N'Creating [dbo].[Mwus_GetFileUrlsByFileID]...';


GO

CREATE PROCEDURE [dbo].Mwus_GetFileUrlsByFileID
  @fileIDs [MwusFileIDList_DTT] READONLY
AS
BEGIN
  SET NOCOUNT ON;

  -- OPTION (RECOMPILE) is used, so the optimizer can make a correct cardinality estimation 
  -- for @fileIDs table variable when creating the execution plan
  SELECT MwusFileGuid, FileDigest, MUURL
  FROM dbo.MwusFile
  WHERE MwusFileGuid IN (SELECT FileID 
                         FROM @fileIDs)
  OPTION (RECOMPILE)

END -- Mwus_GetFileUrlsByFileID
GO
PRINT N'Checking existing data against newly created constraints';


GO

ALTER TABLE [dbo].[Asset_Device] WITH CHECK CHECK CONSTRAINT [Asset_Device_DeviceGuid_FK];


GO
PRINT N'Update complete.';


GO
