index...

Index

965

AAbort method, 34AbstractCollectionBase property, 800Access

client, 22–24in-process. See In-process data accesssecurity for, 269–270tracing. See Tracing data access

Access control lists (ACLs), 567–568Access modifiers, 908–909Accessors in XQuery, 462–463Account Lockout Duration setting, 236–237Account Lockout policies, 236–237Account Lockout Threshold setting, 236–237Accumulate method, 213–214, 217ACID properties in transactions, 128, 278, 500ACLs (access control lists), 567–568Activation in service programs, 504–509Active Directory Users and Computers

tool, 236Activity Monitor, 782Add Database Reference dialog box, 952–953Add Reference dialog box

for SMO projects, 774for Visual Studio, 955–956

Add Table dialog box, 935Add Web Reference wizard

for Visual Studio, 597–598, 619–620, 624for Webmethods, 573–575for WSDL, 579, 600–601, 603

Additionin LDim, 182–183in SqlTypes, 87–88

ADDR_TAB data type, 10ADDRESS data type, 10ADDRESS option for routes, 548AddSubscriber method, 863AddSubscriberDevice method, 864AddSubscription method, 865–867AddUser function, 93–94ADFs (application definition files),

839–842Adjacent Broker Protocol, 545, 550ADO, 22–24

vs. SMO, 767–769UDTs in, 654–656for XDR schemas, 409–410XML data type in, 664–667

ADO.NET API, 641–644best practices, 890multiple active resultsets in, 691–695SqlClient in. See SqlClientUDTs in, 647–648XML data type in, 657–659

AdoNetDiag, 742Advanced Data Tablegrams (ADTG), 22after keyword, 472Aggregate functions

with PIVOT, 361user-defined, 207–209

best practices, 885creating, 214–215implementing, 209–214

in XQuery, 464Aggregate method, 210Aggregate partitions, 374–376

dotNET_Index 4/6/06 12:14 PM Page 965

Alerts provider, 826Alias types, 11ALLOW_SNAPSHOT_ISOLATION

option, 283AllowPartiallyTrustedCallers attribute, 274ALTER ASSEMBLY statement, 56–58,

185–187, 262, 264, 271, 957ALTER AUTHORIZATION statement, 263ALTER CERTIFICATE statement, 260ALTER DATABASE statement, 513ALTER ENDPOINT statement, 223, 552,

564, 627ALTER LOGIN statement, 226, 237–238Alter method, 802–803ALTER QUEUE statement, 505, 508, 542ALTER ROUTE statement, 547–548ALTER TRACE permission, 225ALTER TRIGGER statement, 292ALTER XML SCHEMA COLLECTION

statement, 399AlterImpl method, 802Analysis Services event provider, 825Anchors in recursive queries, 352ANSI SQL standard compliance for XML,

422–426ANY direction for contracts, 529APIs, 22–24AppDomains

loading, 40–43protection by, 7threads in, 43, 748unloading, 35–37

AppendToFile option, 809Appids, 950Application definition files (ADFs), 839–842Application roles, 229<Application> section, 839ApplicationDefinitionFilePath entry, 836, 854ApplicationDefinitionFileSchema.xsd

schema, 835, 839ApplicationName entry, 836Applications

critical, 28hosting. See Hostingmessaging, 481–493service-oriented. See Service-oriented

database applicationsAPPLY operators, 340–343<Arguments> node, 856Array data type, 11

ArrayList class, 755as first keywords, 470as last keywords, 470AS HTTP clause, 565, 570, 573, 608AS TCP clause, 552ASIN function, 154ASP.NET

for notifications, 672–673, 682SqlCacheDependency class in, 685–687for streams, 631for Webmethods, 573

Assemblies, 903–905altering, 56–58AppDomains for, 41code access security for, 270–274dependencies in, 49–51dropping, 55–56loading, 38ownership of, 51–55permissions for, 48–50, 262–268runtime hosting for, 29SGen for, 189–191SMO, 772, 774–775storing, 46–49

Assembly language, 900AssemblyInfo.cs module, 954AssemblyName property, 189Asymmetric keys, 239–243, 245, 247Asynchronous messages, 24–25Asynchronous support in ADO.NET,

743–748ATLTRACE macro, 731Atomic values in XQuery, 432Atomicity in ACID properties, 128, 278Attach to Process dialog box, 961–962Attribute-centric mapping in XML, 15Attributes

in complex types, 9dangerous, 45for stored procedures, 75–77for UDTs, 13in Visual Studio, 957–961in XML, 15, 479in XQuery, 439, 441

Audit object, 763–764AUTHENTICATE privilege, 261Authentication

in security process, 224–231for SMO connections, 778for Web services, 626–627

Index966


AUTHORIZATION parameter, 263AUTO mode for XML, 403–406AutoCreatedLocal route, 547AutoDisconnectMode property, 787Automatic operations

SMO connections, 780–782Visual Studio deployment, 956–958

Autonomous transactions, 132–133AVG function, 14, 156, 207Axes in XQuery, 439–441

BBackgroundWorker class, 748Backup object model, 763BadInsert stored procedure, 314–315Base class libraries (BCLs), 2

in exceptional handling, 34loading, 38

Base classes in ADO.NET, 705–709BaseDirectoryPath entry, 836Basic authentication, 626–627Batch processing

batch-enabled Web Services, 622–626in Service Broker, 504in SQL, 622–626

BatchCommands element, 623BCLs (base class libraries), 2

in exceptional handling, 34loading, 38

BCP utility, 749before keyword, 472BEGIN CATCH statement, 314, 321–322BEGIN CONVERSATION TIMER

statement, 515BEGIN DIALOG CONVERSATION

statement, 486–489, 510for broker instances, 545–547contracts in, 530for conversation groups, 518for endpoints, 497formats in, 492for lifetime, 514

BEGIN TRANSACTION statement, 129, 517BEGIN TRY phrase, 314BeginCriticalRegion method, 34BeginExecuteReader method, 744BeginTransaction method, 128–131Behaviors for types, 9Benchmarks, 28Best practices

service-oriented database applications,882–891

transactions, 133–135XML DML, 474–475

BINARY data type with XML, 387, 389,415–416

Binary representationbyte order in, 168of dates, 154–155IBinarySerialize, 167–170in LDim data type, 165–170

Binding problems, 740–742Bit data type, 416BizTalk program, 410BLOB data type, 11Blocking points in thread scheduling, 34Bodies in XQuery, 437–446BOOLEAN data type

inclusion of, 10–11for XQuery functions, 463

Broker instances, 544–548Broker_Queue_Disabled event, 541Buffer pools, 33Bulk import in SqlClient, 749–752BULK INSERT statement, 301, 420–421,

749, 751Bulk load

in SQLXML, 630–631, 636–638in XML, 420

BULK Provider, 301Business Intelligence Development Studio,

949–950Business transactions, 499–503

compensation in, 541–544contracts in, 528–531conversation groups in, 516–524conversations in, 509–515distribution in, 544–559message types in, 524–528, 530–534poison messages in, 540–541processing loops in, 534–539send types in, 530–534service programs, 503–509

Byte ordering, 20, 168

CC# language, null values in, 78–79Cache class, 672, 682, 685Cached execution plans, 302CacheDependency class, 685

Index 967


CachesDataSet, 755dispatching notifications to, 677–679

CalculateCipherLen function, 244–245Cancel method, 123CapturedSql property, 814Cartesian products, 340, 447Case-sensitivity

in CLR, 66in XQuery, 436

CAST operatorin SqlTypes, 83, 87–88for UDTs, 12in XML, 387, 389–390

Casting functions in XQuery, 463Categories

metadata, 721–722security, 39

CEILING function, 154Certificates, 220, 258

in code signing, 259–261in encryption, 239–241, 243, 702–703

Chaining, ownership, 232–235, 267, 274ChangePassword method

in ServerConnection, 784–785in SqlConnection, 120, 699–700

CHAR data typein messaging applications, 493in SqlTypes, 92in Visual Studio, 959in XML, 387

Character-based data types in XML, 415–416Charts of accounts, 351–353CHECK constraints, 386–387CHECK_EXPIRATION option, 236CHECK_POLICY option, 236CheckDirectInput method, 184–185, 198CheckRangeAndAccuracy method, 164,

184–185, 198–199Child axes in XQuery, 439Children in DependencyTree, 813<ChronicleRule> node, 858Chronicles and chronicle tables, 828, 830,

857–858CIM (Common Information Model)

repository, 816–817Class Library project type, 951, 955Classes

in CLR type system, 907–908loading, 2

nesting, 97–98visibility of, 59

Clients, 641best practices, 888–890data types for, 645–646database access by, 22–24encryption support in, 702–703failover support in, 701–702large value data types on, 667–670multiple active resultsets in, 689–695native, 641–645password change support in, 699–701Query Notification support in. See Query

Notification supportstored procedures in, 703transactions in, 695–699UDTs for, 646

from DataReaders, 648–654.NET Framework, 647–648in ODBC, OLE DB, and ADO,

654–656XML data type on, 657

in ADO, 664–667in ADO.NET, 657–659documents, fragments, and FOR XML

support, 661–664as string, 659–661

CLOB data type, 11Close method, 120, 664CLR (Common Language Runtime)

languages, 61, 897–900for extensions, 61–63

basics, 63–74details, 74–77

hosting feature in, 27parameters and return values in, 93SqlTypes in, 77–92stored procedures in, 106–109string operations in, 70–74table-valued functions in, 99–106triggers in, 109type system in, 905–907

classes, 907–908instances, 910–912namespaces, 912–914visibility, 908–909

user-defined functions in, 93–99CLS (Common Language Specification), 3cmdlets, 766CoCreateInstance method, 712

Index968


Codeaccess security, 2, 269–270executing, 959–962loading, 37–39safe, 43–45signing, 259–261

COLLATE keyword, 385Collect method, 918COLLECTION metadata information, 667COM (Component Object Model), 20–21, 642Comma-separated-value (CSV) files, 734–735Command class for providers, 706–707CommandNotification keyword, 687CommandStream property, 631CommandText property, 114

in DeleteCommand, 754in SqlCommand, 112, 673

comment function, 407COMMIT statement, 517Common class for providers, 708Common Information Model (CIM)

repository, 816–817Common Language Runtime. See CLR

(Common Language Runtime)languages

Common Language Specification (CLS), 3Common namespace, 778Common object request broker architecture

(CORBA), 20Common table expressions (CTEs)

in recursive queries, 351–359working with, 343–350

Common Type System (CTS), 905–906CompareAllSearchableValues option, 757CompareRowVersion option, 758Comparisons

in LDim, 172in SqlTypes, 88–89UDTs, 12in XML, 389in XQuery, 443, 464

Compensation in transactions, 501, 503,541–544

CompilationJust-In-Time, 7, 900statement-level, 301–303

Complete method, 129Complex data types, 9–10ComplexNumber data type, 632Component Object Model (COM), 20–21, 642

