Transport & Message Security in WCF

WCF provides inbuilt service security, need to configure only as per requirements.

Message Security: This is end to end security, encrypted & signed messages transported, security is in messages itself. Its recommended when some other mediator (service, external routers) exist between client and service. Our won (custom) algorithms can be used for encryption/decryption.
User and Password need to pass to service before calling service methods.

Modes of security to achieve transfer security

• Transport
• Message
• TransportWithMessageCredential

Transport Security
This security achieved by by transport protocol https using SSL certificate. SSL (Secure Sockets Layer) certificate is supplied by Microsoft, its paid.
Transport security is available for all bindings except 'wsDualHttpBinding'.
Transport security is based on

• Caller authentication
• Message integrity
• Message Confidentiality 

Scenarios to use:

• Intranet (Client and service are in same network)
• Client & server communicating directly, there is no other intermediate system
  (If there is some intermediate system that also should communicate on new SSL).

Advantages:

• Caller need not to understand WS security specifications
• Better performance
• Hardware accelerators can be used for more better performance

Disadvantages:

• Recommended scenarios are intranet, or point to point means there transport should not done through some other intermediate system
• Provide limited set of credentials

Message Security 

In message security credentials are encapsulated with every message & messages are encrypted & signed. It uses WS-Security specifications for message security, WS-Security specifications specify enhancements on SOAP.

If we are using ClientCredentialType 'windows' then service uses windows credentials in token to secure messages. Other than windows authentication types are 'Certificate', 'UserName', 'IssuedToken' these all are required service certificate that is used to secure messages.

Service uses service certificate as authentication token.

Tracing in WCF Service

Tracing in wcf service enabled developers to get logged information about events. Only config settings can enable tracing in wcf.

Below given are trace levels, combination of these trace levels can be used  for 'SwitchValue' attribute of Source element.

• Critical 
• Error  
• Warning 
• Information
• Verbose
• Activity Tracing
• All

There are number of Trace sources, traces generated within an assembly are accessed by the listeners defined for that source.

Common listener file can be used for different configured traces sources.
Commonly used is System.ServiceModel

• System.ServiceModel: Logs all stages of WCF processing, whenever configuration is read, a     message is processed in transport, security processing, a message is dispatched in user code, and so on.
• System.ServiceModel.MessageLogging: Logs all messages that flow through the system.
• System.IdentityModel
• System.ServiceModel.Activation
• System.IO.Log: Logging for the .NET Framework interface to the Common Log File System (CLFS).
• System.Runtime.Serialization: Logs when objects are read or written.

Config Settings:

  <system.serviceModel>

    <behaviors>

      <serviceBehaviors>

        <behavior >

          <serviceMetadata httpGetEnabled="true" />         

          <serviceDebug includeExceptionDetailInFaults="false"/>

        </behavior>

      </serviceBehaviors>

    </behaviors>

    <serviceHostingEnvironment multipleSiteBindingsEnabled="true" />

    <diagnostics>

      <messageLogging logMessagesAtServiceLevel="true" logMessagesAtTransportLevel="true"

        logMalformedMessages="true" maxMessagesToLog="20000" maxSizeOfMessageToLog="10000">       

      </messageLogging>

    </diagnostics>

  </system.serviceModel>

  <system.diagnostics>

    <sources>

      <source name="System.ServiceModel"  switchValue="Error, Critical" >

        <listeners >

          <add name="log" initializeData="E:\TracedLog.svclog" type="System.Diagnostics.XmlWriterTraceListener"></add>

        </listeners>

      </source>

    </sources>

  </system.diagnostics>

Self Hosting of WCF Service

WCF service can be hosted with IIS, Self hosting & WAS server.

Self Hosting : In self hosting of WCF service we can host the service in our own application domain.
Whole WCF service can consists in an assembly, as you run the application (assembly) service got started and can be accessed.

Port you are using for service should not be in use by other IIS site and this port should be open on server in order to access it publicly.

Source Code:

Self hosting class
namespace ConsoleApplication1

{

