Design Patterns : An introduction

Jan 12, 2011 Posted by Lara Kannan 1 comments
Design Patterns

Design Patterns are an essential tool for any object orientated programmer who wants to take his/her skills to the next level. Understanding the specifics of Design Pattern practices and methodologies will help developers uncover reoccurring patterns in software development and give them the tools to apply the correct solution.

Types of Design Patterns

  • Informal Design Patterns - such as the use of standard code constructs, best practice, well structured code, common sense, the accepted approach, and evolution over time.

  • Formal Design Patterns - documented with sections such as "Context", "Problem", "Solution", and a UML diagram.

Formal patterns usually have specific aims and solve specific issues, whereas informal patterns tend to provide guidance that is more general. Formal patterns usually have specific aims and solve specific issues, whereas informal patterns tend to provide guidance that is more general.

For example, some patterns provide presentation logic for displaying specific views that make up the user interface. Others control the way that the application behaves as the user interacts with it. There are also groups of patterns that specify techniques for persisting data, define best practices for data access, and indicate optimum approaches for creating instances of objects that the application uses.

The following list shows some of the most common design patterns within these groups:

  • Presentation Logic

    1. Model-View-Controller (MVC)

    2. Model-View-Presenter (MVP)

    3. Use Case Controller

  • Host or Behavioral

    1. Command

    2. Publish-Subscribe / Observer

    3. Plug-in / Module / Intercepting Filter

  • Structural

    1. Service Agent / Proxy / Broker

    2. Provider / Adapter


  • Creational

    1. Factory / Builder / Injection

    2. Singleton


  • Persistence

    1. Repository


    1. test





Share
Labels: ,

Common Table Expressions (CTEs)

Jan 11, 2011 Posted by Lara Kannan 0 comments
Common Table Expressions (CTEs)

Why we need CTEs ?

You need to implement recursive queries to retrieve data which is presented in a hierarchical format. We normally resort to implementing views, cursors or derived tables and perform queries against them. The problem arises when the hierarchy level increases as SQL Server is limited to 32 levels of recursion.

We need a better way to implement recursive queries in SQL Server 2005 / 08. How do we do it? Use CTE.

Common Table Expressions (CTEs) are one of the most exciting features to be introduced with SQL Server 2005. CTEs come in 2 forms, non-recursive and recursive

A CTE is a "temporary result set" that exists only within the scope of a single SQL statement. It allows access to functionality within that single SQL statement that was previously only available through use of functions, temp tables, cursors, and so on.

CTE can be used for both selects and DML statements. CTEs can also contain references to themselves. CTEs can also be used in place of views.

You can also use a query hint to stop a statement after a defined number of loops. This can stop a CTE from going into an infinite loop on a poorly coded statement. You do this by including the MAXRECURSION keyword in the SELECT query referring to the CTE.

1. Non-Recursive CTE

Non-Recursive CTE as a derived table, or a temporary view.

The concept behind a CTE is simplicity itself. Consider the following statement:
with MyCTE(x)
as
(select x = 'Kannan')

select x from MyCTE

In brackets after the 'as' keyword is the query that defines the CTE and it simply returning the string "Kannan".




2. Recursive CTE

To print the numbers 5 to 10 using SQL. How ? Before we go to actual code, let we see the syntex of it.

Syntex
WITH cte_alias (column_aliases) 
AS
(
cte_query_definition --initialization
UNION ALL
cte_query_definition2 --recursive execution
)
SELECT * FROM cte_alias

The table defined in the CTE can be referenced in the CTE itself to give a recursive expression, using union all.
DECLARE @Start INT
DECLARE @End INT
SET @Start = 5;
SET @End = 10;

WITH CTE AS
(SELECT @Start AS NUM
UNION ALL
SELECT NUM + 1 FROM CTE WHERE NUM < @End
)

SELECT * FROM CTE

The query:
SELECT @Start AS NUM

is called the anchor member. This is executed in the first pass and will populate the CTE with the result, in this case 5. This initial CTE is repeatedly executed until the complete result set is returned. The next entry:
SELECT NUM + 1 FROM CTE WHERE NUM < @End

is a recursive member as it references the CTE, CTE. The recursive member is executed with the anchor member output to give 6. The next pass takes 6 as input and returns 7, and so on so that we arrive at a CTE populated with rows as follows:
5
6
7
8
9
10

The recursion will terminate when the recursive member produces no rows – in this case recursion stops when the length of x equals 10.




Note:
Note the ";" before the CTE definition – that's just a syntax requirement if the CTE declaration is not the first statement in a batch.

Disadvantage :

1. CTE will be available with the scope of the statement or block of code.
2. Compute, Order By (without a TOP), INTO, Option, FOR XML, and FOR BROWSE are all not allowed.

Hope this will help you. If so please add your comments. Thanks.

Share