ou cannot expect to build a first-class application unless you use the best available tools. Besides well-known tools such as Visual Studio® .NET, there are a multitude of small, lesser-known tools available from the .NET community. In this article, I'm going to introduce you to some of the best free tools available today that target .NET development. I'll walk you through a quick tutorial of how to use each of them, some of which will save you a minute here and there, while others may completely change the way that you write code. Because I am squeezing so many different tools into this single article, I will not be able to cover each of them extensively, but you should learn enough about each to decide which tools are useful for your projects.

Snippet Compiler

The Snippet Compiler is a small Windows®-based application that allows you to write, compile, and run code. This tool is useful if you have small pieces of code for which you don't want to create an entire Visual Studio .NET project (along with all the files that come with it).

As an example, let's say that I wanted to show you how to launch another application from the Microsoft® .NET Framework. In the Snippet Compiler I would start by creating a new file which creates a small console application. The snippet can be created inside the Main method of the console application, which is what I will do here. The following code snippet demonstrates how to create an instance of Notepad from the .NET Framework:

System.Diagnostics.Process proc = new System.Diagnostics.Process(); proc.StartInfo.FileName= "notepad.exe"; proc.Start(); proc.WaitForExit(); 

Of course this snippet would not compile by itself, but that is where Snippet Compiler comes into play. Figure 1 shows this code sample in Snippet Compiler.

Figure 1 Snippet Compiler

To test this snippet, just press the play button (green triangle), and it will run in debug mode. The snippet will generate a console application popup, and Notepad will appear. When you close Notepad, the console application will close as well.

Personally, I have found Snippet Compiler to be invaluable when trying to create a small example for someone who has asked me for help, when normally I would have to create a project, make sure everything compiles, send them the code snippet, and then delete the project. Snippet Compiler makes this process much easier and much more pleasant.

Snippet Compiler was written by Jeff Key and can be downloaded from http://www.sliver.com/dotnet/SnippetCompiler.

Regulator

Regulator is the most recent addition to my top tools list. It is a full-featured tool that makes it easy to build and test regular expressions. There is a renewed interest in regular expressions because of the excellent support for them in the .NET Framework. Regular expressions are used to define patterns in strings based on characters, frequency, and character order. They are most commonly used as a means to validate user input or as a way to find a string of characters inside a larger string—for instance, when looking for a URL or e-mail address on a Web page.

Regulator allows you to enter a regular expression and some input against which you would be running this expression. This way you can see how the regular expression will act and what kind of matches it will return before implementing it in your application. Figure 2 shows Regulator with a simple regular expression.

The document contains the regular expression, in this example it is [0-9]* which should match any number of digits in a row. The box in the bottom-right contains the input for this regular expression, and the box on the bottom-left shows the matches that this regular expression finds in the input. The ability to write and test regular expressions in a separate application like this is much easier than trying to work with them in your app.

One of the best features in Regulator is the ability to search the online regular expressions library at regexlib.com. For example, if you enter the string "phone" in the search box, you will find more than 20 different regular expressions that will match various phone numbers, including expressions for UK, Australian, and many other phone numbers.

Regulator was written by Roy Osherove and can be downloaded at http://regex.osherove.com.

CodeSmith

CodeSmith is a template-based code-generation tool that uses a syntax similar to ASP.NET to generate any type of code or text. Unlike many other code-generation tools, CodeSmith does not require you to subscribe to a particular application design or architecture. Using CodeSmith, you can generate anything from a simple, strongly typed collection to an entire application.

When you are building an application, you will often find yourself repeating certain tasks, whether it's writing data access code or building custom collections. CodeSmith is particularly useful at such times because you can write templates to automate those tasks and not only improve your productivity but also automate the tasks that are the most tedious to perform.

CodeSmith ships with a number of templates, including ones for all the .NET collection types as well as ones to generate stored procedures, but the real power of this tool comes from being able to create custom templates. To get you started, I'll provide a quick introduction to building a custom template.

Building a Custom Template

CodeSmith templates are simply text files which you can create in any text editor. Their only requirement is that they be saved with the .cst file extension. The sample template that I'm going to build will accept a string and then build a class based on that string. The first step to creating a template is to add the template header, which declares the language of the template, the target language, and a brief description of the template:

<%@ CodeTemplate Language="C#" TargetLanguage="C#" Description="Car Template" %> 

The next part of the template is the property declarations, where you declare the properties that will be specified each time the template is run. With this template, the single property that I'm going to use is just a string, so the property declaration looks like this:

<%@ Property Name="ClassName" Type="String" Category="Context" Description="Class Name" %> 

