sql server 2012 执行计划 set showplain_all on

Hi All:

     

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('UF_GETPARTITIONID') IS NOT NULL
BEGIN
    PRINT 'Dropping function UF_GETPARTITIONID'
    DROP FUNCTION UF_GETPARTITIONID
    IF @@ERROR = 0
        PRINT 'Function UF_GETPARTITIONID dropped'
END
GO 
CREATE FUNCTION [dbo].UF_GETPARTITIONID()
RETURNS BIGINT
AS
BEGIN
	DECLARE @PARTITIONID BIGINT = NULL 
	SELECT  @PARTITIONID=RECID FROM DBO.PARTITIONS WITH(NOLOCK)   where partitionkey='initial'
	RETURN @PARTITIONID
END
GO  

1. Calling function dbo.uf_getpartitionid()

2.Adding column partition to  the query  and compareits query plan to a traditional one. 

SELECT DATAAREAID,[PARTITION],ACCOUNTNUM,TRANSDATE,VOUCHER FROM custtrans 
        WHERE  DATAAREAID = '835' AND [PARTITION] = '5637144576' AND ACCOUNTNUM =        '835-000112' AND 
        TRANSDATE > '2014-07-15' AND VOUCHER = 'AR-0000287'
        GO        
 SELECT DATAAREAID,[PARTITION],ACCOUNTNUM,TRANSDATE,VOUCHER FROM custtrans 
        WHERE DATAAREAID = '835' AND [PARTITION] = dbo.uf_getpartitionid() --returns value of partition 
AND ACCOUNTNUM =  '835-000112'  AND  TRANSDATE > '2014-07-15' AND VOUCHER = 'AR-0000287'

3.Query Plan: getting data through seeking relative indexes.