index fragmentation in sqlserver, When to reorganize and when to rebuild indexes? How to defragment indexes?

Query shows index fragmentation information:

SELECT object_name(dt.object_id) Tablename,si.name
IndexName,dt.avg_fragmentation_in_percent AS
ExternalFragmentation,dt.avg_page_space_used_in_percent AS
InternalFragmentation
FROM
(
    SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
    FROM sys.dm_db_index_physical_stats (db_id('AdventureWorks'),null,null,null,'DETAILED'
)
WHERE index_id <> 0) AS dt INNER JOIN sys.indexes si ON si.object_id=dt.object_id
AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC 


--> Analyzing the result, you can determine where the index fragmentation has occurred, using the following rules:

    ExternalFragmentation value > 10 indicates external fragmentation occurred for the corresponding index
    InternalFragmentation value < 75 indicates internal fragmentation occurred for the corresponding index


When to reorganize and when to rebuild indexes?

You should "reorganize" indexes when the External Fragmentation value for the corresponding index is between 10-15 and the Internal Fragmentation value is between 

60-75. Otherwise, you should rebuild indexes.


How to do it?
Go to Indexes --> Right click on specific indexes --> we can see Rebuid and Reorganize options.


How to defragment indexes?

1.Reorganize the fragmented indexes: execute the following command to do this
ALTER INDEX ALL ON TableName REORGANIZE

2.Rebuild indexes: execute the following command to do this
ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=90,ONLINE=ON) 

You can also rebuild or reorganize individual indexes in the tables by using the index name instead of the 'ALL' keyword in the above queries. Alternatively, you can 

also use SQL Server Management Studio to do index defragmentation.

State Design Patterns - Behavioral Patterns in C#

Allow an object to alter its behavior when its internal state changes. The object will appear to change its class

 class Program
    {
        static void Main(string[] args)
        {
            Context c = new Context(new ConcreteStateA());
            c.Request();
            c.Request();
            c.Request();
            Console.ReadKey();
        }
    }

    class Context
    {
        private State _state;

        public State State
        {
            get { return _state; }
            set
            {
                _state = value;
                Console.WriteLine("State: " + _state.GetType().Name.ToString());
            }
        }
        public Context(State state)
        {
            this.State = state;
        }
        public void Request()
        {
            _state.Handle(this);
        }
    }
    abstract class State
    {
        public abstract void Handle(Context context);
    }
    class ConcreteStateA : State
    {
        public override void Handle(Context context)
        {
            context.State =new ConcreteStateB();
        }
    }
    class ConcreteStateB : State
    {
        public override void Handle(Context context)
        {
            context.State = new ConcreteStateA();
        }
    }

State Design Patterns in C#

State Design Patterns - Behavioral Patterns in  C#

State: Allows an object to change its behavior when its internal state changes.

