Structured Query Language (SQL)


  • Be familiar with the programming environment and industry-standard coding language.

SQL is an industry-standard programming language used to insert, retrieve, modify, and delete data in a relational database. SQL also contains statements for defining and administering the objects in a database. SQL is the language supported by almost all database systems, and is the subject of published industry standards. SQL Server 2000 uses a version of the SQL language called Transact- SQL, or T-SQL.

Transact-SQL Language Elements

Transact-SQL is the language containing the commands used to administer SQL Server, to create and manage all objects in SQL Server, and to insert, retrieve, modify, and delete all data in tables. T-SQL is an extension of the language defined in the SQL standards published by the International Standards Organization (ISO) and the American National Standards Institute (ANSI).

The basic elements of T-SQL are those used to view and edit the data in underlying tables and views. The basis for any database system is the handling of ACD operations which will use these T-SQL operations. ACD is an acronym used in data management for add, change, and delete. These four primary functions for Add (Insert), Change (Alter, Update), Delete, and T-SQL's capability to read and view data (known as "Querying" the data), are the basis for most SQL Server database activity.

In T-SQL you read data with a SELECT statement, add data with an INSERT operation, remove data with DELETE , and change data with UPDATE . T-SQL is presented throughout this book; for a detailed look into the structure and use of the language go to Chapter 6, "Programming SQL Server 2000."

Executing a Program

Executing T-SQL statements and other commands against a database can be achieved using a variety of techniques. You can develop front-end applications with Visual Basic, Visual C, Access, the Internet, or in combination with other data and programming interfaces. Commands can be executed through ODBC or OLE-DB standard libraries connecting to the server from virtually any computer. Statements can be executed directly on the server using the Query Analyzer or OSQL.

Query Analyzer

Figure 1.1. shows the Query Analyzer, which is your primary tool for executing Transact-SQL code, known as queries. This utility is discussed at great depth throughout the book. Most of the queries you execute are INSERT queries that add data, DELETE queries that remove data, SELECT queries that retrieve data, and UPDATE queries that change existing data. You can also create database objects such as views and indexes.

Figure 1.1. The Query Analyzer ”your primary querying tool.

graphics/01fig01.jpg

SQL Query Analyzer is an interactive, graphical tool that enables a database administrator or developer to write queries, execute multiple queries simultaneously , view results, analyze the query plan, and receive assistance to improve the query performance. The Execution Plan options graphically display the data retrieval methods chosen by the query optimizer.

Some queries, however, are not used to change data, but they will modify database and table design. These types of queries are called data definition statements and are used for tasks such as creating tables, indexes, views, and so on.

Not only can Query Analyzer execute any T-SQL statements, but it can also analyze them. Query Analyzer can report things such as how much time the queries took to run, how much time it took to read data from the hard disk, and so on.

If you think a particular query is inefficient, you can use tools built into the analyzer to diagnose the query to redesign the query in a way to achieve better performance. If you look at Figure 1.1, you can see a screen display of the Query Analyzer requesting all the data from the Authors table in the Pubs database. The bottom half is known as the Results Pane and contains the output of your query, which is known as a resultset.

ODBC Structured Query Language (OSQL)

OSQL is an interactive command prompt utility provided with SQL Server that replaces the ISQL utility used with SQL Server 6.5 and earlier versions. ISQL is still being supported for backward compatibility, but future versions of SQL Server will not support its use. Both ISQL and OSQL provide similar functionality. Both utilities enable users to execute T-SQL statements or batches from a server or workstation and view the results returned. ISQL performs this activity using the near obsolete DB-Library protocol, whereas OSQL uses industry-standard ODBC mechanisms.

The OSQL utility is similar to the Query Analyzer in that it executes batches of T-SQL code. The utility is run on the command line. Other than this, the two tools (Query Analyzer and OSQL) perform more or less the same function.

NOTE

OSQL Uses OSQL can be used interactively to execute one or more T-SQL statements, display results in a text window, or save results to a text file. Its primary purpose, however, is to allow the scheduling of operations via the operating system scheduler, SQL Server scheduler, or any other schedule application provided by a third party.


The question arises, "Then why use the OSQL utility?" The answer: One reason is scheduling.

It is possible to custom-schedule the OSQL utility. Say that you need to access sales figures on a daily basis. You could extract data manually every morning using the Query Analyzer, but this way would be too time consuming and ineffective . Or you could set up a job to automate the process. But the best and most efficient way would be to query with OSQL and save the results into a text file. You could then use scheduling to make that process automatic. The following represents the options for the OSQL utility:

 OSQL[  -?  ]  [  -L  ]      [      {{  -U   login_id  [  -P   password  ]}  E  }      [  -S   server_name  [  \   instance_name  ]][  -H   wksta_name  ]      [  -d   db_name  ][  -l   time_out  ][  -t   time_out  ][  -h   headers  ]      [  -s   col_separator  ][  -w   column_width  ][  -a   packet_size  ]      [  -e  ][  -I  ][  -D   data_source_name  ][  -c   cmd_end  ][  -q "   query   "  ]      [  -Q "   query   "  ][  -n  ][  -m   error_level  ][  -r  {    1  }]      [  -i   input_file  ][  -o   output_file  ][  -p  ][  -b  ][  -u  ][  -R  ][  -O  ]     ] 

The -L parameter lists the locally configured servers and the names of the servers broadcasting on the network. Use -U to supply a login; if -U is omitted, the Windows login is passed in from environment variables , or alternatively, the operating system. If you omit the -P password option, you are prompted to provide your password. If neither the -U or the -P options are used, SQL Server 2000 attempts to connect using Windows Authentication mode. For more information on the other switches and particular examples, consult SQL Server Books Online.

Figure 1.2 illustrates the OSQL command prompt being used to execute a query that has been stored as a SQL script.

Figure 1.2. The OSQL Command Interface.

graphics/01fig02.gif



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net