    public class clsSelfHosting

    {

        static void Main(string[] args)

        {

            ServiceHost host;

            Uri uri = new Uri("http://localhost:1300/MyWCFSelfHosting");

            host = new ServiceHost(typeof(ConsoleApplication1.Service1), uri);

            host.AddServiceEndpoint(typeof(IService1), new WSHttpBinding(), "");

            ServiceMetadataBehavior smb = new ServiceMetadataBehavior();

            smb.HttpGetEnabled = true;

            host.Description.Behaviors.Add(smb);

            host.Open();

            Console.WriteLine("Service Started...");

            Console.ReadLine();

        }       

    }

}

Service Oriented Architecture (SOA)

Service oriented architecture is an architecture style provides a way of making business applications using loosely coupled architecture of services.

SOA stands on services and communication between services using standard messages.

Services: A services is self contained & self-explanatory business logic accessible across platforms.

Services should be –

• Self contained business logic
• Self-explanatory
• Hosted to be discovered by any one in web world 
• Accessible on any platform in web world 

Messages: Messages are standard format text that is readable across platforms to communicate among services.

Messages should be –

• Standard
• Understandable across platforms in web world
• Able to explain services

Memento Design Pattern

Memento pattern is a way to preserve internal state of an object without violating encapsulation.

It is a widely used behavioral pattern.

There are three section of this pattern

Originator: This is the original object for that memento get created
Memento: This is the copy of original class to preserve the original state of originator object.
Care Taker: This object holds memento object to restore to originator at any point of time.


Requirement: During changing of object state if something goes wrong, object can be reverted to original state.

Use: Original state can be re-stored at any point of time during application running.

Problem Solved: Generally when required people restore object to its original state from database. So by using this pattern no need to query database to restore object.

Sample Code:

    /// <summary>

    /// Original class

    /// </summary>

    public class Origintor

    {

        private string name;

        private string mobile;

        private string eMail;

        public Origintor(string _name, string _mobile, string _email)

        {

            name = _name;

            mobile = _mobile;

            eMail = _email;

        }

        public string Name

        {

            get

            {

                return name;

            }

            set

            {

                name = value;

            }

        }

        public string Mobile

        {

            get

            {

                return mobile;

            }

            set

            {

                mobile = value;

            }

        }

        public string Email

        {

            get

            {

                return eMail;

            }

            set

            {

                eMail = value;

            }

        }

        public Memento SaveMemento()

        {

            return new Memento(name, mobile, eMail);

        }

        public void RestoreMemento(Memento objMemento)

        {

            this.Name = objMemento.Name;

            this.Mobile = objMemento.Mobile;

            this.Email = objMemento.Email;

        }

    }

Builder Design Pattern

The intent of this pattern is to separate construction of an object from its representation, so the same construction process can create different representation.

Its a creational design pattern.
Separates presentation of an object from its construction process.

There are three sections of this pattern:

• Builder: Builder is responsible for defining construction process of each individual part of product. Builder has these small construction processes in it. 
• Director: Director calls concrete builder of product as per client requirement.
• Product: Product is final product that have multiple forms depends on which builder created the product.

Requirement: Whenever we have to create same kind of products with something different representation, we need this pattern.

Use: We can create different products using same construction process.

Problem Solved:  For creating products having different representation, no need to create whole separate process for each.

Sample Code:

    public class ReportDirector

    {

        public ReportDirector()

        {

        }

        public Report CreateReport(ReportBuilder reportBuilder)

        {

            reportBuilder.SetReportType();

            reportBuilder.SetReportTitle();

            reportBuilder.SetReportHeader();

            reportBuilder.SetReportFooter();

            return reportBuilder.GetReport();       

        }

    }

Abstract Factory Pattern

Provides a way to encapsulate a group of individual factories those have common theme (related) without specifying their concrete classes.

• Its an important creational pattern
• Abstract factory is an extension on factory pattern

Requirement: If you have created many factories or looking to create, client code become complex and scattered to call many factories. In that case abstract factory provides a centralized & simple way to call different individual factories using abstract factory.
Client only knows about Abstract factory & calls only Abstract factory for all individual factories.

