SQL 2012 Data Warehouse type environment...
I have a Non Clustered Index on a Type 2 Dimension table, which I'll just call DimStudent.
The index is defined as below, with data types and other info in parentheses for context:
( [StudentID] ASC (VARCHAR(10)) ,[EffectiveStartDate] ASC (SMALLDATETIME) ,[EffectiveEndDate] ASC (SMALLDATETIME) ) INCLUDE [StudentKey] (INT, IDENTITY, PK).
The query in question is being run during an SSIS ETL package, and is defined as below. It is specified in a Lookup Transformation using a partial cache, in the Advanced tab using the Custom query section, which is mostly auto-generated by Visual Studio (which put the [refTable] alias and SELECT * into the query) with some modifications to the query for SCD2 lookup usage:
select * from (SELECT LTRIM(RTRIM(StudentID)) AS StudentID_Trimmed ,StudentKey ,EffectiveStartDate ,EffectiveEndDate FROM dbo.DimStudent ) AS [refTable] where [refTable].[StudentID_Trimmed] = @P1 and [refTable].[EffectiveStartDate] <= @P2 and [refTable].[EffectiveEndDate] > @P3
Note that the order of the columns in the query is the student ID (@P1), then the Start date (@P2) and then End Date (@P3).
Also note that I am currently TRIMMing the StudentID. I have determined that this is no longer necessary (it was necessary at some point for some reason), and I'm already working on changing the ETL to remove that TRIM aspect.
In the query plan for this query (using Adam Macahanic's sp_WhoIsActive), I've confirmed it's doing a Non Clustered Scan using this index. When I view Properties of the scan operator, it lists the Predicates as: (db and schema removed)
[DimStudent].[EffectiveStartDate] <= **[@P2]** AND [DimStudent].[EffectiveEndDate] > **[@P3]** AND ltrim(rtrim([DimStudent].[StudentID])) = **[@P1]**
My questions are:
Why are the predicates in the query plan in a different order (@P2, @P3, @P1)than how the query was written? (@P1, @P2, @P3) Caused by the TRIM?
Why is it a scan and not a seek as the index was designed to do for this query? Caused by the TRIM (not SARGable)?