item
item_location_date

ild_item (FK to Item)
ild_location (FK to location)
ild_pallet (Nullable FK to pallet id)
ild_created_when (creation timestamp)

select *
from item
cross apply (
    select top 1 *
    from item_location_date
    where ild_item = itm_id
    order by ild_date_created desc
) ild

ALTER TABLE [dbo].[item_location_date] ADD  CONSTRAINT [PK_item_location_date] PRIMARY KEY CLUSTERED
(
    [ild_date_created] DESC,
    [ild_item] DESC,
    [ild_location] DESC
)

select *
from
item inner join
(select *
 from (
        select *,
                ROW_NUMBER() over (PARTITION by ild_item order by ild_date_created desc) rn
        from item_location_date
    ) ild
    where rn = 1
) itm_location
on itm_location.ild_item = itm_id

select *
from item
cross apply (
    select top 1 *
    from item_location_date
    where ild_item = itm_id
    order by ild_date_created desc
) ild

CREATE NONCLUSTERED INDEX NCI_item_location_date_item_date_created
    ON [dbo].[item_location_date] (ild_item, ild_date_created)