MASTERING SQL SERVER 2000- P11

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

0
40
lượt xem
9
download

MASTERING SQL SERVER 2000- P11

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

Tham khảo tài liệu 'mastering sql server 2000- p11', 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ủ đề:
Lưu

Nội dung Text: MASTERING SQL SERVER 2000- P11

  1. 520 CHAPTER 14 • STORED PROCEDURES 6. On the third screen, check the Insert box next to the authors table. Click Next. 7. On the final screen, click the Edit button. 8. On the Edit screen, change the name of the stored procedure to Authors_Insert, then click OK.
  2. UNDERSTANDING STORED PROCEDURES 521 9. On the final screen, click Finish. Note that the Edit button will allow you to change the code used to create the stored procedure, but you do not want to make any changes here. PA R T III Digging into SQL Server
  3. 522 CHAPTER 14 • STORED PROCEDURES If you would like to see exactly what this stored procedure will do for you, open it and look at its properties in Enterprise Manager. You will notice in Figure 14.1 that it is a simple stored procedure that uses input parameters to insert new records into the authors table. FIGURE 14.1 The Create Stored Procedure Wizard is designed to create quick, simple stored procedures. Now that you know how to create and use stored procedures, you need to know how to keep them running fast. Let’s get a little more in depth on how they work and how to optimize them. Optimizing Stored Procedures To optimize a stored procedure, it is best for you to understand a little more about how SQL Server executes queries. When SQL Server first executes a query (any query, not just stored procedures), it compiles the query first. The compiling process is just SQL Server peering inside your query to see what you are trying to accomplish. Specif- ically, SQL Server looks at what tables you are JOINing and what columns you have specified in the WHERE clause of your query. Once the server has this knowledge, it can develop an execution plan, which is a map of what indexes would return data fastest. Once the execution plan has been devised, SQL Server stores it in procedure cache, which is an area of RAM that has been specifically apportioned for this purpose.
  4. UNDERSTANDING STORED PROCEDURES 523 Now, whenever you run the same query again or a very similar query, SQL Server does not need to create another execution plan to get your data; it simply uses the execu- tion plan that has been stored in the procedure cache. This can cause a problem for you at times, though. For instance, you may need to change the structure (or schema) of your database, adding a new table or columns to an existing table. If this kind of change occurs, SQL Server will automatically recom- pile your stored procedures to use the changes in the structure. The only time the stored procedure will not be recompiled is if you create a new index; in that instance, you must recompile the stored procedure manually so that SQL Server can create an execution plan that takes advantage of the new index. Or, suppose that you have a stored procedure that uses widely varied input para- meters every time you run it. Some of those parameters may affect the JOIN or WHERE clause statements in the stored procedure, and because SQL Server uses those parameters to create an execution plan, it may not be wise to use the same execution plan every time the stored procedure is run—you may want to recompile it. You have two ways to force SQL Server to recompile your stored procedure; the first is by creat- ing it with the WITH RECOMPILE statement. WITH RECOMPILE will force SQL Server to create a new execution plan each and every time you execute the stored procedure and is the best way to create a stored pro- cedure that has input parameters that change drastically every time you use it (and affect the JOIN and WHERE clauses in the stored procedure). For example, if you wanted to recompile the Show_Authors stored procedure every time you run it, the code to create it would look as follows: CREATE PROCEDURE Show_Authors @city varchar(50) WITH RECOMPILE AS SELECT au_fname, au_lname, address, city, state, zip PA R T FROM authors WHERE city = @city III ORDER BY au_lname DESC It is the WITH RECOMPILE option that tells SQL Server to create a new execution plan every time the stored procedure is executed and not store that execution plan in Digging into SQL cache. That can be tedious and slow if you need to change the execution plan only occasionally, though. If that is the case, you should use the second method for recom- piling a stored procedure: the EXECUTE…WITH RECOMPILE statement. EXECUTE… Server WITH RECOMPILE tells SQL Server to create a new execution plan just this one time, not every time the statement is executed. If you use this statement, the code used to
  5. 524 CHAPTER 14 • STORED PROCEDURES create the stored procedure does not change, but when you execute the stored proce- dure, it looks as follows: EXEC Show_Authors WITH RECOMPILE By using these RECOMPILE statements, you can keep your stored procedures running fast. However, thus far, you haven’t secured them from prying eyes—let’s do that now. Securing Your Stored Procedures When you create a stored procedure, you are just creating a query that is stored on the server rather than on the client machines. These stored procedures are contained in the syscomments system table in each database and are completely accessible by default. This means that by executing a simple SELECT query against the syscom- ments table in the database where the stored procedure was created, your users could see all of the code used to create the procedure. This may not be desirable because one of the main uses of a stored procedure is to remove the user from the complexity and structure of the underlying tables, and, as we will discuss in Chapter 18, stored proce- dures are used for securing tables as well. By reading the definition of the stored pro- cedure right from syscomments, the users would be bypassing that security; in other words, they would be hacking. To avoid that, you should create stored procedures using the WITH ENCRYPTION statement. WITH ENCRYPTION is designed to keep prying eyes out of definitions stored in the syscomments table—not just for stored procedures, but for everything stored there (views, triggers, etc.). In the following exercise, you will execute a SELECT query against the syscomments table in the pubs database to see what is stored there and, therefore, what your users could see: 1. Open Query Analyzer and log in using Windows NT Authentication (unless you need to use SQL Server Authentication). 2. Enter the following code and execute it by clicking the green arrow button on the toolbar (you have to join the sysobjects table because the name is stored there—only the ID is stored in syscomments): USE Pubs SELECT ob.name, com.text FROM syscomments com JOIN sysobjects ob ON ob.id = com.id WHERE ob.name = ‘Show_Authors’
  6. UNDERSTANDING STORED PROCEDURES 525 3. Notice in the result set that you can read the code used to create and run the stored procedure. 4. To encrypt it, open Enterprise Manager, expand the pubs database, then select the Stored Procedures icon. 5. In the contents pane, double-click the Show_Authors stored procedure to bring up the properties. 6. To encrypt the stored procedure, change the definition to look as follows (notice the bold changes): CREATE PROCEDURE DBO.Show_Authors PA R T @city varchar(50) = ‘Oakland’ WITH ENCRYPTION III AS SELECT au_fname, au_lname, address, city, state, zip FROM authors Digging into SQL WHERE city = @city ORDER BY au_lname DESC Server
  7. 526 CHAPTER 14 • STORED PROCEDURES 7. Click OK to apply the changes. 8. To verify that it has been encrypted, double-click Show_Authors to bring up the properties again. You should receive an error message stating that the object is encrypted and therefore unreadable. Click OK to return to the stored procedure properties screen. 9. Return to Query Analyzer and execute the query from step 2 again; notice that this time you cannot read the text from syscomments, because it is full of unreadable characters (these characters may vary depending on your system).
  8. UNDERSTANDING STORED PROCEDURES 527 10. Close Query Analyzer. WAR N I N G Once you create an object, such as a stored procedure, using WITH ENCRYPTION, you cannot decrypt the object. Make sure you are finished modifying the object for a while before encrypting. User-defined stored procedures (the ones you make yourself) are a very powerful tool, but they are not the only stored procedures with which you have to work. Microsoft has PA R T given you a batch of ready-made stored procedures that are designed to help you work with system tables. These are called system and extended stored procedures. III Using System and Extended Stored Procedures Digging into SQL Microsoft has started using the term metadata quite a bit these days; it means informa- tion about information. When the term is applied to SQL Server, it means information about objects on the server, such as how big a database file is or what permissions a user has. When you want to change or read such system information, you could open Server the system tables directly and start fiddling with the data inside, but that usually turns
  9. 528 CHAPTER 14 • STORED PROCEDURES out badly because most of the values in the system tables are not designed to be under- stood by mere mortal humans (most of the values in these tables are numeric and not easily decoded). A much better way, the supported way, to change or read the system information is by using system stored procedures. Using System Stored Procedures Every time you add a database, add a login (which is used to grant access to SQL Server), create an index, or add or modify any object on the server, you are making changes to the system tables, which is where SQL Server stores information about your objects. The information stored in these system tables is mostly numeric data, which is difficult to read, let alone modify, directly. That is why Microsoft has given you scores of stored procedures (about 650) to help with the task of modifying system tables. They are all stored in the master and msdb databases, and most begin with the characters sp_. Here is a synopsis of some of the more common system stored procedures: sp_tables: This stored procedure will show you any object that can be used in the FROM clause of a SELECT query. This is useful if you have forgotten or just don’t know the exact name of the table or view you need to query. sp_stored_procedures: This will list all of the stored procedures available for your use. Again this is useful if you have forgotten or just don’t know the name of the procedure you need. sp_server_info: Using this procedure is the best way to determine how your SQL Server was configured at setup, such as the character set or sort order that was defined at install, what version of SQL Server you are running (for exam- ple, desktop or standard), etc. sp_databases: This lists all of the available databases on the server. It can be useful for finding database names. sp_start_job: This is used to start an automation job in SQL Server. This is very handy for jobs that are scheduled on demand. We’ll be discussing jobs and automation in Chapter 17. sp_stop_job: This procedure will stop a job that has been started already. sp_addlogin: This procedure is used to add a standard login to the server to allow users access to the server as a whole. This is very useful for creating a script that will regenerate user logins in the event of a system crash. We’ll dis- cuss security and logins in Chapter 18. sp_grantlogin: This is used to grant access on SQL Server to a Windows NT account. This should be combined with the sp_addlogin account to create a script to re-create user accounts in the event of a disaster.
  10. UNDERSTANDING STORED PROCEDURES 529 sp_setapprole: An account role in SQL Server (as you will see in Chapter 18) is used to make sure that only approved applications are used to access your database. This stored procedure activates the application role so that the user can access the database with the permissions that are granted to the applica- tion role. sp_password: As you will see in Chapter 18, there is a difference between standard and Windows NT login accounts; this stored procedure is used to change passwords for standard, and only standard, logins. sp_configure: Several global configuration options can be set to change the way SQL Server behaves. For example, you can tell the server whether to allow updates to system tables directly or how much system memory to use. The sp_configure stored procedure can be used to change such options. The avail- able options are listed here: • affinity mask • allow updates • concat_null_yields_null • cost threshold for parallelism • cursor threshold • default full-text language • default language • extended memory size • fill factor • index create memory • language in cache • lightweight pooling PA R T • locks III • max degree of parallelism • max server memory • max text repl size Digging into SQL • max worker threads • media retention • min memory per query Server • min server memory • nested triggers
  11. 530 CHAPTER 14 • STORED PROCEDURES • network packet size • numeric_roundabort • open objects • priority boost • query governor cost limit • query wait • recovery interval • remote access • remote login timeout • remote proc trans • remote query timeout • resource timeout • scan for startup procs • set working set size • show advanced options • spin counter • time slice • two digit year cutoff • user connections • user options sp_attach_db: All of the databases on your SQL Server have a record in the sysdatabases system table in the master database. This record tells SQL Server where the database is on disk, how big it is, etc. If you were to lose your master database and (heaven forbid) not have a good backup, you would need to run this stored procedure to re-create the records in sysdatabases for each of the databases on your server. sp_processmail: SQL Server is capable of not only sending, but receiving and responding to e-mail. When SQL Mail is configured (which you will learn how to do in Chapter 17), you can send a query via e-mail to the MSSQLServer service. When you run this stored procedure, the MSSQLServer service will read the query in the e-mail and send back the result set.
  12. UNDERSTANDING STORED PROCEDURES 531 sp_monitor: This stored procedure gives a quick snapshot of how your server is doing—i.e., how busy the processor is, how much RAM is in use, etc. sp_who: You cannot perform some administrative tasks, such as renaming or restoring a database, if someone is using it at the time. To find out who is using a database on the server so that you can disconnect them, use the sp_who stored procedure. sp_rename: This will change the name of any object in the database. sp_renamedb: This will change the name of the database itself. sp_help: This can be used to find information about any object in the data- base. It returns properties such as created date, column names, foreign-key con- straints, etc. sp_helptext: This is used to display the actual text that was used to create an object in the database. This information is read from the syscomments table. sp_help*: There are many other stored procedures that have sp_help as the first few characters. All of them are designed to give you specific information about a type of object in the database. These system stored procedures are used like any other stored procedure. Let’s look at an example: 1. Open Query Analyzer from the SQL Server 2000 group under Programs on the Start menu and log in with Windows NT Authentication (unless you must use SQL Server Authentication). 2. To use sp_help to get information about the authors table in the pubs database, enter and execute the following code: USE Pubs EXEC sp_help ‘authors’ PA R T III Digging into SQL Server
  13. 532 CHAPTER 14 • STORED PROCEDURES 3. To see how your SQL Server is faring at the moment, use the sp_monitor stored procedure: EXEC sp_monitor 4. Close Query Analyzer.
  14. UNDERSTANDING STORED PROCEDURES 533 Using Extended Stored Procedures Another type of stored procedure is the extended stored procedure. These do just what the name implies: They extend the capabilities of SQL Server so that it can do things that a database server would not ordinarily be capable of doing. For example, you wouldn’t expect a database server to be able to execute a command from the com- mand prompt, but thanks to an extended stored procedure that comes with SQL Server, called xp_cmdshell, SQL Server can do just that. Extended stored procedures are just C++ code saved in and executed from a Dynamic Link Library (DLL). Most of the extended stored procedures are executed with other system stored procedures, so you won’t use them very often by them- selves, but here is a short list of the ones you may use: xp_cmdshell: This stored procedure is used to run programs that are ordi- narily run from the command shell, such as the dir command or md (make directory). This comes in very handy when you need to have SQL Server create a directory for automatically archiving BCP files or something of that nature. xp_fileexist: This procedure can be used to test for the existence of a file and, if that file exists, to do something (such as BCP) with it. The following code shows you how to test for the existence of the autoexec.bat file. If @ret = 1, the file exists; if it equals 0, the file does not exist. This is not documented in Books Online or on the Microsoft Web site, so we will give you the syntax here. The second line declares a variable to hold an output parameter, the third line calls the procedure with an output parameter, and the fourth line displays the output (note that this must be done in the master database): USE Master DECLARE @ret int EXEC xp_fileexist ‘c:\autoexec.bat’, @ret output SELECT @ret PA R T xp_fixeddrives: This shows you the drive letters of the fixed disks and how III many MBs of available space are on each one. Again, each of these extended stored procedures is executed just like a regular stored procedure. Let’s try some here: Digging into SQL 1. Open Query Analyzer from the MS SQL Server group under Programs on the Start menu and log in with Windows NT Authentication. 2. To use xp_cmdshell to get a directory listing of your C drive, enter and execute Server the following code: EXEC xp_cmdshell ‘dir c:’
  15. 534 CHAPTER 14 • STORED PROCEDURES 3. To see whether you have a file named autoexec.bat on your C drive, enter and execute the following code (it will return a 1 if the file exists): DECLARE @ret int EXEC xp_fileexist ‘c:\autoexec.bat’, @ret output SELECT @ret 4. Close Query Analyzer.
  16. SUMMARY 535 Summary In this chapter, you learned all about stored procedures. You learned first what they are—just a collection of Transact-SQL statements, usually a query, that is stored cen- trally on the server waiting to be executed by users. The advantage to storing these centrally is that when your users execute them, they are not sending hundreds of lines of code over the network and thus bogging it down—they are sending only one line of code: EXEC stored_procedure. These stored procedures are also easier to man- PA R T age than dispersed code because when you need to make a change to the code, you have to do it only at the server rather than running around to each client machine. III After learning what stored procedures are, you learned how to create them. You learned first how to create a simple stored procedure that returns a result set to the user using static parameters that cannot be changed. Next you learned how to allow Digging into SQL users to control the information they get back by using input and output parameters. Then you learned how to create a simple query for inserting, updating, or deleting data using the Create Stored Procedure Wizard. Server
  17. 536 CHAPTER 14 • STORED PROCEDURES Once that section was complete, you learned how to optimize some stored proce- dures by recompiling them when necessary. Then you learned that all stored procedures have a record associated with them in the syscomments table that contains all of the text that is used to create and execute the procedure. To secure this code, you learned that you can encrypt the entry in syscomments with the WITH ENCRYPTION clause. After that, you discovered the power of system and extended stored procedures. The system stored procedures are the easiest and best way to modify system data, and the extended stored procedures are used for extending the abilities of SQL Server beyond those of a normal database server. Now that you have stored procedures under your belt, you can make access to your data faster and more efficient. However, you still need to be able to control what the users are putting in those databases. In the next chapter, we will introduce you to one method of controlling that data: using triggers.
  18. CHAPTER 15 Using Triggers F E AT U R I N G : Understanding Triggers 538 Advanced Considerations 560 Summary 569
  19. A s a database administrator or developer, you want to be able to control what data your users are inserting, updating, or deleting in your tables. For example, you may not want a user to be able to delete a customer account from one table if there is a pending sale for that account in another table. For that type of control, a simple foreign-key relationship will work just fine. Another example would be when you want your users to insert and update data, but not delete it. In that instance, you would just need to modify the security settings on your server to deny delete permissions to your users for that one table (we’ll dis- cuss permissions in Chapter 18). Suppose, though, that you have a credit limit column in your customers table and that you do not want users to be able to increase that credit limit past $10,000 with- out management approval. Or suppose that you want to automatically notify a man- ager every time a customer is deleted from a database so that the manager can ask the person who deleted the account for details. Maybe you want to know when a user has inserted a new customer so that you can track the user’s sales and give them a big, fat bonus later. In each of these examples, you cannot use the simple permissions or foreign-key relationships—you need to use triggers. In this chapter, we are going to discuss all four types of triggers: INSERT, UPDATE, DELETE, and INSTEAD OF. We’ll see not only how they work, but also how you can use them to enforce complex business logic on your databases. We’ll begin by getting a basic understanding of triggers. Understanding Triggers A trigger is a collection of SQL statements that looks and acts a great deal like a stored procedure (which we discussed in Chapter 14). The only real difference between the two is that a trigger cannot be called with the EXEC (short for execute) command; triggers are activated (or fired) when a user tries to insert, update, or delete data. For example, suppose that you have defined an INSERT trigger on a customer information table that states that your users cannot add a new customer from outside the United States. As soon as any user tries to insert a new customer, the INSERT trigger will exe- cute and determine whether the record passes the criteria set forth in the trigger. If the record passes, the insert is completed; if the record does not pass, the record is not inserted. SQL Server is able to block data modifications if they don’t pass your stringent cri- teria because triggers are considered transactions. A transaction (as discussed in Chap- ter 8) is a block of Transact-SQL code that SQL Server treats as a unit. Code is grouped
  20. UNDERSTANDING TRIGGERS 539 into a transaction by placing a BEGIN TRAN statement at the beginning of the code and a COMMIT statement at the end, either by the user (an explicit transaction) or by SQL Server (an implicit transaction). Because a trigger is seen as a transaction, you need to add only the ROLLBACK command to the appropriate spot in the code if you don’t want to let a record pass the trigger. The ROLLBACK command will cause the server to stop processing the modification and disallow the transaction, forgetting that it ever took place (this is true of all types of triggers). To go one step further, you can send an error message to the user who tried to vio- late the trigger by using the RAISERROR() command. If you want to get really fancy, you can even tell on them and have the error message sent to a manager. In this sense, triggers can be thought of as database watchdogs. If you’ve never seen a watchdog in action, it may help to visualize it. A watchdog is generally used to guard animals out in the pasture—cows, sheep, horses, etc. The watchdog just quietly sits and waits, doing nothing, until something happens—such as a predator approaching the flock. As soon as that predator comes up, the watchdog springs into action, barking, chasing, and attacking until the predator has been vanquished. Trig- gers act in the same way, waiting quietly on the database server until a user tries to modify data, then springing into action to enforce your business logic. Of course there are other ways to enforce business logic For example, you learned about foreign-key relationships in Chapter 4. With a foreign-key relationship in place between a customers table and an orders table, you can keep your users from deleting a customer with a pending order. You can also keep a user from inserting an order for a customer who does not exist in the customers table. You will also learn about permissions in Chapter 18, where you will find that you can deny users the permission to insert, update, or delete. If, for example, you deny insert permission to some of your users, those users cannot insert any records at all. The same goes for the update and delete permissions—if any of these are denied, the action just does not take place. If any of these permissions are granted, the users can do whatever they would like with very little inhibition. PA R T These methods are great for implementing simple business logic, such as marketing III cannot delete, but they can insert or customers cannot be deleted if they have a pending order. Most companies have business logic that is a great deal more complex than that. They may, for example, have a business rule that states sales cannot update a Digging into SQL user’s credit limit to exceed $10,000 without management approval or a user may not delete a customer with a credit limit above $10,000. These are very common business rules that cannot be implemented by using the foreign-key relationships or permissions on a table. Only by using triggers can you properly enforce this complex business logic. Server Let’s start that process now, by working with INSERT triggers.
Đồng bộ tài khoản