Use: Client is able to call different related concrete factories from a single point of contact.
Client doesn't know about concrete classes & concrete factories. Client is aware only with abstract factory & abstract products (Interfaces).

Problems Solved: New concrete factories with related theme can be added without updating client.

Sample Code:

    public abstract class AbstractFactory

    {      

        /// <summary>

        /// Get Connection Factory

        /// </summary>

        /// <param name="connectionType">

        /// 1- Sql Connection

        /// 2- Oledb Connection

        /// </param>

        /// <returns></returns>

        public abstract Connection GetConnectionObject(int connectionType)

        {

           return (new ConnectionFactory()).GetConnectionObject(connectionType);

        }

        /// <summary>

        /// Get Command Factory

        /// </summary>

        /// <param name="commandType">

        /// 1- Sql Command

        /// 2- Oledb Command

        /// </param>

        /// <returns></returns>

        public abstract Command GetCommandObject(int commandType) 
        {

           return (new CommandFactory()).GetCommandObject(commandType);

        }

     }

Factory Design Pattern

Define an interface for creating an object, but let sub-classes decide which class to instantiate. Factory Method lets a class defer instantiation to sub-classes.
1. Its a creational pattern.
2. As its name say its to construct some thing.

Problems those solved by this pattern
1. So much of new keywords.
2. Client aware always about all concrete classes.
3. If we need to add / remove a concrete class then client should know about it, code updation required in client code.

Switch(invoiceType)
{
Case : 1
{
objInvoice = new invoiceWithHeader();
}
Case : 2
{
objInvoice = new invoiceWithFooter();
}
:
:
}

Benefits:
1. Reduces no. of new keywords.
2. Client just know about Factory and interface(that is implemented by all concrete classes) only.
3. No changes required at client whenever need to add a new concrete class or need to remove a concrete class.

Conclusion:
When you design an application just think if you really need it a factory to create objects. Maybe using it will bring unnecessary complexity in your application. If you have many objects of the same base type and you manipulate them mostly casted to abstract types, then you need a factory.


Code:
namespace FactoryPatternConsole

{
    //Invoice interface
    public interface IInvoice
    {
        void PrintInvoice();
    }
}

namespace FactoryPatternConsole
{   //Invoice factory
    class InvoiceFactory
    {
        public IInvoice GetInvoice(int invoiceType)
        {
            switch (invoiceType)
            {
                case 1:
                    return new clsInvoiceWithHeader();
                case 2 :
                    return new clsInvoiceWithFooter();
                default :
                    return null;
            }
        }
    }
}

namespace FactoryPatternConsole
{
    //Client
    class Program
    {
        static void Main(string[] args)
        {
            InvoiceFactory objFactory = new InvoiceFactory();
            //Here client just need to pass invoice type code to get required invoice
            IInvoice objInvoice = objFactory.GetInvoice(2);
            objInvoice.PrintInvoice();
            Console.ReadLine();
        }
    }
}

namespace FactoryPatternConsole
{
    //Concrete class invoice with header
    class clsInvoiceWithHeader : IInvoice
    {
        public void PrintInvoice()
        {
            Console.WriteLine("This is an invoice with header.");
        }
    }
}


namespace FactoryPatternConsole
{
    //Concrete class invoice with footer
    class clsInvoiceWithFooter : IInvoice
    {
        public void PrintInvoice()
        {
            Console.WriteLine("This is an invoice with footer.");
        }
    }
}

✅ Benefits

• Decouples object creation logic
• Centralizes creation (easy to change later)
• Supports adding new types easily

---

🧠 Real Uses in .NET Core

1. Dependency Injection Container (built-in factory)

   • When you call builder.Services.AddTransient<IMyService, MyService>(),
     the DI container acts as a factory, creating MyService instances as needed.

2. LoggerFactory

   ILoggerFactory loggerFactory = LoggerFactory.Create(builder =>
   {
       builder.AddConsole();
   });
   var logger =
   loggerFactory.CreateLogger<Program>();
   

