1. 수동

일단, 복사할 파일은 .asmx와 /bin/***.dll 파일입니다.

이외에 global.asax와 Web.config 파일을 사용한다면, 이 파일도 함께 복사하여야 합니다.

운영 웹 서버에 디렉토리를 만든 뒤에, 앞서 언급한 파일을 복사하신 후에, IIS 콘솔에서 가상디렉토리를 만드시면 됩니다.

출처 : http://www.devpia.com/Forum/BoardView.aspx?no=24222&page=6&Tpage=11&forumname=aspnet_qa&stype=&ctType=&KeyR=title&KeyC=

3. 웹 설치 프로젝트 이용

Introduction

Deployment is a great feature in Visual Studio .NET which allows you to deploy your applications by creating a Setup Program. You can make a setup project for both your Windows and Web applications.

Using this feature for deploying has many advantages for example:

• Installed applications can be uninstalled completely.
• If an error occurred through installation process, the system goes back to the state of before the installation.
• Adding user interface to setup project easily.
• Making setup project on different kinds of distribution media such as CD or floppy disks.
• Easy reinstallation when installed files get corrupted.

In this article, I will create a simple ASP.NET Web project and then create a setup project for deploying that.

First of all create an ASP.NET Web project using Visual Studio .NET and name it for example "TestWeb". In the WebForm designer (WebForm1.aspx). Drag and drop a Label and a button server control. Double click on button and then add the following code and compile the project.

private void Button1_Click(object sender, System.EventArgs e) {     Label1.Text = "My first deployment web project"; }  

Creating the Web Setup project

After creating the web project or opening an existing web project, You can use VS.NET installer to create a Web Setup project. So do the following steps.

Add a Web Setup project to the solution through File/New/Project menu. Choose the Setup and Deployment Projects in Project Types tree control and after that select the Web Setup Project from Templates list box and name it WebSetup. Be aware of select the Add to Solution radio button.

Now the project is added to the Solution Explorer, and you can see the File System Editor. With File System Editor, you can add files and dependencies to the setup project and configure the location of installation.

Now you must add project files and dependencies to the setup project. For this reason right click on the Web Application Folder in File System Editor and choose Add/Project Output from the menu (You can do this through Action menu as well). Then select the Primary Output and Content Files from the Add Project Output Group dialog.

By pressing the OK button the primary output and content files of the TestWeb project will be added to the Application Folder automatically. Also the dependencies are automatically detected in the Solution Explorer. As you can see the dotnetfxredist_x86_enu.msm merge module is added to the WebSetup project. This merge module includes all assemblies of .NET framework. The exclude property of dotnetfxredist_x86_enu.msm is TRUE by default. In our case we suppose that .NET runtime is already installed on the target machine, otherwise you can define .NET Framework Lauch Condition in your setup project.

In the properties window, set the DefaultDocument property to "Webform1.aspx" page.

It's now time to build your web setup. So right click on WebSetup on Solution Explorer and choose Build. With a successful build, you can find the following files in the Debug or Release directory (depending on your build settings).

By executing Setup.exe the TestWeb project will be installed on the target system. Since we excluded .NET framework from our setup project, we must install the TestWeb application on the system with .NET framework installed. After installation process you can see TestWeb in IIS directory.

About Majid Shahabfar

Electronic engineering (B.Sc) Nuclear engineering (M.Sc) My programming experience includes Visual C++, MC++, C#, ASP.NET, Web Services and GDI+. I'm developing software application under .NET technology now and I enjoy keeping my skills up-to-date. Click here to

출처 : http://www.codeproject.com/aspnet/deployingwebapplications.asp

Concurrency control mechanism

Concurrency control maintains the consistency and isolation properties of transactions. This is the responsibility of the User/application, so we will not give a detailed description of concurrency control here (but see chapter 11 in [11]). However, as NFR provides Locks as the tool for building concurrency control, we will provide a brief overview of locking-based concurrency control and show how it can be implemented on top of NFR.

Figure 3: Classifications of concurrency control mechanisms.

The main categories of concurrency control mechanisms are shown in figure 3. Pessimistic algorithms synchronize the concurrent executions of transactions early in their execution life cycle, whereas Optimistic algorithms delay the synchronization for transactions until their termination.

Figure 4: Strict two-phase locking.

The main idea behind locking-based concurrency control is to ensure that the data shared by conflicting transactions is accessed by one operation at a time. This is accomplished by associating a lock with each data unit, and not allowing a transaction to access the data unit if a lock has been created on it by another transaction. One often-used locking scheme is (pessimistic) strict two-phase locking (2PL, figure 4). It is popular because it is relatively simple to implement and avoids cascading aborts¹¹. Strict 2PL acquires locks before data items are accessed, holds all locks until all accesses are finished, and releases all locks at once when the transaction commits/aborts. An NFR application can implement strict 2PL like this: While the transaction is executed, ask NFR to create Locks on Files before they are accessed. When the transaction commits/aborts, ask NFR to remove all Locks.

To conclude, we list some possible applications and the locking schemes they are likely to use:

출처 : http://www.vermicelli.pasta.cs.uit.no/ipv6/students/andrer/doc/html/node20.html

The Two-Phase Commit Protocol

The Two-Phase Commit (2PC) protocol is a simple and elegant ACP with two types of processes: A single coordinator that decides whether to reach a global commit or abort decision, and the participants that execute the transaction's resource accesses and vote whether to commit or abort. The commit decision is made according to the global commit rule [11]:

-

If even one participant votes to abort the transaction, the coordinator has to reach a global abort decision.

-

If all participants vote to commit the transaction, the coordinator has to reach a global commit decision.

A description of 2PC that does not consider failures is provided in this section. Failure scenarios and a recovery protocol is described in section 2.6.2. Figure 2 should be helpful: The circles represent states, the whole lines represent state changes, the dashed lines represent messages (labeled with the message types), and the rectangles represent logging actions.

T-SQL Coding Standards

Brian Walker

Surprising as it may be, there don't seem to be any "official" T-SQL coding standards. Back in late 1999, I was pleased to discover John Hindmarsh's proposed standards for SQL Server 7.0 and summarized some of his recommendations in a February 2000 editorial. (John's original standards were included in the February 2000 Downloads and are also included in this month's.) More recently, Ron Talmage has written a series of columns with his recommendations for a variety of "best practices," and, of course, the SQL Server team has officially shipped a Best Practices Analyzer for SQL Server (SQLBPA). Now, Brian Walker, a DBA and application developer with more than 25 years of experience, weighs in with advice and tips.

Coding standards are often overlooked when it comes to T-SQL programming, but they're a crucial component of a smoothly operating development team. The coding standards I espouse here are ones that I've developed over the years. No, they're not universally accepted, and, admittedly, some of them are subjective. My message is really more about raising awareness than promoting myself as an arbiter of T-SQL style: The most important thing is to establish some reasonable coding standards and adhere to them. What you'll find in this article is a series of miscellaneous coding standards, hints, and tips for T-SQL programming. They're not arranged in any particular order of priority or importance.

Let's start with formatting. On the surface, the formatting of T-SQL code may not seem that important, but consistent formatting makes it easier for colleagues–be they fellow team members or members of the larger worldwide T-SQL fraternity–to scan and understand your code. T-SQL statements have a structure, and having that structure be visually evident makes it much easier to locate and verify various parts of the statements. Uniform formatting also makes it much easier to add sections to and remove them from complex T-SQL statements for debugging purposes. Here's a formatting example for a SELECT statement:

 SELECT C.Name , E.NameLast , E.NameFirst , E.Number , ISNULL(I.Description,'NA') AS Description FROM tblCompany AS C JOIN tblEmployee AS E ON C.CompanyID = E.CompanyID LEFT JOIN tblCoverage AS V ON E.EmployeeID = V.EmployeeID LEFT JOIN tblInsurance AS I ON V.InsuranceID = I.InsuranceID WHERE C.Name LIKE @Name AND V.CreateDate > CONVERT(smalldatetime, '01/01/2000') ORDER BY C.Name , E.NameLast , E.NameFirst , E.Number , ISNULL(I.Description,'NA') SELECT @Retain = @@ERROR, @Rows = @@ROWCOUNT IF @Status = 0 SET @Status = @Retain 

► Use four-space indentation for statements within a nested block of code. (The multi-line SELECT statement in the preceding code is a single SQL statement.) Right-align SQL keywords when starting a new line within the same statement. Configure the code editor to use spaces instead of tabs. This makes the formatting consistent regardless of what program is used to view the code.

► Capitalize all T-SQL keywords, including T-SQL functions. Use mixed case for variable names and cursor names. Use lowercase for data types.

► Keep table name aliases short, but as meaningful as possible. In general, use the capital letters of a table name as an alias and use the AS keyword to specify aliases for tables or fields.

► Always qualify field names with table name aliases when there are multiple tables involved in a T-SQL statement. This adds clarity for others and avoids ambiguous references.

► Align related numbers into columns when they appear in contiguous lines of code, such as with a series of SUBSTRING function calls. This makes the list of numbers easier to scan.

► Use single (not double) blank lines to separate logical pieces of T-SQL code, and do so liberally.

► Use appropriate data type declarations and consistent capitalization when declaring T-SQL local variables (such as @lngTableID).

► Always specify the length of a character data type, and make sure to allow for the maximum number of characters users are likely to need, since characters beyond the maximum length are simply lost.

► Always specify the precision and scale of the decimal data type, which otherwise defaults to an unspecified precision and integer scale.

► Employ error handling, but bear in mind that the error-checking examples in BOL don't work as advertised. The T-SQL statement (IF) used to check the @@ERROR system function actually clears the @@ERROR value in the process and will never capture a value other than zero. (Even if the examples did work, they'd only capture the last error that occurred rather than what is likely to be of more interest–the first error.) The error code must be captured immediately using SET or SELECT as in the preceding example. It should then be transferred to a status variable if the status variable is still zero.

► Avoid using "undocumented" features such as undocumented columns in system tables, undocumented functionality in T-SQL statements, or undocumented system or extended stored procedures.

► Do not rely upon any implicit data type conversions. For example, don't assign a character value to a numeric variable assuming that T-SQL will do the necessary conversion. Instead, use the appropriate CONVERT function to make the data types match before doing a variable assignment or a value comparison. Another example: Although T-SQL does an implicit and automatic RTRIM on character expressions before doing a comparison, don't depend on that behavior because compatibility level settings and use of nchar complicate things.

► Do not directly compare a variable value to NULL with comparison operators (symbols). If the variable could be null, use IS NULL or IS NOT NULL to do a comparison, or use the ISNULL function.

► Don't use the STR function to perform any rounding; use it with integers only. Use the CONVERT function (going to a different scale) or the ROUND function before converting to a string if the string form of a decimal value is needed. CEILING and FLOOR are other options.

► Be careful with mathematical formulas, since T-SQL may force an expression into an unintended data type. Add point zero (.0) to integer constants if a decimal result is desired.

► Never depend on a SELECT statement returning rows in any particular order unless the order is specified in an ORDER BY clause.

► In general, use an ORDER BY clause with any SELECT statement. A predictable order, even if it's not the most convenient one, is better than an unpredictable order, especially during development or debugging. (You may want to remove the ORDER BY clause before deployment to production.) In those situations where the order of the resulting rows really doesn't matter, don't bother with the overhead of an ORDER BY.

► Don't ever use double quotes in your T-SQL code. Use single quotes for string constants. If it's necessary to qualify an object name, use (non-ANSI SQL standard) brackets around the name.

► As much as possible, use table variables in place of temporary tables with SQL Server 2000. If the table variable will contain a large set of data, be aware that indexing is very limited (primary key index only).

► Create temporary tables early in the routine, and explicitly drop them at the end. Interspersed DDL and DML statements can contribute to excessive recompile activity.

► Recognize that temporary tables are not inherently evil, and considered use of them can make some routines much more efficient–for example, when a certain set of data from a large or heavily used table will be referenced repeatedly. However, for a one-off, a derived table may be a better choice.

► Be cautious with table-valued UDFs, because passing in a parameter with a variable, rather than a constant, can result in table scans if the parameter is used in a WHERE clause. And avoid using the same table-valued UDF more than once in a single query. Table-valued UDFs do, however, have some dynamic compilation features that can be handy. [Related: See Tom Moreau's use of a UDF to populate a table variable in his November 2003 column on generating sequence numbers.–Ed.]

► Almost every stored procedure should have SET NOCOUNT ON near the beginning, and it's good form to have SET NOCOUNT OFF near the end. [SET NOCOUNT ON eliminates SQL Server's sending DONE_IN_PROC messages to the client for each statement in a stored procedure.–Ed.] This standard also applies to triggers.

► You should consider declaring an explicit transaction any time more than one database modification statement is used in a routine. This includes a single statement executed multiple times in a loop.

► Always look for a set-based solution to a problem before implementing a cursor-based approach or a temporary table approach. A set-based approach is usually more efficient.

► Cursors, like temporary tables, aren't inherently evil. A FAST_FORWARD cursor over a small set of data will often outperform other methods of handling row-by-row processing. This is especially true if several tables must be referenced to get the necessary data. A routine that includes "running totals" in the result set often executes fastest when implemented using a cursor. If development time allows, try both a cursor-based approach and a set-based approach to see which one performs best.

► A table of sequence numbers, 1 through N, can be very handy.

► Understand how a CROSS JOIN works, and take advantage of it. For example, you can use a CROSS JOIN effectively between a table of working data and a table of sequence numbers; the result set contains a record for every combination of working data and sequence number.

► I'll wrap things up with this observation: T-SQL code tends to be concise, so if a block of code looks unwieldy or repetitive, there's probably a simpler and better method.

Conclusion

Let me know what you think of my recommendations, and feel free to e-mail me to discuss any of them–or to make suggestions for others. I invite you to think of this as an opening statement in a dialogue.

Sidebar: From Karen's February 2000 Editorial

On the standards front, there's a new, independent effort, spearheaded by SQL Server DBA John Hindmarsh, MCT, MCSE, MCDBA, which is most definitely worth your time. What John has done is write a detailed white paper outlining his recommendations for all sorts of standards associated with SQL Server. The only other similar effort I'm familiar with is Andrew Zanevsky's chapter "Format and Style" in Transact-SQL Programming (ISBN 1-56592-401-0). Andrew, as well as SQL Server Professional contributors Tom Moreau and Paul Munkenbeck, contributed to John's white paper, as did John's friend and colleague Stephen James. Here's an example of John's recommendations for writing stored procedures:

• Use SQL-92 standard join syntax.
• Use joins in preference to sub or nested queries for improved performance.
• Ensure variables and parameters match table data columns in type and size.
• Ensure all variables and parameters are used or else deleted.
• Keep temporary objects local in scope wherever possible.
• Limit use of temp tables to those created in stored procs.
• Check input parameters for validity.
• Use INSERT…SELECT in preference to SELECT…INTO, to avoid extensive locks.
• Maintain the logical unit of work requirement; don't create extensive or long-running processes where these can be shortened.
• Don't use SELECT * in any code.
• Lay out the procedure with indents, blocks, tabs, and white space–refer to the sample scripts.
• Use uppercase for T-SQL statements.
• Comment procedures extensively to ensure the processes are identified. Use line comments where these help clarify a processing step.
• Include transaction management unless the procedure is to be called from MTS processes. (Write separate procedures for MTS processes.)
• Monitor @@TRANCOUNT to determine transaction responsibility level.
• Avoid GOTO–except for error handling.
• Avoid nested procedures.
• Avoid implicit resolution of object names–make sure all objects are owned by dbo.

www.microsoft.com/downloads/details.aspx?displayla%20ng=en&familyid=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en

To find out more about SQL Server Professional and Pinnacle Publishing, visit their Web site at http://www.pinpub.com/

Note: This is not a Microsoft Corporation Web site. Microsoft is not responsible for its content.

This article is reproduced from the December 2004 issue of SQL Server Professional. Copyright 2004, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.

출처 : http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsqlpro04/html/sp04l9.asp

아래는 기사 원문입니다.

-----------------------------------------------

T-SQL Coding Standards

Brian Walker

Surprising as it may be, there don't seem to be any "official" T-SQL coding standards. Back in late 1999, I was pleased to discover John Hindmarsh's proposed standards for SQL Server 7.0 and summarized some of his recommendations in a February 2000 editorial. (John's original standards were included in the February 2000 Downloads and are also included in this month's.) More recently, Ron Talmage has written a series of columns with his recommendations for a variety of "best practices," and, of course, the SQL Server team has officially shipped a Best Practices Analyzer for SQL Server (SQLBPA). Now, Brian Walker, a DBA and application developer with more than 25 years of experience, weighs in with advice and tips.

Coding standards are often overlooked when it comes to T-SQL programming, but they're a crucial component of a smoothly operating development team. The coding standards I espouse here are ones that I've developed over the years. No, they're not universally accepted, and, admittedly, some of them are subjective. My message is really more about raising awareness than promoting myself as an arbiter of T-SQL style: The most important thing is to establish some reasonable coding standards and adhere to them. What you'll find in this article is a series of miscellaneous coding standards, hints, and tips for T-SQL programming. They're not arranged in any particular order of priority or importance.

Let's start with formatting. On the surface, the formatting of T-SQL code may not seem that important, but consistent formatting makes it easier for colleagues–be they fellow team members or members of the larger worldwide T-SQL fraternity–to scan and understand your code. T-SQL statements have a structure, and having that structure be visually evident makes it much easier to locate and verify various parts of the statements. Uniform formatting also makes it much easier to add sections to and remove them from complex T-SQL statements for debugging purposes. Here's a formatting example for a SELECT statement:

 SELECT C.Name , E.NameLast , E.NameFirst , E.Number , ISNULL(I.Description,'NA') AS Description FROM tblCompany AS C JOIN tblEmployee AS E ON C.CompanyID = E.CompanyID LEFT JOIN tblCoverage AS V ON E.EmployeeID = V.EmployeeID LEFT JOIN tblInsurance AS I ON V.InsuranceID = I.InsuranceID WHERE C.Name LIKE @Name AND V.CreateDate > CONVERT(smalldatetime, '01/01/2000') ORDER BY C.Name , E.NameLast , E.NameFirst , E.Number , ISNULL(I.Description,'NA') SELECT @Retain = @@ERROR, @Rows = @@ROWCOUNT IF @Status = 0 SET @Status = @Retain 

► Use four-space indentation for statements within a nested block of code. (The multi-line SELECT statement in the preceding code is a single SQL statement.) Right-align SQL keywords when starting a new line within the same statement. Configure the code editor to use spaces instead of tabs. This makes the formatting consistent regardless of what program is used to view the code.

► Capitalize all T-SQL keywords, including T-SQL functions. Use mixed case for variable names and cursor names. Use lowercase for data types.

► Keep table name aliases short, but as meaningful as possible. In general, use the capital letters of a table name as an alias and use the AS keyword to specify aliases for tables or fields.

► Always qualify field names with table name aliases when there are multiple tables involved in a T-SQL statement. This adds clarity for others and avoids ambiguous references.

► Align related numbers into columns when they appear in contiguous lines of code, such as with a series of SUBSTRING function calls. This makes the list of numbers easier to scan.

► Use single (not double) blank lines to separate logical pieces of T-SQL code, and do so liberally.

► Use appropriate data type declarations and consistent capitalization when declaring T-SQL local variables (such as @lngTableID).

► Always specify the length of a character data type, and make sure to allow for the maximum number of characters users are likely to need, since characters beyond the maximum length are simply lost.

► Always specify the precision and scale of the decimal data type, which otherwise defaults to an unspecified precision and integer scale.

► Employ error handling, but bear in mind that the error-checking examples in BOL don't work as advertised. The T-SQL statement (IF) used to check the @@ERROR system function actually clears the @@ERROR value in the process and will never capture a value other than zero. (Even if the examples did work, they'd only capture the last error that occurred rather than what is likely to be of more interest–the first error.) The error code must be captured immediately using SET or SELECT as in the preceding example. It should then be transferred to a status variable if the status variable is still zero.

► Avoid using "undocumented" features such as undocumented columns in system tables, undocumented functionality in T-SQL statements, or undocumented system or extended stored procedures.

► Do not rely upon any implicit data type conversions. For example, don't assign a character value to a numeric variable assuming that T-SQL will do the necessary conversion. Instead, use the appropriate CONVERT function to make the data types match before doing a variable assignment or a value comparison. Another example: Although T-SQL does an implicit and automatic RTRIM on character expressions before doing a comparison, don't depend on that behavior because compatibility level settings and use of nchar complicate things.

► Do not directly compare a variable value to NULL with comparison operators (symbols). If the variable could be null, use IS NULL or IS NOT NULL to do a comparison, or use the ISNULL function.

► Don't use the STR function to perform any rounding; use it with integers only. Use the CONVERT function (going to a different scale) or the ROUND function before converting to a string if the string form of a decimal value is needed. CEILING and FLOOR are other options.

► Be careful with mathematical formulas, since T-SQL may force an expression into an unintended data type. Add point zero (.0) to integer constants if a decimal result is desired.

► Never depend on a SELECT statement returning rows in any particular order unless the order is specified in an ORDER BY clause.

► In general, use an ORDER BY clause with any SELECT statement. A predictable order, even if it's not the most convenient one, is better than an unpredictable order, especially during development or debugging. (You may want to remove the ORDER BY clause before deployment to production.) In those situations where the order of the resulting rows really doesn't matter, don't bother with the overhead of an ORDER BY.

► Don't ever use double quotes in your T-SQL code. Use single quotes for string constants. If it's necessary to qualify an object name, use (non-ANSI SQL standard) brackets around the name.

► As much as possible, use table variables in place of temporary tables with SQL Server 2000. If the table variable will contain a large set of data, be aware that indexing is very limited (primary key index only).

► Create temporary tables early in the routine, and explicitly drop them at the end. Interspersed DDL and DML statements can contribute to excessive recompile activity.

► Recognize that temporary tables are not inherently evil, and considered use of them can make some routines much more efficient–for example, when a certain set of data from a large or heavily used table will be referenced repeatedly. However, for a one-off, a derived table may be a better choice.

► Be cautious with table-valued UDFs, because passing in a parameter with a variable, rather than a constant, can result in table scans if the parameter is used in a WHERE clause. And avoid using the same table-valued UDF more than once in a single query. Table-valued UDFs do, however, have some dynamic compilation features that can be handy. [Related: See Tom Moreau's use of a UDF to populate a table variable in his November 2003 column on generating sequence numbers.–Ed.]

► Almost every stored procedure should have SET NOCOUNT ON near the beginning, and it's good form to have SET NOCOUNT OFF near the end. [SET NOCOUNT ON eliminates SQL Server's sending DONE_IN_PROC messages to the client for each statement in a stored procedure.–Ed.] This standard also applies to triggers.

► You should consider declaring an explicit transaction any time more than one database modification statement is used in a routine. This includes a single statement executed multiple times in a loop.

► Always look for a set-based solution to a problem before implementing a cursor-based approach or a temporary table approach. A set-based approach is usually more efficient.

► Cursors, like temporary tables, aren't inherently evil. A FAST_FORWARD cursor over a small set of data will often outperform other methods of handling row-by-row processing. This is especially true if several tables must be referenced to get the necessary data. A routine that includes "running totals" in the result set often executes fastest when implemented using a cursor. If development time allows, try both a cursor-based approach and a set-based approach to see which one performs best.

► A table of sequence numbers, 1 through N, can be very handy.

► Understand how a CROSS JOIN works, and take advantage of it. For example, you can use a CROSS JOIN effectively between a table of working data and a table of sequence numbers; the result set contains a record for every combination of working data and sequence number.

► I'll wrap things up with this observation: T-SQL code tends to be concise, so if a block of code looks unwieldy or repetitive, there's probably a simpler and better method.

Conclusion

Let me know what you think of my recommendations, and feel free to e-mail me to discuss any of them–or to make suggestions for others. I invite you to think of this as an opening statement in a dialogue.

Sidebar: From Karen's February 2000 Editorial

On the standards front, there's a new, independent effort, spearheaded by SQL Server DBA John Hindmarsh, MCT, MCSE, MCDBA, which is most definitely worth your time. What John has done is write a detailed white paper outlining his recommendations for all sorts of standards associated with SQL Server. The only other similar effort I'm familiar with is Andrew Zanevsky's chapter "Format and Style" in Transact-SQL Programming (ISBN 1-56592-401-0). Andrew, as well as SQL Server Professional contributors Tom Moreau and Paul Munkenbeck, contributed to John's white paper, as did John's friend and colleague Stephen James. Here's an example of John's recommendations for writing stored procedures:

• Use SQL-92 standard join syntax.
• Use joins in preference to sub or nested queries for improved performance.
• Ensure variables and parameters match table data columns in type and size.
• Ensure all variables and parameters are used or else deleted.
• Keep temporary objects local in scope wherever possible.
• Limit use of temp tables to those created in stored procs.
• Check input parameters for validity.
• Use INSERT…SELECT in preference to SELECT…INTO, to avoid extensive locks.
• Maintain the logical unit of work requirement; don't create extensive or long-running processes where these can be shortened.
• Don't use SELECT * in any code.
• Lay out the procedure with indents, blocks, tabs, and white space–refer to the sample scripts.
• Use uppercase for T-SQL statements.
• Comment procedures extensively to ensure the processes are identified. Use line comments where these help clarify a processing step.
• Include transaction management unless the procedure is to be called from MTS processes. (Write separate procedures for MTS processes.)
• Monitor @@TRANCOUNT to determine transaction responsibility level.
• Avoid GOTO–except for error handling.
• Avoid nested procedures.
• Avoid implicit resolution of object names–make sure all objects are owned by dbo.

www.microsoft.com/downloads/details.aspx?displayla%20ng=en&familyid=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en

To find out more about SQL Server Professional and Pinnacle Publishing, visit their Web site at http://www.pinpub.com/

Note: This is not a Microsoft Corporation Web site. Microsoft is not responsible for its content.

This article is reproduced from the December 2004 issue of SQL Server Professional. Copyright 2004, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.

How to configure RPC dynamic port allocation to work with firewalls

On this page

	SUMMARY
	MORE INFORMATION