ComposeTx method, 134Composing transactions, 130Composition functionality, 414–415Concatenation, 88, 254Concurrent transaction processing, 501–502Condition action, 870–871Condition class, 866Configuration

for Notification Services, 834–839for SMO, 815–817for SSMS, 925–927

ConfigurationFileSchema.xsd schema, 835ConflictOption property, 757Connect method, 778–781, 785–786Connect to Server dialog box, 930ConnectAsUser property, 782Connection class, 706–707connection strings, 715–717ConnectionContext, 788ConnectionInfo assembly, 772, 774Connections

for in-process data access, 118–121for providers, 706–707in SMO, 778

automatic, 780–782default Windows credentials, 778–780managing, 785–787Runas utility, 780–781SQL Server login, 783–785Windows identity impersonation, 782–783

in SSMS, 928–931in Web Services, 562

ConnectionStringBuilder class, 715ConnectionStrings collection, 647ConnectionStringSettings class, 713ConnectionStringsSettingsCollection

class, 713Consistency in ACID properties, 128, 278Constraints in XML, 385–387Constructors

defined, 914in XQuery, 462–464

Content formatters, 825–826, 872CONTENT keyword for schema

collections, 396ContentFormatter node, 872Context functions in XQuery, 462–463Contexts, execution

for procedural code, 253–259in security, 232–235

Index 969


Contractsin business transactions, 528–531in messaging applications, 492

conversation_group_id, 516, 518–520, 523Conversation groups, 25, 516–524Conversation handles

in business transactions, 516, 523, 530in message processing, 496–497

conversation_id, 520Conversation timers, 514–515Conversations

in business transactions, 509–515in messaging applications, 486, 492, 497

Conversionsfor messages, 526, 534SqlString, 86SqlTypes, 82–86XML, 387, 389–390

CONVERT functionfor messages, 526, 534for XML, 387, 389–390

Cookieswith EXECUTE AS, 256for sp_unsetapprole, 229–230

Cooperative thread scheduling, 33CORBA (common object request broker

architecture), 20CorBindToRuntimeEx method, 29Correlating messages, 502CORRESPONDING BY clause, 327–328COUNT function, 14, 207COUNT_BIG function, 207Counters in SNAPSHOT isolation, 289CREATE AGGREGATE statement, 214CREATE APPLICATION ROLE statement,

249–250CREATE ASSEMBLY statement, 46–49

permissions for, 262–264security levels in, 43, 270–273, 957for SGen assemblies, 190

CREATE ASYMMETRIC KEYstatement, 265

Create command in NSControl,843–845, 868

CREATE CONTRACT statement, 524, 529CREATE ENDPOINT statement

for batches, 622–623for declarations, 564sections for, 569security for, 222, 567–568

for sessions, 627SSL for, 608for state, 571system views for, 552URL for, 570for Web services, 615for Webmethods, 573

CREATE EVENT NOTIFICATIONstatement, 295, 509

CREATE FUNCTION statement, 66CREATE LOGIN statement, 226–227, 236CREATE MASTER KEY statement, 239CREATE MESSAGE TYPE statement, 524Create method, 802–803CREATE PROCEDURE statement, 114CREATE QUEUE statement, 486, 491–492,

505, 507, 541CREATE ROLE statement, 250CREATE ROUTE statement, 547–548, 553CREATE RULE statement, 11CREATE SCHEMA statement, 249CREATE SERVICE statement, 486,

529–530CREATE SYNONYM statement, 253CREATE TABLE statement

for Event Notifications, 297ON DELETE and ON UPDATE clauses,

333–336for schemas, 250

CREATE TRIGGER statement, 289–290CREATE TYPE statement, 155, 170, 185,

267, 651CREATE USER statement, 228, 249CREATE VIEW statement, 675CREATE XML SCHEMA COLLECTION

statement, 394, 399–400, 528CreateCommand method, 121CreateImpl method, 802CreateReader method, 662Credentials, 235–238Critical applications, 28CriticalFinalizerObject class, 37CROSS APPLY operator

for joining sets, 340–343for nodes, 458–460

CSV (comma-separated-value) files, 734–735CTEs (common table expressions)


CTS (Common Type System), 905–906

Index970


Current propertyin IEnumerator, 99–100in Transaction class, 149

Cursorless mode in clients, 689

DDangerous attributes, 45Data accessor functions in XQuery, 463Data Definition Language (DDL)

statements, 446for code loading, 37triggers in, 289–294

Data Links API, 630Data manipulation language (DML),

468–475Data protection API (DPAPI), 239Data providers, 111Data source enumerations, 714–715Data Source property, 715Data type compatibility mode, 645–646Data types, 8

for clients, 645–646defined, 914SqlTypes, 77–92SQLXML, 632–634user-defined. See User-defined

types (UDTs)Visual Studio, 958–959XML. See XML and XML data typein XQuery, 443in XSD, 16

DataAccess property, 76, 95–96DataAccessKind attribute, 117DataAdapter class, 708Database Lister application, 773–778Database master keys, 239–241, 245Database Object Explorer, 923Database transactions, 500DatabaseCollection class, 770–771DatabaseOptions, 804Databases property, 769–770, 777DataColumns, 755–756DATALENGTH function, 386DATALINK data type, 10–11DataReader class

for providers, 706–707UDTs from, 648–654

DataRows, 755DataSet class, 614, 620, 753–758

DataSourceInformation collection, 721,724–727

DataTables, 755DataTypes metadata category, 722Dates

representation of, 153–155in XML, 417–418

DATETIME data type, 153–155db_owner role, 229DBCC CHECKTABLE command, 57DbCommand class, 151DbCommandBuilder class, 720DbConnection class, 151, 711, 713DbConnectionStringBuilder class,

715–717DbDataReader class, 708, 710DbDataSourceEnumerator class, 714DBDROPSET_SS_ROWSET property, 676DbMetaDataCollectionNames class, 721DbMetaDataFactory class, 726, 728–729dbo.find_expensive stored procedure, 5DbParameter class, 652DBPROPSET_SQLSERVER_PARAMETERS

property, 656DbProviderFactories class, 710DbProviderFactory class, 709–711, 714DBSCHEMA_COLUMNS property, 656DBSCHEMA_PROCEDURE_PARAMETERS

property, 656DBSCHEMA_SQL_ASSEMBLIES

property, 656DBSCHEMA_SQL_ASSEMBLY_

DEPENDENCIES property, 656DBSCHEMA_SQL_USER_TYPES

property, 656DBSCHEMA_XML_COLLECTIONS

property, 667DBTYPE_BYTES data type, 654DbType class, 658, 669DBTYPE_UDT data type, 646DBTYPE_WSTR data type, 654DBTYPE_XML data type, 632DCOM systems, 21, 485DDL (data definition language)

statements, 446for code loading, 37triggers in, 289–294

DDL_DATABASE_LEVEL_EVENTShierarchy, 290

Deadlock errors, 320–321

Index 971


Debuggingparameter binding problems, 740–742in Visual Studio, 954, 957, 959–962

DECIMAL data typeprecision of, 756in Visual Studio, 959in XML, 415–416

Declarationsendpoint, 564–569property, 916

Declarative transactions, 697–699Decomposition functionality, 414–415DecryptByKeyAutoCert function, 248DEFAULT contracts, 492, 529–530DEFAULT message type, 524, 527, 531DEFAULT_SCHEMA keyword, 252Default Windows credentials connections,

778–780DefaultConnection program, 782DefaultSMOConnection program, 779–780Deferred processing in messaging, 482–483DEFINITION_SCHEMA for metadata, 721DeKlarit tool, 720DELETE permission, 230, 266DELETE statement

CTEs with, 343OUTPUT clause in, 336–338TOP clause in, 329–330in XML, 473–474

DeleteCommand command, 753–754DELETED table, 149, 337–339Deleting reservations, 569Delivery of Notification Services, 829,

873–874channels for, 830–831customizing, 877protocols for, 826, 831, 874–877

DeliveryChannelName property, 864DeliveryChannels entry, 836<DeliveryChannels> node, 864DENSE_RANK function, 367, 370–371Dependencies

in assemblies, 49–51in scripts, 810–812

DependencyTree class, 813DependencyTreeNodes class, 813Deployment in Visual Studio, 956–958Derivation by restriction process, 394Descendant axes in XQuery, 439Descendant-or-self axes in XQuery, 440

Describe packets for metadata, 138Design patterns for Notification Services,

828–829Destructors, 919–920Deterministic functions, 75Development languages, 899–900DeviceAddress property, 864DeviceName property, 865Devices for Notification Services, 830DeviceTypeName property, 865Dialogs in Service Broker, 24–25Digest delivery, 829, 867Dimensions for scalars, 194Dimensions namespace, 158–159Dirty property, 799DISABLE LOGIN statement, 226DISABLED option for batches, 622DISABLED state for endpoints, 571Disconnect method, 781, 786DisconnectIfPooled property, 787Dispatching notifications, 677–679Dispose method, 112–113

in IDisposable, 920–921in TransactionScope, 129in XmlReader, 664

Disposing objects, 920–921DISTINCT keyword, 327, 375Distinct types, 8Distributed processing, 483Distributed systems, 894Distributed transaction coordinator

(MSDTC), 697Distributed transactions, 130–132, 135, 697Distribution in business transactions,

544–559Distributor node in ADFs, 841Distributors in Notification Services, 823,

830, 871–873DLINQ language, 893DLLs (Dynamic Link Libraries), 3, 901dm_qn_subscriptions view, 689DML (data manipulation language),

468–475DMVs (dynamic management views),

33, 689Document fragments, 16, 525DOCUMENT keyword for schema

collections, 396Document Object Model (DOM), 24Document order in XQuery, 434

Index972


Document Type Definitions (DTDs), 18for message types, 526in well-formed documents, 525, 533–534

Documents with XML data type, 661–664DOM (Document Object Model), 24Domain-neutral code, 42Domain-specific aggregate functions, 14DoWork method, 682DPAPI (data protection API), 239DROP ASSEMBLY statement, 55–56, 262DROP ENDPOINT statement, 564Drop method, 802–804DROP ROUTE statement, 547DROP TABLE statement, 291–292DROP TYPE statement, 185DROP XML SCHEMA COLLECTION

statement, 400DropImpl method, 802Drops object, 763–764DTDs (Document Type Definitions), 18

for message types, 526in well-formed documents, 525, 533–534

DUMPBIN.EXE tool, 902dumpfile.csv file, 735Durability in ACID properties, 128, 278Dynamic Link Libraries (DLLs), 3, 901Dynamic management views (DMVs), 33, 689

EEager notifications, 687–688Element-centric mapping, 15Elements in XML, 15ELEMENTS XSINIL directive, 418EmployeeRoutines library, 51EMPTY validation, 525, 528Enable command in NSControl, 854ENABLED option for batches, 622Encryption

functions for, 242–248keys for, 220, 238–248in SQL Server login, 702–703

END CATCH phrase, 314, 321–322END CONVERSATION statement, 486,

