title: How to make SQL Server indexes take less space?
body: <p>I have a database created by some application. Whole database is more than 50 gb, some problems with backups are occurring and my task is to get this database as small as possible. </p>
  
  <p>Especially one table is very big(22 gb), from which 16.5 gb is taken by indexes, rest 5.5 gb is data. It contains little more than 12 000 000 rows.</p>
  
  <p>Could You tell me is it possible to shrink the indexes? I've already tried rebuilding, reorganizing, recreating clustered index, dbcc cleantable. I also know that nvarchar type takes twice size than varchar, so I changed columns type to varchar, but thanks to that I saved only about 2 gb(1 gb on data and 1 gb on indexes).</p>
  
  <p>Here is an sql for this table(fld0 and fld1 are always NULL):</p>
  
  <pre><code>CREATE TABLE [dbo].[DOC8](
   [ASSOCIATION] [nvarchar](64) NULL DEFAULT (NULL),
   [DOCID] [char](32) NOT NULL,
   [FLD0] [nvarchar](255) NULL DEFAULT (NULL),
   [FLD1] [nvarchar](2048) NULL DEFAULT (NULL),
   [FLD10] [nvarchar](255) NULL DEFAULT (NULL),
   [FLD2] [nvarchar](32) NULL DEFAULT (NULL),
   [FLD3] [nvarchar](255) NULL DEFAULT (NULL),
   [FLD4] [nvarchar](255) NULL DEFAULT (NULL),
   [FLD5] [datetime] NULL DEFAULT (NULL),
   [FLD6] [nvarchar](255) NULL DEFAULT (NULL),
   [FLD7] [nvarchar](255) NULL DEFAULT (NULL),
   [FLD8] [nvarchar](255) NULL DEFAULT (NULL),
   [FLD9] [datetime] NULL DEFAULT (NULL),
   [PARENTID] [char](32) NULL DEFAULT (NULL),
   [POOLID] [char](32) NULL DEFAULT (NULL),
   [PROPERTIES] [ntext] NULL DEFAULT (NULL),
   [FLD11] [nvarchar](255) NULL DEFAULT (NULL),
   [FLD12] [nvarchar](255) NULL DEFAULT (NULL),
  PRIMARY KEY CLUSTERED 
  (
   [DOCID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  GO
  SET ANSI_PADDING OFF
  GO
  CREATE NONCLUSTERED INDEX [DOC8_IDX_0] ON [dbo].[DOC8] 
  (
   [ASSOCIATION] ASC,
   [PARENTID] ASC,
   [POOLID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  CREATE NONCLUSTERED INDEX [DOC8_IDX_1] ON [dbo].[DOC8] 
  (
   [POOLID] ASC,
   [FLD0] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  CREATE NONCLUSTERED INDEX [DOC8_IDX_10] ON [dbo].[DOC8] 
  (
   [POOLID] ASC,
   [FLD11] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  CREATE NONCLUSTERED INDEX [DOC8_IDX_11] ON [dbo].[DOC8] 
  (
   [POOLID] ASC,
   [FLD12] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  CREATE NONCLUSTERED INDEX [DOC8_IDX_2] ON [dbo].[DOC8] 
  (
   [POOLID] ASC,
   [FLD2] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  CREATE NONCLUSTERED INDEX [DOC8_IDX_3] ON [dbo].[DOC8] 
  (
   [POOLID] ASC,
   [FLD3] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  CREATE NONCLUSTERED INDEX [DOC8_IDX_4] ON [dbo].[DOC8] 
  (
   [POOLID] ASC,
   [FLD4] ASC,
   [FLD5] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  CREATE NONCLUSTERED INDEX [DOC8_IDX_5] ON [dbo].[DOC8] 
  (
   [POOLID] ASC,
   [FLD6] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  CREATE NONCLUSTERED INDEX [DOC8_IDX_6] ON [dbo].[DOC8] 
  (
   [POOLID] ASC,
   [FLD7] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  CREATE NONCLUSTERED INDEX [DOC8_IDX_7] ON [dbo].[DOC8] 
  (
   [POOLID] ASC,
   [FLD8] ASC,
   [FLD9] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  CREATE NONCLUSTERED INDEX [DOC8_IDX_8] ON [dbo].[DOC8] 
  (
   [POOLID] ASC,
   [FLD10] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  CREATE NONCLUSTERED INDEX [DOC8_IDX_9] ON [dbo].[DOC8] 
  (
   [PARENTID] ASC,
   [POOLID] ASC,
   [DOCID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  </code></pre>