T-SQL Filtering views for PKs vs data
-- This table captures one type of problem with the hardware
-- Has over 6 Million records
TABLE HardwareProblems
(
  MachineId VARCHAR(30) NOT NULL, -- PK
  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...
  MachineComponent VARCHAR(15) NOT NULL, -- PK, again, the name as a PK... ugh
  ErrorCode VARCHAR(10) NOT NULL, --PK
  CodePartA INT,
  CodePartB INT,
  DATA1 INT,
  DATA2 VARCHAR(50)
  -- MORE DATA
)

-- This table captures information about the machine
-- Has over 6 Million records
TABLE HardwareInformation
(
  MachineId VARCHAR(30) NOT NULL,
  TimeOfError VARCHAR(17) NOT NULL,
  MachineComponent VARCHAR(15) NOT NULL,
  SoftwareVersion VARCHAR(20),
  PartNumber VARCHAR(20)
  -- MORE DATA
)

-- This table captures the environment in which the equipment was present when the error occurred
-- Has over 6 Million records
TABLE EnvData
(
  MachineId VARCHAR(30) NOT NULL,
  TimeOfError VARCHAR(17) NOT NULL,
  MachineComponent VARCHAR(15) NOT NULL,
  ErrorCode VARCHAR(10) NOT NULL,
  TempF DECIMAL(3,2)
  -- More data
)

-- This table stores different types of problems
-- Has about 1 Million records
TABLE DifferentProblems
(
  MachineId VARCHAR(30) NOT NULL,
  TimeOfError VARCHAR(17) NOT NULL,
  MachineComponent VARCHAR(15) NOT NULL,
  ErrorCode VARCHAR(10) NOT NULL,
  CodePartA INT,
  CodePartB INT,
  CountOfErrors INT
  -- MORE DATA
)

VIEW vHardwareProblems
AS
  SELECT * -- no, my production code does not use SELECT *, this is for brevity
  FROM HardwareProblems hp INNER JOIN HardwareInformation hi ON     hp.MachineId = hi.MachineId
                                                                AND hp.TimeOfError = hi.TimeOfError
                                                                AND hp.MachineComponent = hi.MachineComponent
                           INNER JOIN EnvData             ed ON     hp.MachineId = ed.MachineId
                                                                AND hp.TimeOfError = ed.TimeOfError
                                                                AND hp.MachineComponent = ed.MachineComponent
                                                                AND hp.ErrorCode = ed.ErrorCode

UNIQUE CLUSTERED INDEX
  ON dbo.vHardwareProblems (hp.TimeOfError, hp.MachineComponent, hp.MachineId)

VIEW vOtherProblems
AS
   SELECT * -- again, no I'm not using SELECT *
   FROM OtherProblems op INNER JOIN HardwareInformation hi ON     op.MachineId = hi.MachineId
                                                              AND op.TimeOfError = hi.TimeOfError
                                                              AND op.MachineComponent = hi.MachineComponent

UNIQUE CLUSTERED INDEX
  ON dbo.vOtherProblems(op.TimeOfError, op.MachineComponent, op.MachineId)

CREATE FUNCTION ufn_GetFilteredHardwareProblemKeys
(
  @CodeA INT, -- Required
  @CodeB INT = NULL, -- Optional
  -- Minimum Error count does not belong in this function since it is not part of the view
  @MinTempF DECIMAL(3,2) = NULL, -- Optional, only available to this view
  -- There is a filter for Software version, but it is pretty complex so omitted
)
RETURNS @HardwareProblemKeys TABLE
(
  -- Primary Keys for the View and underlying tables
  MachineId VARCHAR(30) NOT NULL,
  TimeOfError VARCHAR(17) NOT NULL,
  MachineComponent VARCHAR(15) NOT NULL,
  ErrorCode VARCHAR(10) NOT NULL
)
AS BEGIN
  INSERT INTO @HardwareProblemKeys
  SELECT vhp.MachineId AS MachineId,
         vhp.TimeOfError AS TimeOfError,
         vhp.MachineComponent AS MachineComponent,
         vhp.ErrorCode AS ErrorCode
  FROM [dbo].[vHardwareProblems] vhp WITH (NOEXPAND) -- seems to be required even though I have Enterprise edition and the view is indexed...
  WHERE     vhp.CodeA = @CodeA -- There is a NONCLUSTERED INDEX for (CodeA, CodeB) in the view
        AND (@CodeB IS NULL OR vhp.CodeB = @CodeB)
        AND (@MinTempF IS NULL OR vhp.TempF >= @MinTempF)
        -- more filtering
END

SELECT hp.DATA1 AS Data1
       --... more data as required by the specific Stored Procedure
FROM [dbo].[vHardwareProblems] hp INNER JOIN
(
  SELECT filtered.MachineId,
         MAX(filtered.TimeOfError), -- get the lastest record which satisfies the COMMON filters only
         filtered.MachineComponent,
         filtered.ErrorCode
  FROM [dbo].[ufn_GetFilteredHardwareProblemKeys](@CodeA, @CodeB, @MinTempF) filtered
  GROUP BY filtered.MachineId, filtered.MachineComponent, filtered.ErrorCode
  ORDER BY filtered.MachineId ASC
) keys ON     keys.MachineId = hp.MachineId
          AND keys.TimeOfError = hp.TimeOfError
          AND keys.MachineComponent = hp.MachineComponent
          AND keys.ErrorCode = hp.ErrorCode
WHERE -- filter the extra data as required by the stored procedure, these are NON-COMMON filters
     hp.DATA1 < @MaxData1