510–514END TRY phrase, 314End users, dispatching notifications to,

677–679EndCriticalRegion method, 34EndDocument method, 191–193

EndExecuteRead method, 744Endianness, 20Endpoints

in HTTP, 895creating, 222–223, 569declaring, 564–569metadata, 572–573in SQLXML, 639SSL for, 608–609states, 571–572system views for, 552–553URL, 570–571

in message conversations, 489, 497, 509EnterpriseServices library, 129Entitized XML, 633–634ENTITYs for message types, 526Enumerating data sources, 714–715Enumerator methods, 100–101Equals method, 906.err extension, 855Error class, 113error function, 462ERROR_LINE function, 314–315ERROR_MESSAGE function, 147,

314–315ERROR_NUMBER function, 147, 314–315,

319, 321–322ERROR_PROCEDURE function, 314–315ERROR_SEVERITY function, 314–315ERROR_STATE function, 314–315, 319@@ERROR variable, 312–315, 319Errors

with conversations, 512messages for, 224in .NET Framework, 2in T-SQL, 312–326

ErrorTestW stored procedure, 323–325ETW (Event Tracing for Windows) provider,

732–734, 742–743Event-driven subscriptions, 862Event Notifications, 294–297, 509Event property in SqlTrigger, 109, 961Event Tracing for Windows (ETW) provider,

732–734, 742–743EventClass node, 847EventClasses, 841, 845EventClassName argument, 853EVENTDATA function, 291–294, 296–297EventRule element, 870<EventRules> node, 858, 862

Index 973

dotNET_Index 4/6/06 12:14 PM 73

Eventsdefined, 914for Notification Services, 822–823, 830, 846

chronicles for, 857–858classes for, 846–850providers for, 825, 846, 852–856rules for, 868–870stored procedures for, 850–852

for queues, 509SSB service for, 294–296

EventsData.xml file, 855EWT (Event Tracing for Windows) provider,

732–734, 742–743EXCEPT operator, 326–329Exception class, 113Exception handling, 6

in hosting, 34–37for in-process data access, 143–149in T-SQL, 312–326

ExceptionThrower method, 145–146Executable (EXE) files, 901EXECUTE AS clause, 255–259EXECUTE permission

for assemblies, 266for schema collections, 400for stored procedures, 230for UDTs, 267

Execute Procedure dialog box, 945ExecuteAndCaptureSql property, 814ExecuteAndSend method, 135–136, 147ExecuteNonQuery method, 744ExecuteReader method, 125–126, 710, 744ExecuteScalar method, 125, 664ExecuteSql property, 814ExecuteXmlReader method, 657,

663–664, 744Executing code in Visual Studio, 959–962Execution contexts

for procedural code, 253–259in security, 232–235

Execution plans, 301exist method, 403, 450–452EXISTS operator, 326exit function, 6Expensive property, 799Expiration time for notifications, 867Expired passwords, 699Explicit data type conversions, 83EXPLICIT mode for XML, 403–406Explorer tree, 945

ExpressionsCTEs


FLWOR, 438, 441, 446–448, 450regular, 71–74in XPath, 439

Extended data types, 13Extended functions, 465–467Extended stored procedures, 5–6, 62, 115Extensible Stylesheet Language

Transformations (XSLT), 19, 445EXTERNAL_ACCESS security level, 39–40

for assemblies, 49, 264, 271–272attributes disallowed by, 45

EXTERNAL NAME clauseparts of, 66for triggers, 109for UDTs, 170

FFacets, 394, 416–417Factories

in ADO.NET, 705–709provider, 709–712

Failover support, 701–702Failure escalation policy, 35Fibers in runtime hosting, 33Fiddler tool, 578Field Object Properties collection, 655Fields, 916–917

defined, 914in UDTs, 173–181

File protocol, 874–875File provider, 826File Watcher event provider, 825, 852–853FileSystemWatcher class, 853FileSystemWatcherProvider class, 854Fill method, 736FillPropertyList method, 798–799FillRowMethod method, 100, 102–103,

105–106FillRowMethodName property, 76, 96, 100,

103, 960FilterCallbackFunction function, 811–812Final queries, 352–354Finalization, object, 919–920Finalize method, 906, 919–920Finalizers in exceptional handling, 36–37

Index974


finally blocksin exceptional handling, 36in T-SQL, 312

Firehose mode, 689Firewalls, 562Fix points in recursive queries, 354Float data types, 416FLWOR expressions, 438, 441, 446–448, 450fn:collection function, 438fn:doc function, 438FOR clause

in FLWOR expressions, 447in PIVOT, 361–364in UNPIVOT, 365in XQuery, 442

FOR PATH clause, 401FOR PROPERTY clause, 401FOR SOAP clause

for batches, 622, 626for endpoints, 565, 573for rowsets, 621WSDL option in, 579, 605, 607, 614

FOR VALUE clause, 401FOR XML clause, 403–406

for inline XSD format schema, 409–411for nested XML queries, 413–414for NULL values, 411–412for root elements, 412–413support for, 20, 23, 661–664with UDTs, 187for whitespace entitization, 413for XML data types, 407–409XMLSCHEMA version, 410

FOR XML AUTO clause, 404–406, 632FOR XML EXPLICIT clause, 404–406, 632FOR XML PATH clause, 406–407FOR XML RAW clause, 403–406force flag in Update, 849FOREIGN KEY clause, 385, 864Format.Native implementation vs.

Format.UserDefined, 215–217FORMAT option for WEBMETHOD

clause, 621Format property in SqlUserDefinedType, 165Formatters for Notification Services,

825–826, 830, 871–873Forward-only mode, 689Fragments, 16, 392, 661–664FROM clause in FLWOR expressions, 447FROM OPENROWSET clause, 301

FROM SERVICE clausefor messaging applications, 488for queues, 542

FromBytes method, 204–205Functions

aggregate. See Aggregate functionsCLR

table-valued, 99–106user-defined, 93–99

deterministic, 75XML, 402–403XQuery, 461–463, 465–467

FX libraries, 39

GGAC (global assembly cache), 38, 649Garbage-collected heap, 912Garbage collection, 34, 918–920GC class, 918GenerateRepairWSDL function, 613–614<Generator> node, 841, 848Generators in Notification Services,

823, 830Geographic Information Systems (GIS), 154GET command, 564, 574–575, 608GET CONVERSATION GROUP statement,

538–539get-member cmdlet, 766get method, 175GetBits method, 212GetBytes method

in SqlDataReader, 667for UDTs, 650

GetChars method, 667GetCLRControl method, 30GetCLRManager method, 30GetColumnInfo method, 655GetColumnsRowset method, 655GetConnectionBaseClass2 method, 711GetConnectStringFromConfigFile

method, 647GetDataSources method, 714GetDecimal method, 127GetEstimate method, 574–577, 598–600,

603, 605GetEstimateResponse method, 601–602GetFactory method, 711GetFactoryClasses method, 710GetHashCode method, 906

Index 975


GetHostManager method, 30GetInitializedConnectionBaseClass

method, 713GetJobs method, 683GetKeys method, 715GetListOfTables method, 719–720GetOuterXml method, 662GetParameterProperties method, 656GetPropertyObject method, 797–798GetProviderFactoryFromUserInput

method, 711GetProviderFromConfigFile method, 707GetProviderSpecificFieldType method, 660GetProviderSpecificValue method, 660GetPubAuth stored procedure, 302–303GetResult method, 746GetRow method, 102–103, 105–106GetScaled method, 178–180, 202GetSchema method

in IXmlSerializable, 191, 193in LDim, 205–206for metadata, 726–728, 730for restrictions, 722–724in SqlConnection, 120for table lists, 719–720

GetSchemaRowset method, 655GetSchemaTable method, 651–652,

667, 731GetSmoObject method, 791–794GetSqlBinary method, 667GetSqlBytes method

in SqlDataReader, 667for UDTs, 650

GetSqlChars method, 667GetSqlDecimal method, 127GetSqlString method, 667GetSqlXml method, 660GetType method, 906GetValue method

in IDataRecorder, 126in SqlDataReader, 659, 667–668, 670

GetValues method, 670, 715GetWSDLUrl function, 610–611GetXmlReader method, 410GIS (Geographic Information Systems), 154Global assembly cache (GAC), 38, 649Globally Unique Identifiers. See GUIDs

(Globally Unique Identifiers)GRANT statement, 228GRANT CONNECT statement, 552

GRANT CREATE XML SCHEMACOLLECTION TO statement, 400

GRANT permission, 230Granting permissions, 220GROUP BY clause

with aggregate partitions, 375–376with XML, 389

Groupsconversation, 516–524Windows, 229

Guest user, 228GUIDs (Globally Unique Identifiers)

for assemblies, 904for brokers, 513, 545for conversation endpoints, 509for conversation groups, 519for messaging applications, 487for providers, 733–734for routes, 553

HHasRows property

in DataReader, 707in DbDataReader, 708

Helper functions, 181–183Hints, query, 303–308Host Protection Attributes (HPAs), 44–45, 262<HostedProvider> node, 848Hosting, 27

AppDomain loading for, 40–43assemblies in

dependencies, 49–51maintenance, 55–58storing, 46–49

code loading in, 37–39exception handling in, 34–37.NET Framework, 29–31purpose of, 27–29resource management for, 31–34safe code in, 43–45security in, 39–40specification compliance in, 58–59

HostProtectionAttribute class, 44–45Hot-added memory, 28HPAs (Host Protection Attributes), 44–45, 262HTTP

endpoints, 895creating, 222–223, 569declarations, 564–569

Index976


metadata, 572–573in SQLXML, 639SSL for, 608–609states, 571–572URL, 570–571

for Web Servicesbasic authentication, 626–627connections, 562response, 563

http//www.w3.org/2001/XMLSchema-instance namespace, 392

http//www.w3.org/2001/XMLSchemanamespace, 392

HTTP provider, 826httpcfg utility, 568HTTPS connections, 627

IIAlterable interface, 802IBCPSession interface, 645IBinarySerialize interface, 159, 165, 167–170,

196, 210, 650ICommandStream interface, 631IContentFormatter interface, 826, 871ICorRuntimeHost interface, 30ICreateable interface, 802IDataReader interface, 707, 751–752IDataRecord interface, 126IDBCommandWithParameters interface, 656IDbConnection interface, 706IDbParameter interface, 652IDeliveryProtocol interface, 826, 877Identity impersonation in Windows, 782–783IDispatch interface, 646IDisposable interface, 920–921IDropable interface, 802IEnumerable interface, 99–100IEnumerator interface, 99–100IEventProvider interface, 825IHttpProtocolProvider interface, 877IL (intermediate language), 3ILDasm.EXE (Intermediate Language

Disassembler) tool, 726, 902–903IMAGE data type

in clients, 669for LOBs, 298vs. MAX, 299

Impersonate method, 140ImpersonateConnect program, 782–783

Impersonation of Windows identity,782–783

