这篇文章主要讲解了“SQL Server中怎么改写内联表值函数”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“SQL Server中怎么改写内联表值函数”吧！

问题SQL：

SELECTTOP1001ha.HuntApplicationID,ha.PartyNumber,mht.NameASMasterHuntTypeName,htly.LicenseYear,lStatus.[Status]ASDrawTicketStatus,isnull(dbo.udf_GetHuntApplicationPartyCount(ha.HuntApplicationID),0)ASMemberCount,count(won.DrawTicketLicenseID)ASDrawnMemberCount,won.drawticketid,dt.PreDrawNonResidentMemberCountASNRMemberCount,dbo.udf_GetAvgPreferencePoints(dt.DrawTicketID)ASPreferencePointAverage,CASEWHENha.Quantity>1THENNULLELSEdt.PreDrawRandomNumberENDASPreDrawRandomNumber,dsm.NameASDrawSelectionMethodName,dt.DrawnSequence,dt.PreferencePointRank,dt.DrawID,dt.RandomRankFROMdbo.HuntApplicationhaJOINdbo.HuntTypeLicenseYearhtlyONha.HuntTypeLicenseYearID=htly.HuntTypeLicenseYearIDJOINdbo.MasterHuntTypemhtONhtly.MasterHuntTypeID=mht.MasterHuntTypeIDLEFTJOINdbo.HuntApplicationLicensehalONha.HuntApplicationID=hal.HuntApplicationIDLEFTJOINdbo.DrawTicketdtONha.HuntApplicationID=dt.HuntApplicationIDLEFTJOINdbo.DrawTicketLicensewonONdt.DrawTicketID=won.DrawTicketIDANDwon.WasDrawn=1LEFTJOINdbo.DrawSelectionMethoddsmONdt.DrawSelectionMethodID=dsm.DrawSelectionMethodIDLEFTJOINdbo.StatusCodelStatusONdt.StatusCodeID=lStatus.StatusCodeIDJOINdbo.DrawTicketHuntChoicedthcONdt.DrawTicketID=dthc.DrawTicketIDCROSSAPPLYdbo.tvf_GetHuntApplicationPartyCount(ha.HuntApplicationID)hapcCROSSAPPLYdbo.tvf_GetAvgPreferencePoints(dt.DrawTicketID)appWHERE1=1ANDhtly.MasterHuntTypeID=@iMasterHuntTypeIDANDhtly.LicenseYear=@iLicenseYearANDdt.StatusCodeID=@iDrawTicketStatusCodeIDANDdthc.WasDrawn=@iHuntChoiceWasDrawnGROUPBYha.HuntApplicationID,ha.PartyNumber,mht.[Name],htly.LicenseYear,lStatus.[Status],isnull(dbo.udf_GetHuntApplicationPartyCount(ha.HuntApplicationID),0),won.DrawTicketID,dt.PreDrawNonResidentMemberCount,dbo.udf_GetAvgPreferencePoints(dt.DrawTicketID),CASEWHENha.Quantity>1THENNULLELSEdt.PreDrawRandomNumberEND,dsm.[Name],dt.DrawnSequence,dt.PreferencePointRank,dt.DrawID,dt.RandomRankORDERBYhtly.LicenseYearDESC,mht.Name,lStatus.[Status],dt.DrawID,PreferencePointAverageDESC,PreDrawRandomNumber,ha.PartyNumber

静态函数：

CREATEFUNCTION[dbo].[udf_GetAvgPreferencePoints](@DrawTicketIDINT)RETURNSNUMERIC(18,3)ASBEGINRETURN(SELECTTOP1CONVERT(DECIMAL,dt.PreDrawPreferencePointTotal)/NULLIF(CONVERT(DECIMAL,dt.PreDrawMemberCount),0)FROMdbo.DrawTicketdtWHEREdt.DrawTicketID=@DrawTicketID)END

执行时间40s

这是典型可以进行静态函数改写内联表值函数的sql：

改写后：

