Shailendra Saxena's Technical Blog

Using Common Table Expressions

clock February 7, 2013 02:32 by author ShailendraSaxena

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

·  Create a recursive query.

·  Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

·  Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

·  Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

Structure of a CTE


A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE. After a CTE is defined, it can be referenced like a table or view can in a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]


( CTE_query_definition )

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

The statement to run the CTE is:

SELECT <column_list>

FROM expression_name;


The following example shows the components of the CTE structure: expression name, column list, and query. The CTE expression Sales_CTE has three columns (SalesPersonID, SalesOrderID, and OrderDate) and is defined as the total number of sales orders per year for each salesperson.


USE AdventureWorks2008R2;
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
-- Define the CTE query.
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;

UML in Brief

clock February 6, 2013 20:52 by author ShailendraSaxena



Use Case

A use case illustrates a unit of functionality provided by the system. The
main purpose of the use-case diagram is to help development teams
visualize the functional requirements of a system, including the
relationship of "actors" (human beings who will interact with the system)
to essential processes, as well as the relationships among different use
cases. Use-case diagrams generally show groups of use cases -- either all
use cases for the complete system, or a breakout of a particular group of
use cases with related functionality (e.g., all security administrationrelated
use cases).


Class diagram

The class diagram shows how the different entities (people, things, and
data) relate to each other; in other words, it shows the static structures of
the system. A class diagram can be used to display logical classes, which
are typically the kinds of things the business people in an organization talk
about -- rock bands, CDs, radio play; or loans, home mortgages, car
loans, and interest rates. Class diagrams can also be used to show
implementation classes, which are the things that programmers typically
deal with. An implementation class diagram will probably show some of
the same classes as the logical classes diagram.The implementation class
diagram won't be drawn with the same attributes, however, because it will
most likely have references to things like Vectors and HashMaps.


Sequence diagram

Sequence diagrams show a detailed flow for a specific use case or even
just part of a specific use case. They are almost self explanatory; they
show the calls between the different objects in their sequence and can
show, at a detailed level, different calls to different objects.
A sequence diagram has two dimensions: The vertical dimension shows
the sequence of messages/calls in the time order that they occur; the
horizontal dimension shows the object instances to which the messages
are sent.


Statechart diagram

The statechart diagram models the different states that a class can be in
and how that class transitions from state to state. It can be argued that
every class has a state, but that every class shouldn't have a statechart
diagram. Only classes with "interesting" states -- that is, classes with
three or more potential states during system activity -- should be


Activity diagram

Activity diagrams show the procedural flow of control between two or
more class objects while processing an activity. Activity diagrams can be
used to model higher-level business process at the business unit level, or
to model low-level internal class actions. In my experience, activity
diagrams are best used to model higher-level processes, such as how the
company is currently doing business, or how it would like to do business.
This is because activity diagrams are "less technical" in appearance,
compared to sequence diagrams, and business-minded people tend to
understand them more quickly.


Component diagram

A component diagram provides a physical view of the system. Its purpose
is to show the dependencies that the software has on the other software
components (e.g., software libraries) in the system. The diagram can be
shown at a very high level, with just the large-grain components, or it can
be shown at the component package level.


Deployment diagram

The deployment diagram shows how a system will be physically deployed
in the hardware environment. Its purpose is to show where the different
components of the system will physically run and how they will
communicate with each other. Since the diagram models the physical
runtime, a system's production staff will make considerable use of this

Service error cannot open <service name> service on computer '.'

clock February 4, 2013 07:57 by author ShailendraSaxena


I have a windows service which is running properly in Windows XP machine but while run it on Windows2008 64 bit Machine it gives error as Service error cannot open <service name> service on computer '.'.

I find if you install your windows service app with UAC turned off then it works fine on windows2008 64 bit machine.

Optional Parameters in C#

clock February 3, 2013 06:24 by author ShailendraSaxena


You specify that a parameter is optional when you define a method by providing a default

value for the parameter. You indicate a default value by using the assignment operator. In the

optMethod method shown next, the first parameter is mandatory because it does not specify

a default value, but the second and third parameters are optional:


void optMethod(int first, double second = 0.0, string third = "Hello")





You must specify all mandatory parameters before any optional parameters.


You can call a method that takes optional parameters in the same way that you call any other

method; you specify the method name and provide any necessary arguments.


The difference with methods that take optional parameters is that you can omit the corresponding

arguments, and the method will use the default value when the method runs.

In the following example code, the first call to the optMethod method provides values for all three parameters. The second call specifies only two arguments, and these values are applied to the first and second parameters. The third parameter receives the default value of “Hello” when

the method runs.


optMethod(99, 123.45, "World"); // Arguments provided for all three parameters


optMethod(100, 54.321); // Arguments provided for 1st two parameters only



<<  August 2017  >>






Flag Counter


The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2017

Sign In