Implicit data type conversions, 83, 85<import> statement, 399Importing types into schemas, 399Imports directive, 914IN clause

with PIVOT, 361–364with UNPIVOT, 365

In-process data access, 111commands for, 121–124connections for, 118–121exception handling for, 143–149pipes for, 135–137return values for, 124–127rowsets for, 137–139SqlClient for, 111–115, 150–151SqlContext for, 115–118transactions for, 128–135triggers for, 149–150Web Services from SQLCLR for, 140–143WindowsIdentity for, 139–140

IncludeIfNotExists property, 807Index buffers, 33Indexed views, 674Indexers for SMO objects, 801Indexes

for XML data type, 401–402for XQuery, 477–480

IndexSqlSchema node, 847Infinite loops, 540Info property, 687Information identification for provider

factories, 712Information property, 769–770INFORMATION_SCHEMA, 728

for assemblies, 52–55for metadata, 721

INFORMATION_SCHEMA.ROUTINESview, 94

Infosets, 18Inheritance in CLR, 908Init method, 210, 217INITIATOR direction for contracts, 529Injection, guarding against, 256Inline XSD format schema, 409–411Input/output parameters for stored

procedures, 106Input validation, 183–185INSERT permission, 230, 266

Index 977


INSERT statementwith BULK Provider, 301CTEs with, 343encryption in, 238, 246OUTPUT clause in, 336–338TOP clause in, 329–330in XML, 469–473

InsertCommand command, 753–754INSERTED table, 149, 337–339Inserting rows, 112–114Instance configuration files, 834–839Instance members for user-defined types,

174–175InstanceName entry, 836InstanceName property, 796Instances in CLR type system, 910–912INSTEAD OF triggers, 290, 338Integral data types in XML, 416–417Integrity checking, 395–396IntelliSense for SMO, 766, 768Intermediate language (IL), 3Intermediate Language Disassembler

(ILDasm.EXE) tool, 726, 902–903Internal activation in service programs, 504Interoperability

in service-oriented database applications,891–894

with SqlContext, 117–118INTERSECT operator, 326–329<Interval> element, 855–856INTO clause

with INTERSECT and EXCEPT, 329with OUTPUT, 339–340with RECEIVE, 522–523with XML, 470

INullable interface, 159–160invariant attribute, 710Invocation links, 265InvokeIfReceiverIsNull property, 177,

180–181IP addresses, 553IRowsetFastLoad interface, 645, 749IS NULL clause, 181is operator in XQuery, 443ISAPI application, 563ISAPI filters, 563IsAvailable property, 116–117IsByteOrdered property, 166IScheduledEventProvider interface, 825ISchemaRowset method, 726

IsDeterministic property, 76, 93–97IsFixedLength property, 165, 960IsInvariantToDuplicates property, 211–212IsInvariantToNulls property, 211, 213IsInvariantToOrder property, 211IsMatch method, 163IsMutator property, 177–178, 180IsNull property

in LDim, 159, 161, 197in SqlChars and SqlBytes, 668in SqlTypes, 81in UDTs, 181

IsNullable propertyin SqlFacet, 960in SqlSmoObject, 804

IsNullIfEmpty property, 211–212Isolation

in ACID properties, 128, 278SNAPSHOT, 278–289

Isolation levels in transactions, 695–699IsPrecise property, 76, 93–97ISQLServerErrorInfo interface, 645ISSAbort interface, 645ISSAsyncStatus interface, 645ISSCommandWithParameters interface, 656IsSendingResults method, 136IsUpdatedColumn method, 149Item property, 797–798Items in XQuery sequences, 432IUnknown interface, 646IXmlSerializable interface, 191–193, 757

Jjar files, 59Join equivalents in XQuery, 468Joins with APPLY operators, 340–343Just-In-Time (JIT) compilation, 7, 900

KKernel, 2Keyrings, 246Keys, encryption, 220, 238–248KILL command, 36

LLarge object data types (LOBs), 298–301,

667–670Latitude data type, 154Lawrence, Eric, 578

Index978


Lax validation in XML, 423LDim data type, 156

creating, 157–160, 170–173in DataReader, 649helper functions in, 181–183IBinarySerialize for, 167–170implementations in

binary, 165–170Format.Native vs. Format.UserDefined,

215–217null value, 160string, 160–164

maintaining, 185–187objects for, 193–197public members in, 173–181source code for, 196–206validation in, 183–185XML with, 187–193

let keywordin FLWOR expressions, 447in XQuery, 441–442

Libraries, loading, 38–39Library modules (DLLs), 3, 901LIFETIME clause

for dialogs, 514for routes, 547

Lightweight pooling, 33LinkDemand, 44–45Links for assemblies, 265–266LINQ language, 893Load balancing, 549Loading

AppDomains, 40–43with BULK Provider, 301classes, 2code, 37–39XML data, 420–422

LOBs (large object data types), 298–301,667–670

Local Security Settings tool, 236–237Locality-of-reference choices, 891LOCATOR data type, 10–11Locking

conversation groups, 516–517, 522in thread scheduling, 34

Logarithms, 101–106Login property, 783Logins

security for, 225–228for SMO connections, 783–785

LoginSecure property, 782–783logman command, 733–734LogRange function, 101–106Long-running transactions, 500–501Longitude data type, 154LONGVARBINARY data type, 670LONGVARCHAR data type, 670LONGWVARCHAR data type, 670Lookup tables, 671Low-memory conditions, 35

Mmachine.config file, 710, 729MakeCall CTE, 346–347, 349Managed code, 2Managed Object Format (MOF) file, 732ManagedComputer class, 762–763, 815–817Management class, 762, 795Manifests, 903–904Mapping

in runtime hosting, 33schemas, 20SQL catalogs and tables, 424–425SQL types, 425–426SqlTypes, 82–83in XML, 15, 414–418

mapping_schema.xml file, 637MARS (multiple active resultsets), 23,

689–691in ADO.NET, 691–695for asynchronous support, 748in SQL native client, 695with SqlCommand, 123

Marshaling data, 20–22, 40Master keys, 238–241, 245Match method, 71, 163Math class, 97–98, 107–108MAX data types, 299–300MAX function, 14, 156, 207MAX_QUEUE_READERS clause, 505–506MaxByteSize property, 165MaximumVersion element, 727maxInclusive facet, 394, 417maxOccurs facet, 418, 476MaxPoolSize property, 787MAXRECURSION query hint, 306, 355MaxSize property, 960MDAC (Microsoft Data Access

Components), 642

Index 979


Members, 914–915MemberwiseClone method, 906Memory and memory allocation, 918–921

for conversations, 511hot-added, 28in .NET Framework, 2out-of-memory conditions, 35for runtime hosting, 33–35

Memory clerk, 33MERGE JOINs, 305Merge method, 210, 214, 217MESSAGE TYPE clause, 532Messages

in business transactions, 524–528, 530–534compensation in, 541–544order of, 502poison, 540–541in SSB, 494–499

Messaging applications, 481–493Meta-collections, 721–722Metadata

for assemblies, 52–55for endpoints, 572–573in instance configuration files, 836for Notification Services, 827in OLE DB, 656for rowsets, 137–139in schemas

availability of, 721–726customizing and extending, 726–728purpose of, 719–720

security for, 220, 223–224in SNAPSHOT isolation, 288–289

MetaDataCollections collection, 721–722, 727MetaDataXml attribute, 730Methods, 917–918

defined, 914overloading, 98in UDTs, 173–181

Microsoft Alerts provider, 826Microsoft Data Access Components

(MDAC), 642Microsoft Data Engine (MSDE), 4–5Microsoft Transaction Server (MTS), 697Middle-tier servers, 562MIN function, 14, 207minExclusive schema element, 394MinimumVersion element, 727minInclusive facet, 417minOccurs facet, 418, 476

MinPoolSize property, 787MinutesDelay, 14MIXED authentication, 627modify function, 403

delete, 473–474insert, 469–473replace value of, 474

Modules, 901–903MOF (Managed Object Format) file, 732Monad shell, 766, 768Monitoring SNAPSHOT isolation

versioning, 288–289MOVE CONVERSATION statement, 518MoveNext method, 99, 103mscoree.dll file, 2, 899mscorlib.dll file, 899, 903–904mscorsvr.dll file, 899mscorwks.dll file, 899MSDE (Microsoft Data Engine), 4–5MSDTC (distributed transaction

coordinator), 697MSN Messenger program, 25MTS (Microsoft Transaction Server), 697Multicast deliveries, 867Multiple active resultsets (MARS), 23,

689–691in ADO.NET, 691–695for asynchronous support, 748in SQL native client, 695with SqlCommand, 123

Multiple Connections property, 691Multiple-document queries, 468MUST_CHANGE keyword, 236Mutators, 177

NNames and Name property

for assemblies, 904–905in CLR, 66in SqlFunction, 96in SqlSmoObject, 803in SqlTrigger, 109synonyms, 252–253

Namespace keyword, 913Namespaces

CLR, 912–914XML, 16–17, 392, 604–605

NaN (not a number) value, 86–87Native client, 641–645, 695

Index980


Native XML Web Service feature, 571Navigation in SMO, 800–802NCHAR data type

in messaging applications, 493for SqlTypes, 92in XML, 387

Nestingclasses, 97–98transactions, 130, 134XML queries, 413–414XML subelements, 15

.NET Framework, 1–3, 897assemblies in, 903–905CLR. See CLR (Common Language

Runtime) languagesmembers in, 914–918memory management in, 918–921modules in, 901–903runtime hosting, 29–31security for, 219–220SQL Server affected by, 3–8UDTs

in ADO.NET, 647–648in ODBC, OLE DB, and ADO, 654–656

NetValidatePasswordPolicy function, 236New Database Reference dialog box, 952New Project dialog box

in SSMS, 927–928in Visual Studio, 773, 952

New Query Editor Window, 944–945NextResult method, 126NGen.exe program, 900NIH (not invented here) syndrome, 221999 Standard, 8–11No-namespace schema, 395node function, 407Node tables, 401nodes function, 403, 457–461Nodes in XQuery, 19, 432

comparisons, 443functions, 463identity, 434tests, 440

Non-Uniform Memory Access (NUMA)architecture, 28

NONE validation for message types, 525<NonHostedProvider> node, 848NonPooledConnection property, 785–786Nonvalidated XML schema, 18Not a number (NaN) value, 86–87

Not invented here (NIH) syndrome, 22Notification Services, 24–25, 761, 819–821

application definition files for, 839–842applications, 821–824

coding steps, 832generation steps, 833planning steps, 831–832sample, 833–834

best practices, 891components of, 824–828delivery of, 829, 873–874

channels for, 830–831customizing, 877protocols for, 826, 831, 874–877

design patterns for, 828–829distributors and formatters for, 871–873events in, 822–823, 830, 846

chronicles for, 857–858classes for, 846–850providers for, 825, 846, 852–856rules for, 868–870stored procedures for, 850–852

