Thực hành tốt nhất Với mỗi bộ hồ sơ trả lại, SQL Server sẽ, theo mặc định, cũng gửi một thông điệp rõ số hàng bị ảnh hưởng hoặc trả lại. Đây không chỉ là một phiền toái, nhưng tôi đã tìm thấy trong thử nghiệm chính thức của tôi mà nó có thể làm chậm một truy vấn bằng cách lên đến 17 phần trăm tùy thuộc vào độ phức tạp của câu truy vấn. | Part IV Developing with SQL Server FIGURE 24-2 The path and scope of return methods differ among the five possible methods of returning data. Client App Best Practice With every returned record set SQL Server will by default also send a message stating the number of rows affected or returned. Not only is this a nuisance but I have found in my informal testing that it can slow a query by up to 17 percent depending on the query s complexity. Therefore get into the habit of beginning every stored procedure with the following code CREATE PROC MyProc AS SET NOCOUNT ON Summary Using stored procedures is a way to save and optimize batches. Stored procedures are compiled and stored in memory the first time they are executed. No method is faster at executing SQL commands or more popular for moving the processing close to the data. Like a batch a stored procedure can return a record set by simply executing a SELECT command. The next chapter covers user-defined functions which combine the benefits of stored procedures with the benefits of views at the cost of portability. 622 Building User-Defined Functions q a. .2 . e j ïvçv 8 Kl S V i si 1 . - ÎTv- . ïî . . vS- sM 4 v - A . .H SQL Server 2000 introduced user-defined functions UDFs and the SQL Server community was initially slow to adopt them. Nevertheless UDFs were my personal favorite new feature in SQL Server 2000 and I still use them frequently. The community discovered that UDFs can be used to embed complex T-SQL logic within a query and problems that were impossible or required cursors could now be solved with UDFs. The result is that UDFs have become a favorite tool in the toolbox of any serious SQL Server database developer. The benefits of UDFs can be easily listed UDFs can be used to embed complex logic within a query. This is huge. I ve solved several nasty problems using user-defined functions. UDFs can be used to create new functions for complex expressions. UDFs offer the benefits
