MSSQL query to look for duplicate record This query took 6 seconds to complete. How can I optimize it?
Total records in table is 166803.

SELECT ltrim(rtrim(CAST(cageID as nvarchar(max))))+ltrim(rtrim(CAST(trayNo as nvarchar(max)))) as _unique,* 
from lf_transit_cage
where ltrim(rtrim(CAST(cageID as nvarchar(max))))+ltrim(rtrim(CAST(trayNo as nvarchar(max)))) in
(
    SELECT dt._unique FROM
    (
        SELECT ltrim(rtrim(CAST(cageID as nvarchar(max))))+ltrim(rtrim(CAST(trayNo as nvarchar(max)))) as _unique 
        from lf_transit_cage 
    ) as dt
    group by dt._unique 
    HAVING COUNT(dt._unique)&gt;1
)
order by cageID,trayNo
As mentioned in the comments, there are benefits to casting/storing that unique key in the table during the ETL process, especially if it's going to be used in other places than just this query.

Most likely, the performance hit is coming from using IN (typically results in a row by row lookup) and from de-duping with the casted key.  You could get a performance gain from JOINing the subequery instead of using IN.  You could also use ROW_NUMBER which, in my experience, is typically more performant than the GROUP BY with HAVING clause.

Here's my example using ROW_NUMBER and CTE's for easier reading:

--Calculate Unique NVARCHAR key
;WITH cte_lf_transit_cage AS (
    SELECT
      ltrim(rtrim(CAST(cageID as nvarchar(max))))+ltrim(rtrim(CAST(trayNo as nvarchar(max)))) as _unique,
      *
    FROM 
      lf_transit_cage
)
--Get the Row Count
, cte_rowcount AS (
  SELECT
    _unique,
    ROW_NUMBER() OVER (PARTITION BY _unique ORDER BY cageID, trayNo) AS rowcnt
  FROM
    cte_lf_transit_cage
)
--Grab all instances of duplicate rows
SELECT
  ltc.*
FROM
  cte_lf_transit_cage ltc
WHERE
  EXISTS
    (SELECT unique FROM cte_rowcount rc WHERE rc._unique = ltc._unique AND rc.rowcnt &gt; 1 )
ORDER BY
  ltc.cageID,
  ltc.trayNo


Also, was mentioned in the comments that you may not need to generate the _unique key depending on how the data is stored.  Might compare results to confirm:

--Get the Row Count
;WITH cte_rowcount AS (
  SELECT
    cageID,
    trayNo,
    ROW_NUMBER() OVER (PARTITION BY cageID, trayNo ORDER BY trayNo) AS rowcnt
  FROM
    lf_transit_cage
)
--Grab all instances of duplicate rows
SELECT
  ltrim(rtrim(CAST(ltc.cageID as nvarchar(max))))+ltrim(rtrim(CAST(ltc.trayNo as nvarchar(max)))) as _unique,
  ltc.*
FROM
  lf_transit_cage ltc
WHERE
  EXISTS
    (SELECT * FROM cte_rowcount rc WHERE rc.cageID = ltc.cageID AND rc.trayNo = ltc.trayNo AND rc.rowcnt &gt; 1 )
ORDER BY
  ltc.cageID,
  ltc.trayNo