1 / 12

Executing T-SQL Statements SQL Server 2000 provides several methods to execute T-SQL Statements:

Executing T-SQL Statements SQL Server 2000 provides several methods to execute T-SQL Statements: Execute single statement Execute statement as a batch Execute statements through Stored Procedures and Triggers. How to Execute single statement? Optimizing

terry
Download Presentation

Executing T-SQL Statements SQL Server 2000 provides several methods to execute T-SQL Statements:

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Executing T-SQL Statements SQL Server 2000 provides several methods to execute T-SQL Statements: Execute single statement Execute statement as a batch Execute statements through Stored Procedures and Triggers

  2. How to Execute single statement? Optimizing The process to determine the most efficient way to extract the requested data is called as ‘Query Optimizing’ Query Optimizer The component that performs this process is called as Query Optimizer. Optimization The process of choosing one execution plan from several possible plans is called Optimization. The query optimizer is important b’caz it enables server to adjust dynamically to changing conditions in the database without requiring input from a programmer or from a database administrator. This process enables programmers to focus on describing the final result of the query.

  3. Processing a Select Statement: The steps used to process a Select Statement are- • The parser scans select statement and breaks it into logical units such as keywords, expressions, operators, identifiers. • A query tree/sequence tree is built by describing logical steps needed to transform the source data into format needed by the result set. • The query optimizer analyses all of the ways in which the source tables can be accessed and selects the series of steps that will return the result fastest .The query is updated to record this exact series of steps and the final optimized version of the query is called optimization plan. • The relational engine begins to execute the plan. As steps that need data from base tables are processed, the relational engine uses the OLE DB the storage engine to pass up data from the row sets that are requested from the relational engine. • The relational engine process the data returned from the storage engine into the format defined for the result set and returns the result set to the client.

  4. Processing Other Statements: The basic steps applied to execute select statements are also applied to other SQL staements suct as insert, update, delete.

  5. Batches: • A batch is a group of one or more T-SQL statements sent all at once from an application to SQL server for execution. • SQL server compiles the statements of a batch into a single executable unit. • The statements in the execution plan are then executed one at a time. • A compiler error prevents the compilation of the execution plan so that none of the statements in the batch are executed. • A run-time error has two effects- 1. Most run-time errors stop the current statements and the statements that follow it in the batch. 2. A few run-time errors such as constraint violation, stop only the current statement . All the remaining statements in the batch are executed.

  6. (….continued) • As a batch is compiled into a single execution plan , a batch must be logically complete. • The execution plan created for one batch has no capacity to reference any variables declared in another batch. • Comments must start and end in the same batch.

  7. Rules To Apply Batches: • Create default, create procedure, create rule, create trigger and create view cannot be combined with other statements in the batch. • The create statement must begin the batch. All other statements that follow in that batch will be interpreted as part of definition of the first Create statement. • A table cannot be altered and then the new columns referenced in the same batch. • If an execute statement is the first statement in the batch , the execute keyword is not required. The Execute keyword is required if the execute statement is not the first statement in the batch.

  8. Go command: • It is used to signal the end of the batch. • It is not a T-SQL statement. • It is simply signals to the utilities how many SQL statements should be included in a batch. • In SQL Query Analyzer all of the T-SQL statements from one Go command to the next are put in the string sent to SQLExecDirect.

  9. Use Of Go Command: Use pubs Go Create view Sample_vw As Select * From authors Go Select * from Sample_vw Go

  10. Batch Processing: Each batch is compiled into a single execution plan. If the batch contains multiple SQL statements, all fo the optimized steps needed to perform all of the statements are built into a single execution plan. There are different ways to specify a batch: 1. All of the T-SQL statements sent in a single execution unit from an application make up a single batch and generate a single execution plan. 2. All of the statements in a stored procedures or triggers make up a single batch and generate a single execution plan. 3. The string executed by an Execute statement is a batch compiled into a single execution plan. 4. The string executed by an sp_executesql is a batch compiled into a single execution plan.

  11. T-SQL Script: • A script is a series of T-SQL statements stored in a file. The file can be used as input to SQL Query Analyzer. The utilities then execute the SQL statements stored in the file. • T-SQL scripts have one or more batches. The Go command signals the end of batch. • If T-SQL sript do not have any Go commands, it is executed as a single batch.

  12. Use of T-SQL Scripts: • Keeping a permanent copy of the steps used to create and populate the databases on your server. 2. Transferring the statements from computer to another. 3. Quickly educating new employees by enabling them to find problems in the code, to understand the code, or to change the code.

More Related