Transact-SQL User-Defined Functions by Andrew Novick

480 Pages·2003·3.19 MB·English

Transact-SQL
User-Defined
Functions
Andrew Novick
Wordware Publishing, Inc.
LibraryofCongressCataloging-in-PublicationData
Novick,AndrewN.
Transact-SQLuser-definedfunctions/byAndrewNovick.
p. cm.
Includesbibliographicalreferencesandindex.
ISBN1-55622-079-0(pbk.)
1. SQLserver. 2. Databasemanagement. I.Title.
QA76.9.D3N6952003
005.75'85--dc22 2003020942
CIP
©2004,WordwarePublishing,Inc.
AllRightsReserved
2320LosRiosBoulevard
Plano,Texas75074
Nopartofthisbookmaybereproducedinanyformorbyanymeans
withoutpermissioninwritingfromWordwarePublishing,Inc.
PrintedintheUnitedStatesofAmerica
ISBN1-55622-079-0
10 9 8 7 6 5 4 3 2 1
0309
CrystalReportsisaregisteredtrademarkofCrystalDecisions,Inc.intheUnitedStatesand/orothercountries.
NamesofCrystalDecisionsproductsreferencedhereinaretrademarksorregisteredtrademarksofCrystalDecisionsor
its
Transact-SQLisatrademarkofSybase,Inc.oritssubsidiaries.
SQLServerisatrademarkofMicrosoftCorporationintheUnitedStatesand/orothercountries.
Allbrandnamesandproductnamesmentionedinthisbookaretrademarksorservicemarksoftheirrespectivecompanies.
Anyomissionormisuse(ofanykind)ofservicemarksortrademarksshouldnotberegardedasintenttoinfringeonthe
propertyofothers.Thepublisherrecognizesandrespectsallmarksusedbycompanies,manufacturers,anddevelopersas
ameanstodistinguishtheirproducts.
Thisbookissoldasis,withoutwarrantyofanykind,eitherexpressorimplied,respectingthecontentsofthisbookandany
disks or programs that may accompany it, including but not limited to implied warranties for the book’s quality,
performance,merchantability,orfitnessforanyparticularpurpose.NeitherWordwarePublishing,Inc.noritsdealersor
distributorsshallbeliabletothepurchaseroranyotherpersonorentitywithrespecttoanyliability,loss,ordamagecaused
orallegedtohavebeencauseddirectlyorindirectlybythisbook.
AllinquiriesforvolumepurchasesofthisbookshouldbeaddressedtoWordwarePublishing,
Inc.,attheaboveaddress.Telephoneinquiriesmaybemadebycalling:
[phone]
Tomyparents,ToniandLarryNovick
This page intentionally left blank.
Contents
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . xv
PartI—CreatingandUsingUser-DefinedFunctions . . . . . 1
1 OverviewofUser-DefinedFunctions . . . . . . . . . . . . . 3
IntroductiontoUDFs . . . . . . . . . . . . . . . . . . . . . . . . . . 4
ScalarUDFs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
InlineUser-DefinedFunctions . . . . . . . . . . . . . . . . . . . . 9
MultistatementTable-ValuedUser-DefinedFunctions . . . . . . . 12
WhyUseUDFs? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Reuse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
OrganizingCodethroughModularization. . . . . . . . . . . . . . 19
EaseofCoding. . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
WhyNotUseUDFs? . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
2 ScalarUDFs. . . . . . . . . . . . . . . . . . . . . . . . . . 23
Creating,Dropping,andAlteringScalarUDFs. . . . . . . . . . . . . 25
PermissionstoUseCREATE/DROP/ALTERFUNCTION . . . . 25
UsingtheCREATEFUNCTIONStatement . . . . . . . . . . . . 28
TheFunctionBody . . . . . . . . . . . . . . . . . . . . . . . . . 31
DeclaringLocalVariables(IncludingTABLEs) . . . . . . . . . 31
Control-of-flowStatementsandCursors. . . . . . . . . . . . . 33
UsingSQLDMLinScalarUDFs. . . . . . . . . . . . . . . . . 34
AddingtheWITHClause . . . . . . . . . . . . . . . . . . . . . . 36
SpecifyingWITHENCRYPTION . . . . . . . . . . . . . . . . 36
SpecifyingWITHSCHEMABINDING. . . . . . . . . . . . . . 39
UsingScalarUDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
GrantingPermissiontoUseScalarUDFs. . . . . . . . . . . . . . 44
UsingScalarUDFsinSQLDML . . . . . . . . . . . . . . . . . . 45
UsingScalarUDFsintheSelectList . . . . . . . . . . . . . . 45
UsingScalarUDFsintheWHEREandORDERBYClauses. . 47
UsingScalarUDFsintheONClauseofaJOIN . . . . . . . . . 49
UsingScalarUDFsinINSERT,UPDATE,andDELETE
Statements . . . . . . . . . . . . . . . . . . . . . . . . . . 50
UsingScalarUDFsinSETStatements . . . . . . . . . . . . . 50
UsingScalarUDFsinEXECUTEandPRINTStatements . . . 51
v
Contents
UsingScalarUDFsinSQLDDL . . . . . . . . . . . . . . . . . . 52
UsingScalarUDFsinCHECKConstraints . . . . . . . . . . . 53
UsingScalarUDFsinComputedColumns . . . . . . . . . . . 56
CreatingIndexesonComputedColumnswithUDFs . . . . . . 58
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
3 WorkingwithUDFsintheSQLServerTools . . . . . . . . . 63
QueryAnalyzer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
DebuggingUDFs . . . . . . . . . . . . . . . . . . . . . . . . . . 65
CreatingUDFswithTemplates . . . . . . . . . . . . . . . . . . . 70
SQLProfiler. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
TryingtoGenerateYourOwnTraceEventsfromaUDF. . . . . . 79
EnterpriseManagerandOtherTools. . . . . . . . . . . . . . . . . . 80
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
4 YouCan’tDoThatinaUDF. . . . . . . . . . . . . . . . . . 83
RestrictiononInvokingNondeterministicFunctions . . . . . . . . . 84
TaketheNondeterministicValueasaParameter. . . . . . . . . . 85
UsetheViewTrick . . . . . . . . . . . . . . . . . . . . . . . . . 86
RestrictionsonDataAccess . . . . . . . . . . . . . . . . . . . . . . 88
RestrictionsontheEXECUTEStatement. . . . . . . . . . . . . . . 88
RestrictiononAccesstoTemporaryTables . . . . . . . . . . . . . . 89
RestrictiononReturningMessages . . . . . . . . . . . . . . . . . . 90
RestrictionsonSettingConnectionOptions . . . . . . . . . . . . . . 91
NoSETCommandsAllowed . . . . . . . . . . . . . . . . . . . . 91
TheExecutionEnvironmentofaUDF . . . . . . . . . . . . . . . 92
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
5 HandlingRun-timeErrorsinUDFs. . . . . . . . . . . . . . 99
ErrorHandlingInsideUDFs . . . . . . . . . . . . . . . . . . . . . 100
TestingParametersandReturningNULL . . . . . . . . . . . . . . 106
ReturningSpecialValues . . . . . . . . . . . . . . . . . . . . . . . 107
CausingUnrelatedErrorsinUDFs . . . . . . . . . . . . . . . . . . 108
ReportingErrorsinUDFsOuttheBackDoor . . . . . . . . . . . . 109
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
6 Documentation,Formatting,andNamingConventions . . 113
SeparatorFirstFormatting . . . . . . . . . . . . . . . . . . . . . . 115
HeaderComments. . . . . . . . . . . . . . . . . . . . . . . . . . . 118
*description. . . . . . . . . . . . . . . . . . . . . . . . . . . 120
*RelatedFunctions. . . . . . . . . . . . . . . . . . . . . . . 120
*Attribution. . . . . . . . . . . . . . . . . . . . . . . . . . . 120
*MaintenanceNotes . . . . . . . . . . . . . . . . . . . . . . 120
*Example. . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
*Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
*TestScript. . . . . . . . . . . . . . . . . . . . . . . . . . . 122
vi
Contents
*History. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
*Copyright . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
What’sNotintheHeader . . . . . . . . . . . . . . . . . . . . . 122
*Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . 122
*AlgorithmandFormulas. . . . . . . . . . . . . . . . . . . . 123
NamingConventions . . . . . . . . . . . . . . . . . . . . . . . . . 123
NamingUser-DefinedFunctions. . . . . . . . . . . . . . . . . . 123
NamingColumns. . . . . . . . . . . . . . . . . . . . . . . . . . 129
DomainNames. . . . . . . . . . . . . . . . . . . . . . . . . . . 129
NamingFunctionParametersandLocalVariables. . . . . . . . . 131
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
7 InlineUDFs . . . . . . . . . . . . . . . . . . . . . . . . . 133
ManagingPermissionsonInlineUDFs. . . . . . . . . . . . . . . . 133
PermissionstoCreate,Alter,andDropInlineUDFs . . . . . . . 134
PermissiontoUseInlineUDFs . . . . . . . . . . . . . . . . . . 134
CreatingInlineUDFs . . . . . . . . . . . . . . . . . . . . . . . . . 134
TemplateforInlineUDFs . . . . . . . . . . . . . . . . . . . . . 136
CreatingaSampleInlineUDF . . . . . . . . . . . . . . . . . . . 137
RetrievingDatawithInlineUDFs. . . . . . . . . . . . . . . . . . . 138
SortingDatainInlineUDFs. . . . . . . . . . . . . . . . . . . . . . 140
UpdatableInlineUDFs . . . . . . . . . . . . . . . . . . . . . . . . 141
UsingInlineUDFsforPagingWebPages. . . . . . . . . . . . . . . 146
RetrievingMinimalDataforEachWebPage . . . . . . . . . . . 146
CreatingtheInlineUDF . . . . . . . . . . . . . . . . . . . . . . 147
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
8 MultistatementUDFs . . . . . . . . . . . . . . . . . . . . 155
ManagingPermissionsonMultistatementUDFs. . . . . . . . . . . 156
PermissionstoCreate,Alter,andDropMultistatementUDFs . . 156
PermissiontoSelectonMultistatementUDFs . . . . . . . . . . 156
CreatingandUsingMultistatementUDFs . . . . . . . . . . . . . . 157
TemplateforMultistatementUDFs . . . . . . . . . . . . . . . . 161
UsingCursorsinUDFswithaTemplate. . . . . . . . . . . . . . 163
ListManagement . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
CreatingTablesfromDelimitedText . . . . . . . . . . . . . . . 165
TurningTablesintoDelimitedText . . . . . . . . . . . . . . . . 169
UsingUDFstoReplaceCodeTables. . . . . . . . . . . . . . . . 172
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
9 MetadataaboutUDFs. . . . . . . . . . . . . . . . . . . . 175
SystemStoredProcedurestoHelpwithUDFs. . . . . . . . . . . . 176
sp_help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
sp_helponaScalarUDF . . . . . . . . . . . . . . . . . . . . 177
sp_helponanInlineUDF. . . . . . . . . . . . . . . . . . . . 177
sp_helponMultistatementUDFs. . . . . . . . . . . . . . . . 179
vii
Contents
sp_helptext. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
sp_rename . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
sp_depends. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
RetrievingMetadataaboutUDFs. . . . . . . . . . . . . . . . . . . 183
FindingoutaboutUDFsinINFORMATION_SCHEMA . . . . . 183
INFORMATION_SCHEMA.ROUTINES. . . . . . . . . . . . 184
INFORMATION_SCHEMA.ROUTINE_COLUMNS . . . . . 185
INFORMATION_SCHEMA.PARAMETERS . . . . . . . . . 185
Built-inMetadataFunctions . . . . . . . . . . . . . . . . . . . . 186
InformationaboutUDFsinSystemTables . . . . . . . . . . . . 187
MetadataUDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
FunctionInformation. . . . . . . . . . . . . . . . . . . . . . . . 189
WhatAretheColumnsReturnedbyaUDF? . . . . . . . . . . . 190
WhatAretheParametersUsedWhenInvokingaUDF? . . . . . 192
MetadataFunctionsthatWorkonAllObjects . . . . . . . . . . . 193
SQL-DMO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
10 UsingExtendedStoredProceduresinUDFs . . . . . . . . 197
Whichxp_sCanBeUsedinaUDF? . . . . . . . . . . . . . . . . . 198
xp_logevent. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
xp_sprintf. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
sp_OA*andOLEAutomation:TheKeystotheVault . . . . . . . . 210
PermissionstoUsesp_OA* . . . . . . . . . . . . . . . . . . . . 210
PickingtheBestUsesofOLEAutomation . . . . . . . . . . . . 211
InvokingaCOMObject . . . . . . . . . . . . . . . . . . . . . . 212
BreakingDownanHRESULT . . . . . . . . . . . . . . . . . . . 215
LoggingOLEAutomationErrors . . . . . . . . . . . . . . . . . 219
CreatingaUsefulOLEAutomationUDF . . . . . . . . . . . . . 221
EncapsulatingPropertiesandMethodswithUDFs . . . . . . . . 223
InvokingYourOwnOLEObjects . . . . . . . . . . . . . . . . . 223
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
11 TestingUDFsforCorrectnessandPerformance . . . . . . 229
EmbeddingTestsintheHeaderoftheUDF . . . . . . . . . . . . . 230
TestScripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
DrillingDownintothePerformanceProblem . . . . . . . . . . . . 235
TheFunctionandtheTemplateforTestingPerformance. . . . . 236
ConstructingaLargeTestTable. . . . . . . . . . . . . . . . . . 239
ExperimentingwithUDFvs.ExpressionPerformance. . . . . . 242
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
12 ConvertingbetweenUnitSystems . . . . . . . . . . . . . 247
GettingConversionFactorsfortheMetricSystem. . . . . . . . . . 248
DesignIssuesforUnitConversions. . . . . . . . . . . . . . . . . . 250
ScalingtheResults. . . . . . . . . . . . . . . . . . . . . . . . . 253
viii
Contents
CombiningScaleandPrecision. . . . . . . . . . . . . . . . . . . 253
WritingtheConversionFunctions . . . . . . . . . . . . . . . . . . 255
ConvertingMeasurementstheSimpleWay. . . . . . . . . . . . 255
AddingtheChoiceofOutputUnits. . . . . . . . . . . . . . . . . 258
Anything-to-AnythingConversions . . . . . . . . . . . . . . . . 260
PuttingtheUnitConversionFunctionstoWork . . . . . . . . . . . 262
WhatIsEqual?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266
TestingNumbersforEquality . . . . . . . . . . . . . . . . . . . 268
ReducingBugReportsDuetoNumericRounding. . . . . . . 274
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275
13 CurrencyConversion . . . . . . . . . . . . . . . . . . . . 277
BackgroundonVariableExchangeRates . . . . . . . . . . . . . . . 278
DesignIssuesforCurrencyConversion . . . . . . . . . . . . . . . 279
CreatingtheSchematoSupporttheFunctions . . . . . . . . . . 279
PickingDataTypesforAmountsandExchangeRates . . . . . . 280
ReturningaMeaningfulResultWhenDataIsMissing. . . . . 281
WritingtheFunctionsforCurrencyConversion . . . . . . . . . . . 283
UsingCurrencyConversionFunctions . . . . . . . . . . . . . . . . 289
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291
PartII—SystemUser-DefinedFunctions . . . . . . . . . . 293
14 IntroductiontoSystemUDFs . . . . . . . . . . . . . . . . 295
DistinguishingSystemUDFsfromOtherFunctions . . . . . . . . . 296
NamingRequirementsforSystemUDFs . . . . . . . . . . . . . 297
Location,Location,Location. . . . . . . . . . . . . . . . . . . . 297
ReferencingSystemUDFs. . . . . . . . . . . . . . . . . . . . . 298
PayNoAttentiontotheManBehindtheUDFCurtain . . . . . . 299
DocumentedSystemUDFs. . . . . . . . . . . . . . . . . . . . . . 300
Special-purposeSystemUDFs . . . . . . . . . . . . . . . . . . . . 302
fn_helpcollations . . . . . . . . . . . . . . . . . . . . . . . . . . 302
fn_virtualservernodes . . . . . . . . . . . . . . . . . . . . . . . 304
fn_servershareddrives . . . . . . . . . . . . . . . . . . . . . . . 304
fn_get_sql. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305
ViewingaConnection’sSQLtheOldWay . . . . . . . . . . . 305
Callingfn_get_sql . . . . . . . . . . . . . . . . . . . . . . . . 306
AnExampleofUsingfn_get_sql . . . . . . . . . . . . . . . . 308
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
15 DocumentingDBObjectswithfn_listextendedproperty . . 313
UnderstandingandCreatingExtendedProperties . . . . . . . . . . 314
MaintainingExtendedProperties . . . . . . . . . . . . . . . . . 314
MaintainingMS_DescriptionUsingEnterpriseManager. . . . . 316
Usingfn_listextendedproperty . . . . . . . . . . . . . . . . . . . . 317
TheProblemwithNULLArgumentstofn_listextendedproperty 320
ix
Contents
UnderstandingNULLArgumentstofn_listextendedproperty . . 322
Task-orientedUDFsthatUsefn_listextendedproperty . . . . . . . 324
FetchinganExtendedPropertyforAllTables. . . . . . . . . . . 324
FetchinganExtendedPropertyforAllColumns . . . . . . . . . 328
FindingMissingTableDescriptions . . . . . . . . . . . . . . . . 331
ReportingonAllTables . . . . . . . . . . . . . . . . . . . . . . 333
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335
16 Usingfn_virtualfilestatstoAnalyzeI/OPerformance . . . 337
Callingfn_virtualfilestats . . . . . . . . . . . . . . . . . . . . . . . 338
GettingSupportingInformation. . . . . . . . . . . . . . . . . . . . 339
MetadataFunctionstoGetDatabaseandFileIDs. . . . . . . . . 339
ConvertingLogicalFileNamestoPhysicalFileNames. . . . . . 341
What’sinsysfiles?. . . . . . . . . . . . . . . . . . . . . . . . 341
What’sinmaster..sysaltfiles?. . . . . . . . . . . . . . . . . . 342
HowLongHastheInstanceBeenUp? . . . . . . . . . . . . . . 344
StatisticsfortheSQLServerInstance . . . . . . . . . . . . . . . . 346
SummarizingtheFileStatisticswithUDFs. . . . . . . . . . . . . . 347
GettingStatisticsforaSingleDatabase . . . . . . . . . . . . . . 348
GettingStatisticsbyDatabase . . . . . . . . . . . . . . . . . . . 349
BreakingDowntheI/OStatisticsbyPhysicalFile. . . . . . . . . 350
I/OStatisticsbyDiskDrive . . . . . . . . . . . . . . . . . . . . 352
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353
17 fn_trace_*andHowtoCreateandMonitorSystemTraces. 355
ScriptingTraces . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357
fn_trace_getinfo . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360
udf_Trc_InfoTAB. . . . . . . . . . . . . . . . . . . . . . . . . . 363
PivotingDatatoCreatetheColumns . . . . . . . . . . . . . . . 364
MiningBitsfromtheTraceOptionsField . . . . . . . . . . . . . 366
StoppingTraces. . . . . . . . . . . . . . . . . . . . . . . . . . . 366
fn_trace_gettable . . . . . . . . . . . . . . . . . . . . . . . . . . . 370
fn_trace_geteventinfo . . . . . . . . . . . . . . . . . . . . . . . . . 372
MakingaListofEvents . . . . . . . . . . . . . . . . . . . . . . 374
AdditionalUDFsBasedonfn_trace_geteventinfo. . . . . . . . . 376
fn_trace_getfilterinfo . . . . . . . . . . . . . . . . . . . . . . . . . 377
ConvertingOperatorCodestoReadableText . . . . . . . . . 377
SampleOutputfromfn_trace_getfilterinfo . . . . . . . . . . . 378
ConvertingtheFilterintoaWHEREClause. . . . . . . . . . 379
ReportingonAllRunningTraces . . . . . . . . . . . . . . . . . . . 384
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386
18 UndocumentedSystemUDFs . . . . . . . . . . . . . . . . 387
ListingAllSystemUDFs . . . . . . . . . . . . . . . . . . . . . . . 388
SourceCodefortheUndocumentedSystemUDFs . . . . . . . . . 389
SelectedUndocumentedSystemUDFs. . . . . . . . . . . . . . . . 392
x