Sql server rebuild index

When rebuild a index sql server lock the table to prevent database to any modification. Prevent this situation you can use "ONLINE" option. Index rebuild in three step. I am mention below

1. Preparation phase.
2. Build   
3. Final 
In  first phase collect all system metadata to create new structure of empty index. It is snapshot of table and row to provide transaction level read consistency and blocked all DML operation for short time.

Second phase all database record is scanned , merged, sorted and inserted into table.

And final phase, all uncommitted update transactions must be completed and all data modification is blocked for very short time until this is completed and system metadata is update and replace source object by the target.

You can use ALTER statement for rebuilding, but you need to pass index name one by one. So you can use below statement for update all indexes at a time

DECLARE @Database VARCHAR(255)   
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT 

SET @fillfactor = 90 

DECLARE DatabaseCursor CURSOR FOR  
SELECT name FROM MASTER.dbo.sysdatabases   
WHERE name = 'TestTable'
ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
BEGIN  

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + 
  table_name + '']'' as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES 
  WHERE table_type = ''BASE TABLE'''   

   -- create table cursor  
   EXEC (@cmd)  
   OPEN TableCursor   

   FETCH NEXT FROM TableCursor INTO @Table   
   WHILE @@FETCH_STATUS = 0   
   BEGIN   

    IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
       BEGIN
           -- SQL 2005 or higher command 
           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' +CONVERT(VARCHAR(3),@fillfactor) + ')' 
           EXEC (@cmd) 
       END
    ELSE
    BEGIN
          -- SQL 2000 command 
          DBCC DBREINDEX(@Table,' ',@fillfactor)  
       END

    FETCH NEXT FROM TableCursor INTO @Table   
   END   

   CLOSE TableCursor   
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  
END  
CLOSE DatabaseCursor   

DEALLOCATE DatabaseCursor

Check the result after index rebuild

How to check sql server fragmentation

In sql server database heavily index fragmented reduce the performance of query response. Below query to help you identify the details of fragmentation.

select
OBJECT_NAME(stats.object_id) as [Object_Name],
idx.name as [Index_Name] ,
stats.avg_fragmentation_in_percent,
stats.avg_page_space_used_in_percent
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('NewEcater'),null,null,null,'DETAILED')
where index_id<>0) as stats
join sys.indexes idx
on idx.object_id=stats.object_id
and idx.index_id = stats.index_id

Index has "avg_fragmentation_in_percent" is greater then 10 its indicate is this index is highly fragmented and internal fragmentation value goes below 70

Sql server change column data types and rename column

I have used below statement successful for rename column and change column data type in sql server

Its working

EXEC  sp_rename 'PurchaseOrder.bisOrderNow' , 'vcOrdeNow'

ALTE TABLE PurchaseOrder ALTER  COLUMN  vcOrdeNow VARCHAR(60)

"PurchaseOrder" is Table name.