instance configuration files for, 834–839notifications, 867–871NSControl for, 824, 843–845, 849, 854, 868subscribers, 862–865subscription classes, 860–862subscriptions, 858–860, 865–867terms for, 829–831

<NotificationClass> node, 867<NotificationClasses> node, 841, 848Notifications

event, 294–297query. See Query Notification support

NotificationServices.dll file, 824NotificationServices namespace, 860, 862NSApplication class, 860, 865NSBeginEventBatch stored procedure, 850NSControl tool, 819, 824, 839, 842–845, 849,

854, 868NSDeliveryChannels table, 864NSDiagnosticEventClass stored

procedure, 852NSDiagnosticEventProvider stored

procedure, 852NSEventBatchDetails stored procedure, 852NSEventFlushBatch stored procedure,

850–851NsEventSubmitBatch stored procedure, 850NSEventWrite stored procedure, 850

Index 981


NSInstance class, 860, 865NSNotificationClasses table, 845NSService process. See Notification Servicesnssub class, 863NSSubscriberChannels table, 864NSSubscriptionClasses table, 845NTEXT data type

in clients, 669for LOBs, 298vs. MAX, 299in XML, 387, 389

NTILE function, 367, 371–373NULL constraint, 386Null instances in SSMS, 179Null method, 197NULL values and property

in FLWOR expressions, 447–448, 450in INTERSECT and EXCEPT, 326–329in INullable, 159in LDim, 160, 180–181, 197in ON DELETE and ON UPDATE, 334–336in SqlTypes, 77–79, 81in WSDL, 601–602in XML, 411–412, 418

Nullable type, 80NUMA (Non-Uniform Memory Access)

architecture, 28Numeric class, 97–98Numeric operators in XQuery, 463–464NVARCHAR data type

in clients, 667in MAX, 299maximum size for, 298in messaging applications, 493in SqlTypes, 92in SQLXML, 629WSDL files for, 599–600in XML, 387, 408

OObject class, 906–907Object data type, 90–92Object Explorer

for SMO object model, 770–772for SSMS, 944–948

Object Oriented Database ManagementSystem (OODBMS), 447

Object-oriented programming concepts, 893Object Query Language (OQL), 447

Objects for UDTs, 193–197ObjectType function, 90–91ODBC API, 642–643, 646

for connection information, 717isolation levels in, 696.NET Framework UDTs in, 654–656Query Notifications in, 676–677

Odbc bridge, 641OLE DB API, 22–24, 642–644, 646

in ADO.NET, 705–706for connection information, 717isolation levels in, 696.NET Framework UDTs in, 654–656Query Notifications in, 676–677

OLE DB for Analysis, 24OleDb bridge, 641OleDbProviderFactory class, 709OLTP operations, 768–769ON clause for DDL triggers, 290ON CONTRACT clause, 530ON CONVERSATION clause, 489, 509–510ON DELETE clause, 333–336ON UPDATE clause, 333–336OnChange event, 680–681OnNullCall property, 177–178OO4O (Oracle Objects for OLE), 705OODBMS (Object Oriented Database

Management System), 447op_ methods, 87op_addition operator, 87–88, 181–183op_Equality operator, 88op_Explicit method, 85op_Implicit method, 85op_Inequality operator, 88Open Data Services API, 5Open method, 112, 130–131, 736OPENROWSET function, 420OpenXML, 418–420, 461, 638operator + method, 198Operators

in SqlTypes, 87–90in XQuery, 464–467

OPTIMIZE FOR clause, 305–306Optimizing XQuery queries, 402OPTION keyword, 305Optional features, turned off, 221Options dialog box in SSMS, 925–926OQL (Object Query Language), 447Oracle Objects for OLE (OO4O), 705OracleClient provider, 718

Index982


OracleProviderFactory class, 709ORDER BY clause

in ranking functions, 368, 372in XML, 389in XQuery, 443–444, 465

ORIGINAL_LOGIN function, 259out modifier, 917Out-of-memory conditions, 35OUTER APPLY operator

for joining sets, 340–343for nodes, 458–460

OUTPUT clause, 336–340Output parameters for stored procedures, 108OutputCache directive, 673, 686OutputDebugString system, 742OVER clause

for aggregate partitions, 374–375for ranking functions, 367–368

Overflow conditions, stack, 36Overloading methods, 98OverwriteChanges option, 757Ownership

of assemblies, 51–55chaining, 232–235, 267, 274

P<Parameter> node, 837<ParameterDefaults> node, 837–839ParameterDirection enumeration, 123–124Parameterized queries, 718–719Parameters, 917–918

binding problems, 740–742in CLR, 93for scalar user-defined functions, 98–99for SqlCommand, 123–124for stored procedures, 106–108XML for, 390–391

Parameters element, 623Parameters property, 123, 653ParametersCollection, 653Parent axes in XQuery, 439Parent-child relationships, 334Parent navigation in SMO, 800–802Parent property, 800, 803Parents in DependencyTree, 813Parse method, 160–163, 184, 201Partitions

aggregate, 374–376PARTITION BY clause, 373–374

Password property, 783Passwords, 222

change support for, 699–701for connections, 114in HTTP authentication, 627policies for, 220, 235–238in ServerConnection, 783for SMO connections, 778, 780for SQL Server login, 784–785

PATH indexes, 477–478PATH mode, 406–407Path value, 736–737Pathnames in XML, 407Paths

in data tracing, 736–737in HTTP schemes, 570in XML, 406–407in XQuery, 438, 477–478

Pattern-based data types, 417–418PERCENT phrase, 380Performance benchmarks, 28Performance counters in SNAPSHOT

isolation, 289Performance Logs and Alerts snap-in, 739PERMISSION_SET clause, 957Permissions

for assemblies, 48–50, 262–268, 957for schema collections, 400for security, 224–231for SQLCLR objects, 261–262

Persistent Stored Modules, 449PERSON data type, 10PInvoke

for interoperability, 117for safe assemblies, 48for thread scheduling, 34

Pipe property, 117, 135Pipeline method, 139Pipes, 135–137PIVOT operator, 359–365Plan guides and plan forcing, 306–308Points, 194–196Poison messages, 540–541PooledConnectionLifetime property, 787PopulationMechanism, 728PopulationString, 728Ports

for distribution, 553HTTP, 570SSL, 608–609

Index 983


PORTS option, 570POST command, 573–574Post Schema Validation Infosets (PSVIs), 18,

434–435Precision property, 960Predicates in XQuery, 441–442Preemptive thread scheduling, 34PrepareCommand value, 728Primary indexes, 401, 477–479PRIMARY KEY option, 385Principals in security, 224–231Principle of least privilege, 140, 219Private keys, 243private keyword, 59Procedural code, execution contexts for,

253–259PROCEDURE_NAME clause, 505Processes in runtime loading, 40ProcessID property, 781–782processing-instruction function, 407Processing loops in business transactions,

534–539Processing messages in SSB, 494–499Processing serialization, 502ProcessObject function, 91Processors in marshaling data, 20Product aggregate, 208–209Projects

in SMO, 772–777in SSMS, 924–937in Visual Studio, 951–954

Prologs in XQuery, 436–437Promotable transactions, 697–699PromptPassword method, 700Properties, 916–917

defined, 914in UDTs, 173–181

Properties folder, 954Properties property, 796–798PROPERTY indexes, 477, 479PropertyGrid control, 717PropertyRetrieved property, 798<Protocol> node, 837, 876Protocols for Notification Services, 826, 831,

874–877<Protocols> node, 837ProviderBase namespace, 726ProviderFactories class, 717Providers

in ADO.NET, 705–707

in ETW, 742factories for, 709–712GUIDs for, 733–734in Notification Services, 825, 846, 852–856SQLXMLOLEDB, 629–632

<Providers> node, 848Proxy classes

WDSL, 600, 603Web Service, 141Webmethods, 575

PSVIs (Post Schema Validation Infosets), 18,434–435

Public keys, 242–243public keyword, 59Public members in UDTs, 173–181public principal, 229Public-private key pairs, 241Pulley/belt drive database example, 63–70Pulley class, 64–69pulley.cs file, 66–67PulleyDistance method, 64–69PulleyDistanceTSQL function, 69

QQNames functions, 464Quantum for Notification Services, 830Queries

hints for, 303–308nesting, 413–414recursive, 351–359SSMS, 931–936statement-level recompilation for, 301XPath, 18–20, 429

expressions in, 439for SMO objects, 791–793in SQLXML, 634

XQuery. See XQuery query languageQuery Designer dialog box, 933–936query method, 403, 454–457Query Notification support, 670–673

from databases, 679–680dispatching in, 677–679eager notifications, 687–688OLE DB and ODBC, 676–677SQL Server 2005, 673–676SqlCacheDependency for, 685–687SqlDependency for, 680–682SqlNotificationRequest for, 682–685usage, 688–689

Index984


Query plans, 304Query predicates, 441Query window, 923QueryMemoryResourceNotification API, 35QUEUE_ACTIVATION events, 509Queues

for messages, 485–486, 533, 541–544for Query Notifications, 677in service programs, 505–509

QuoteIdentifier property, 731

RRAISERROR statement, 147, 164, 319–320,

322–323Random samples, 376–381Range class, 102–103, 106Range errors, 184RangeFunctions class, 103–106RANK function, 367, 369–370Ranking functions, 366–373RAW mode for XML, 403–406RDS (remote data services) architecture, 21READ COMMITTED isolation level,

278–281, 283, 288READ_COMMITTED_SNAPSHOT option,

283, 286, 288Read method

in IBinarySerialize, 167–170in LDim, 160, 204in SqlDataReader, 126, 664, 668in XmlReader, 663

Read-mostly lookup tables, 671Read-only lookup tables, 671Read-only mode, 689READ UNCOMMITTED isolation level, 278ReadCommitted method, 697Reader-makeright approach, 20–21Readers in SAX, 665Reading trace output, 735–736ReadLine method, 779READTEXT method, 670ReadXml method

in IXmlSerializable, 191–193in LDim, 206

RECEIVE statementfor conversation groups, 518, 520–523for Event Notifications, 297for processing loops, 535–536, 539for queues, 491, 542–543

Recompilation, statement-level, 301–303Recordsets, 666Recursive queries, 351–359REF data type, 11ref modifier, 917Reference types, 158, 912ReferenceEquals method, 906References folder, 954REFERENCES permission

for assemblies, 266–267for schema collections, 400for tables, 230

Reflection for assemblies, 48Regex class, 162Register command, 855Registry

for data tracing, 732, 738for provider factories, 712for SMO, 816

Regular expressions, 71–74Relational data access APIs, 646Relational databases, 892Remote data services (RDS) architecture, 21Remote procedure calls (RPCs), 21REMOVE PRIVATE KEY statement, 260RENAME LOGIN statement, 226RepairWSDL function, 611–613REPEATABLE clause, 379–381REPEATABLE READ isolation level, 278, 288replace value of statement, 474REPLICATE function, 299Reporting Services feature, 25, 761ReportingServices namespace, 795Reservations for URLs, 569ReservedWords metadata category, 722Reset Lockout Counter After setting,

