SQL Server : Drop vs Disable Index

If you are dropping the index; the database will not have any information for dropped index and once the index is dropped, the metadata, statistics and index pages are removed.

But if you disable the existing index of table; database have the metadata of index but it will deallocate index page and the space is freed in database.

 

Here is a script to drop the SQL index from table

 

DROP INDEX OrderNumber_IX ON dbo.Order;

 

To drop all indexes of table

 

DROP INDEX ALL ON dbo.Order;

 

Here is a script to disable an index on table

 

ALTER INDEX OrderNumber_IX ON dbo.Order

Disable;

 

To disable all indexes of table

 

ALTER INDEX ALL ON dbo.Order

Disable;

 

Note : There is one additional data impact of  disabling the clustered index of table;  table will not be available for any DML operation except rebuild and drop command; so before disable clustered index; be very caution about its impact.

 

If you want to enable the disabled index on table; simply run the rebuild command

 

To enable a disabled index of table

 

ALTER INDEX OrderNumber_IX ON dbo.Order

rebuild;

 

To enable the all disabled index of table

 

ALTER INDEX ALL ON dbo.Order

rebuild;

How to modify system-versioned temporal table schema in SQL Server

In SQL Server, Temporal Table is used to keep the full history of data change for table without doing any extra coding or efforts. It will allow to you get table data at any point of time of past. For more information about SQL Server temporal tables, please visit temporal table in SQL Server .

If you want to modify (add or modify or delete column) the existing temporal table schema, you need to make sure history table schema should be sync.

Here are steps how to add new column ‘ZipCode’ in system-version temporal table.

1.   Turn off System Versioning

ALTER TABLE [dbo].[Office] SET (SYSTEM_VERSIONING = OFF);

2.   Modify the existing both Tables schema ( Office and OfficeHistory )

ALTER TABLE [dbo].[Office] ADD ZipCode varchar(10) NULL;

ALTER TABLE [dbo].[OfficeHistory] ADD ZipCode varchar(10) NULL;

3.   If data update is needed in history table, do it

Update [dbo].[OfficeHistory] SET ZipCode = '43212' where City = 'NYK'

4.   Turn on System Version option

        ALTER TABLE [dbo].[Office]

        SET

         (

            SYSTEM_VERSIONING = ON

           ( HISTORY_TABLE = [dbo].[OfficeHistory])

         );

Tables:

CREATE TABLE dbo.OfficeHistory(

     OfficeID int NOT NULL 

   , OfficeName varchar(50) NOT NULL

   , Street   varchar(50) NOT NULL

   , City varchar(50) NOT NULL

   , State varchar(2) NOT NULL

   ,SysStartTime   datetime2  NOT NULL,

   ,SysEndTime datetime2  NOT NULL,

)

CREATE TABLE Office  

(   

    OfficeID int NOT NULL PRIMARY KEY CLUSTERED

   , OfficeName varchar(50) NOT NULL

   , Street   varchar(50) NOT NULL

   , City varchar(50) NOT NULL

   , State varchar(2) NOT NULL

   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL

   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL

   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)    

)  

WITH   

   (  

      SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OfficeHistory)  

   )

Other blogs related to Temporal Table:

SQL - How to create temporal table in SQL Server

SQL - How to truncate system version temporal table

How to Select Top N Rows for Each Group

In this blog, we will learn about the usage of SQL Row_Number() function, SQL Row_Number() function is used to generate the sequential number of each row.

SQL  - The Difference between ROW_NUMBER (), RANK () and DENSE_RANK ()

Order Table:

OrderID	OrderNumber	StatusID	CustomerID	OrderAmount	OrderDate
1	11252019	1	1	$12.00	11/25/2019
2	10252019	3	1	$200.00	10/26/2019
3	10222019	3	1	$388.00	10/26/2019
4	11252019	1	2	$576.00	11/25/2019
5	11222019	3	2	$764.00	10/26/2019
6	11262019	1	3	$952.00	11/25/2019
7	11212019	3	3	$1,140.00	10/26/2019

Here are few examples of select top N rows for each group:

Example 1: we need to find most recent order of each customers

SELECT * FROM

(

Select *, ROW_NUMBER() OVER (Partition BY CustomerID ORDER BY orderDate Desc) [RowNumber] 

From dbo.ORDERS

) P

WHERE P.RowNumber = 1

Output:

OrderID	OrderNumber	StatusID	CustomerID	OrderAmount	OrderDate	RowNumber
1	11252019	1	1	$12.00	11/25/2019	1
4	11252019	1	2	$576.00	11/25/2019	1
6	11262019	1	3	$952.00	11/25/2019	1

Example 2: we need to find most recent top 2 order of each city

SQL Script:

SELECT * FROM

(

Select orders.*, Taddress.City, ROW_NUMBER() OVER (Partition BY TAddress.city ORDER BY orderDate Desc) [RowNumber]

FROM dbo.Orders inner join dbo.Tcustomers on orders.CustomerID = Tcustomers.CustomerID

inner join dbo.TAddress on Tcustomers.ShippingAddressID = Taddress.AddressID

) P

WHERE P.RowNumber <= 2

Output :

OrderID	OrderNumber	StatusID	CustomerID	OrderAmount	OrderDate	City	RowNumber
4	11252019	1	2	$576.00	11/25/2019	DUBLIN	1
1	11252019	1	1	$12.00	11/25/2019	DUBLIN	2
6	11262019	1	3	$952.00	11/25/2019	POWELL	1
7	11212019	3	3	$1,140.00	10/26/2019	POWELL	2