PIVOT in SQL Server

PIVOT in SQL Server.
What this allows you to do is to turn query results on their side, so instead of having results listed down like the listing below,
you have results listed across.

SalesPerson	Product	SalesAmount
Bob	Pickles	$100.00
Sue	Oranges	$50.00
Bob	Pickles	$25.00
Bob	Oranges	$300.00
Sue	Oranges	$500.00

With a straight query the query results would be listed down, but the ideal solution would be to list the Products across the top for each SalesPerson, such as the following:

SalesPerson	Oranges	Pickles
Bob	$300.00	$125.00
Sue	$550.00

To use PIVOT you need to understand the data and how you want the data displayed.
First you have the data rows, such as SalesPerson and the columns,
such as the Products and then the values to display for each cross section.
Here is a simple query that allows us to pull the cross-tab results.

SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
FROM
(SELECT SalesPerson, Product, SalesAmount
FROM ProductSales ) ps
PIVOT
(
SUM (SalesAmount)
FOR Product IN
( [Oranges], [Pickles])
) AS pvt

There are three pieces that need to be understood in order to construct the query.

• (1) The SELECT statement
  • SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
  • This portion of the query selects the three columns for the final result set (SalesPerson, Oranges, Pickles)
  
  
• (2) The query that pulls the raw data to be prepared
  • (SELECT SalesPerson, Product, SalesAmount FROM ProductSales) ps
  • This query pulls all the rows of data that we need to create the cross-tab results.  The (ps) after the query is creating a temporary table of the results that can then be used to satisfy the query for step 1.
  
  
• (3) The PIVOT expression
  • PIVOT (SUM (SalesAmount) FOR Product IN ( [Oranges], [Pickles]) ) AS pvt
  • This query does the actual summarization and puts the results into a temporary table called pvt

Another key thing to notice in here is the use of the square brackets [ ] around the column names in both the SELECT in part (1) and the IN in part (3).  These are key, because the pivot operation is treating the values in these columns as column names and this is how the breaking and grouping is done to display the data.
 refrence:http://www.mssqltips.com

Can we overload on return types

What is a Delegate?

What is a Delegate?
delegates are function pointers that point to function of matching signatures.

On the other hand .NET framework has introduced a type-safe mechanism called delegates, with automatic verification of the signature by the compiler.

While using delegates it is very much necessary to make sure that the functions which the delegates points has the same number of argument type and same return type. For example if we have a method that takes a single string as a parameter and another method that takes two string parameters, then we need to have two separate delegate type for each method.

Types of Delegate

Delegates are of two types:

Single cast delegate

A delegate is called single cast delegate if it invokes a single method. In other words we can say that SingleCast Delegates refer to a single method with matching signature.

Multicast Delegates

MultiCast Delegates are nothing but a single delegate that can invoke multiple methods of matching signature. MultiCast Delegate derives from System.MulticastDelegate class which is a subclass of System.Delegate.

For example if we are required to call two methods on a single button click event or mouse over event then using MultiCast Delegates we can easily call the methods.

How to Detect an Error in T-SQL

After each statement in T-SQL, SQL Server sets the global variable @@error to 0, unless an error occurs, in which case @@error is set to the number of that error.

More precisely, if SQL Server emits a message with a severity of 11 or higher,
@@error will hold the number of that message.
And if SQL Server emits a message with a severity level of 10 or lower,
SQL Server does not set @@error,
and thus you cannot tell from T-SQL that the message was produced.


There is no way to prevent SQL Server from raising error messages.
There is a small set of conditions for which you can use SET commands to control whether these conditions are errors or not

@@error is set after each statement.

Example

CREATE TABLE notnull(a int NOT NULL)
DECLARE @err int,
       @value int
INSERT notnull VALUES (@value)
SELECT @err = @@error
IF @err <> 0
  PRINT '@err is ' + ltrim(str(@err)) + '.'

The output is:

Server: Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column 'a', table
'tempdb.dbo.notnull'; column does not allow nulls. INSERT fails.
The statement has been terminated.
@err is 515.

How can we take decision about when we have to use Interface and when Abstract Class