236–237Resource management for hosting, 31–34Responses for Web Services, 563Restore object module, 763RestrictionNumbers, 723Restrictions, URN retrieval, 721–724Restrictions collection, 721–724Result method, 163Results window, 923Resultsets, 19Retention for queues, 541–544Retrieved property, 799RetrieveStatistics method, 718Retry stored procedure, 322–324

Index 985


Retrying transactions, 321–322return expressions

in FLWOR, 447in XQuery, 441, 444

Return valuesin CLR, 93for in-process data access, 124–127for stored procedures, 107

ReturnProviderSpecificTypes property, 757Reverse method, 176REVERT statement, 256Roles

assigning, 228–229schemas for, 249–251

ROLLBACK TRAN statement, 517Rollup accounts, 351Rollup query, 352–359Root elements in XML, 412–413Root nodes in XQuery, 434, 440Routes and routing tables, 547–553ROW_NUMBER function, 367–368Rows

inserting, 112–114in SNAPSHOT isolation, 280

ROWS clause, 380–381Rowsets

creating and sending, 137–139watching, 674from XML documents, 418–420

ROWSETS_ONLY option, 621RPCs (remote procedure calls), 21Rules

creating, 11for Notification Services, 868–870

Rules.dll file, 824Runas utility

for ImpersonateConnect program, 783for SMO connections, 780–781

Runtime hosts. See Hosting

SSAFE security level, 39, 45

for assemblies, 48, 264, 271–272for hosting, 43–45

SafeHandle class, 37Safety levels, 268–274Sagas, 500–501, 541–543Sample applications for Notification

Services, 824–825

Samples, random, 376–381SAXXMLReader40 class, 666Scalars

dimensions for, 194points for, 195–196user-defined functions, 96–98user-defined types, 154

ScaleBy method, 176–179, 201<Schedule> node, 855ScheduledRules section, 862Schema-bound links, 266Schema node, 847SchemaFile argument, 853SchemaGen option, 637Schemas and schema collections

in ADO.NET, 719–728for assemblies, 51–55for HTTP, 570inline XSD format, 409–411managing, 397–400mapping, 20for message types, 526metadata in

availability of, 721–726customizing and extending,

726–728purpose of, 719–720

for Notification Services, 824, 835security for, 400user customization in, 729–731user separation from, 248–252in XML, 17–18, 392–400, 475–477, 824in XQuery, 475–477

SCOPE_IDENTITY function, 336, 338Script method, 807–809Script Stored Procedure As

command, 945ScriptDrops property, 807, 809Scripter objects, 808ScriptingOptions object, 807Scripts

Object Explorer for, 944–945for SMO, 807–816

“Sealing the data” process, 243SecondsDelay data type, 13–14Securables, 225Secure Sockets Layer (SSL)

encryption in, 239for Web Services, 608–609

SecureString class, 784–785

Index986


Security, 219for assemblies, 48–50authentication and authorization in,

224–231best practices, 885code signing, 259–261configuring, 221–224encryption for, 238–248execution contexts

and ownership chaining, 232–235for procedural code, 253–259

in hosting, 39–40for metadata, 220, 223–224in .NET Framework, 2new features, 219–221optional features in, 221password policies and credentials in,

235–238safety levels for, 268–274for schema collections, 400separation of users and schemas for,

248–252for SQLCLR objects, 261–262for synonyms, 252–253

Security Configuration Manager screen, 222select_and_count stored procedure, 258SELECT permission, 230, 266SELECT statement

CTEs with, 343encryption in, 238, 246FOR XML clause in. See FOR XML clausefor queues, 491return data from, 125in SNAPSHOT isolation, 288TABLESAMPLE clause in, 376–381TOP clause in, 329vs. XQuery, 446–449

SelectCommand command, 753–754Self axes in XQuery, 440Self-signed certificates, 702Semicolons (;)

for CTEs, 344for SSB, 497

“Send a message and go on about yourbusiness” technique, 481–482

Send method, 136SEND statement, 522, 531–532SEND ON CONVERSATION statement, 489Send type in business transactions, 530–534SendResultsEnd method, 137

SendResultsRow method, 137SendResultsStart method, 137SENT BY clause, 529SENT BY ANY clause, 529SEP (SQL Server event provider), 825, 855Separation of users and schemas, 220,

248–252Sequences in XQuery, 432–434, 464SequentialAccess method, 664, 668, 670Serializable attribute, 40, 211SERIALIZABLE isolation level, 278–282Serialization

of DataSets, 756IBinarySerialize for, 167–170of transactions, 502for UDTs, 189–191in XML, 15, 143

Serialization class, 756Serialization namespace, 191Server class, 762

properties in, 769–770, 777, 789for SMO, 787–789

Server Explorer, 953Server Management Objects (SMO), 761–764

vs. ADO, 767–769best practices, 890configuration management for, 815–817connections in, 778

automatic, 780–782default Windows credentials, 778–780managing, 785–787Runas utility, 780–781SQL Server login, 783–785Windows identity impersonation,

782–783object model for, 763, 769–772objects in, 790

GetSmoObject for, 793–794identity of, 790–793methods of, 802–807parent navigation, 800–802properties for, 796–800state of, 802URN retrieval object model, 795–796URN retrieval restrictions, 794–795

projects, 772–777purpose of, 764–767scripts for, 807–816Server class for, 787–789

Server rules, 11

Index 987


Server Service Broker (SSB) service, 24–25best practices, 888business transactions in. See Business

transactionsfor events, 294–296message processing in, 494–499for messaging applications, 481–493in Query Notification, 675–676

Server-side cursors, 689ServerConnect object, 784ServerConnection class, 787–788

for credentials, 778–779for passwords, 784–786properties of, 782–783for SMO, 780–781, 814

Service Broker. See Server Service Broker(SSB) service

Service master keys, 238–241Service-oriented database applications, 879

architecture, 891–894best practices, 882–891data models and programming, 880–881new features, 879–880platforms in, 894–895tiers in, 881–882

Service programs, 503–509ServiceMasterKey property, 800Services

in messaging applications, 486Notification. See Notification ServicesWeb. See Web Services

Session keys, 243, 702SESSION_TIMEOUT clause, 627SESSIONS clause, 627set method, 173–175SET NEW_BROKER statement, 513SET ROWCOUNT statement, 333SET TRANSACTION ISOLATION LEVEL

SNAPSHOT statement, 696Setback field, 175SetDefaultInitField method, 800SetFieldValue method, 865SetHostControl method, 30SetParameterProperties method, 656SETUSER statement, 256SGen utility

for UDTs, 188–190for XML serialization, 143

SGML (Standard Generalized MarkupLanguage), 14

Showplans, XML-based, 304–305, 308Shredding XML documents, 419Signing

certificates, 243code, 259–261

Simple Mail Transfer Protocol (SMTP), 826,874–876

Simple Message Service (SMS), 25, 826Simple Object Access Protocol (SOAP)

protocol, 21message format in, 573payload format for, 597for stored procedure, 616–618for Webmethods, 576–577

SimpleEnum class, 100SimpleType class, 415SINGLE_BLOB keyword, 301, 421SINGLE_CLOB keyword, 301, 421SINGLE_NCLOB keyword, 301, 421SITE option, 570Skip validation, 423Sleep method, 138SMO. See Server Management Objects

(SMO)SMO.dll file, 824SMO namespace, 761SmoCollectionBase class, 769–772, 800SMS (Simple Message Service), 25, 826SMTP (Simple Mail Transfer Protocol), 826,

874–876Snapin for Performance Logs and

Alerts, 739SNAPSHOT isolation level, 278–286

in ADO.NET, 695–697versioning in, 286–289

SNI (SQL Server Network Interface),643, 736

SOAP (Simple Object Access Protocol)protocol, 21

message format in, 573payload format for, 597for stored procedure, 616–618for Webmethods, 576–577

SOAP over HTTP, 562–563Social Security numbers, 70–74Solution Explorer, 774, 923, 928–930Solutions in SSMS, 924–937Sort Options property, 668Source property, 687sp_addapprole stored procedure, 250

Index988


sp_addlogin stored procedure, 226, 236sp_addmessage stored procedure, 147sp_addtype stored procedure, 11sp_adduser stored procedure, 62, 228, 250sp_bindrule stored procedure, 11sp_bindsession stored procedure, 116sp_bindtoken stored procedure, 120sp_delete_http_namepace_reservation

stored procedure, 568–569sp_grantlogin stored procedure, 226sp_helptext stored procedure, 224sp_http_generate_wsdl_defaultcomplexor-

simple stored procedure, 579, 613sp_http_generate_wsdl_defaultsimpleor-

complex stored procedure, 605sp_lock stored procedure, 284sp_OACreate stored procedure, 62, 221sp_reserve_http_namespace stored

procedure, 568–569sp_setapprole stored procedure, 229sp_tableoption stored procedure, 299sp_unsetapprole stored procedure,

229–230sp_xml_preparedocument stored procedure,

419–420Specialized namespace, 807Specification compliance in hosting, 58–59Specify Values for Template Parameters

dialog box, 939–943SPIDs (system process identifiers), 36Spinlock, 34SQL BULK INSERT provider, 638SQL catalogs and tables, mapping to XML,

424–425sql:column function, 424, 465–467SQL_COPT_SS_MARS_ENABLED

option, 695SQL engine enhancements, 277

best practices, 885–886BULK Provider, 301DDL triggers, 289–294Event Notifications, 294–297large value data types, 298–301plan guides and plan forcing, 306–308query hints, 303–308SNAPSHOT isolation, 278–289statement-level recompilation, 301–303

SQL_LONGVARBINARY data type, 654SQL native client, 641–645, 695SQL Profiler utility, 578

SQL-PSM queries, 19SQL Server 1205 errors, 321SQL Server event provider (SEP), 825, 855SQL Server Integration Services (SSIS),

186, 749SQL Server login

encryption support in, 702–703failover support in, 701–702password change support in, 699–701query notifications in, 673–676for SMO connections, 783–785

SQL Server Management Studio (SSMS)configuration, 925–927for Event Notifications, 297introduction, 923–924null instances in, 179Object Explorer in, 944–948solutions and projects in, 924–937templates in, 937–944for UDTs, 171for XML-based showplans, 305, 308

SQL Server Network Interface (SNI),643, 736

SQL Server Operating System (SQLOS), 28,32–34

SQL Server Service Broker. See Server ServiceBroker (SSB) service

SQL Server Surface Area ConfigurationUtility, 221–224, 571–572

SQL_VARBINARY data type, 654sql:variable function, 465, 467SQL_VARIANT data type, 90–91, 418SQL_WLONGVARCHAR data type, 654SQL_WVARCHAR data type, 654sqlBatch element, 623sqlBatchResponse element, 623–624, 626SQLBinary type, 83SqlBoolean data type, 80, 88, 90, 92SqlBulkCopy class, 150, 749–752SqlBulkCopyColumnMapping class, 750SqlBulkCopyColumnMappingCollection

