How to fix indexes to reduce the fragmentation percentage I need your guidance and help to understand a SQL topic that I've never considered before but now I'm trying to build tables thinking on performance, one of the topics is Fragmentation but after reading some website data I need more knowledge.
The code below will create a new table , that table will have a clustered index as the primary key  but also it will have a non clustered index for a column called HosttTimeStamp which is basically a DATETIME that storage the transaction date . The problem is that both indexes are using 98% of fragmentation and I'm looking a way to reduce that when new data gets inserted
USE [DB1]

IF OBJECT_ID('dbo.ProductionTable', 'U') IS NULL BEGIN
CREATE TABLE dbo.ProductionTable
(
    [Tier1CustomerAccountID] INT NOT NULL,
    [Tier1CustomerBusinessName] VARCHAR(255) NULL,
    [Businessname]NVARCHAR(50) NULL,
    [AccountID] INT NOT NULL,
    [RetailerAcc]VARCHAR(10) NULL,
    [Product] VARCHAR(10) NULL,
    [Title] NVARCHAR(50) NOT NULL, 
    [PlanName] VARCHAR(200) NULL,
    [PlanValue] NUMERIC(18,3) NOT NULL,
    [Hosttimestamp] DATETIME NOT NULL,
    [TerminalID] INT NOT NULL,
    [TxnIDType] CHAR(3) NOT NULL,
    [Region] VARCHAR(255) NULL,
    [State] VARCHAR(255) NULL,
    [RetailerAccTxnID] BIGINT NOT NULL,
    [TxnType] CHAR(3),
    [FirstTxnDate] DATETIME NULL,
    [Classification] VARCHAR(50) NULL
PRIMARY KEY CLUSTERED 
(
    [RetailerAccTxnID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

DECLARE @tableName varchar(max) = 'dbo.ProductionTable';
DECLARE @indexName varchar(max) = 'IX_ATTActivationsData_Hosttimestamp';
DECLARE @indexDefn varchar(max) = '([Hosttimestamp] ASC)';
IF NOT EXISTS(
    SELECT * FROM sys.indexes 
    WHERE Name=@indexName AND OBJECT_ID = OBJECT_ID(@tableName) ) BEGIN

    EXEC('CREATE NONCLUSTERED INDEX ' + @indexName + ' ON ' + @tableName + @indexDefn)

END;

I'm new on the index topic so I was thinking that maybe my non cluster index is wrong, that instead of being &quot; on dbo.ProductionTable (HostTimeStamp) &quot; must be something like  &quot;on dbo.productionTable (RetailerAccTxnID) INCLUDE (HostTimeStamp). The reason is because if I'm not wrong the word &quot;ON&quot; is 100% more for table joins and there will be multiple queries pointing to this table using the HosttimeStamp for the &quot;WHERE&quot; conditions
Check out the IndexOptimize job from Ola Hallengren
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
IndexOptimize is the SQL Server Maintenance Solution’s stored procedure for rebuilding and reorganizing indexes and updating statistics.
Example (Able to run once you've ran MaintenanceSolution.sql from Ola Hallengren)
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30