Rebuilding and Reorganizing Indexes based on Fragmentation in SQL Server

Why we have to Rebuild and Reorganize the indexes?
As the time passes, data pages are scattered/fragmented and not in order due to insert, update and delete operation. So, its better the rebuild / reorganize the indexes to improve the query performance. Otherwise, query response would be slow because its would take more time to collect the data's which is scattered.

Rebuilding Indexes:
1. Its nothing but dropping and creating the indexes.
2. More CPU resources need.
3. Deadlock would happen while rebuilding the indexes.

Reorganizing Indexes:
1. Rearranging the leaf pages in correct order.
2. Less CPU resources need.
3. Deadlock possibility is very less.

When to use REORGANIZE and REBUILD?
Generally if fragmentation percentage for each index is from 5 - 30 percent, reorganize the index. if fragmentation percentage is more than 30 percent, its better to REBUILD the index.

SQL Script to REORGANIZE and REBUILD each index in each table:

 DECLARE @ReOrgLowPercent INT,  
   @ReOrgHighPercent INT,  
   @TableName VARCHAR(200)  
 SET @ReOrgLowPercent = 5  
 SET @ReOrgHighPercent = 30  
 DECLARE @IndexTable TABLE ( AlterStatement VARCHAR(4000) )  
 DECLARE C CURSOR  
   FOR SELECT name  
     FROM  sys.tables  
 OPEN C  
 FETCH NEXT FROM C INTO @TableName  
 WHILE ( @@FETCH_STATUS = 0 )  
   BEGIN  
     --PRINT @TableName  
     INSERT INTO @IndexTable ( AlterStatement )  
         SELECT CASE when avg_fragmentation_in_percent between @ReOrgLowPercent and @ReOrgHighPercent  
                then 'ALTER INDEX ' + i.name + ' ON ' + t.name  
                  + ' REORGANIZE;'  
                when avg_fragmentation_in_percent > @ReOrgHighPercent  
                then 'ALTER INDEX ' + i.name + ' ON ' + t.name  
                  + ' REBUILD WITH(ONLINE=ON);' --- enterpise version obly  
             END  
         FROM  sys.dm_db_index_physical_stats(DB_ID(),  
                             OBJECT_ID(@TableName),  
                             NULL, NULL, NULL) AS a  
             JOIN sys.indexes AS i ON a.object_id = i.object_id  
                          AND a.index_id = i.index_id  
             JOIN sys.tables AS t ON t.object_id = i.object_id  
     FETCH NEXT FROM C INTO @TableName  
   END  
 CLOSE C  
 DEALLOCATE C  
 SELECT AlterStatement  
 FROM  @IndexTable  
 WHERE  
      AlterStatement IS NOT NULL