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; }
        }

    }