class Program
    {
        static void Main(string[] args)
        {
            Account account = new Account("paramesh");
            account.Deposite(500.0);

            account.Deposite(500.0);
            account.Deposite(300.0);
            account.Deposite(550.0);
            account.PayInterest();
            account.Withdraw(2000.00);
            account.Withdraw(1100.00);

            // Wait for user
            Console.ReadKey();
        }
    }
    abstract class State
    {
        protected Account account;
        protected double balance;

        protected double interest;
        protected double lowerLimit;
        protected double upperLimit;

        public Account Account
        {
            get { return account; }
            set { account = value; }
        }
        public double Balance
        {
            get { return balance; }
            set { balance = value; }
        }

        public abstract void Deposit(double amount);
        public abstract void Withdraw(double amount);
        public abstract void PayInterest();
    }
    class RedState : State
    {
        private double _serviceFee;
        public RedState(State state)
        {
            this.balance = state.Balance;
            this.account = state.Account;
            Initialize();
        }

        private void Initialize()
        {
            interest = 0.0;
            lowerLimit = -100.0;
            upperLimit = 0.0;
            _serviceFee = 15.00;
        }
        public override void Deposit(double amount)
        {
            balance += amount;
            StateChageCheck();
        }

        private void StateChageCheck()
        {
            if (balance > upperLimit)
            {
                account.State = new SilverState(this);
            }
        }

        public override void Withdraw(double amount)
        {
            amount = amount - _serviceFee;
            Console.WriteLine("No funds available for withdrawal!");
        }

        public override void PayInterest()
        {
            //
        }
    }
    class SilverState : State
    {
        public SilverState(State state) : this(state.Balance, state.Account) { }
        public SilverState(double balance, Account account)
        {
            this.balance = balance;
            this.account = account;
            Initialize();
        }

        private void Initialize()
        {
            interest = 0.0;
            lowerLimit = 0.0;
            upperLimit = 1000.0;
        }
        public override void Deposit(double amount)
        {
            balance += amount;
            StateChangeCheck();
        }

        private void StateChangeCheck()
        {
            if (balance < lowerLimit)
                account.State = new RedState(this);
            else if (balance > upperLimit)
                account.State = new GoldState(this);
        }

        public override void Withdraw(double amount)
        {
            balance -= amount;
            StateChangeCheck();
        }

        public override void PayInterest()
        {
            balance += interest * balance;
            StateChangeCheck();
        }
    }
    class GoldState : State
    {
        public GoldState(State state) : this(state.Balance, state.Account) { }
        public GoldState(double balance, Account account)
        {
            this.balance = balance;
            this.account = account;
            Initialize();
        }

        private void Initialize()
        {
            interest = 0.05;
            lowerLimit = 1000.0;
            upperLimit = 1000000.0;
        }
        public override void Deposit(double amount)
        {
            balance += amount;
            StateChangeCheck();
        }

        private void StateChangeCheck()
        {
            if (balance < 0.0)
                account.State = new RedState(this);
            else if (balance < lowerLimit)
                account.State = new SilverState(this);
        }

        public override void Withdraw(double amount)
        {
            balance -= amount;
            StateChangeCheck();
        }

        public override void PayInterest()
        {
            balance += interest * balance;
            StateChangeCheck();
        }
    }



    class Account
    {
        private State _state;
        private string _owner;

        public Account(string owner)
        {
            this._owner = owner;
            this._state = new SilverState(0.0, this);
        }
        public double Balance
        {
            get { return _state.Balance; }
        }
        public State State
        {
            get { return _state; }
            set { _state = value; }
        }
        public void Deposite(double amount)
        {
            _state.Deposit(amount);
            Console.WriteLine("Deposited {0:C}", amount);
            Console.WriteLine("Balance={0:C}", this.Balance);
            Console.WriteLine("Status={0}", this.State.GetType().Name);
            Console.WriteLine("");
        }
        public void Withdraw(double amount)
        {
            _state.Withdraw(amount);
            Console.WriteLine("Withdres {0:C}", amount);
            Console.WriteLine("Balance={0:C}", this.Balance);
            Console.WriteLine("Status={0}\n", this.State.GetType().Name);
        }
        public void PayInterest()
        {
            _state.PayInterest();
            Console.WriteLine("Interest Paid..");
            Console.WriteLine("Balance ={0:C}", this.Balance);
            Console.WriteLine("State={0}\n", this.State.GetType().Name);

        }

    }

Observer Design Patterns in c#

Observer: Allows a single object to notify many dependent objects that its state has changed.

