Performance issue with TOP (1) on a nullable column
The query takes a lot of time because it finds no rows.

The plan first is setup top pick one row from tabSparePart where sparepartname = '1270-3132'. It then moves along to the index scan of tabData. For each row fetched from tabData there is a clustered index seek in locSL followed by a clustered index seek in tabDataDetail which also checks for not null on gsp_part_price and finally returns the values of fiSparePart and GSP_Part_Price. fiSparePart is then compared against the idSparePart returned from the Index Seek in tabSparePart. If they are the same the query immediately terminates and return the gsp_part_price. If they are not the same the index scan in tabData moves to the next record and all the checks are executed again. That keeps happening until there is a row returned or all rows in tabData have been processed.

As it turns out you get a totally different query plan when you do top(1000).

You can use the optimize for hint to make SQL Server think you actually want 1000 rows when you only need one.

Your scalar valued function would then look something like this:

CREATE FUNCTION [dbo].[FunctionName]()
RETURNS INT
AS
BEGIN

  DECLARE @TOP INT
  DECLARE @RET INT

  SET @TOP = 1

  SELECT TOP (@TOP) @RET = dd.gsp_part_price 
  FROM  tabdata 
          INNER JOIN locsl 
                  ON tabdata.fisl = locsl.idsl 
          INNER JOIN locgsp 
                  ON locsl.figsp = locgsp.idgsp 
          INNER JOIN tabdatadetail dd
                  ON dd.fidata = tabdata.iddata 
          INNER JOIN tabsparepart sp 
                  ON dd.fisparepart = sp.idsparepart 
  WHERE  sp.sparepartname = '1270-3132' 
          AND dd.gsp_part_price IS NOT NULL 
  ORDER BY Claim_Submitted_Date DESC 
  OPTION (OPTIMIZE FOR(@TOP = 1000))

  RETURN @RET

END