3. DbContextFactory (EF Core 5+)

   var factory = new PooledDbContextFactory<AppDbContext>();
   using var context = factory.CreateDbContext();
   
   

Session State Management Strategies

There are two types of session state management strategy

1. In Process
2. out of process

In process – Stores session data on the same server.

Out of process – Stores data on other server like sql server, state server.

1. In process

Its best to choose when requests to come to same physical server rather than split to multiple IIS machines.

Its very fast and easy to implement because it runs in process.

Its wrong choice if your session state data is expensive to rebuild because when ever ASP.Net worker process or IIS restarts data flushes and rebuilds.

In case of multiple server its unsuitable until you implement some form of servers affinity like ‘network load balancing’.

2. Out of process

A. State Server – Its relies on a windows service, which remains stopped by default. You can start it from administrative tools / services / ASP.Net state service.

You can run it on same web server or can have a different ‘state server’ associated with your web server. It runs separately from IIS.

Beneficial is that you can restart IIS your session state data will not loss.

It require plenty of RAM because it runs separately from IIS.

Losses session state data on restart of server.

B. Sql Server - Stores data in table.

Its more reliable and flexible option than other.

You can restart server without losing data, you can even backup session state data.

Its also most expensive to build and maintain.

IEnumerable and IEnumerator

IEnumerable and IEnumerator are used to iterate over non generic collection.
IEnumerable exposes an enumerator.

IEnumerator has a property 'Current' that returns value of list item at current cursor position.
It has 2 methods
MoveNext() - Moves cursor to next item.
Reset() -  Reset cursor and send it to first item.

You can type cast a collection to IEnumerable or IEnumerator like list, array, array list.

List<int> myCol = List<int>();
mycol.add(5);
mycol.add(15);
mycol.add(20);

IEnumerable<int> ienmb = (IEnumerable<int>) myCol ;
IEnumerator<int> ienmr = (IEnumerator<int>) myCol ;

Now you can use these objects to iterate like this
Itration of IEnumerable
foreach(int i in ienmb) console.writeline(i);

Itration of IEnumerator
while(ienmr.MoveNext()) Console.Writeline(ienmr.Current.tostring());

You can also get enumrator object from enumrable object
IEnumerator<int> IEnumrObject = ienmb.GetEnumerator();

Differance b\w IEnumerable and IEnumerator
IEnumerable actually uses IEnumrator internally.
IEnumerator remember it's state, means it knows where is it's cursor position currently.
Syntactically IEnumerable easy to use.
If there is not any requirement that your collection object know about it's state than you can use IEnumerable.

SDLC

SDLC phases
1. Preliminary Analysis: The objective of phase 1 is to conduct a preliminary analysis, propose alternative solutions, describe costs and benefits and submit a preliminary plan with recommendations.
The 1st stage of SDLC is the investigation phase (Feasibility study). During this stage, business opportunities and problems are identified, and information technology solutions are discussed. Multiple alternative projects may be suggested and their feasibility analyzed. Operational feasibility is assessed, and it is determined whether or not the project fits with the current business environment, and to what degree it addresses business objectives. In addition, an economic feasibility investigation is conducted to judge the costs and benefits of the project. Technical feasibility must also be analyzed to determine if the available hardware and software resources are sufficient to meet expected specifications. A legal feasibility study is important to discover any potential legal ramification. The results of the feasibility study can then be compiled into a report, along with preliminary specifications. When the investigation stage ends, a decision whether or not to move forward with the project should be made. If it is decided to move ahead, a proposal should have been produced that outlines the general specifications of the project.
Conduct the preliminary analysis: in this step, you need to find out the organization's objectives and the nature and scope of the problem under study. Even if a problem refers only to a small segment of the organization itself then you need to find out what the objectives of the organization itself are. Then you need to see how the problem being studied fits in with them.
Propose alternative solutions: In digging into the organization's objectives and specific problems, you may have already covered some solutions. Alternate proposals may come from interviewing employees, clients , suppliers, and/or consultants. You can also study what competitors are doing. With this data, you will have three choices: leave the system as is, improve it, or develop a new system.
Goal of this phase is to prepare BRS (Business Resource specification) or URS (User Resource specification) or CRS (Customer Resource specification). It is good practice if BRS is prepared by company owner that describes what is his business, so analyst can decide and suggest appropriate solution for the business.
Describe the costs and benefits.

