Thanks for all the reply, i been focus on other things and just read your messages. So it seems it is already optimized (Casting). Yes, on the latter part they used it to do insert/update on this field and some cases use it as a condition.
For the cursor here below is the scripts. This is created as scalar-valued functions, and being called multiples times in one process, >5millions calls. The results/returns records of each cursor called will not be more than 10 records and most of the times, 95%, it will only return one record. dbo.table holds more or less 800K records. And there are only 4 objects dependent on this function. Can you suggest any optimization on this scripts.
-----------------
DECLARE @m INT
SET @m = 0
DECLARE @l INT
SET @l = 0
DECLARE l_cursor CURSOR FOR
SELECT LId
FROM [dbo].table
WHERE EH = @param1
ORDER BY LId
OPEN l_cursor FETCH NEXT FROM l_cursor INTO @l
WHILE @@FETCH_STATUS = 0
BEGIN
IF @l = 0 SET @m = @m | 0x0001
ELSE IF @l = 1 SET @m = @m | 0x0009
ELSE IF @l = 2 SET @m = @m | 0x0011
........multiple else if
FETCH NEXT FROM l_cursor
INTO @l
END
CLOSE l_cursor
DEALLOCATE l_cursor
return @m;
--------------
Thanks again.