# MASTERING SQL SERVER 2000- P5

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

0
50
lượt xem
6

## MASTERING SQL SERVER 2000- P5

Mô tả tài liệu

Tham khảo tài liệu 'mastering sql server 2000- p5', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0)

Lưu

## Nội dung Text: MASTERING SQL SERVER 2000- P5

1. 170 CHAPTER 5 • TRANSACT-SQL OVERVIEW AND BASICS TABLE 5.8: SQL SERVER DATEPART CONSTANTS Constant Meaning yy or yyyy Year qq or q Quarter mm or m Month wk or ww Week dy or y Day of year (1 to 366) dd or d Day hh Hour mi or n Minute ss or s Second ms Millisecond For example, the DATEADD function takes as arguments a datepart, a quantity, and a date. It returns the result of adding the given quantity of the given datepart to the given date. Thus, to add three days to the current date, you could use the follow- ing expression: PRINT DATEADD(d, 3, GETDATE()) WARN I NG The datepart constants are not strings and thus should not be enclosed in single quotes. Here’s the full list of available date and time functions: • DATEADD adds time to a date. • DATEDIFF reports the number of dateparts between two dates. • DATENAME extracts textual names (for example, February or Tuesday) from a date. • DATEPART returns the specified datepart from a specified date. • DAY returns the day from a date. • GETDATE returns the current date and time. • MONTH returns the month from a date. • YEAR returns the year from a date.
2. FUNCTIONS 171 Mathematical Functions SQL Server supplies almost two dozen mathematical functions for manipulating inte- ger and floating-point values. These functions include all the common functions that you’d naturally expect to find in any programming language. Table 5.9 lists the avail- able mathematical functions. TABLE 5.9: MATHEMATICAL FUNCTIONS IN T-SQL Function Meaning ABS Absolute value ACOS Arccosine ASIN Arcsine ATAN Arctangent PA R T ATN2 Arctangent of the angle defined by two angles II CEILING Smallest integer greater than the expression COS Cosine COT Cotangent DEGREES Converts radians to degrees Transact-SQL EXP Exponential FLOOR Largest integer smaller than the expression LOG Base 2 logarithm LOG10 Base 10 logarithm PI The constant pi POWER Exponentiation operator RADIANS Converts degrees to radians RAND Random number generator ROUND Rounds floating-point numbers by precision SIGN Sign of the expression SIN Sine SQRT Square root SQUARE Square TAN Tangent
3. 172 CHAPTER 5 • TRANSACT-SQL OVERVIEW AND BASICS TI P SQL Server uses radians to measure angles for trigonometric functions. System and Metadata Functions System and metadata functions return internal information about SQL Server and the data it’s storing. Most of these functions are pretty obscure, and you can find a full list in the T-SQL help in Books Online. However, you might find a few of the following functions useful in your databases: • The CONVERT function converts one type of data to another (for example, inte- ger to character). • The CURRENT_USER function returns the name of the current user (the one running the SQL batch). • The ISDATE function will tell you whether its input represents a valid date. • The ISNULL function replaces any null value with a specified replacement value. • The ISNUMERIC function will tell you whether its input is a number. Figure 5.8 demonstrates the use of these functions in SQL Query Analyzer.
4. FUNCTIONS 173 FIGURE 5.8 Some useful system functions PA R T II Transact-SQL User-Defined Functions SQL Server 2000 also allows you to define your own functions for use anywhere you can use the system-defined functions. To do this, you use the CREATE FUNCTION statement: CREATE FUNCTION [owner_name].function_name ( [{@parameter_name data_type [=default_value]} [,…n]] ) RETURNS data_type [AS] {BEGIN function_body END}
5. 174 CHAPTER 5 • TRANSACT-SQL OVERVIEW AND BASICS NOTE This definition has been simplified somewhat. In particular, we’ve omitted the clauses you’d use to return a table from a user-defined function. See Books Online for more details. For example, you could define a function named TwoTimes in the following way: CREATE FUNCTION TwoTimes ( @input int=0 ) RETURNS int AS BEGIN RETURN 2 * @input END After it’s been created, you could call this function as part of a SELECT statement: SELECT OrderID, dbo.TwoTimes(Quantity) AS Extra FROM [Order Details] Figure 5.9 shows the result set from this query. Note that you need to specify the owner of the function (by default, the creating user—in this case, dbo, the owner of the database) when you call the function, even if you don’t specify the owner when you create the function. NOTE You’ll learn more about the SELECT statement in Chapter 6.
6. EXECUTING T-SQL 175 FIGURE 5.9 Calling a user-defined function PA R T II Transact-SQL Executing T-SQL So far, the few examples we’ve shown for executing SQL have all used SQL Query Analyzer. In this section, we’ll look at Query Analyzer in a bit more detail. Then we’ll consider two alternatives for executing SQL: SQL Enterprise Manager and the com- mand line OSQL utility. Using Query Analyzer In addition to simply executing queries, Query Analyzer offers some additional func- tionality to make it both easier to use and more powerful. In this section, you’ll learn how to create, save, and retrieve queries; how to view results in several formats; and
7. 176 CHAPTER 5 • TRANSACT-SQL OVERVIEW AND BASICS how to view the execution plan of a query, which is a list of the actions that SQL Server will undertake to deliver the results of the query. Creating a Query You’ve already learned how to create a query to test arbitrary SQL statements, but let’s review the steps here: 1. Launch Query Analyzer from the Start menu by choosing Programs ➢ Microsoft SQL Server ➢ Query Analyzer. 2. Choose the SQL Server that you want to connect to from the combo box. This box will show servers with which you’ve recently connected. To see other servers on your network, click the Browse button. You can also use the special name “(local)” to connect to a server on the computer that you’re using. 3. Either click the Windows NT Authentication option button or click the SQL Server Authentication option button, and supply your SQL Server username and password. If you don’t know how to log on, try Windows NT Authentication first, before you call your database administrator. We recommend this option for all new installations of SQL Server. 4. Click OK to log on to the server. 5. A new query window appears. You can select a database to use from the combo box on the main Query Analyzer toolbar. You can also type in as many SQL statements as you’d like to execute. 6. Click the Execute Query button or press F5 to see the results. You can also use the New Query button on the toolbar to open additional query windows. Query Analyzer will let you open an almost unlimited number of windows, so you don’t have to lose one set of results to try something else. Saving a Query Query Analyzer lets you save SQL batches for later. This is useful for complex queries that you might want to run again in the future. It’s also useful if you need to keep track of versions of a SQL batch during development; you can save the SQL batch and use a source code control tool such as Visual Sourcesafe to store it. For example, you might have a query that gives you aggregate sales results by joining half a dozen tables from your sales database. Once you’ve perfected the query, you’ll want to save it so you don’t have to type in the complex SQL code again the next time that you want to see current results.
8. EXECUTING T-SQL 177 To save a query, choose File ➢ Save from the Query Analyzer menu or click the Save button. You’ll need to supply a filename, of course. By default, Query Analyzer uses .SQL as an extension for queries. Opening a Saved Query To open a previously saved query, choose File ➢ Open from the Query Analyzer menu or click the Open button. Browse to the query you want to open and click OK. The query will be displayed in the current query window, and you’ll be able to execute it immediately. Viewing Results Query Analyzer lets you view results in two formats. The first format, results in text, is the format that we’ve used for all of the examples so far in this chapter. This format is most useful for queries that return only a bit of information. The other format is to view the results in a grid. This is useful if the query returns a PA R T set of records. Figure 5.10 shows a set of results in a Query Analyzer grid. II FIGURE 5.10 Viewing results in a grid Transact-SQL To switch from one format to the other, choose the Execute Mode drop-down tool- bar button, or select Query ➢ Results in Text or Query ➢ Results in Grid from the Query Analyzer menus.
9. 178 CHAPTER 5 • TRANSACT-SQL OVERVIEW AND BASICS TI P As you can see in Figure 5.10, white space is generally not significant in the T-SQL language. You can insert new lines, spaces, and tabs to make your SQL statements more readable. You can also select Query ➢ Results to File to save the results instead of seeing them immediately on-screen. Viewing the Execution Plan Query Analyzer can also show you the execution plan for any query. The execution plan is the set of steps that SQL Server will use to execute the query. This information is useful because each step will show its estimated relative cost (in time). You can use this tool to locate bottlenecks in your applications and to help you make changes to slow queries to make them faster. To see the execution plan for a query, select Query ➢ Display Estimated Execution Plan or use the Ctrl+L shortcut. Figure 5.11 shows the execution plan for a query. Each step is represented by an icon. If you make the mouse hover over an icon, you’ll see detailed information for that step. FIGURE 5.11 Viewing a query’s execution plan
10. EXECUTING T-SQL 179 N OTE There’s more information on using execution plans to optimize queries in Chapter 26. Viewing a Server Trace Query Analyzer can show you exactly which operations were performed on the server when executing a query. This is similar to the tracing provided by SQL Server Profiler, which we mentioned in Chapter 3. To see a server trace for a query, select Query ➢ Show Server Trace. Figure 5.12 shows a sample server trace. FIGURE 5.12 PA R T Viewing the server trace for a query II Transact-SQL TI P One use for a trace is identifying statements in a batch that take a long time to com- plete. The Duration column shows the number of milliseconds taken by each statement. Using SQL Server Enterprise Manager SQL Query Analyzer is not the only tool that will let you execute SQL statements. You can also use the tools within SQL Server Enterprise Manager to evaluate queries. To do so, you need to save the queries as either views or stored procedures within a database, so this method is less useful for ad hoc exploration of the language. On the other hand, the visual designer for views makes it easy to create quite complex queries.