SELECTTOP1001ha.HuntApplicationID,ha.PartyNumber,mht.NameASMasterHuntTypeName,htly.LicenseYear,lStatus.[Status]ASDrawTicketStatus,--isnull(dbo.udf_GetHuntApplicationPartyCount(ha.HuntApplicationID),0)ASMemberCount,isnull(hapc.MemberCount,0)ASMemberCount,count(won.DrawTicketLicenseID)ASDrawnMemberCount,won.drawticketid,dt.PreDrawNonResidentMemberCountASNRMemberCount,--dbo.udf_GetAvgPreferencePoints(dt.DrawTicketID)ASPreferencePointAverage,app.PreferencePointAveragePreferencePointAverage,CASEWHENha.Quantity>1THENNULLELSEdt.PreDrawRandomNumberENDASPreDrawRandomNumber,dsm.NameASDrawSelectionMethodName,dt.DrawnSequence,dt.PreferencePointRank,dt.DrawID,dt.RandomRankFROMdbo.HuntApplicationhaJOINdbo.HuntTypeLicenseYearhtlyONha.HuntTypeLicenseYearID=htly.HuntTypeLicenseYearIDJOINdbo.MasterHuntTypemhtONhtly.MasterHuntTypeID=mht.MasterHuntTypeIDLEFTJOINdbo.HuntApplicationLicensehalONha.HuntApplicationID=hal.HuntApplicationIDLEFTJOINdbo.DrawTicketdtONha.HuntApplicationID=dt.HuntApplicationIDLEFTJOINdbo.DrawTicketLicensewonONdt.DrawTicketID=won.DrawTicketIDANDwon.WasDrawn=1LEFTJOINdbo.DrawSelectionMethoddsmONdt.DrawSelectionMethodID=dsm.DrawSelectionMethodIDLEFTJOINdbo.StatusCodelStatusONdt.StatusCodeID=lStatus.StatusCodeIDJOINdbo.DrawTicketHuntChoicedthcONdt.DrawTicketID=dthc.DrawTicketIDCROSSAPPLYdbo.tvf_GetHuntApplicationPartyCount(ha.HuntApplicationID)hapcCROSSAPPLYdbo.tvf_GetAvgPreferencePoints(dt.DrawTicketID)appWHERE1=1ANDhtly.MasterHuntTypeID=@iMasterHuntTypeIDANDhtly.LicenseYear=@iLicenseYearANDdt.StatusCodeID=@iDrawTicketStatusCodeIDANDdthc.WasDrawn=@iHuntChoiceWasDrawnGROUPBYha.HuntApplicationID,ha.PartyNumber,mht.[Name],htly.LicenseYear,lStatus.[Status],--isnull(dbo.udf_GetHuntApplicationPartyCount(ha.HuntApplicationID),0),isnull(hapc.MemberCount,0),won.DrawTicketID,dt.PreDrawNonResidentMemberCount,--dbo.udf_GetAvgPreferencePoints(dt.DrawTicketID),app.PreferencePointAverage,CASEWHENha.Quantity>1THENNULLELSEdt.PreDrawRandomNumberEND,dsm.[Name],dt.DrawnSequence,dt.PreferencePointRank,dt.DrawID,dt.RandomRankORDERBYhtly.LicenseYearDESC,mht.Name,lStatus.[Status],dt.DrawID,PreferencePointAverageDESC,PreDrawRandomNumber,ha.PartyNumber

对应的表值函数：

CREATEFUNCTION[dbo].[tvf_GetAvgPreferencePoints](@DrawTicketIDINT)RETURNSTABLEWITHSCHEMABINDINGASRETURN(SELECTTOP1CONVERT(DECIMAL,dt.PreDrawPreferencePointTotal)/NULLIF(CONVERT(DECIMAL,dt.PreDrawMemberCount),0)asPreferencePointAverageFROMdbo.DrawTicketdtWHEREdt.DrawTicketID=@DrawTicketID)GO

改写后执行时间从40s降低到16s，对于倾斜列的优化速度更为明显

感谢各位的阅读，以上就是“SQL Server中怎么改写内联表值函数”的内容了，经过本文的学习后，相信大家对SQL Server中怎么改写内联表值函数这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！