Order By In Table Function

[BenSCooper]

Dear All,
Please can someone explain why the results of the following table type function in SQL Server 2005 don't sort as per the ORDER BY statement.

ALTER FUNCTION [dbo].[CONTACT_HISTORY_UNION]
(
@ContactID AS int
)
RETURNS TABLE
AS
RETURN
(
SELECT TOP 100 PERCENT *
FROM (
SELECT ID, Vehicle, CAR_SALES_ID, INVOICE_ID, InvoiceDate, Detail, [TYPE], CAR_SALES_ID as JOBNO
FROM [dbo].[CONTACT_HISTORY_Cars_Credited] (@ContactID)
UNION
SELECT ID, Vehicle, CAR_SALES_ID, INVOICE_ID, InvoiceDate, Detail, [TYPE], CAR_SALES_ID as JOBNO
FROM [dbo].[CONTACT_HISTORY_CarsSold_Financed] (@ContactID)
UNION
SELECT ID, Vehicle, CAR_SALES_ID, INVOICE_ID, InvoiceDate, Detail, [TYPE],CAR_SALES_ID as JOBNO
FROM [dbo].[CONTACT_HISTORY_CarsSold] (@ContactID)
) uni
ORDER BY INVOICE_ID
)

The select carries out the union on the three functions correctly, but applies no sortation on the results.

Does anyone know why this may be?

Many Thanks
Ben Cooper

 

[bborissov]

Why you need to order by in Function?
Order that table there where you need it.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Take a look at Brad's comment in this thread

http://social.msdn.microsoft.com/Fo...l/thread/466300f7-2e84-44f8-a967-bdeb9fbbc040

--------------------------
TOP 100 PERCENT is not guaranteed to work. It's a kludge that people used to use to make sure views came out in order, but starting in SQL2005 (I believe), the optimizer most likely throws it out the window... we can't fool it anymore.

PluralSight Learning Library

 

[BenSCooper]

Thanks guys, I guess I'll just have to order on the call expression.

Cheers
Ben Cooper