Microsoft SQL Server 2005 Developer’s Guide- P53

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:10

Thêm vào BST

Báo xấu

47
lượt xem 2
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Microsoft SQL Server 2005 Developer’s Guide- P53:SQL Server 2005 is a feature-rich release that provides a host of new tools and technologies for the database developer. This book is written to help database developers and DBAs become productive immediately with the new features and capabilities found in SQL Server 2005.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Microsoft SQL Server 2005 Developer’s Guide- P53

Appendix: SQL Profiler 499 Figure A-5 Query plan diagram displayed on the Trace Properties dialog. This tab will display an area for you to save the extracted Showplan events to either a single .SQLPlan file or separate .SQLPlan files for each event. When the trace is run, you can select the Showplan items from the upper portion of the trace window to display the query plan diagram in the lower portion of the window. Figure A-5 shows a query plan diagram.
This page intentionally left blank
Index References to figures are in italics. opening a DSN-less connection with OLE DB Provider for ODBC, A 270–271 opening a trusted connection using administrative tools, 14 OLE DB Provider for SQL Server, ADO, 258–260 273–274 adding the ADO reference to Visual overview, 262–263 Basic, 263–264 Recordset object, 281–305, 308–312 architecture, 260–262 transactions, 322–324 batch updates, 320–321 types of cursors, 281–282 closing a Recordset, 289–290 ADOMD.NET, 418 Command object, 301–305, 312–318 adding references, 422–423 Connection object, 305–307 AdomdCommand object, 427–434 connecting to SQL Server, 265–281 AdomdConnection object, 423–426 connecting to SQL Server using a UDL AdomdDataAdapter object, 434–435 file, 276–277 AdomdDataReader object, 427–430 connecting to SQL Server using the building BI applications with, Data Link dialog, 277–281 421–436 connection string keywords, 266 CellSet object, 432–434 data bound Recordsets, 295–298 connection string keywords, 425–426 error handling, 318–320 CubeDef object, 436 finding and bookmarking rows, 298–301 object model, 419–421 forward-only Recordset object, XMLReader object, 431–432 283–289 ADO.NET keyset Recordset object, 290–295 architecture, 170–172, 173 modifying data with, 307–316 core classes in System.Data, MSDASQL prompt constants, 269 177–182 and OLE DB, 260 namespaces, 172 opening a connection using the .NET Data Providers, 172–177 Connection object’s properties, aggregates, CLR, 105–110 274–276 Analysis Management Objects (AMO), opening a connection with OLE DB 417–418 Provider for ODBC, 265–270 hierarchy, 418–419 opening a connection with OLE DB Analysis Server Scripts projects, 9 Provider for SQL Server, 271–273 501 Copyright © 2006 by The McGraw-Hill Companies. Click here for terms of use.
502 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Analysis Services common table expressions (CTEs), 62–64 databases, 17 Connect To Server dialog box, 7 overview, 416–421 connection pooling, 187–189 projects, 17–18 constraints, 36–37 application definition files (ADFs), 140–141, CREATE DATABASE statement, 35 144–152 credentials, 46 AS keyword, 51–52 Cube Wizard, 18 assemblies, 78, 80, 81–82 cursors, 60–62 assisted editors, 14 dynamic, 283 asymmetric keys, 47–48 forward-only, 283 asynchronous query support, 209–210 keyset, 283 static, 283 B BI Development Studio. See Business D Intelligence (BI) Development Studio Data Mining Designer, 18 BLOB data, retrieving, 212–215 data source views, 18 BULK INSERT statements, 68–69 Data Transformation Pipeline (DTP), Business Intelligence (BI) Development 375–376 Studio, 14–16 Data Transformation Runtime (DTR), Designer window, 16 376–377 Output window, 20 data types, 37–38 Properties window, 20 Database Tuning Advisor, 14 Solution Explorer window, 16–20 databases, 35 Toolbox window, 20 DataReader, 204–215 DDL triggers, 45 C debugging, CLR database objects, 110–115 defaults, 40 certificates, 48 DELETE statements, 71–72 CLR, 78 Dimension Wizard, 18 aggregates, 105–110 DML architecture, 79–80 modifying data with, 65–74 creating database objects, 82–110 querying and updating with T-SQL database object components, 80–83 DML, 49–75 debugging database objects, 110–115 dynamic SQL, executing with the ADO enabling support, 80 Connection object, 305–307 stored procedures, 83–90 dynamic SQL statements, executing, 191–193 system views, 115 triggers, 94–98 user-defined functions (UDFs), 90–94 E user-defined types (UDTs), 99–105 error handling clustered indexes, 39 ADO, 318–320 CommandBuilder, 216–220 CLR, 74–75 Common Language Runtime. See CLR SMO, 474–475
Index 503 F J FLWR (For-Let-Where-Return) statements, joins, retrieving related data using, 56–58 230–231 full-text catalogs, 49 L full-text searching, 48–49 logins, 46 functions, 43–45 G M Management Studio. See SSMS Generate SQL Server Scripts Wizard, 5, 7, 8 master keys, 47 GROUP BY clause, 53–54 Microsoft Full-Text Engine for SQL Server (MSFTESQL), 48–49 H multiple active result sets (MARS), 210–211 HTTP SOAP creating SOAP endpoints, 247–249 N using SOAP endpoints, 249–253 .NET Data Providers, 81, 172 adding the System.Data.SqlClient I namespace, 182–183 Import Analysis Service 9.0 Database, 19 connection string keywords, 184–186, indexed views, 39 189–190 indexes, 38–40 core classes, 175–177 INSERT statements, 65–68 namespaces, 173–174 instance configuration files (ICFs), 140–144 .NET database object security, 112–115 INSTEAD OF triggers, 72 nonclustered indexes, 39 Integration Services Notification Services APIs, 404–412 application definition files (ADFs), breakpoints, 395–397 140–141, 144–152 checkpoints, 397–398 building a .NET subscription/event creating configurations, 400–403 application, 158–166 Data Transformation Pipeline (DTP), building applications using nscontrol, 375–376 155–157 Data Transformation Runtime (DTR), building applications using SSMS, 376–377 153–155 deploying packages, 399–404 compiling applications, 139 overview, 374–375 defining applications, 139 Package Deployment Utility, 403–404 events, 136–138 package security, 399 firing data events using .NET, 163–166 projects, 18 firing data events using T-SQL, 166 SSIS Designer, 18–19, 378–395 instance configuration files (ICFs), SSIS Import and Export Wizard, 140–144 377–378 notification engine, 138–139 transactions, 398 notifications, 138
504 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Notification Services (Cont.) Report Model Project template, 20 overview, 136 Report Project template, 19 sample application, 140–157 Report Project Wizard, 19–20 subscriptions, 138, 159–163 Reporting Services updating applications, 157 accessing reports, 362–363 nscontrol, building Notification Services architecture, 326–327 applications using, 155–157 authoring tools, 328, 348–358 command-line installation options, 331 O components, 327–329 configuration and management tools, Object Explorer window, 5–8 328, 341–347 OLE DB, 256 configuration tool, 342–344 and ADO, 260 creating a report, 364–369 architecture, 256–258 deploying reports, 369 OLE DB Provider for ODBC development stages, 363–364 provider-specific keywords, 267–268 embedding a browser control on OLE DB Provider for SQL Server a Windows form, 360 connection string keywords, 272 extensions, 338–340, 361–362 ORDER BY clause, 53 installing, 329–336 output, 73–74 parameterized queries, 368–369 Output window, 20 parameters used with ADDLOCAL and REMOVE properties, 335 P programmability, 329, 359–362 parameterized queries, 368–369 report authoring, 363–371 parameterized SQL statements, executing, Report Builder, 357–358 193–196 Report Designer, 348–353 PIVOT operator, 64–65 Report Manager, 328, 340–341 prepared SQL, 301–305 Report Model Designer, 353–356 Profiler, 14 Report Processor, 337–338 Properties window Report Server, 328, 336–340 BI Development Studio, 20 Report Wizard, 348–350 SSMS, 10–11 rs utility, 344–345, 346 rsconfig utility, 344, 345 Q rskeymgmt utility, 345–347 running reports, 369–371 Query Analyzer. See Query Editor Scheduling and Delivery Processor, 338 Query Builder, 24–26 Setup .ini file options, 332–334 parameterized queries, 368–369 starting Internet Explorer from Query Editor, 11–13, 22–24 a Windows form, 359–360 using SOAP, 361 R Results window, 13 Registered Servers window, 4–5 roles, 47 Report Definition Language (RDL), 362 rules, 40
Index 505 S system views, 132–133 using queues, 124–125 scalar-valued functions, 43–44 SMO, 440–441 schemas, 47 adding SMO objects to Visual Studio, scripts, generating, 5–7 441–442 searching, 48–49 connecting to the selected SQL Server security, 46–48 system, 461–463 CLR, 112–115 creating databases, 468–469 opening a trusted connection, 186–187 creating the Server object, 442–444 SSIS packages, 399 Database object hierarchy, 453–456 SELECT DISTINCT statement, eliminating error handling, 474–475 duplicate rows with, 54–55 getting property values, 444 SELECT INTO statement, creating tables hierarchy, 449 using, 55 instance classes, 452 SELECT statements, 49 JobServer object hierarchy, 457–459 building queries using, 50–51 listing columns, 465–466 filtering using the WHERE clause, listing databases, 463–464 51, 52 listing registered SQL systems, 461 grouping results with GROUP BY, listing tables, 464–465 53–54 namespaces, 450 ordering results with ORDER BY, 53 property collections, 445–449 renaming columns with AS, 51–52 retrieving column attributes, 467–468 using the TOP clause, 56 sample application, 459–475 server types, 6 Server object hierarchy, 452–453 Service Broker setting property values, 445 activation, 131–132 showing T-SQL script for tables, Adjacent Broker Protocol, 121 472–474 architecture, 118–121 Table and View object hierarchy, contracts, 120 456–457 creating objects, 126–127 transferring tables, 469–472 Dialog Protocol, 121 utility classes, 450–451 dialog security, 132 Solution Explorer window dialogs, 120–121 BI Development Studio, 16–20 enabling, 122–124 SSMS, 8–10 message transport protocol, 121 SQL Management Objects. See SMO messages, 119 SQL Mobile Scripts projects, 10 new T-SQL commands, 122, 123 SQL Profiler, 492 queues, 120 predefined templates, 494 retrieving messages from a queue, replaying a trace, 497 130–131 Showplan events, 497–499 sample application, 125–131 starting, 492–496 sending messages to a queue, 127–129 starting, pausing, and stopping a trace, services, 120 496–497
506 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e SQL Server Management Studio. See SSMS symmetric keys, 48 SQL Server Scripts projects, 9 synonyms, 41 SqlAdapter object, 215–220 system views Sqlcmd utility, 478 CLR, 115 command shell, 478–479 Service Broker, 132–133 command-line parameters, 479–483 System.Data developing scripts with Query Editor, Constraint, 181 485–487 DataColumn, 179–180 extended commands, 484 DataException, 182 nesting scripts, 488 DataRelation, 181 variables, 484–485, 487–488, 489 DataRow, 180 SqlCommand object, 190–200 DataSet, 178, 179 SqlConnection object, 183–190 DataTable, 178–179 SqlDataReader object, 204–215 DataView, 180 SqlDependency object, 201–204 DataViewManager, 180 SSIS. See Integration Services ForeignKeyConstraint, 181 SSMS UniqueConstraint, 181 building Notification Services applications, 153–155 T Object Explorer window, 5–8 tables, 36–43 overview, 2–3 table-valued functions, 44–45 Properties window, 10–11 temporary tables, 37 Query Builder, 24–26 Toolbox window, 20 Query Editor, 11–13, 22–24 TOP clause, 56 Registered Servers window, 4–5 transactions, 73 Results window, 13 ADO, 322–324 Solution Explorer window, 8–10 executing, 198–200 as T-SQL development tool, 22–27 Integration Services, 398 user interface, 3–4 triggers, 45 using projects, 26–27 CLR, 94–98 version control, 27 trusted connections, 186–187 static SQL, 301–305 Try-Catch, 74–75 storage for searching, 48–49 T-SQL stored procedures, 41–43 creating database objects using T-SQL CLR, 83–90 DDL, 34–49 deploying, 89–90 development tools, 22–34 executing with Command objects, executing and debugging with Visual 316–318 Studio 2005, 33–34 executing with return values, 196–198 firing data events using, 166 extended, 83–84 querying and updating with T-SQL setting security, 89 DML, 49–75 using, 90 using Sqlcmd variables and T-SQL subqueries, 59–60 statements, 489 Summary Page, 7–8
Index 507 U X UNION statements, combining related data XML using, 58–59 bulk-loading XML documents, unique indexes, 39 245–246 Universal Data Link (UDL) files, 276–277 data type, 222–227 UNPIVOT operator, 64–65 Exist method, 231–232 UPDATE statements, 70–71 indexes, 40, 235–236 user-defined aggregates, 105–110 Modify method, 232–233 user-defined functions (UDFs), 90–94 nested For XML queries, 242 user-defined types (UDTs), CLR, 99–105 OPENXML keyword, 244–245 users, 46–47 Query method, 233–234 Value method, 234 V For XML Auto mode, 237 For XML Explicit mode, 237–239 version control, 12–13, 27 For XML Path mode, 240–241 views, 40–41 For XML Raw mode, 236 Visual SourceSafe, 12, 27 For XML Type mode, 239–240 Visual Studio 2005, 27–32 XSD schema generation, 242–244 executing and debugging T-SQL with, See also HTTP SOAP 33–34 XMLA protocol, 416, 417 XQuery, 227–231 W XSD schemas Web services, 250–253 data validation using, 223–227 WHERE clause, 51, 52 inline XSD schema generation, WSDL, 250 242–244