How can we take decision about when to use Interface and when Abstract Class.

When we thinking about the entity there are two things one is intention and one is implemntation.

I am trying to make a Content Management System where content is a genralize form of article, reviews, blogs etc.

CONTENT

ARTICLE

BLOGS

REVIEW

So content is our base class now how we make a decision whether content class should be Abstract class, Interface or normal class.

First normal class vs other type (abstract and interface).

If content is not a core entity of my application means as per the business logic if content is nothing in my application only Article, Blogs, Review are the core part of business logic then content class should not be a normal class because I’ll never make instance of that class.

So if you will never make instance of base class then Abstract class and Interface are the more appropriate choice.

Second between Interface and Abstract Class.

CONTENT Publish ()

ARTICLE

BLOGS

REVIEW

As you can see content having behavior named “Publish”. If according to my business logic Publish having some default behavior which apply to all I’ll prefer content class as an Abstract class.

If there is no default behavior for the “Publish” and every drive class makes their own implementation then there is no need to implement “Publish” behavior in the base case I’ll prefer Interface.

These are the in general idea of taking decision between abstract class, interface and normal class. But there is one catch.

As we all know there is one constant in software that is “CHANGE”.

If I made content class as Interface then it is difficult to make changes in base class because if I add new method or property in content interface then I have to implement new method in every drive class.

These problems will over come if you are using abstract class for content class and new method is not an abstract type.

So we can replace interface with abstract class except multiple inheritance.

CAN-DO and IS-A relationship is also define the deference between Interface and abstract class.

Interface can be use for multiple inheritance

for example we have another interface named “ICopy” which having behavior copy and every drive class have to implements its own implementation of Copy.

If “Article” class drive from abstract class Content as well as ICopy then article “CAN-DO” copy also.

IS-A is for “generalization” and “specialization” means content is a generalize form of Article, Blogs, Review and Article, Blogs, Review are a specialize form of Content.

So, abstract class defines core identity.

If we are thinking in term of speed then

abstract is fast than interface because interface requires extra in-direction.

Abstract Class vs Interface -1

Abstract Class vs Interface

We can not make instance of Abstract Class as well as Interface.

Here are few differences in Abstract class and Interface as per the definition.

Abstract class can contain abstract methods, abstract property as well as other members (just like normal class).

Interface can only contain abstract methods, properties but we don’t need to put abstract and public keyword. All the methods and properties defined in Interface are by default public and abstract.

//Abstarct Class

public abstract class Vehicles

{

private int noOfWheel;

private string color;

public abstract string Engine

{

get;

set;

}

public abstract void Accelerator();

}

//Interface

public interface Vehicles

{

string Engine

{

get;

set;

}

void Accelerator();

}

abstract class contains private members,

we can put some methods with implementation also.

But in case of interface only methods and properties allowed.

We use abstract class and Interface for the base class in our application.

Transaction-,SQL Transaction

What Is Transaction And Type Of Transaction In SQL

A transaction in SQL is a larger unit of database processing that contains one or more database access operations like insertion ,deletion, retrieval and modification operations.

These transaction are required to fulfil certain characteristics and they are :

ACID Property

Atomicity: The transaction is either performed entirely or not performed at all.

Isolation: The transaction should not be interfered by any other transaction executing concurrently.
Durability: The changes made by the transaction should be permanently committed in the database.
Consistency : If the database was consistent before the execution of the transaction .It should remain consistent after the complete execution of that transaction.

There are two types of transactions: explicit and implicit

Explicit are those that need to be specified like : commit and roll-back

Commit transaction signals that the transaction was successfully executed and the changes/ updates (if any) made by the transaction have been committed to the database and these changes cannot be undone.

Roll-back signals that the transaction was not successfully executed , and the changes/updates made by the transaction have to be undone.

Implicit transactions are those that mark beginning and end of the transaction, each statement like update, delete , insert run within these implicit transactions.,However, you can explicitly specify the beginning and ending of the transaction by "begin transaction" and "end transaction" statements respectively. All the statements within these two boundaries will be marked as one transaction.