class Program
    {
        static void Main(string[] args)
        {
            DummyProduct product = new DummyProduct();

            // We have four shops wanting to keep updated price set by product owner
            Shop shop1 = new Shop("Shop 1");
            Shop shop2 = new Shop("Shop 2");

            Shop shop3 = new Shop("Shop 3");
            Shop shop4 = new Shop("Shop 4");

            //Lets use WAY_1 for first two shops
            product.Attach(shop1);
            product.Attach(shop2);

            //Lets use WAY_2 for other two shops
            product.Attach2(shop3);
            product.Attach2(shop4);

            //Now lets try changing the products price, this should update the shops automatically
            product.ChangePrice(23.0f);

            //Now shop2 and shop 4 are not interested in new prices so they unsubscribe
            product.Detach(shop2);
            product.Detach2(shop4);

            //Now lets try changing the products price again
            product.ChangePrice(26.0f);

            Console.Read();
        }
    }
    interface IObserver
    {
        void Update(float price);
    }
    class Shop : IObserver
    {
        string name;
        float price = 0.0f;

        public Shop(string name)
        {
            this.name = name;
        }

        #region IObserver Members

        public void Update(float price)
        {
            this.price = price;
            Console.WriteLine(@"Price at {0} is now {1}", name, price);
        }

        #endregion
    }
    abstract class ASubject
    {
        ArrayList list = new ArrayList();

        public delegate void StatusUpdate(float price);
        public event StatusUpdate OnStatusUpdate = null;

        public void Attach(Shop product)
        {
            list.Add(product);
        }
        public void Detach(Shop product)
        {
            list.Remove(product);
        }
        public void Attach2(Shop product)
        {
            OnStatusUpdate += new StatusUpdate(product.Update);
        }
        public void Detach2(Shop product)
        {
            OnStatusUpdate -= new StatusUpdate(product.Update);
        }
        public void Notify(float price)
        {
            foreach (Shop p in list)
            {
                p.Update(price);
            }
            if (OnStatusUpdate != null)
                OnStatusUpdate(price);
        }

    }
    class DummyProduct : ASubject
    {
        public void ChangePrice(float price)
        {
            Notify(price);
        }

    }

Observer Design Patterns in c#

*The Observer pattern in which registered investors are notified every time a stock changes value.

class Program
    {
        static void Main(string[] args)
        {
            IBM ibm = new IBM("IBM", 1202.00);
            ibm.Attach(new Investor("Sorros"));
            ibm.Attach(new Investor("Berkshire"));

            ibm.Price = 120.00;
            ibm.Price = 121.00;
            ibm.Price = 120.44;

            Console.ReadKey();
        }
    }

    interface IInvestor
    {
        void Update(Stock stock);
    }
    abstract class Stock
    {
        //An abstract class cannot be instantiated. 
        //The purpose of an abstract class is to provide "a common definition of a base class" that multiple derived classes can share. 
        private string _symbol;
        private double _price;
        private List<IInvestor> _investors = new List<IInvestor>();

        public Stock(string symbol, double price)
        {
            _symbol = symbol;
            _price = price;
        }
        public void Attach(IInvestor investor)
        {
            _investors.Add(investor);
        }
        public void Detach(IInvestor investor)
        {
            _investors.Remove(investor);
        }
        public void Notity()
        {
            foreach (IInvestor investor in _investors)
            {
                investor.Update(this);
            }
        }
        public double Price
        {
            get { return _price; }
            set
            {
                if (_price != value)
                {
                    _price = value;
                    Notity();
                }
            }
        }
        public string Symbol
        {
            get { return _symbol; }
        }
    }

    class IBM : Stock
    {
        public IBM(string symbol, double price) : base(symbol, price) { }
    }
    class Investor : IInvestor
    {
        private string _name;
        private Stock _stock;

        public Investor(string name)
        {
            this._name = name;
        }

        internal Stock Stock
        {
            get { return _stock; }
            set { _stock = value; }
        }

        public void Update(Stock stock)
        {
            Console.WriteLine("Notified {0} of {1}'s " + "Change to {2:C}", _name, stock.Symbol, stock.Price);
        }
    }

Observer Design Patterns - Behavioral Patterns in C#

Observer: Allows a single object to notify many dependent objects that its state has changed.

* Define a one-to-many dependency between objects so that when one object changes state,

     *   all its dependents are notified and updated automatically.