2. Systems analysis, requirements definition: Defines project goals into defined functions and operation of the intended application. Analyzes end-user information needs.
Goal of this phase is to prepare SRS (System Requirement Specification).

3. Systems design: Describes desired features and operations in detail, including screen layouts, business rules, process diagrams, pseudo-code and other documentation.
Goal of this phase is to prepare HLD (High Level Document) / Architectural Design and LLD (Low Level Design).

4. Development: The real code is written here. Goal of this phase is to prepare code for divided modules and unit testing (N unit).

5. Integration and testing: Brings all the pieces together into a special testing environment, then checks for errors, bugs and interoperability. Goal of this phase is to integrate different prepared modules from coding phase and testing of integrated module (Product).

6. Acceptance, installation, deployment: The final stage of initial development, where the software is put into production and runs actual business.

7. Maintenance: What happens during the rest of the software's life: changes, correction, additions, moves to a different computing platform and more. This is often the longest of the stages.

Waterfall Model

V & V - Validation and Varification

Some new features of .net Framework 4.0

1. Named parameters - you can pass parameter to any function with name in any sequence.
Ex. Private void Shape(string Name, int Height, int Width)
you can call the function like
Shape(Height : 10, Name : "Rectangle", Width : 20)

2. Optional parameters - You just need to assign value to parameter at definition, then at the time of calling that parameter will be optional.
Ex. Private void Shape(string Name, int Height, int Width = 50)
you can call the function like
Shape(Height : 10, Name : "Rectangle")

3. Dynamic keyword - It represent an object and it's operations resolved at run time.
Ex. private void Shape(dynamic A)
{
A.CircleShap();
}
You can pass any type of object here that have a function CircleShap().

4. Co-Varience and Contravariance - If you create a delegate and assign a method to delegate then it required that signature of method and delegate should be match. Co-Varience and Contravariance changes this need little bit.
Covariance - It allows a more derived return type method to be assign than delegate’s return type.
Contravariance - it allows less derived parameters type method to be assign than delegate’s parameter’s type.

5. Generate from usage - If you write code for usage of any object then you can generate the object by right clicking and clicking on generate.
Ex.
[TestMethod]
public void TransferFundsTest()
{
Account source = new Account() {Balance = 300.0};
Account destination = new Account() {Balance = 100.0};
var xferService = new XferService();

xferService.Transfer(source, destination, 50.0);

Assert.AreEqual(250, source.Balance);
Assert.AreEqual(150, destination.Balance);
}
Right-click on the one of the "Account" instances and select "Generate" and then "Class". Visual Studio generates a simple Account class with no members. Now right-click on one of the "Balance" references and select "Generate" and then "Property". Our Account class now has a simple get/set Balance field. Repeat these same steps with XferService and the Transfer method. Without too much work, we’ve got the following code generated from our unit tests:
class Account
{
public double Balance { get; set; }
}

class XferService
{
internal void Transfer(Account source, Account destination, double p)
{
throw new NotImplementedException();
}
}

6. Reference highlighting - If you select any word in IDE window all same words in window become selected. Now you can navigate to those selected words by clicking ctrl+shift+down arrow and ctrl+shift+ up arrow.

SQL Injection

What is Sql injection
A SQL Injection attack is a form of attack that comes from user input that has not been checked to see that it is valid. The objective is to fool the database system into running malicious code that will reveal sensitive information.

