Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on May 8th, 2012  |  syntax: None  |  size: 4.82 KB  |  hits: 9  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. T-SQL Filtering views for PKs vs data
  2. -- This table captures one type of problem with the hardware
  3. -- Has over 6 Million records
  4. TABLE HardwareProblems
  5. (
  6.   MachineId VARCHAR(30) NOT NULL, -- PK
  7.   TimeOfError VARCHAR(17) NOT NULL, -- Stupidly, this is a timedate field stored as VARCHAR and is a PK... which I've argued repeatedly to get changed...
  8.   MachineComponent VARCHAR(15) NOT NULL, -- PK, again, the name as a PK... ugh
  9.   ErrorCode VARCHAR(10) NOT NULL, --PK
  10.   CodePartA INT,
  11.   CodePartB INT,
  12.   DATA1 INT,
  13.   DATA2 VARCHAR(50)
  14.   -- MORE DATA
  15. )
  16.  
  17. -- This table captures information about the machine
  18. -- Has over 6 Million records
  19. TABLE HardwareInformation
  20. (
  21.   MachineId VARCHAR(30) NOT NULL,
  22.   TimeOfError VARCHAR(17) NOT NULL,
  23.   MachineComponent VARCHAR(15) NOT NULL,
  24.   SoftwareVersion VARCHAR(20),
  25.   PartNumber VARCHAR(20)
  26.   -- MORE DATA
  27. )
  28.  
  29. -- This table captures the environment in which the equipment was present when the error occurred
  30. -- Has over 6 Million records
  31. TABLE EnvData
  32. (
  33.   MachineId VARCHAR(30) NOT NULL,
  34.   TimeOfError VARCHAR(17) NOT NULL,
  35.   MachineComponent VARCHAR(15) NOT NULL,
  36.   ErrorCode VARCHAR(10) NOT NULL,
  37.   TempF DECIMAL(3,2)
  38.   -- More data
  39. )
  40.  
  41. -- This table stores different types of problems
  42. -- Has about 1 Million records
  43. TABLE DifferentProblems
  44. (
  45.   MachineId VARCHAR(30) NOT NULL,
  46.   TimeOfError VARCHAR(17) NOT NULL,
  47.   MachineComponent VARCHAR(15) NOT NULL,
  48.   ErrorCode VARCHAR(10) NOT NULL,
  49.   CodePartA INT,
  50.   CodePartB INT,
  51.   CountOfErrors INT
  52.   -- MORE DATA
  53. )
  54.        
  55. VIEW vHardwareProblems
  56. AS
  57.   SELECT * -- no, my production code does not use SELECT *, this is for brevity
  58.   FROM HardwareProblems hp INNER JOIN HardwareInformation hi ON     hp.MachineId = hi.MachineId
  59.                                                                 AND hp.TimeOfError = hi.TimeOfError
  60.                                                                 AND hp.MachineComponent = hi.MachineComponent
  61.                            INNER JOIN EnvData             ed ON     hp.MachineId = ed.MachineId
  62.                                                                 AND hp.TimeOfError = ed.TimeOfError
  63.                                                                 AND hp.MachineComponent = ed.MachineComponent
  64.                                                                 AND hp.ErrorCode = ed.ErrorCode
  65.  
  66. UNIQUE CLUSTERED INDEX
  67.   ON dbo.vHardwareProblems (hp.TimeOfError, hp.MachineComponent, hp.MachineId)
  68.  
  69. VIEW vOtherProblems
  70. AS
  71.    SELECT * -- again, no I'm not using SELECT *
  72.    FROM OtherProblems op INNER JOIN HardwareInformation hi ON     op.MachineId = hi.MachineId
  73.                                                               AND op.TimeOfError = hi.TimeOfError
  74.                                                               AND op.MachineComponent = hi.MachineComponent
  75.  
  76. UNIQUE CLUSTERED INDEX
  77.   ON dbo.vOtherProblems(op.TimeOfError, op.MachineComponent, op.MachineId)
  78.        
  79. CREATE FUNCTION ufn_GetFilteredHardwareProblemKeys
  80. (
  81.   @CodeA INT, -- Required
  82.   @CodeB INT = NULL, -- Optional
  83.   -- Minimum Error count does not belong in this function since it is not part of the view
  84.   @MinTempF DECIMAL(3,2) = NULL, -- Optional, only available to this view
  85.   -- There is a filter for Software version, but it is pretty complex so omitted
  86. )
  87. RETURNS @HardwareProblemKeys TABLE
  88. (
  89.   -- Primary Keys for the View and underlying tables
  90.   MachineId VARCHAR(30) NOT NULL,
  91.   TimeOfError VARCHAR(17) NOT NULL,
  92.   MachineComponent VARCHAR(15) NOT NULL,
  93.   ErrorCode VARCHAR(10) NOT NULL
  94. )
  95. AS BEGIN
  96.   INSERT INTO @HardwareProblemKeys
  97.   SELECT vhp.MachineId AS MachineId,
  98.          vhp.TimeOfError AS TimeOfError,
  99.          vhp.MachineComponent AS MachineComponent,
  100.          vhp.ErrorCode AS ErrorCode
  101.   FROM [dbo].[vHardwareProblems] vhp WITH (NOEXPAND) -- seems to be required even though I have Enterprise edition and the view is indexed...
  102.   WHERE     vhp.CodeA = @CodeA -- There is a NONCLUSTERED INDEX for (CodeA, CodeB) in the view
  103.         AND (@CodeB IS NULL OR vhp.CodeB = @CodeB)
  104.         AND (@MinTempF IS NULL OR vhp.TempF >= @MinTempF)
  105.         -- more filtering
  106. END
  107.        
  108. SELECT hp.DATA1 AS Data1
  109.        --... more data as required by the specific Stored Procedure
  110. FROM [dbo].[vHardwareProblems] hp INNER JOIN
  111. (
  112.   SELECT filtered.MachineId,
  113.          MAX(filtered.TimeOfError), -- get the lastest record which satisfies the COMMON filters only
  114.          filtered.MachineComponent,
  115.          filtered.ErrorCode
  116.   FROM [dbo].[ufn_GetFilteredHardwareProblemKeys](@CodeA, @CodeB, @MinTempF) filtered
  117.   GROUP BY filtered.MachineId, filtered.MachineComponent, filtered.ErrorCode
  118.   ORDER BY filtered.MachineId ASC
  119. ) keys ON     keys.MachineId = hp.MachineId
  120.           AND keys.TimeOfError = hp.TimeOfError
  121.           AND keys.MachineComponent = hp.MachineComponent
  122.           AND keys.ErrorCode = hp.ErrorCode
  123. WHERE -- filter the extra data as required by the stored procedure, these are NON-COMMON filters
  124.      hp.DATA1 < @MaxData1
create a new version of this paste RAW Paste Data