class Program
    {
        static void Main(string[] args)
        {
            ConcreteSubject s = new ConcreteSubject();

            s.Attach(new ConcreteObserver(s, "x"));
            s.Attach(new ConcreteObserver(s, "y"));
            s.Attach(new ConcreteObserver(s, "z"));

            s.SubjectState = "abc";
            s.Notify();
            Console.ReadKey();
        }
    }

    abstract class Observer
    {
        public abstract void Update();
    }
    abstract class Subject
    {
        private List<Observer> _observer = new List<Observer>();
        public void Attach(Observer observer)
        {
            _observer.Add(observer);
        }
        public void Detach(Observer observer)
        {
            _observer.Remove(observer);
        }
        public void Notify()
        {
            foreach (Observer o in _observer)
            {
                o.Update();
            }
        }
    }

    class ConcreteObserver : Observer
    {
        private string _name;
        private string _observerState;
        private ConcreteSubject _subject;

        public ConcreteObserver(ConcreteSubject subject, string name)
        {
            _subject = subject;
            _name = name;
        }

        public ConcreteSubject Subject
        {
            get { return _subject; }
            set { _subject = value; }
        }

        public string ObserverState
        {
            get { return _observerState; }
            set { _observerState = value; }
        }

        public string Name
        {
            get { return _name; }
            set { _name = value; }
        }


        public override void Update()
        {
            _observerState = _subject.SubjectState;
            Console.WriteLine("Observer {0}'s new state is {1}", _name, _observerState);
        }
    }
    class ConcreteSubject : Subject
    {
        private string _subjectState;

        public string SubjectState
        {
            get { return _subjectState; }
            set { _subjectState = value; }
        }

    }

Memento Design Patterns - Behavioral Patterns in C#

Memento: Allows you to save the state of an object externally from that object.

* Without violating encapsulation, capture and externalize an object's internal state so that the 

object can be restored to this state later.


 class Program
    {
        static void Main(string[] args)
        {
            Originator org = new Originator();
            org.State = "ON";

            //Store internal state
            Caretaker c = new Caretaker();
            c.Memento = org.CreateMemento();

            //Continue changing originator
            org.State = "OFF";

            //Restore saved state
            org.SetMemento(c.Memento);

            Console.ReadKey();
        }
    }
    class Originator
    {
        private string _state;

        public string State
        {
            get { return _state; }
            set
            {
                _state = value;
                Console.WriteLine("State = " + _state);
            }
        }
        //Creates memento
        public Memento CreateMemento()
        {
            return (new Memento(_state));
        }
        public void SetMemento(Memento memento)
        {
            Console.WriteLine("Restoring state..");
            State = memento.State;
        }
    }
    class Memento
    {
        private string _state;
        public Memento(string state)
        {
            this._state = state;
        }
        public string State
        {
            get { return _state; }
        }
    }
    class Caretaker
    {
        private Memento _memento;
        public Memento Memento
        {
            set { _memento = value; }
            get { return _memento; }
        }

    }

Mediator Design Patterns in c#

What is mediator pattern?

According to the GOF the "Mediator Pattern defines an object that encapsulates how a set of objects interact."
Mediator promotes loose coupling by keeping objects from referring to each other explicitly, and it let's you vary their interaction independently.
When to use?

Consider a situation where we have many classes encapsulating specific logic inside them (like invoicing, inventory management, payroll).
     * 
     * Chat application using Mediator Pattern

Let's build a small chat-based application.

Note:

This will not be a real-time chat application.
***** A real chat application requires sockets or other network-based programming along with a Mediator pattern.

     * ***A Mediator Pattern or any other design pattern we have already discussed are not rules, and its not even mandatory that it be used in every application. 
     * Try to understand your problem first and then see which design pattern satisfies your requirements.