Two types of Sql injection attacks -
First order attack - Attacker harm the DB immediately, when attacker passes some malicious query via any web application to DB.
Example 1 -
User id ->   hi' or 1=1;--
Password ->  hello' or 1=1;--
This will enter the user in.
Example 2 - If web application displaying some data in three columns with some filter criteria. If attacker type below given string in criteria then it will show name, type and id of sysobject table with application data.
' UNION SELECT name, type, id FROM sysobjects;--
Now attacker can type below given string, it will show columns and their lenths of a perticular table of given id.
' UNION SELECT name, '', length FROM syscolumns WHERE id = 1845581613;--
Now attacker have enough information to destory your DB. Now below given string can be passed to criteria, it will provide admin user details.
' UNION SELECT UserName, Password, IsAdmin FROM Users;--

Trigger in Sql

A trigger is a special kind of stored procedure, that is executed automatically when a specified event fired on database server.

There are three types of triggers DML trigger, DDL trigger and Logon trigger.
1. DML trigger - A DML trigger can be created on a table or view, its executes when a user tries to update data through DML operations (insert / update / delete), trigger fires regardless of whether or not data is updated.
DML triggers have two types of nature 'After' and 'Instead of'
After - Trigger with after fires after execution of specified event for that triger is created. We use keyword FOR or AFTER for it, both are same.
Instead Of - Trigger with instead of option fires instead of executing specified event. Trigger of this nature overrides specified event or triggering statement with statements defined in body of trigger.
Instead of option can't be used with DDL or Logon triggers.
Syntax - 

CREATE TRIGGER trigger_name
ON { table | view }
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement }

Ex. This trigger enforces a business rule when user try to insert an employee record in employee table, that protect the table from inserting bonus out of the range that company has specified.

create trigger trgEmpSalary
on tbl_employee
for Insert
AS
begin
if exists (select * from tbl_employee where bonus between 1000 and 10000)
begin
raiserror('Bonus range in company is 1000 to 10000',10,1)
rollback transaction
end
end

LLD (Low level design document)

At start of LLD (Low level design document) mention customer name, project name, list of authors and name and signature of the person who approve the document. After that index and then according to below content

Revision History - Should be a table with fields version number, date of release, author, history of changes and approver.        

1.      Introduction - General description.

1.1              Brief system description - Brief description of system.                          

1.2              Glossary - Vocabulary list, describe terms used in document.

1.3              References - Mention references, given in document if any.

2.      Low Level Design

2.1              Physical Database Design - Describe database structure, with all objects, relations etc.

2.2              Database Engineering - Include data access methods, optimization techniques, query

   construction techniques.

2.3              User Interface Design - Describe UI of project.

2.4              Data Inflows & Outflows - Create end to end data flow diagram from login to last

   operations.

Stored Procedure in Sql

A stored procedure is a group of Transact-Sql statement compiled into a single execution plan. Its faster than ordinary sql statements because they will undergone in a sequence of steps to execute.
By default SPs stored in DB in compiled form, but if we need to create SP that compile each time it runs than we can add 'with recompile' at the time of creation.

Simple syntax -
create procedure procedureName
[@param1 datatype, @param2 datatype, [@param3 datatype out,]...]

[with RECOMPILE]

as
Begin
--Sql statements
End

A procedure can take multiple parameters, and can return -
1. Data like single int or string value.
2. Can return a local or global cursor.
3. Can return a result set for all select statement(and/or stored procedure) contained in SP.

Cursor in Sql

A cursor is a database object, having set of rows with a pointer that identify current row. So one can manipulate data row by row basis. Its like recordset in .net.

A cursor can be declared as forward only or scroll. It can be declared as read only.
A Static cursor has a separate copy of data in tempdb and serve all request from there so changes made to DB does not affect result of cursor, It does not allow modifications.
A Local cursor has scope in area where it is declared like in trigger or in stored procedure, while a Global cursor name have scope throughout the connection.
A Fast_forward cursor specifies forward_only, read_only cursor with performance optimization.

Ex.
declare @emp varchar(10), @sal money
declare myCursor cursor for
select empname, salary from tbl_employee

