SQL Server中DATETIME与CHAR时间列转换及查询性能调优求助

Got it, let's tackle this tricky datetime split scenario you're dealing with—totally understand the frustration of working around a legacy database you can't modify. Here are some practical, performance-focused approaches to handle those two columns efficiently in your SELECT queries:

• Avoid Implicit Conversions at All Costs
  When combining the date and time columns, never let SQL Server handle implicit conversion—this will kill index usage and force full table scans. Instead, structure your query to let the optimizer leverage any existing indexes on the date column.

  Bad practice (forces full scan, no index utilization):

  SELECT *
  FROM your_table
  WHERE CAST(date_col + ' ' + time_col AS DATETIME) BETWEEN '2024-01-01 08:00' AND '2024-01-01 17:00'
  

  Better approach (uses index on date_col, minimal overhead):

  SELECT 
    *,
    CAST(CONCAT(CAST(date_col AS DATE), ' ', time_col) AS DATETIME2(0)) AS full_datetime
  FROM your_table
  WHERE date_col = '2024-01-01'
    AND time_col BETWEEN '08:00' AND '17:00'
  

  Filtering on date_col directly first lets SQL Server quickly narrow rows using its index, and the time filter uses raw string comparison (no conversion needed for the CHAR(5) column).

• Use SARGable Predicates for Multi-Day Ranges
  For date ranges spanning multiple days, split your WHERE clause into three logical parts to keep it SARGable (Search ARGument Able)—this ensures the optimizer can still use the date column index:

  1. The start date with time >= your start threshold
  2. All full middle dates (no time filter required)
  3. The end date with time <= your end threshold

  Example for a range from 2024-01-01 10:00 to 2024-01-03 15:00:

  SELECT *
  FROM your_table
  WHERE 
    (date_col = '2024-01-01' AND time_col >= '10:00')
    OR (date_col BETWEEN '2024-01-02' AND '2024-01-02')
    OR (date_col = '2024-01-03' AND time_col <= '15:00')
  

  This approach avoids modifying the date column in any way, so the index remains usable for each condition.

• Compute Combined Datetime Only After Filtering
  If you need to reference the combined datetime multiple times, compute it once in a CTE or subquery—but only after filtering down to a small subset of rows. Never calculate it for the entire table first, as that will trigger a full scan.

  Example:

  WITH filtered_rows AS (
    SELECT 
      *,
      CAST(CONCAT(CAST(date_col AS DATE), ' ', time_col) AS DATETIME2(0)) AS full_datetime
    FROM your_table
    WHERE date_col BETWEEN '2024-01-01' AND '2024-01-03'
  )
  SELECT *
  FROM filtered_rows
  WHERE full_datetime BETWEEN '2024-01-01 10:00' AND '2024-01-03 15:00'
  

  By narrowing rows first with the date index, you minimize the number of datetime conversions needed.

• Stick to String Comparisons for Time
  Since your time column is CHAR(5) in hh:mm format, you don’t need to cast it to a TIME type for comparisons. String comparisons work perfectly here (e.g., '09:30' > '08:00' behaves exactly like a time comparison). Avoid functions like CAST(time_col AS TIME) in your WHERE clause—they add unnecessary overhead and can block efficient filtering.

内容的提问来源于stack exchange，提问作者Mr Big Spender