class Program
    {
        static void Main(string[] args)
        {
            ClsAbstractMediator objMediator = new ClsMediator();
            ClsUser userA = new ClsMobileUser("userA");
            ClsUser userB = new ClsMobileUser("userB");
            ClsUser userC = new ClsDesktopUser("userC");
            ClsUser userD = new ClsDesktopUser("userD");
            objMediator.AddUser(userA);
            objMediator.AddUser(userB);
            objMediator.AddUser(userC);
            objMediator.AddUser(userD);
            userA.Send("userB", "Hi userB, can you help me to understand mediator pattern");
            userB.Send("userA", "sorry man, even i m trying to understand the same.Try to consult userC");
            userA.Send("userB", "OK\n");
            userA.Send("userC", "hey userC can you help me.");
            userC.Send("userA", "sorry bro, m busy right now, working on an article,will ping in some time ;)");
            userC.Send("userA", "As usual :P");
        }
    }
    public abstract class ClsUser
    {
        internal string userName { get; set; }
        internal ClsAbstractMediator objMediator;
        public ClsUser(string userName)
        {
            this.userName = userName;
        }
        public abstract void Send(string toUser, string message);
        public abstract void Receive(string fromUser, string message);
    }
    public abstract class ClsAbstractMediator
    {
        public abstract void AddUser(clsUser user);
        public abstract void RemoveUser(string userName);
        public abstract void SendMessage(string fromUser, string toUser, string message);
    }
    public class ClsDesktopUser : ClsUser
    {
        public override void Send(string toUser, string message)
        {
            objMediator.SendMessage(toUser, this.userName, message);
        }

        public override void Receive(string fromUser, string message)
        {
            Console.WriteLine(this.userName + ":" + message);
        }
    }


    public class ClsDesktopUser : ClsUser
    {
        public override void Receive(string fromUser, string message)
        {
            Console.WriteLine(this.userName + ":" + message);
        }
        public override void Send(string toUser, string message)
        {
            objMediator.SendMessage(toUser, this.userName, message);
        }
    }
    public class ClsMobileUser : ClsUser
    {
        public override void Receive(string fromUser, string message)
        {
            Console.WriteLine(this.userName + ":" + message);
        }
        public override void Send(string toUser, string message)
        {
            objMediator.SendMessage(this.userName, toUser, message);
        }
    }

Mediator Design Patterns in c#

Mediator: The mediator pattern encapsulate the interaction between a set of objects.

class Program
    {
        static void Main(string[] args)
        {
            FacebookGroupMediator m = new FacebookGroupMediator();
            
            Fan fan1 = new Fan(m, "Fan 1");
            FanB fan2 = new FanB(m, "Fan 2");
            Fan fan3 = new Fan(m, "Fan 3");

            fan1.Send("i like this group");
            fan2.Send("yes i also like this group");

            m.Block(fan3.Receive);

            fan1.Send("Do you agree that this is the best group");
            fan2.Send("Yes i agree");
            m.Unblock(fan3.Receive);
            fan1.Send("Thanks all");

            Console.ReadKey();
        }
    }

    class FacebookGroupMediator
    {
        public delegate void CallBack(string message, string from);
        CallBack respond;
        public void SignOn(CallBack method)
        {
            respond += method;
        }
        public void Block(CallBack method)
        {
            respond -= method;
        }
        public void Unblock(CallBack method)
        {
            respond += method;
        }
        //Send is implemented as a broadcast
        public void Send(string message, string from)
        {
            respond(message, from);
        }
    }
    class Fan
    {
        FacebookGroupMediator mediator;
        protected string name;

        public Fan(FacebookGroupMediator mediator, string name)
        {
            this.mediator = mediator;
            mediator.SignOn(Receive);
            this.name = name;
        }
        public virtual void Receive(string message, string from)
        {
            Console.WriteLine(name + " received from " + from + ": " + message);
        }
        public void Send(string message)
        {
            Console.WriteLine("Send (From " + name + "): " + message);
            mediator.Send(message, name);
        }
    }
    class FanB : Fan
    {
        public FanB(FacebookGroupMediator mediator, string name) : base(mediator, name) { }
        //Does not get copies of own messages
        public override void Receive(string message, string from)
        {
            if (!string.Equals(from, name))
                Console.WriteLine(name + " received from " + from + ": " + message);
        }

    }