How to reduce index space of sql table??

Question

I have a table containing 20 lacs record. index space - 817.911mb table space - 30mb after pagewise compression can i reduce index space of table???? Give any suggesstion. Thanks in advance

Iftikar Hussain · Answer

you can't do directly on index you need to do for column on which index are created. refer the below link http://stackoverflow.com/questions/4553460/how-to-make-sql-server-indexes-take-less-space

Ravi Shekhar · Answer

I reduced the the size of column with 2 . but i want to know that how to reduce index space???

Iftikar Hussain · Answer

can you check weather the size for below column is correct? [OuterBoxNo] [varchar](25) NOT NULL [MonoboxNo] [varchar](23) NOT NULL [aielse_code] [varchar](50) NULL [slot_code] [varchar](50) NULL if you know that above will never contain that much length of data then reduce the size Regards Iftikar

Ravi Shekhar · Answer

USE [WMS_RDC] GO /****** Object: Table [dbo].[wms_loose_transaction] Script Date: 08/21/2013 10:42:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[wms_loose_transaction]( [OuterBoxNo] [varchar](25) NOT NULL [MonoboxNo] [varchar](23) NOT NULL [GateInNo] [varchar](10) NULL [concept] [varchar](50) NULL [category] [varchar](50) NULL [Status] [varchar](3) NULL [StatusTemp] [varchar](50) NULL [StatusTemp1] [varchar](50) NULL [aielse_code] [varchar](50) NULL [slot_code] [varchar](50) NULL [InvoiceNo] [varchar](15) NULL [lt_attribute2] [varchar](50) NULL [lt_attribute3] [varchar](50) NULL [lt_attribute4] [varchar](50) NULL [transaction_on] [datetime] NULL [transaction_by] [varchar](50) NULL [OKStatus] [varchar](50) NULL [OLDOUTERBOX] [varchar](50) NULL [REPACKSERIAL] [varchar](50) NULL [PRINTSERIALNO] [varchar](50) NULL [REPACKON] [datetime] NULL [REPACKBY] [varchar](50) NULL [putaway_on] [datetime] NULL [putaway_by] [varchar](50) NULL [picking_on] [datetime] NULL [picking_by] [varchar](50) NULL [loaded_on] [datetime] NULL [loaded_by] [varchar](50) NULL [steging_on] [datetime] NULL [steging_by] [varchar](50) NULL [grn_on] [datetime] NULL [grn_by] [varchar](50) NULL [letdown_on] [datetime] NULL [letdown_by] [varchar](50) NULL [hold_on] [datetime] NULL [hold_by] [varchar](50) NULL [StatusTemp2] [char](1) NULL [TempUserID] [varchar](50) NULL [cyclecountdone] [varchar](50) NULL [cyclecountdoneon] [datetime] NULL [cyclecountdoneby] [varchar](50) NULL [GRNNO] [varchar](10) NULL [VARIENCEFLAG] [varchar](15) NULL [UPLOADSTATUS] [varchar](10) NULL [OrderNo] [varchar](15) NULL [Capacity] [int] NULL [ArtCapacity] [int] NULL CONSTRAINT [PK_wms_loose_transaction] PRIMARY KEY CLUSTERED ( [OuterBoxNo] ASC [MonoboxNo] ASC )WITH (PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF IGNORE_DUP_KEY = OFF ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[wms_loose_transaction] WITH CHECK ADD CONSTRAINT [FK_wms_loose_transaction_wms_aielse_master] FOREIGN KEY([aielse_code]) REFERENCES [dbo].[wms_aielse_master] ([aielse_code]) GO ALTER TABLE [dbo].[wms_loose_transaction] CHECK CONSTRAINT [FK_wms_loose_transaction_wms_aielse_master] GO ALTER TABLE [dbo].[wms_loose_transaction] WITH CHECK ADD CONSTRAINT [FK_wms_loose_transaction_wms_gatein_receiving_data1] FOREIGN KEY([GateInNo]) REFERENCES [dbo].[wms_gatein_receiving_data] ([GateInNo]) GO ALTER TABLE [dbo].[wms_loose_transaction] CHECK CONSTRAINT [FK_wms_loose_transaction_wms_gatein_receiving_data1] GO ALTER TABLE [dbo].[wms_loose_transaction] WITH CHECK ADD CONSTRAINT [FK_wms_loose_transaction_wms_slot_master] FOREIGN KEY([slot_code]) REFERENCES [dbo].[wms_slot_master] ([slot_code]) GO ALTER TABLE [dbo].[wms_loose_transaction] CHECK CONSTRAINT [FK_wms_loose_transaction_wms_slot_master] GO

Iftikar Hussain · Answer

Hi You can do it. Can you share your table schema? Regards Iftikar