This property declaration will make the ClassName property appear in the CodeSmith property window so that it can be specified when the template is run. The next step is to actually build the body of the template, which is very similar to coding with ASP.NET. You can see the body of the template in Figure 3. [Editor's Update - 6/16/2004: The code in Figure 3 has been updated to be safe for multithreaded operations.]

As you can see, the template will take the string input and generate a singleton class using that class name. In the template body, the same opening and closing tags are used as in ASP.NET. In this template, I am simply inserting the property value, but you can also use any type of .NET code inside these tags. Once the template is complete, you load it into CodeSmith by either double-clicking or opening it from the CodeSmith application. Figure 4 shows this template loaded into CodeSmith.

You can see that the property on the left is the one I declared in the template. If I enter "SingletonClass" as the class name and click the Generate button, the class shown in the bottom part of Figure 3 will be generated.

CodeSmith is relatively easy to use and can produce some incredible results if applied correctly. One of the most common sections of an application that is targeted for code generation is the data access layer. CodeSmith includes a special assembly called the SchemaExplorer which can be used to generate templates from tables, stored procedures, or almost any other SQL Server™ object.

CodeSmith was written by Eric J. Smith and is available for download at http://www.ericjsmith.net/codesmith.

NUnit

NUnit is an open source unit testing framework built for the .NET Framework. NUnit allows you to write tests in the language of your choice to test a specific function of your application. Unit tests are an excellent way to test the functionality of your code when you first write it, and also to provide a method for regression testing of your application. The NUnit application provides a framework for writing unit tests, as well as a graphical interface to run these tests and view the results.

Writing an NUnit Test

As an example, I'm going to test the functionality of the Hashtable class in the .NET Framework to determine if two objects can be added and then retrieved. My first step will be to add a reference to the NUnit.Framework assembly, which will give me access to the attributes and methods of the NUnit framework. Next I'll create a class and mark it with the TestFixture attribute. This attribute lets NUnit know that this class contains NUnit tests:

using System; using System.Collections; using NUnit.Framework; namespace NUnitExample { [TestFixture] public class HashtableTest { public HashtableTest() { } } } 

Next I'll create a method and mark it with the [Test] attribute so that NUnit knows that this method is a test. Then I'll set up a Hashtable and add two values to it, then use the Assert.AreEqual method to see if I can retrieve the same values that I added to the Hashtable, as shown in the following:

[Test] public void HashtableAddTest() { Hashtable ht = new Hashtable(); ht.Add("Key1", "Value1"); ht.Add("Key2", "Value2"); Assert.AreEqual("Value1", ht["Key1"], "Wrong object returned!"); Assert.AreEqual("Value2", ht["Key2"], "Wrong object returned!"); } 

This will confirm that I can add and then retrieve values from the Hashtable—a simple test, but one that showcases the capabilities of NUnit. There are a number of test types, as well as various Assert methods, that can be used to test every part of your code.

To run this test, I'll need to build the project, open the generated assembly in the NUnit application, and then click the Run button. Figure 5 shows the results. I get a warm and fuzzy feeling when I see that big green bar because it lets me know that the test passed. This simple example shows how easy and powerful NUnit and unit testing can be. Being able to write a unit test that can be saved and rerun whenever you change code not only makes it easier for you to detect defects in your code, but the result is that you can deliver better applications.

Figure 5 NUnit

NUnit is an open-source project that is available for download from http://www.nunit.org. There is also an excellent NUnit Visual Studio .NET add-in which allows you to run unit tests directly from Visual Studio. This can be found at http://sourceforge.net/projects/nunitaddin. For more information on NUnit and its place in test-driven development, see the article "Test-Driven C#: Improve the Design and Flexibility of Your Project with Extreme Programming Techniques" in the April 2004 issue of MSDN® Magazine.

FxCop

The .NET Framework is very powerful, which means there is great potential to create excellent applications, but there is equal opportunity to create poor programs. FxCop is one of the tools that can be used to help create better applications by enabling you to examine an assembly and check it for compliance using a number of different rules. FxCop comes with a set number of rules created by Microsoft, but you can also create and include your own rules. For instance, if you decided that all classes should have a default constructor that takes no arguments, you could write a rule that checks for a constructor on each class of an assembly. This way, no matter who writes the code, you will have a certain level of consistency. If you want more information on creating custom rules, see John Robbins' Bugslayer column on the subject in the June 2004 issue of MSDN Magazine.

So let's take a look at FxCop in action and see what it finds wrong with the NUnitExample assembly that I have been working with. When you open FxCop you first need to create an FxCop project and then add to it the assembly that you want to test. Once the assembly is added to the project, you can press Analyze, and FxCop will examine the assembly. The errors and warning found in this assembly are shown in Figure 6.

FxCop found a couple of problems with my assembly. You can double-click on an error to see the details, including a description of the rule and where you can find more information. (Something you can do for fun is run FxCop on the Framework assemblies and see what turns up.)

FxCop can help you create better, more consistent code, but it cannot make up for poor application design or just plain poor programming. FxCop is also not a replacement for peer code review, but because it can catch a lot of errors before code review, more time can be spent on serious issues rather than having to worry about naming conventions.

FxCop was developed by Microsoft and is available for download from http://www.gotdotnet.com/team/fxcop.

Lutz Roeder's .NET Reflector

The next essential tool is called .NET Reflector, which is a class browser and decompiler that can examine an assembly and show you just about all of its secrets. The .NET Framework introduced reflection which can be used to examine any .NET-based code, whether it is a single class or an entire assembly. Reflection can also be used to retrieve information about the various classes, methods, and properties included in a particular assembly. Using .NET Reflector, you can browse the classes and methods of an assembly, you can examine the Microsoft intermediate language (MSIL) generated by these classes and methods, and you can decompile the classes and methods and see the equivalent in C# or Visual Basic® .NET.

To demonstrate the workings of .NET Reflector, I am going to load and examine the NUnitExample assembly already shown. Figure 7 shows this assembly loaded in .NET Reflector.

Figure 7 NUnitExample Assembly

Inside of .NET Reflector there are various tools that you can use to examine this assembly further. To view the MSIL that makes up a method, click on the method and select Disassembler from the menu.

In addition to being able to view the MSIL, you can also view the method as C# by selecting Decompiler under the Tools menu. You could also view this method decompiled to Visual Basic .NET or Delphi by changing your selection under the Languages menu. Here is the code that .NET Reflector generated:

public void HashtableAddTest() { Hashtable hashtable1; hashtable1 = new Hashtable(); hashtable1.Add("Key1", "Value1"); hashtable1.Add("Key2", "Value2"); Assert.AreEqual("Value1", hashtable1["Key1"], "Wrong object returned!"); Assert.AreEqual("Value2", hashtable1["Key2"], "Wrong object returned!"); } 

The previous code looks very much like the code I actually wrote for this method. Here is the actual code from this assembly:

public void HashtableAddTest() { Hashtable ht = new Hashtable(); ht.Add("Key1", "Value1"); ht.Add("Key2", "Value2"); Assert.AreEqual("Value1", ht["Key1"], "Wrong object returned!"); Assert.AreEqual("Value2", ht["Key2"], "Wrong object returned!"); } 

Although there are some minor differences with the code, they are functionally identical.

While this example was a good way to show actual code versus decompiled code, it does not represent what I consider to be the best use of .NET Reflector, which is to examine .NET Framework assemblies and methods. The .NET Framework offers many different ways to perform similar operations. For example, if you need to read a set of data from XML, there are a variety of different ways to do this using XmlDocument, XPathNavigator, or XmlReader. By using .NET Reflector, you can see what Microsoft used when writing the ReadXml method of the DataSet, or what they did when reading data from the configuration files. .NET Reflector is also an excellent way to see the best practices for creating objects like HttpHandlers or configuration handlers because you get to see how the team at Microsoft actually built those objects in the Framework.

.NET Reflector was written by Lutz Roeder and can be downloaded from http://www.aisto.com/roeder/dotnet.

NDoc

Code documentation is almost always a dreaded task. I am not talking about the early design documents, or even the more detailed design documents; I am talking about documenting individual methods and properties on classes. The NDoc tool will automatically generate documentation for your code using reflection to examine the assembly and using the XML generated from your C# XML comments. XML comments are only available for C#, but there is a Visual Studio .NET Power Toy called VBCommenter which will do something similar for Visual Basic .NET. In addition, the next release of Visual Studio will support XML comments for more languages.

With NDoc you are technically still documenting your code, but you are documenting as you write it (in the XML comments), which is much easier to swallow. The first step when using NDoc is to turn on XML comments generation for your assembly. Right-click the project and select Properties | Configuration Properties | Build, then enter a path in which to save the XML file in the XML Documentation File option. When the project is built, an XML file will be created with all of the XML comments included. Here is a look at a method from the NUnit example documented with XML:

/// <summary> /// This test adds a number of values to the Hashtable collection /// and then retrieves those values and checks if they match. /// </summary> [Test] public void HashtableAddTest() { //Method Body Here } 

The XML documentation on this method will be extracted and saved in the XML file, shown here:

<member name="M:NUnitExample.HashtableTest.HashtableAddTest"> <summary>This test adds a number of values to the Hashtable collection and then retrieves those values and checks if they match.</summary> </member> 

NDoc uses reflection to look at your assembly, then reads the XML in this document, and matches them up. NDoc uses this data to create any number of different documentation formats, including HTML help files (CHMs). After generating the XML file, the next step is to load the assembly and the XML file into NDoc so they can be processed. This is done simply by opening NDoc and clicking the Add button.

Once the assembly and XML file are loaded into NDoc and after you customize the output using the range of properties available, clicking on the Generate button will start the process of generating the documentation. Using the default properties, NDoc generates some very attractive and functional .html and .chm files, thereby automating in a quick and efficient manner what would otherwise be a tedious task.

NDoc is an open source project and can be downloaded from http://ndoc.sourceforge.net.

NAnt

NAnt is a .NET-based build tool that, unlike the current version of Visual Studio .NET, makes it easy to create a build process for your project. When you have a large number of developers working on a single project, you can't rely on the build from a single user's box. You also do not want to have to build the project manually on a regular basis. Instead, you create an automated build process that runs every night. NAnt allows you to build your solution, copy files, run NUnit tests, send e-mail, and much more. Unfortunately, NAnt is lacking a nice looking graphical interface, but it does have a console application and XML files that specify which tasks should be completed during the build process. Note that MSBuild, the new build platform that's part of Visual Studio 2005, provides for very robust build scenarios and is similarly driven by XML-based project files.

NAnt in Action

In this example I am going to create an NAnt build file for the NUnitExample solution that I created earlier. First I need to create an XML file with the .build extension, place it in the root of my project, and then add an XML declaration to the top of the file. The first tag I need to add to the file is the project tag:

<?xml version="1.0"?> <project name="NUnit Example" default="build" basedir="."> <description>The NUnit Example Project</description> </project> 

The project tag is also used to set the name of the project, the default target, and the base directory. The description tag is used to set a brief description of this project.

Next, I'll add the property tag, which can be used to store a setting in a single location that can then be accessed from anywhere in the file. In this case, I am going to create a property called debug, which I can then set to true or false, reflecting whether or not I want the project to be compiled in the debug configuration. (In the end, this particular property does not actually affect how the project will be built; it is simply a variable that you set and which will be read from later when you are actually determining how to build the project.)

Next, I need to create a target tag. A project can contain multiple targets which can be specified when NAnt is run. If no target is specified, the default is used, which I set in the project element. In this example, the default target is build. Let's take a look at the target element, which will contain the majority of the build info:

<target name="build" description="compiles the source code"> </target> 

Inside the target element, I am going to set the name of the target to build and create a description of what this target will do. I'll also create a csc element, which is used to specify what should be passed to the csc C# compiler. Let's take a look at the csc element:

<csc target="library" output=".\bin\debug\NUnitExample.dll" debug="${debug}"> <references> <includes name="C:\program files\NUnit V2.1\bin\NUnit.Framework.dll"/> </references> <sources> <includes name="HashtableTest.cs"/> </sources> </csc> 

First, I have to set the target of the csc element. In this case I will be creating a .dll file so I set the target to library. Next, I have to set the output of the csc element, which is where the .dll file will be created. Finally, I need to set the debug property, which determines whether the project will be compiled in debug. Since I created a property earlier to store this value, I can use the following string to access the value of that property: ${debug}. The csc element also contains a number of sub-elements. I need to create two elements: the references element will tell NAnt which assemblies I need to reference for this project, and the sources element will tell NAnt which files to include in the build. In this example, I reference the NUnit.Framework.dll assembly and include the HashtableTest.cs file. The complete build file is shown in Figure 8. (You would normally also create a clean target that would be used to delete the generated files, but I have omitted it for the sake of brevity.)

To build this file I need to go to the root directory of my project, where the build file is located, and execute nant.exe from that location. If the build is successful, you can find the .dll and .pdb file in the bin directory of this application. While using NAnt is definitely not as easy as clicking Build in Visual Studio, it is a very powerful tool for developing a build process that runs on an automated schedule. NAnt also includes helpful features such as the ability to run unit tests or copy additional files (features that are not supported by the current Visual Studio build process).

NAnt is an open source project and can be downloaded from http://sourceforge.net/projects/nant.

Switch Tools

I have lumped together two separate tools under the heading Switch Tools. These two tools are rather simple, but can be extremely useful. The first is the ASP.NET Version Switcher, which can be used to switch the version of ASP.NET that a virtual directory is running under. The second tool is the Visual Studio Converter, which can be used to switch a project file from Visual Studio .NET 2002 to Visual Studio .NET 2003.

When IIS handles a request, it looks at the extension of the file that is being requested, and then based on the extension mappings for that Web site or virtual directory, it either delegates the request to an ISAPI extension or handles it itself. This is how ASP.NET works; extension mappings are registered for all of the ASP.NET extensions and directs them to the aspnet_isapi.dll. This works flawlessly until you install ASP.NET 1.1, which upgrades the extension mapping to the new version of aspnet_isapi.dll. This causes errors when an application built on ASP.NET 1.0 tries to run with version 1.1. To fix this, you can switch all of the extension mappings back to the 1.0 version of aspnet_isapi.dll, but with 18 extension mappings it is not a lot of fun to do this by hand. This is where the ASP.NET Version Switcher becomes useful. This small utility can be used to switch the version of the .NET Framework that any single ASP.NET application is using.

Figure 9 ASP.NET Version Switcher

Figure 9 shows the ASP.NET Version Switcher in action. Using it is as simple as selecting the application and then selecting the version of the .NET Framework that you would like the application to use. The tool then uses the aspnet_regiis.exe command-line tool to switch the application to the selected version of the Framework. This tool will become even more useful as future versions of ASP.NET and the .NET Framework are released.

ASP.NET Version Switcher was written by Denis Bauer and is available for download from http://www.denisbauer.com/NETTools/ASPNETVersionSwitcher.aspx.

The Visual Studio .NET Project Converter (see Figure 10) is very similar to the ASP.NET Version Switcher, except that it is used to switch the version of a Visual Studio project file. Even though there is only a small difference between versions 1.0 and 1.1 of the .NET Framework, once a project file from Visual Studio .NET 2002 is converted to Visual Studio .NET 2003, it cannot be converted back. While this might not be an issue most of the time (since there are few breaking changes between the .NET Framework versions 1.0 and 1.1), at some point you may need to switch a project back. This converter can convert any solution or project file from Visual Studio 7.1 (Visual Studio .NET 2003) to Visual Studio 7.0 (Visual Studio .NET 2002), and back if necessary.

Figure 10 Visual Studio .NET Project Converter

The Visual Studio .NET Project Converter was written by Dacris Software. It is available for download from http://www.codeproject.com/macro/vsconvert.asp.

Conclusion

This has been a bit of a whirlwind tour of these tools, but I have tried to present you with at least enough information to whet your appetite. I trust that this article has provided you with some insight into a couple of free tools that you can start using right away to write better projects. I also urge you to make sure you have all the other right tools available to you, whether it is newest version of Visual Studio, a powerful computer, or a free utility. Having the right tools can make all the difference.

출처 : http://msdn.microsoft.com/msdnmag/issues/04/07/MustHaveTools/default.aspx

Introduction

OO Design is more than just using an OO language. Over the years many bright programmers have built up a collection of rules that help to build well designed maintainable code. This article lists the main rules of OO programming. The intention is to inspire the reader to think about these rules and make further reading. There is a lot of material on the web that drills down into more details with plenty of examples.

Ivar Jacabson said ”All systems change during their life cycles. This must be borne in mind when developing systems expected to last longer than the first version”. In other words software requirements change with time. The goal of Object Orientated Design is to program in such a way that such changes to the software are predictable and do not make a large impact in the program. In other words it should be stable in the presence of change

Bad design is characterized by

• A single change affects many other parts of the system (Rigidity)
• A single change affects unexpected parts of the system (Fragility)
• It is hard to reuse in another application (Immobility)

The Dependency Inversion Principle DIP

한글로는 '의존 관계 역전 원칙'

Imagine you have a simple database program. You don’t want to change the entire application when changing the database. This principle is targeted at removing such unwanted interdependency that can cause a design to be fragile. The rule states

• High level modules should not depend upon low level modules. Both should depend upon abstractions
• Abstractions should not depend upon details. Details should depend upon abstractions

Booch said “All well structured object orientated architectures have clearly-defined layers with each layer providing some coherent set of services through a well defined and controlled interface.”

In other words design applications in layers where high level layers call lower level layers using Abstract interfaces. To conform to the principle of dependency inversion, we must isolate abstraction from the details of the problem. Then we must direct the dependencies of the design upon the abstractions.

Good dependencies are extremely unlikely to change. In other words they are stable. We would like to base our architectural design around stable, non-volatile modules.

-> 마이크로소프트웨어 2005년 5월호 p202 '객체지향 소프트웨어 설계의 원칙들 5' 참고

The Open-Close Principle OCP

한글로는 '개방-패쇄 원칙'

Software entities (Classes, Modules, functions etc) should be open for extension, but closed for modification

In other words design classes that never change. When a new requirements come add new code and don’t edit existing code. It is not possible to close against all possible changes. Therefore an experienced developer needs to understand the possible future wishes of users in order to make Strategic Closure. There are two ways of closure:

• Using Abstraction to gain explicit closure - This means the programmer applies abstraction to those parts of the programmer the designer feels are subject to change.
• Using Data Driven Approach to achieve closure

-> 마이크로소프트웨어 2005년 1월호 p202 '객체지향 소프트웨어 설계의 원칙들 1' 참고

-> 마이크로소프트웨어 2005년 5월호 p204 '객체지향 소프트웨어 설계의 원칙들 5' 참고

Liskov Substitution Principle LSP

한글로는 '리스코프 교체 원칙'

Every function that operates upon a reference or pointer to a base class should be able to operate on derivatives of that base class without knowing it. This means that virtual member functions of the derived class must expect only all the corresponding member functions of the base class. In other words any function that uses a base class must not be confused when a derived class is substituted for the base class

This is a difficult principle to apply. To conform avoid overwriting base class functions because this involves programming with details, instead try to program in abstractions

If this is violated then functions that operate on the pointers must first check the type of the actual object in order to work correctly.

-> 마이크로소프트웨어 2005년 5월호 p203 '객체지향 소프트웨어 설계의 원칙들 5' 참고

Heuristics and Conventions

Make all member variables Private: Otherwise no function that calls the class can be closed to change. For example a status variable can change from Boolean to an enumeration, if this is not handled as a property then we cannot close status. This is called encapsulation.

No Global Variables. Because misbehaving modules may write erroneous data to such global variables whose effect can be felt in many places throughout the program. Sometimes Global variables are useful e.g. cout and cin in c++. If they do not violate the open close principle then sometimes they are worth the style violation

Stability Dependencies Principle SDP

The dependencies between packages in a design should be in the direction of stability of the packages. A package should only depend upon packages that are more stable than it is.

Some volatility is necessary if the design is to be maintained. This is achieved by using the Common Closure Principle, in this way we design packages to be volatile and we expect them to change. Any package that we expect to be volatile should not be depended upon by a package that is difficult to change.

Some things we don’t want to change. For example architectural decisions should be stable and not at all volatile. Therefore classes that encapsulate the high level design should be stable.

The stable Abstractions Principle SAP

Packages that are maximally stable should be maximally abstract. Instable packages should be concrete. The abstraction of a package should be in proportion to it's stability

Common Reuse Principle CRP

If you reuse one class of a package, you reuse them all. This because any package delivered contains a released set of classes, therefore a change in any class means a new release of the entire package.

The Reuse / Release Equivalence Principle REP

The granule of reuse is the granule of release. Only components that are released through a tracking system can be effectively reused. This principle is important when there are several teams working on an application. To avoid one team disrupting another all packages used are tested and released. In this way the introduction of modified packages is in a controlled way.

The Common Closure Principle CCP

The classes in a package should be closed together against the same kinds of changes. Any change in a package affects all classes in the package. Just like a well organized team has a common goal because they all have to work together. This principle means that you should have a common strategic closure concept used through all classes in a package because they have to be released all together.

The stability Dependencies Principle SDP

The dependencies between packages in a design should be in the direction of stability of the packages. A package should only depend upon packages that are more stable than it is.

Designs that are highly interdependent tend to be rigid, not reusable and hard to maintain

The Acyclic Dependencies Principle (ADP)

The dependency structure between package must be a Direct Acyclic Graph (DAG). This means that if you plot out all packages it should be possible to arrange the dependencies to always point from top to bottom. Also it should not be possible to follow any lines of dependence and end up back at the same package. Because such packages would have to be released all at the same time defeating the object of having them as separate packages

The Interface Segregation Principle ISP

한글로는 '인터페이스 격리 원칙'

Clients should not be forced to depend upon interfaces that they don’t use.

This principle deals with the disadvantages of fat interfaces. Fat interfaces are not cohesive. In other words the interfaces of classes should be broken into groups of member functions. Each groups servers a different set of clients. Separation can be achieved by:

• Separation through Delegation
• Separation through multiple inheritance

If this principle is violated then there is a coupling between all clients

-> 마이크로소프트웨어 2005년 3월호 p161 '객체지향 소프트웨어 설계의 원칙들 3' 참고

Polyad vs Monad

Monad is when properties are grouped into 1 single object that is then passed in a function parameter. Unfortunately this brings a dependency across all properties in that single object. Therefore its better to pass smaller objects (Polyad), in this way the dependencies are broken into smaller groups.

Interface Pollution

As we build up classes there is a tendency for us to add functionality that is specific for a particular implementation. In this way the interface gets populated by functions and properties that are not required if the class was in a different context, thus making the interface fat. In this way this violates the Liskov Substitution principle. Separate Clients means separate interfaces

There is a backward force applied by clients upon interfaces. For example a user may wish to add a trivial extra function that cannot be exactly positioned in existing interfaces.

Single Responsibility Principle : SRP

하나의 클래스는 하나의 책임만을 가져야 한다.

-> 마이크로소프트웨어 2005년 2월호 p155 '객체지향 소프트웨어 설계의 원칙들 2' 참고

-> 마이크로소프트웨어 2005년 5월호 p201 '객체지향 소프트웨어 설계의 원칙들 5' 참고

About Nigel-Findlater

출처 : http://www.codeproject.com/gen/design/nfOORules.asp

처음 위 질문을 보고, 'SQL Server의 기본 isolation level이 read committed니가 그런거겠지... 그럼 Oracle은 그게 아닌가?'라고 생각하고 Oracle쪽을 찾다가 다음과 같은 글을 보았습니다.

'SQL Server와 Oracle의 격리와 병행제어에는 차이가 있습니다....'

그래서 당연히 default isolation level이 달라서 나타나는 차이라고 생각하고 답글을 올려버렸습니다. --;; (사실 상당히 주의하고 답글을 올리는 편임에도 불구하고 실수를 했죠).

그리고 이 내용에 흥미가 생겨 블로그에 정리하다가 그게 아니라는걸 알게되었습니다.

다시 검색하다가 아래의 내용을 보았습니다.

'The default isolation level for Oracle is read committed'

쩝 --;; 그럼 isolation level이 같은데 왜 그럴까?? 차이는 병행제어 알고리즘의 차이입니다.

데이터의 일관성과 데이터의 동시성

여러 가지 정의가 있겠지만, Oracle 9i의 교재에서는 다음과 같이 정의하고 있습니다.

동시성이란 다수의 사용자가 동시에 데이터에 접근할 수 있어야 한다는 의미이고, 일관성이란, 각각의 사용자가 자신의 트랜잭션이나 다른 사람의 트랜잭션에 의해 변경된 내용을 포함하여 일관된 값을 본다라는 의미라고 합니다.

다수의 사용자가 데이터베이스에 동시에 접근하게 될 경우, 각각의 사용자가 다른 데이터에 접근을 한다면 문제 없겠지만, 같은 데이터에 접근을 한다면 잘못된 데이터의 변경을 막기 위해선 반드시 어떠한 제어를 해 주어야 합니다.

그것이 병행 제어(concurrency control)입니다.

대학원 수업시간에 배운 내용 중 기억 나는 부분만 소개하자면...

병행 제어 알고리즘은 다음과 같은 분류의 알고리즘이 있습니다.

* 낙관적 병행제어 알고리즘 : 다른 사용자가 동시에 같은 데이터에 접근할 경우가 적다고 보고 구현한 알고리즘

* 비관적 병행제어 알고리즘 : 낙관적과는 반대로 다른 사용자가 동시에 같은 데이터에 접근할 경우가 많다고 보고 구현한 알고리즘

위의 내용은 머리속에 남은 조그마한 지식의 일부분이고..

대학원때 배웠던 교제 중, Distributed Systems Concepts and Design 이라는 책에 나온 내용을 소개하자면,

병행제어 알고리즘에는 다음과 같은 것 들이 있습니다.

1. Locking (비관적 병행제어 알고리즘)

2. optimistic concurrency control (낙관적 병행제어 알고리즘)

3. timestamp ordering (비관적 병행제어 알고리즘)

각 알고리즘에 대한 소개는 skip하고 ^^''

SQL Server에서 사용하는 병행제어 알고리즘은 Locking입니다.

그러나 Oracle에서 사용하는 병행제어 알고리즘은 일반적인 Locking과는 조금 다릅니다. Oracle에서는 Multiversion Concurrency Control이라고 말하는 방법을 사용하는데, 중요한 것은 SQL Server와 다른 알고리즘을 사용 한다는 것입니다.

아래의 내용은 첨부된 문서의 내용 중 일부입니다.

오라클의 잠금 정책 요약입니다.

* 오라클은 변경의 경우에만 테이블의 행단위로 잠금을 한다. 블록이나테이블 수준으로잠금을 확대하지는 않는다.
* 오라클은 단지 데이터를 읽기 위해서 잠금을 하지 않는다. 즉, 간단한 읽기에 의한 데이터 행들에 잠금을 두지는 않는다.
*  데이터 기록기(writer)는 데이터 판독기(reader)를 방해하지 않는다. 즉, 읽기는 쓰기에 의해서 방해받지 않는다. 이는 기본적으로 읽기가 쓰기에 의해서 차단되는 거의 모든 다른 데이터베이스들과 구별된다.
*  데이터 기록기는 다른 데이터 기록기가 먼저 접근하고자 하는 데이터에 대해서 잠금을 했을 때에만 차단된다.
*  데이터 판독기는 데이터 기록기에 의해서 전혀 차단되지 않는다.

그러므로, SQL Server에서는 read T가 write T와 충돌이 발생하여, 둘 중 하나의 T는 blocking이 걸리게 되지만, Oracle에서는 read T와 write T가 서로 충돌하지 않습니다.

구체적인 내용은 첨부 파일을 읽어보시면 됩니다.

참고 문헌

* http://www.stanford.edu/dept/itss/docs/oracle/9i/server.920/a96524/c21cnsis.htm#17882

아래 내용들은 위와 같은 내용에 대한 궁금증으로 글을 올린 사람이 밝혀놓은 링크들의 내용을 간략히 소개한 것이다.

출처 :

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=143768

http://www.sql-server-performance.com/clustered_indexes.asp

여기 내용 중

As a rule of thumb, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases--such as an identity column, or some other column where the value is increasing--and is unique. In many cases, the primary key is the ideal column for a clustered index. 

If you have any experience with performance tuning SQL Server 6.5, you may have heard that is not a good idea to add a clustered index to a column that monotonically increases because it can cause a "hotspot" on the disk that can cause performance problems. That advice is true in SQL Server 6.5.

In SQL Server 7.0 and 2000, "hotspots" aren't generally a problem. You would have to have over 1,000 transactions a second before a "hotspot" were to negatively affect performance. In fact, a "hotspot" can be beneficial under these circumstances because it eliminates page splits.

Here's why. If you are inserting new rows into a table that has a clustered index as its primary key, and the key monotonically increases, this means that each INSERT will physically occur one after another on the disk. Because of this, page splits won't occur during INSERTs, which in itself saves overhead. This is because SQL Server has the ability to determine if data being inserted into a table has a monotonically increasing sequence, and won't perform page splits when this happens.

If you are inserting a lot of rows into a heap (a table without a clustered index),  data is not inserted in any particular order onto data pages, whether the data is monotonically or not monotonically increasing. This results in SQL Server having to work harder (more reads) to access the data when requested from disk. On the other hand, if a clustered index is added to a table, data is inserted sequentially on data pages, and generally less disk I/O is required to retrieve the data when requested from disk.

If data is inserted into a clustered index in more or less random order, data is often inserted randomly into physical data pages, which is similar to the problem of inserting any data into a heap.

So again, often, the best overall recommendation is to add a clustered index to a column that monotonically increases (assuming there is a column that does so). This is especially true if the table is subject to many INSERTS, UPDATES, and DELETES. But if a table is subject to few data modification, but to many SELECT statements, then this advice is less useful, and other options for the clustered index should be considered. Read the other tips on this page to learn of situations where you should place the clustered index on other columns.  [7.0, 2000] Updated 11-15-2004

-> 여기선 identity가 걸린 pk 컬럼에 clustered index를 거는 것을 권장한다. 위에 설명한 a와 동일한 이유로. 단 마지막 부분에 보면 거의 변화가 없고 SELECT만 많은 경우는 덜 유용한 방법이라고 설명하고 있다.

하나 중요한 설명이 있는데, 켄 헨더슨씨가 설명한 2번 문제가 SQL Server 6.5에서는 문제가 되지만 SQL Server 7.0과 2000에서는 그다지 문제가 되지 않는다는 설명도 있다.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_6583.asp

Use integer keys for clustered indexes. Additionally, clustered indexes benefit from being created on unique, nonnull, or IDENTITY columns. For more information, see Using Clustered Indexes.

-> 달랑 identity 컬럼에 clustered를 만들라고만 나왔네요 --''

이 외에 사람들의 의견이 조금씩 차이가 있는데...

이 글을 읽는 분의 생각은 어떠신지요...?

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