class, 750SqlBytes data type, 83, 668SqlCacheDependency class, 673, 685–687SqlChars data type, 83, 668SqlClient, 8, 23

ADO.NET with, 705asynchronous support, 743–748base classes and factories, 705–709bulk import, 749–752

Index 989


SqlClient (Continued)configuration information, 712–713connection strings, 715–717enumerating data sources, 714–715generic coding considerations, 717–719.NET Framework DataSet and

SqlDataAdapter enhancements,753–758

provider factories, 709–712schemas, 719–731statistics, 752–753tracing data access, 731–743

classes and methods in, 117for in-process data access, 111–115, 150–151for transactions, 128

SqlClient class, 624, 631, 643, 680SQLCLR

best practices, 883–884permissions with, 261–262thread synchronization in, 34in Visual Studio, 957–960, 962Web Services from, 140–143

SQLCMD, 171SqlCommand class, 112, 623

constructors for, 121for MARS, 692members of, 122–124return data for, 125–127for SMO, 765for UDTs, 653

SqlCommandBuilder class, 754–755, 757SqlConnection class, 112, 114

for client statistics, 752for MARS, 692members of, 118–121for transactions, 128, 130

SqlContext class, 115–118SqlDataAdapter class

for DataSet upgrading, 757enhancements, 753–758for providers, 708

SqlDataReader class, 112for clients, 667for MARS, 692–694for return data, 126–127with SqlCommand, 121, 123UDTs from, 648–654

SqlDataRecord class, 136–139SqlDateTime class, 757SqlDbType class, 658

for UDTs, 652with large data types, 669

SqlDecimal data type, 87SqlDependency class

for Query Notifications, 672–673, 677–682with SqlCommand, 123

SqlDependency.Start method, 677–679SqlDependency.Stop method, 679SQLDescribeCol class, 655SqlDouble data type, 86–87SqlError class, 113SqlErrorCollection class, 113SqlException class, 113SqlExecutionModes property, 814SqlFacet attribute, 173–174, 958–960SqlFunction attribute, 75–76, 94–97,

103–104, 960SqlInt32 data type, 85, 87–90, 92SqlMessages element, 621SqlMetaData class, 137–139SqlMetaDataFactory class, 728SqlMethod attribute, 177, 180sqlncli.h file, 696SQLNCLI provider, 629, 644SqlNotification class, 672SqlNotificationEventArgs class, 687SqlNotificationRequest class, 123, 673, 676,

680–685SQLNS. See Notification ServicesSQLOLEDB provider, 629, 631, 644–645SQLOS (SQL Server Operating System), 28,

32–34SqlParameter class, 113, 123, 624, 652SqlParameterCollection class, 113, 123SqlPipe class, 127, 135–137, 147.sqlplan extension, 305, 308SqlProcedure attribute, 75–76, 960SqlProviderFactory class, 709SqlQueryNotificationService class, 677SqlResponseStream scheme, 615SqlResultCode element, 616, 619SqlResultStream element, 615–616, 619–622SqlRowCount element, 616, 619, 621SqlRowSet element, 616, 619, 621SQLServer class, 624SqlServerSystem entry, 836Sqlservr.exe application, 763SqlSingle data type, 86–87SqlSmoObject class, 769–771, 790SqlStoredProcedure attribute, 108

Index990


SqlString data type, 81, 85–86, 92SqlTransaction class, 113SqlTrigger attribute, 75–76, 109, 960–961SqlTriggerContext class, 149–150SqlTypes class, 658–659, 755, 757

conversions, 82–86for DataSets, 756mapping, 82–83null values in, 77–79parameters and return values in, 93working with, 80–92

SqlUserDefined attribute, 166–167SqlUserDefinedAggregate attribute,

210–211, 961SqlUserDefinedType attribute, 159, 165, 167,

184, 961SQLXML, 563–564, 627

Bulk Load component in, 630–631, 636–638data type support in, 632–634features, 628–629.NET Framework for, 634–636operation of, 629–631support for, 638–640XML streams in, 631–632

SqlXml class, 633, 636, 662SqlXML element, 616SqlXmlAdapter class, 635SqlXmlCommand class, 635SqlXmlCommandType class, 635SqlXmlException class, 635SQLXMLOLEDB providers, 629–632SqlXmlParameter class, 635SSB. See Server Service Broker (SSB) serviceSSIS (SQL Server Integration Services), 186, 749SSL (Secure Sockets Layer)

encryption in, 239for Web Services, 608–609

SSMS. See SQL Server Management Studio(SSMS)

SSNRegex function, 74SSPROP_INIT_MARSCONNECTION

property, 695Stack overflow conditions, 36Stack walks, 270Standard Generalized Markup Language

(SGML), 14Standards in hosting, 58–59StartDocument method, 191STARTED state for endpoints, 552, 571<StartTime> element, 855

State propertyin Server, 769–770in SqlSmoObject, 802

Statement-level recompilation, 301–303Statement-level SNAPSHOT isolation, 279States, endpoint, 571–572Static type checking, 449, 476Statistics for SqlClient, 752–753StatisticsEnabled property, 752STATUS option for WITH ACTIVATION,

507–508Status values for stored procedures, 107STDEV function, 207STOPPED state for endpoints, 552, 571Stored procedures

in ADO.NET, 718–719attributes for, 75–77in clients, 703in CLR, 106–109extended, 5–6, 62, 115for Notification Services, 825, 850–852Object Explorer for, 944–945parameters for, 106–108in T-SQL, 5in Visual Studio, 959for Web Services, 614–622

Stream property, 668Streams in SQLXML, 631–632Strict validation, 423StringCollection class, 807Strings

in CLR, 70–74concatenation, 254for dates, 154in LDim, 160–164in Visual Studio, 958–959in XML, 415–416, 659–661in XQuery, 463

Strong typing, 449, 476Strongly typed instances, 400struct keyword, 158Structured data types, 10–11Structured exception handling, 6

in hosting, 34–37for in-process data access, 143–149in T-SQL, 312–326

Subscriber class, 860, 862–863SubscriberDevice class, 860, 864SubscriberId property, 865Subscribers, 830, 858–860, 862–865

Index 991


Subscription classes, 860–862, 865–866SubscriptionClasses class, 840–841, 860Subscriptions, 822–823, 830, 858–860,

865–867SUBSTRING data type, 299SUM function, 14, 207–208SumLDim function, 156, 209–214, 216–217Summary window, 945SuppressFinalize method, 920Surface Area Configuration tool, 221–224,

571–572SUSER_NAME function, 258Sybase database system, 561Symmetric keys, 239–243, 247Synchronization

thread, 34transaction, 501

Synonyms, 252–253sys.assemblies table, 53–54sys.assembly_files table, 53, 55sys.assembly_references table, 53–55sys.conversation_endpoints view, 489,

510, 520sys.conversation_groups view, 520sys.databases view, 288sys.dm_clr views, 33sys.dm_clr_appdomains view, 41sys.dm_os views, 33sys.dm_os_memory_clerks view, 33sys.dm_qn_subscriptions view, 689sys.dm_tran_ views, 288sys.endpoints view, 572–573sys.http_endpoints view, 572–573, 608–610sys.routes view, 547sys.soap_endpoints view, 579sys.transmission_queue system view, 490sysadmin role, 231syslogins view, 225SYSTEM clause, 377System.Data.dll file, 726System.Data.OracleClient.dll file, 726SystemDataAccess property, 76, 95–96SystemDataAccessKind attribute, 117System metadata tables, 52–55System process identifiers (SPIDs), 36

TT-SQL (Transact-SQL), 19, 880

for asynchronous messages, 24

best practices, 886enhancements

APPLY operators, 340–343CTEs, 343–350error handling, 312–326improvements, 311–312INTERSECT and EXCEPT operators,

326–329ON DELETE and ON UPDATE clauses,

333–336OUTPUT clause, 336–340partitioning, 373–376PIVOT operator, 359–365ranking functions, 366–373recursive queries, 351–359TABLESAMPLE clause, 376–381TOP clause, 329–333UNPIVOT operator, 365–366

integration of, 4–7null values in, 78–79for pulley function, 68–69scripts, 807

Table-access links, 266Table-valued CLR functions, 99–106TableCollection class, 771TableDefinition property, 96, 103–104,

106, 960TABLESAMPLE clause, 376–381Tabular Data Stream (TDS) protocol, 561,

718, 881TARGET direction for contracts, 529Target property, 109, 960targetNamespace attribute, 17, 395,

604–605Task management for runtime hosting, 33TDS (Tabular Data Stream) protocol, 561,

718, 881TEMPDB file, 286Template Explorer, 937–940Templates in SSMS, 937–944Terminate method, 210, 214–215, 217Test Scripts folder, 954, 959TEXT data type

in clients, 667, 669for LOBs, 298vs. MAX, 299in XML, 387, 389

text function, 407TextA event, 735TextW event, 735

Index992


Threadsin AppDomains, 43, 748scheduling, 33–34

Tiles, 371–373TIMEOUT clause, 515Timeouts for conversations, 514–515Timers for conversations, 514–515Timestamps in message processing, 496TNS (transparent network substrate), 718TO SERVICE clause

for broker instances, 546–547for contracts, 529–530for conversations, 549for messaging applications, 488, 491–492

ToBytes method, 169, 202–204TOP clause, 329–333ToString method

for DataSet serialization, 756in LDim, 160–162, 177, 197in Object, 906overriding, 159for UDTs

from DataReaders, 649in ODBC, OLE DB, and ADO, 654

TPC-C (Transaction Processing PerformanceCouncil benchmark C) benchmark, 28

Tracepoints, 731tracerpt command, 734Tracing data access, 225, 731–732

application choices for, 736–740basis of, 742CSV files for, 734–735ETW in, 742–743for parameter binding problems, 740–742reading output in, 735–736running, 733–734setting up, 732–733user data in, 736

Transact-SQL. See T-SQL (Transact-SQL)transaction.h file, 696Transaction-level SNAPSHOT isolation, 279Transaction library, 697Transaction Processing Performance Council

benchmark C (TPC-C) benchmark, 28TransactionRequired attribute, 133Transactions

autonomous, 132–133best practices, 133–135business. See Business transactionsdeclarative and promotable, 697–699

distributed, 130–132, 135, 697for in-process data access, 128–135isolation levels in, 695–699

Transactions library, 129, 699TransactionScope class, 128–129

for distributed transactions, 131–132, 135for promotable transactions, 697–698SqlConnections in, 130

TransactionScopeOption, 132–133, 698Transparent network substrate (TNS), 718TreeMember method, 813–814TriggerContext property, 117Triggers

in CLR, 109DDL, 289–294for in-process data access, 149–150

TrustServerCertificate parameter, 702–703TRY/CATCH blocks, 145–147, 312–314,

320–325TryParse method, 85–86Tuples, 442Type property

for SMO objects, 796–797in SqlNotificationEventArgs, 687