open mycursor
fetch next from mycursor into @emp, @sal
print @emp + ' getting salary ' + cast(@sal as varchar(10))
while @@FETCH_STATUS = 0
begin
fetch next from mycursor into @emp,@sal
print @emp + ' getting salary ' + cast(@sal as varchar(10))
end
close mycursor
deallocate mycursor

Using PIVOT and UNPIVOT in sql

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

Ex. Table Pivot_Test has data given below

Sales Person	Product	Sales Amount
Bob	Pickles	100
Sue	Oranges	50
Bob	Pickles	25
Bob	Oranges	300
Sue	Oranges	500
Due	Mangoes	800

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

Output

Sales Person	Oranges	Pickles
Bob	300	125
Due	NULL	NULL
Sue	550	NULL

So how does this work?
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 Pivot_Test) 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.

Sql UDFs

A User defined function is a transact-sql or CLR routine that accepts parameters, performs some action and returns result of action as a value, returned value can either be a scalar(single) value or a table.
When can be used
1. Replacing stored procedure.
2. To parameterize a view.
3. In T-sql like select statement.
4. Inside another UDF.
5. In applications calling the function.

Types of UDFs
1. Scalar functions
2. Inline table-valued functions
3. Multi-statement table-valued functions

1. Scalar function - Returns single value.
Ex.
create function ProjectEstimation(@developmentDays int, @testingDays int)
returns int
as
begin
declare @totalDays int;
select @totalDays = @developmentDays + @testingDays;
return @totalDays
end
--calling 
select ProjectEstimation(50, 26)

2. Inline table-valued function - Returns a table as result, no need to declare table structure.
Ex.
create function EmpWiseCityCollection(@emp varchar(20))
returns table as
return
(
select city, sum(Collection) [Collection] from tbl_employee where empName = @emp group by city
);
--calling 
select * from EmpWiseCityCollection('mark')

3. Multi-statement table-valued functions - Returns a table as result, need to declare table structure.
Ex.
CREATE FUNCTION SalesByPerson(@sales_ID int)
RETURNS @SalesData TABLE
(
[CustomerID] int,
[SalesAmount] int,
[SalesDate] date
)
AS
BEGIN
INSERT INTO @SalesData
SELECT Sales.CustomerID, Sales.SalesAmount, Sales.SalesDate from Sales
Where Sales.Sales_ID = @sales_ID
RETURN
END
--calling select * from  SalesByPerson (1002)


Why use multi-statement table-valued functions instead of inline table-valued functions?
1) Generally, we use multi-statement table-valued functions when we need to perform further operations (for example, inserts, updates, or deletes) on the contents of the table variable before returning a result set.
2) We would also use them if we need to perform more complex logic or additional processing on the input parameters of the function before invoking the query to populate the table variable.

EXCEPT and INTERSECT

Except - It returns data from left side query expression that is not in the data by right side query expression.
Ex.

A - data that is not in right side query result
B - data that is not in left side query result
C - data that is common in both results

Except returns A. 

Intersect - It returns data that is common in left and right side query expressions.
Intersect returns C.

Syntax -

{ <query_specification> | ( <query_expression> ) } 
{ EXCEPT | INTERSECT }
{ <query_specification> | ( <query_expression> ) }

Indexes in Sql server

Indexes are database objects can be created on one or more columns of a table(Max 16 columns).
Database Index is like book index that make easy to find out specific record. Creating index on table increase performance to fetch data but add some overhead for DML(Insert, Update, Delete) operations.
Clustered Index - When we create primary key database read the column values and creates Clustered Index for it. It can be only one for one table.

Select * from Customer where customerid = 103
Select * from Customer where customerid = 100

Without indexing these queries will return the result after 100+ comparisons.

With indexing, execution of first query will return value at first comparison. Execution of second query will return the value at the third comparison. See below example for second query:

For query no. 2
Compare 100 vs 103 : Move to left node
Compare 100 vs 101 : Move to left node
Compare 100 vs 100 : Matched, return the record

Non Clustered index - Its useful to create on columns those have repetitive values, like those have thousands records but unique are 10-20.