How to reduce index space of sql table??

USE [WMS_RDC]

/****** Object: Table [dbo].[wms_loose_transaction]
Script Date: 08/21/2013 10:42:36 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

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]

SET ANSI_PADDING OFF

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])

ALTER TABLE [dbo].[wms_loose_transaction] CHECK CONSTRAINT [FK_wms_loose_transaction_wms_aielse_master]

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])

ALTER TABLE [dbo].[wms_loose_transaction] CHECK CONSTRAINT [FK_wms_loose_transaction_wms_gatein_receiving_data1]

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])

ALTER TABLE [dbo].[wms_loose_transaction] CHECK CONSTRAINT [FK_wms_loose_transaction_wms_slot_master]

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Iftikar Hussain13y ago
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 Shekhar13y ago
I reduced the the size of column with 2 . but i want to know that how to reduce index space???
Iftikar Hussain13y ago
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 Shekhar13y ago
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 Hussain13y ago
Hi,
You can do it. Can you share your table schema?

Regards,
Iftikar