TYPE qualifier FOR XML, 408–409Typed XML, 395–400Types, 8

for clients, 645–646defined, 914SqlTypes, 77–92SQLXML, 632–634user-defined. See User-defined types

(UDTs)Visual Studio, 958–959XML. See XML and XML data typein XQuery, 443in XSD, 16

UUDDI (Universal Description, Discover and

Integration) servers, 607UDTs. See User-defined types (UDTs)UNCHECKED DATA option, 57Undo method, 140Unicode strings, 92Uniform Resource Names (URNs)

retrieval restrictions, 794–795for SMO object identity, 790–793

UNION ALL operator, 352, 354

Index 993


UNION queries, 404UNIQUE constraints, 385UNIQUEIDENTIFIER class

for broker instances, 544for conversation groups, 519–520for messaging applications, 487

Units of work, 500Universal Description, Discover and

Integration (UDDI) servers, 607UNPIVOT operator, 365–366UnquoteIdentifier property, 731UNSAFE ASSEMBLY permission, 43, 49UNSAFE security level, 39, 43

for assemblies, 264–265, 271–272finalizers in, 36

Untyped XML, 395–400Update command, 849, 854, 868UPDATE permission, 230, 266UPDATE statement

CTEs with, 343OUTPUT clause in, 336–338TOP clause in, 329–330

UpdateBatchSize method, 757UpdateCommand command, 753–754UPDATETEXT method, 670Updating XML columns, 468–475URLs

ACL for, 567–568endpoint, 570–571reservations for, 569for Web Services, 610–611

URNs (Uniform Resource Names)retrieval restrictions, 794–795for SMO object identity, 790–793

USE PLAN query hint, 306UseEventHandler method, 745–746UseExecXmlReader method, 663User assemblies, maintaining, 55–58User-defined aggregates, 207–209

best practices, 885creating, 214–215implementing, 209–214

User-Defined Function template, 954User-defined functions, 93–99User-defined types (UDTs), 11–14, 153

aggregate, 14best practices, 885binary implementation in, 165–170for clients, 646

from DataReaders, 648–654

.NET Framework, 647–648in ODBC, OLE DB, and ADO, 654–656

comparing, 12creating, 157–160, 170–173Format.Native vs. Format.UserDefined

implementations, 215–217helper functions in, 181–183IBinarySerialize, 167–170maintaining, 185–187null value implementation in, 160objects for, 193–197overview, 154–156permissions for, 267public members in, 173–181purpose of, 153–154source code for, 196–206string implementation in, 160–164validation in, 183–185in Visual Studio, 959in XML, 23, 187–193

User ID keyword, 114USER keyword in AddUser, 94User libraries, 39user_name function, 228UserDefinedFunctions class, 954UserDefinedMessage class, 803Users

adding to databases, 62in authentication, 226schema separation from, 248–252

UseWaitHandles method, 747using directive, 913–914, 921

for row insertions, 113for subscribers, 862for transactions, 129

UTF-16 code, 386, 389

VVALID_XML validation, 525–527ValidateFullNull function, 96ValidateSSN function, 71–73ValidateSSNFull function, 73–74ValidateSSNFullNull function, 80–81ValidateSSNTSQL function, 70–71, 81Validation

for message types, 525, 528for UDTs, 183–185for user libraries, 39XML, 18, 423

Index994


ValidationFunctions class, 71–72ValidationMethodName property, 184Value comparison operators, 464VALUE indexes, 477, 479value method, 403, 452–454Value property

for SMO objects, 799in SqlTypes, 81

ValueType class, 158VAR function, 207VARBINARY data type

in clients, 667vs. MAX, 299maximum size for, 298in messaging applications, 489–490in SQLXML, 629in XML, 387, 389

VARCHAR data typein clients, 669with MAX, 299maximum size for, 298in messaging applications, 493with OpenXML, 420for SqlTypes, 92in SQLXML, 629in Visual Studio, 959in XML, 384, 387, 408

VariablesUDT, 13XML, 390–391, 397

verbose flag in Update command, 849<Version> node, 837Versioning in SNAPSHOT isolation, 279–286

drawbacks of, 286–288monitoring, 288–289

Versions for assemblies, 904–905VIEW DEFINITION permission, 224Virtual memory, 33Visibility

of classes, 59in CLR type system, 908–909

Visual Basic null values, 78–79Visual Studio, 949–955

attributes in, 957–961automatic deployment in, 956–958for connection information, 717data types and SqlFacet in, 958–959executing and debugging code in, 959–962for Notification Services, 820

void data type, 915

WWAIT FOR clause, 297, 677WaitAll method, 747WaitAny method, 747WaitHandles method, 746WatchDirectory argument, 853Watching rowsets, 674Web Service Definition Language

(WSDL), 141generation process, 605–614working with, 578–604

Web Services, 561–564best practices, 888–889HTTP endpoints

creating, 569–570declarations, 564–569metadata, 572–573in SQLXML, 639SSL for, 608–609states, 571–572URL, 570–571

and marshaling data, 20–22proxy classes, 141SQL batches, 622–626from SQLCLR, 140–143SQLXML for. See SQLXMLSSL for, 608–609stored procedures for, 614–622Webmethods, 573–578WSDL for

generation process, 605–614working with, 578–604

XML namespaces for, 604–605WEBMETHOD clause, 604–605, 621, 626Webmethods, 573–578WebRequest class, 48WELL_FORMED_XML validation, 525, 528,

533–534Well-formedness rules, 18WHERE clause

in DELETE, 338in XQuery, 442–443

WHERE EXISTS clause, 327WHERE NOT EXISTS clause, 327–328Whitespace entitization, 413 Wildcards

in OUTPUT, 339in XML, 418, 479

Windows groups, 229

Index 995


Windows identity impersonation,782–783

Windows Management Instrumentation(WMI), 816–817

Windows XP Support Tools, 568WindowsIdentity property

for in-process data access, 139–140in SqlContext, 117

WindowsImpersonationContext, 140WITH ACTIVATION clause, 505, 507–508WITH CLEANUP clause, 513WITH ERROR clause, 512WITH EXECUTE AS clause, 139, 255–259WITH keyword for CTEs, 343WITH RELATED_CONVERSATION_

GROUP clause, 519WITH RETENTION clause, 541–542WITH SCHEMA COLLECTION clause,

526–528WITH SCHEMABINDING clause, 95,

230, 675WITH TIES clause, 331WITH XMLNAMESPACES clause, 407WithDependencies option, 810–811WithEvents keyword, 681WITHOUT LOGIN clause, 228WMI (Windows Management

Instrumentation), 816–817WMI CIM Studio tool, 733Wmi class, 815Write method

in IBinarySerialize, 167–170in LDim, 160, 202in MAX, 299–300in XML, 469

WriteLine method, 765–766WriteSchema method, 727WRITETEXT method, 670WriteXml method

in DataSet, 727in IXmlSerializable, 191–193in LDim, 206

Ws-Security specification, 627WSDL (Web Service Definition

Language), 141generation process, 605–614working with, 578–604

wsdl_generator_procedure, 579WSDLComplex query strings,

599–603, 608

WSDLMinimal query string, 608, 613WSDLSimple query strings, 599–603, 608

XX.509 certificates, 239, 243XACT_ABORT function, 317–319XACT_STATE function, 314–317XDR format schema, 409xdt:anyAtomicType data type, 433xdt:untyped data type, 433xdt:untypedAtomic data type, 395, 433, 448XLINQ language, 893XML and XML data type, 18, 383–384

ANSI SQL standard compliance for,422–426

best practices, 886–887on clients, 23–24, 657

in ADO, 664–667in ADO.NET, 657–659documents, fragments, and FOR XML

support, 661–664as string, 659–661

for columns, 384–387conversions with, 387, 389–390functions, 402–403indexes for, 401–402for inserting data, 387–388loading from files, 420–422mapping, 414–418as marshaling format, 20–22namespaces in, 16–17, 604–605OpenXML enhancements, 418–420overview, 14–20producing, 407–409query languages for. See XPath query

language; XQuery query languageschemas in, 17–18

collections, 392–400for Notification Services, 824in XQuery, 433, 475–477

SELECT with. See FOR XML clausein service-oriented database applications,

892for showplans, 304–305, 308in SQLXML, 634streams in, 631–632typed, 395with UDTs, 187–193for variables and parameters, 390–391

Index996


XML for Analysis, 24xml.nodes function, 419XML Schema Definition language (XSD)

standard, 14, 16, 21data types in, 392–393inline format schema, 409–411restrictions in, 394

xml_schema_namespace function, 398XML/XSLT formatter, 825XmlDocument class, 659XmlElement function, 423XmlForest function, 423XmlIgnore attribute, 191XmlNavigator model, 24xmlns attribute, 17XMLPARSE method, 422XmlReader class, 24, 631, 658–659,

663–664XmlReaderSettings class, 662XMLSERIALIZE method, 422XmlSerializer class, 141–143XmlSerializerAssembly attribute,

189–190XmlSerializerContract class, 189XmlSerializers.dll file, 189–190XmlWriter class, 24, 191xp_cmdshell program, 221xp_sendmail program, 221XPath data() function, 407XPath query language, 18–20, 429

expressions in, 439for SMO objects, 791–793in SQLXML, 634

XPathNavigator class, 192XQuery 1.0 and XPath 2.0 Data Model

specification, 461XQuery 1.0 and XPath 2.0 Functions and

Operators Version 1.0 specification, 461XQuery query language, 19–20

best practices, 887bodies in, 437–446functions in, 465–467indexes in, 477–480introduction, 431–436multiple-document queries in, 468operators in, 464–467optimizing, 402overview, 429–431prologs in, 436–437schemas in, 475–477

in service-oriented database applications,892

vs. SQL, 446–449inside SQL Server, 475–480updating XML columns, 468–475with XML data type, 449–450

exist method, 450–452functions, 461–463nodes method, 457–461query method, 454–457typed, 395value method, 452–454

vs. XSLT, 445xs:date data type, 417xs:double data type, 448xs:maxOccurs facet, 417xs:minOccurs facet, 417xs:pattern facet, 417xs prefix, 393xs:string data type, 448xs:time data type, 417xs:timestamp data type, 417XSD (XML Schema Definition language)

standard, 14, 16, 21data types in, 392–393inline format schema, 409–411restrictions in, 394

xsd:annotations data type, 426xsd:any data type, 418xsd:base64Binary data type, 416, 426xsd:dateTime data type, 418xsd:decimal data type, 416xsd:hexBinary data type, 426xsd:integer data type, 417xsd:length facet, 416xsd:maxLength facet, 416xsd:string data type, 415–417xsi:nil attribute, 192, 405xsi:prefix, 393xsi:schemaLocation attribute, 394, 410xsi:type attribute, 397XSL transform for Notification Services,

872–873XSLT (Extensible Stylesheet Language

Transformations), 19, 445XsltFormatter tool, 842, 872

Yyield return instruction, 100–101, 104–105

Index 997
