Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl→      все
 Утрамбовать таблицу  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7480
 		Есть таблица А размером 2.5ГБ. Если сделать ее копию то ее размер около 1.6ГБ. Есть какие-то средства (онлайн) шринкануть таблицу без пересоздания? База постоянно в онлайне.
2 май 15, 01:25    [17593040]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
komrad
Member

Откуда:
Сообщений: 5496
Relic Hunter
Есть таблица А размером 2.5ГБ. Если сделать ее копию то ее размер около 1.6ГБ. Есть какие-то средства (онлайн) шринкануть таблицу без пересоздания? База постоянно в онлайне.

у вас наверно плотность данных на страницу %60
приведите индексы по таблице
2 май 15, 01:36    [17593067]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7480
 		komrad,

+
CREATE TABLE [dbo].[LedgerMisc](
	[Period] [int] NOT NULL,
	[PostSeq] [int] NOT NULL,
	[PKey] [varchar](32) NOT NULL,
	[WBS1] [varchar](30) NULL,
	[WBS2] [varchar](7) NULL,
	[WBS3] [varchar](7) NULL,
	[Account] [varchar](13) NULL,
	[Org] [varchar](14) NULL,
	[TransType] [varchar](2) NULL,
	[SubType] [varchar](1) NULL,
	[RefNo] [varchar](12) NULL,
	[TransDate] [datetime] NULL,
	[Desc1] [varchar](80) NULL,
	[Desc2] [varchar](80) NULL,
	[Amount] [decimal](19, 4) NOT NULL,
	[CBAmount] [decimal](19, 4) NOT NULL,
	[BillExt] [decimal](19, 4) NOT NULL,
	[ProjectCost] [varchar](1) NOT NULL,
	[AutoEntry] [varchar](1) NOT NULL,
	[SuppressBill] [varchar](1) NOT NULL,
	[BillStatus] [varchar](1) NULL,
	[SkipGL] [varchar](1) NOT NULL,
	[BankCode] [varchar](10) NULL,
	[Invoice] [varchar](12) NULL,
	[InvoiceSection] [varchar](1) NULL,
	[Employee] [varchar](20) NULL,
	[Vendor] [varchar](20) NULL,
	[Line] [smallint] NOT NULL,
	[PartialPayment] [decimal](19, 4) NOT NULL,
	[Discount] [decimal](19, 4) NOT NULL,
	[Voucher] [varchar](12) NULL,
	[BilledWBS1] [varchar](30) NULL,
	[BilledWBS2] [varchar](7) NULL,
	[BilledWBS3] [varchar](7) NULL,
	[BilledInvoice] [varchar](12) NULL,
	[BilledPeriod] [int] NOT NULL,
	[Unit] [varchar](11) NULL,
	[UnitTable] [varchar](30) NULL,
	[UnitQuantity] [decimal](19, 4) NOT NULL,
	[UnitCostRate] [decimal](19, 4) NOT NULL,
	[UnitBillingRate] [decimal](19, 4) NOT NULL,
	[UnitBillExt] [decimal](19, 4) NOT NULL,
	[XferWBS1] [varchar](30) NULL,
	[XferWBS2] [varchar](7) NULL,
	[XferWBS3] [varchar](7) NULL,
	[XferAccount] [varchar](13) NULL,
	[TaxCode] [varchar](10) NULL,
	[TaxAmount] [decimal](19, 4) NOT NULL,
	[TaxCBAmount] [decimal](19, 4) NOT NULL,
	[TaxBasis] [decimal](19, 4) NOT NULL,
	[TaxCBBasis] [decimal](19, 4) NOT NULL,
	[BillTaxCodeOverride] [varchar](10) NULL,
	[WrittenOffPeriod] [int] NOT NULL,
	[TransactionAmount] [decimal](19, 4) NOT NULL,
	[TransactionCurrencyCode] [varchar](3) NULL,
	[ExchangeInfo] [varchar](max) NULL,
	[AmountProjectCurrency] [decimal](19, 4) NOT NULL,
	[ProjectExchangeInfo] [varchar](max) NULL,
	[AmountBillingCurrency] [decimal](19, 4) NOT NULL,
	[BillingExchangeInfo] [varchar](max) NULL,
	[AutoEntryAmount] [decimal](19, 4) NOT NULL,
	[AutoEntryExchangeInfo] [varchar](max) NULL,
	[AutoEntryOrg] [varchar](14) NULL,
	[AutoEntryAccount] [varchar](13) NULL,
	[AmountSourceCurrency] [decimal](19, 4) NOT NULL,
	[SourceExchangeInfo] [varchar](max) NULL,
	[PONumber] [varchar](30) NULL,
	[UnitCostRateBillingCurrency] [decimal](19, 4) NOT NULL,
	[LinkCompany] [varchar](14) NULL,
	[LinkWBS1] [varchar](30) NULL,
	[LinkWBS2] [varchar](7) NULL,
	[LinkWBS3] [varchar](7) NULL,
	[Tax2Code] [varchar](10) NULL,
	[Tax2Amount] [decimal](19, 4) NOT NULL,
	[Tax2CBAmount] [decimal](19, 4) NOT NULL,
	[CompoundTax] [varchar](1) NOT NULL,
	[BillTax2CodeOverride] [varchar](10) NULL,
	[GainsAndLossesType] [varchar](1) NULL,
	[Tax2Basis] [decimal](19, 4) NOT NULL,
	[Tax2CBBasis] [decimal](19, 4) NOT NULL,
	[AmountTaxCurrency] [decimal](19, 4) NOT NULL,
	[CBAmountTaxCurrency] [decimal](19, 4) NOT NULL,
	[AmountTax2Currency] [decimal](19, 4) NOT NULL,
	[CBAmountTax2Currency] [decimal](19, 4) NOT NULL,
	[TaxAmountTaxCurrency] [decimal](19, 4) NOT NULL,
	[TaxCBAmountTaxCurrency] [decimal](19, 4) NOT NULL,
	[Tax2AmountTaxCurrency] [decimal](19, 4) NOT NULL,
	[Tax2CBAmountTaxCurrency] [decimal](19, 4) NOT NULL,
	[TaxBasisTaxCurrency] [decimal](19, 4) NOT NULL,
	[TaxCBBasisTaxCurrency] [decimal](19, 4) NOT NULL,
	[Tax2BasisTaxCurrency] [decimal](19, 4) NOT NULL,
	[Tax2CBBasisTaxCurrency] [decimal](19, 4) NOT NULL,
	[TaxAmountFunctionalCurrency] [decimal](19, 4) NOT NULL,
	[TaxCBAmountFunctionalCurrency] [decimal](19, 4) NOT NULL,
	[Tax2AmountFunctionalCurrency] [decimal](19, 4) NOT NULL,
	[Tax2CBAmountFunctionalCurrency] [decimal](19, 4) NOT NULL,
	[TaxBasisFunctionalCurrency] [decimal](19, 4) NOT NULL,
	[TaxCBBasisFunctionalCurrency] [decimal](19, 4) NOT NULL,
	[Tax2BasisFunctionalCurrency] [decimal](19, 4) NOT NULL,
	[Tax2CBBasisFunctionalCurrency] [decimal](19, 4) NOT NULL,
	[TaxAmountSourceCurrency] [decimal](19, 4) NOT NULL,
	[Tax2AmountSourceCurrency] [decimal](19, 4) NOT NULL,
	[DiscountFunctionalCurrency] [decimal](19, 4) NOT NULL,
	[ModUser] [varchar](20) NULL,
	[AuthorizedBy] [varchar](20) NULL,
	[NonRecoverTaxPercent] [decimal](19, 4) NOT NULL,
	[RealizationAmountEmployeeCurrency] [decimal](19, 4) NOT NULL,
	[RealizationAmountProjectCurrency] [decimal](19, 4) NOT NULL,
	[RealizationAmountBillingCurrency] [decimal](19, 4) NOT NULL,
	[NonBill] [varchar](1) NULL,
	[CreditMemoRefNo] [varchar](12) NULL,
	[OriginalAmountSourceCurrency] [decimal](19, 4) NOT NULL,
	[OriginalPaymentCurrencyCode] [varchar](3) NULL,
	[InProcessAccount] [varchar](1) NOT NULL,
	[InProcessAccountCleared] [varchar](1) NOT NULL,
	[EKOriginalLine] [smallint] NOT NULL,
 CONSTRAINT [LedgerMiscPK] PRIMARY KEY NONCLUSTERED 
(
	[Period] ASC,
	[PostSeq] ASC,
	[PKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [LedgerMiscWBS1WBS2WBS3IDX]    Script Date: 5/1/2015 4:48:00 PM ******/
CREATE CLUSTERED INDEX [LedgerMiscWBS1WBS2WBS3IDX] ON [dbo].[LedgerMisc]
(
	[WBS1] ASC,
	[WBS2] ASC,
	[WBS3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [LedgerMiscAccountIDX]    Script Date: 5/1/2015 4:48:00 PM ******/
CREATE NONCLUSTERED INDEX [LedgerMiscAccountIDX] ON [dbo].[LedgerMisc]
(
	[Account] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [LedgerMiscBilledWBS1WBS2WBS3IDX]    Script Date: 5/1/2015 4:48:00 PM ******/
CREATE NONCLUSTERED INDEX [LedgerMiscBilledWBS1WBS2WBS3IDX] ON [dbo].[LedgerMisc]
(
	[BilledWBS1] ASC,
	[BilledWBS2] ASC,
	[BilledWBS3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [LedgerMiscBillStatusIDX]    Script Date: 5/1/2015 4:48:00 PM ******/
CREATE NONCLUSTERED INDEX [LedgerMiscBillStatusIDX] ON [dbo].[LedgerMisc]
(
	[BillStatus] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [LedgerMiscCoveringIDX]    Script Date: 5/1/2015 4:48:00 PM ******/
CREATE NONCLUSTERED INDEX [LedgerMiscCoveringIDX] ON [dbo].[LedgerMisc]
(
	[WBS1] ASC,
	[WBS2] ASC,
	[WBS3] ASC,
	[Account] ASC,
	[Vendor] ASC,
	[Employee] ASC,
	[Unit] ASC,
	[UnitTable] ASC,
	[TransDate] ASC,
	[ProjectCost] ASC,
	[TransType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [LedgerMiscEmployeeVoucherIDX]    Script Date: 5/1/2015 4:48:00 PM ******/
CREATE NONCLUSTERED INDEX [LedgerMiscEmployeeVoucherIDX] ON [dbo].[LedgerMisc]
(
	[Employee] ASC,
	[Voucher] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [LedgerMiscInvoiceIDX]    Script Date: 5/1/2015 4:48:00 PM ******/
CREATE NONCLUSTERED INDEX [LedgerMiscInvoiceIDX] ON [dbo].[LedgerMisc]
(
	[Invoice] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [LedgerMiscTransTypeSubTypeIDX]    Script Date: 5/1/2015 4:48:00 PM ******/
CREATE NONCLUSTERED INDEX [LedgerMiscTransTypeSubTypeIDX] ON [dbo].[LedgerMisc]
(
	[TransType] ASC,
	[SubType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [LedgerMiscVendorVoucherIDX]    Script Date: 5/1/2015 4:48:00 PM ******/
CREATE NONCLUSTERED INDEX [LedgerMiscVendorVoucherIDX] ON [dbo].[LedgerMisc]
(
	[Vendor] ASC,
	[Voucher] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [LedgerMiscWBS1AccountIDX]    Script Date: 5/1/2015 4:48:00 PM ******/
CREATE NONCLUSTERED INDEX [LedgerMiscWBS1AccountIDX] ON [dbo].[LedgerMisc]
(
	[WBS1] ASC,
	[Account] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [Period]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  CONSTRAINT [DF__LedgerMisc__PostSeq_Def0]  DEFAULT ((0)) FOR [PostSeq]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [Amount]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [CBAmount]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [BillExt]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ('N') FOR [ProjectCost]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ('N') FOR [AutoEntry]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ('N') FOR [SuppressBill]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ('N') FOR [SkipGL]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [Line]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [PartialPayment]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [Discount]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [BilledPeriod]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [UnitQuantity]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [UnitCostRate]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [UnitBillingRate]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [UnitBillExt]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [TaxAmount]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [TaxCBAmount]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [TaxBasis]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [TaxCBBasis]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [WrittenOffPeriod]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [TransactionAmount]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [AmountProjectCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [AmountBillingCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [AutoEntryAmount]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [AmountSourceCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT (0) FOR [UnitCostRateBillingCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [Tax2Amount]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [Tax2CBAmount]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ('N') FOR [CompoundTax]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [Tax2Basis]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [Tax2CBBasis]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [AmountTaxCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [CBAmountTaxCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [AmountTax2Currency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [CBAmountTax2Currency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [TaxAmountTaxCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [TaxCBAmountTaxCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [Tax2AmountTaxCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [Tax2CBAmountTaxCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [TaxBasisTaxCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [TaxCBBasisTaxCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [Tax2BasisTaxCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [Tax2CBBasisTaxCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [TaxAmountFunctionalCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [TaxCBAmountFunctionalCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [Tax2AmountFunctionalCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [Tax2CBAmountFunctionalCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [TaxBasisFunctionalCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [TaxCBBasisFunctionalCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [Tax2BasisFunctionalCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [Tax2CBBasisFunctionalCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [TaxAmountSourceCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [Tax2AmountSourceCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [DiscountFunctionalCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [NonRecoverTaxPercent]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [RealizationAmountEmployeeCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [RealizationAmountProjectCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [RealizationAmountBillingCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  CONSTRAINT [DF__LedgerMisc__NonBill_DefN]  DEFAULT ('N') FOR [NonBill]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [OriginalAmountSourceCurrency]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ('N') FOR [InProcessAccount]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ('N') FOR [InProcessAccountCleared]
GO

ALTER TABLE [dbo].[LedgerMisc] ADD  DEFAULT ((0)) FOR [EKOriginalLine]
GO

ALTER TABLE [dbo].[LedgerMisc]  WITH NOCHECK ADD  CONSTRAINT [FK_LedgerMisc_Account_CA] FOREIGN KEY([Account])
REFERENCES [dbo].[CA] ([Account])
GO

ALTER TABLE [dbo].[LedgerMisc] CHECK CONSTRAINT [FK_LedgerMisc_Account_CA]
GO

ALTER TABLE [dbo].[LedgerMisc]  WITH NOCHECK ADD  CONSTRAINT [FK_LedgerMisc_Employee_EM] FOREIGN KEY([Employee])
REFERENCES [dbo].[EM] ([Employee])
GO

ALTER TABLE [dbo].[LedgerMisc] CHECK CONSTRAINT [FK_LedgerMisc_Employee_EM]
GO

ALTER TABLE [dbo].[LedgerMisc]  WITH NOCHECK ADD  CONSTRAINT [FK_LedgerMisc_Vendor_VE] FOREIGN KEY([Vendor])
REFERENCES [dbo].[VE] ([Vendor])
GO

ALTER TABLE [dbo].[LedgerMisc] CHECK CONSTRAINT [FK_LedgerMisc_Vendor_VE]
GO

ALTER TABLE [dbo].[LedgerMisc]  WITH NOCHECK ADD  CONSTRAINT [FK_LedgerMisc_XferAccount_CA] FOREIGN KEY([XferAccount])
REFERENCES [dbo].[CA] ([Account])
GO

ALTER TABLE [dbo].[LedgerMisc] CHECK CONSTRAINT [FK_LedgerMisc_XferAccount_CA]
GO

2 май 15, 01:50    [17593085]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8317
 		Relic Hunter, ALTER TABLE REBUILD.
2 май 15, 10:56    [17593447]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Relic Hunter
Есть таблица А размером 2.5ГБ. Если сделать ее копию то ее размер около 1.6ГБ. Есть какие-то средства (онлайн) шринкануть таблицу без пересоздания?

А смысл? Если бы было 2,5 Tb, тогда ещё можно бы было пытаться.
2 май 15, 10:59    [17593453]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37143
Jovanny
Relic Hunter
Есть таблица А размером 2.5ГБ. Если сделать ее копию то ее размер около 1.6ГБ. Есть какие-то средства (онлайн) шринкануть таблицу без пересоздания?

А смысл? Если бы было 2,5 Tb, тогда ещё можно бы было пытаться.
Чтобы сэкономить io, но, самое главное, - память.
2 май 15, 11:32    [17593526]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
komrad
Member

Откуда:
Сообщений: 5496
 		Relic Hunter,
а фрагментацию индексов не смотрели еще?

полагаю, что она велика
пересоздайте кластерный индекс
3 май 15, 11:48    [17595442]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
komrad
Member

Откуда:
Сообщений: 5496
 		и приведите значение @@version
3 май 15, 11:50    [17595445]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7480
 		Microsoft SQL Server 2008 R2 (SP2) - 10.50.4321.0 (X64)
Jul 9 2014 15:59:57
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
4 май 15, 22:47    [17599504]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7480
Владислав Колосов
Relic Hunter, ALTER TABLE REBUILD.
Не ужимается.
4 май 15, 23:03    [17599549]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
o-o
Guest
Relic Hunter
Владислав Колосов
Relic Hunter, ALTER TABLE REBUILD.
Не ужимается.

и как это соотносится с
Relic Hunter
Есть таблица А размером 2.5ГБ. Если сделать ее копию то ее размер около 1.6ГБ

???
т.е. если SELECT INTO, то ужимается, а если ребилд, то нет?
отличие только в том, что при SELECT INTO FF = 100%, а при ребилде заказанные вами 90%
значит, у вас так легли данные, что если пытаться оставить 90% свободного места,
то приходится больше оставлять, т.е. если еще записей поднапихать, то уже за 90 перевалит,
такие подобрались значения варчаров.
значит, надо ребилдить с FILLFACTOR = 100, ONLINE = ON, это и будет аналог SELECT INTO.

только зачем-то же выбрали FF = 90%, или от фонаря?
поди, апдэйты идут такие, что варчары раздувает
4 май 15, 23:51    [17599663]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7480
 		o-o,

Выполнил команду 
alter table LedgerMisc rebuild WITH(FILLFACTOR = 100)


К сообщению приложен файл. Размер - 4Kb
5 май 15, 00:02    [17599685]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
o-o
Guest 		или еще вариант, вы может просто делаете SELECT INTO и "забываете" кластерный на нее навесить?
у вас исходная таблица кластерная.
это кучу можно как угодно заполнять, а кластерный -- в порядке кластерного ключа.
т.е. конечно, когда порядок записей произвольный, что угодно лучше уложится,
чем когда порядок строго фиксирован -- тут будут дыры на страницах, т.к. между той и этой строкой нельзя какую угодно еще поместить, хоть бы она и влезла.

было бы без разницы, в каком порядке записи, в случае полей фиксированного размера.
но у вас там варчары.
утрированно: если у меня 10 строк по 5 Кб и 10 по 3 Kb,
то я могу их уложить в 10 страниц в произвольном порядке (записывая на страницу по 2 записи: 5 + 3),
а если они идут в указанном порядке, то сразу 10 страниц под записи в 5 Кб и потом еще 5 страниц под 3 + 3,
итого 15, т.е. в 1,5 раза кластерный куче проиграл, + еще дерево навигации
5 май 15, 00:03    [17599686]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
o-o
Guest
Relic Hunter
o-o,

Выполнил команду 
alter table LedgerMisc rebuild WITH(FILLFACTOR = 100)

вторая таблица это полученная при SELECT INTO?
тогда скорее второй вариант, что первая у вас кластерная, а вторая куча
5 май 15, 00:05    [17599691]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7480
 		o-o,

LedgerMisc2 - это "SELECT INTO". Да, LedgerMisc2 - куча. Кластерный индекс был добавлен после заливки.
5 май 15, 00:09    [17599695]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7480
 		Но на размер LedgerMisc2 это не пoвлияло.
5 май 15, 00:12    [17599699]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
o-o
Guest 		Relic Hunter,
покажите на всякий случай:
select OBJECT_NAME(object_id), index_id
from sys.indexes
where OBJECT_NAME(object_id) like 'LedgerMisc%'
5 май 15, 00:13    [17599704]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7480
 		o-o,

К сообщению приложен файл. Размер - 5Kb
5 май 15, 00:17    [17599709]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
o-o
Guest 
select * 
from sys.dm_db_partition_stats 
where object_id in ( object_id('LedgerMisc2'), object_id('LedgerMisc')) and index_id = 1;
5 май 15, 00:27    [17599724]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7480
 		o-o,

partition_id	object_id	index_id	partition_number	in_row_data_page_count	in_row_used_page_count	in_row_reserved_page_count	lob_used_page_count	lob_reserved_page_count	row_overflow_used_page_count	row_overflow_reserved_page_count	used_page_count	reserved_page_count	row_count
72057811627737088	74483344	1	1	209792	210722	210724	0	0	0	0	210722	210724	1854705
72057811627802624	874224848	1	1	210563	211493	211498	0	0	0	0	211493	211498	1854705
5 май 15, 00:28    [17599725]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
o-o
Guest 		это чтобы желающим прочесть удобнее было:
partition_id object_id index_id partition_number in_row_data_page_count in_row_used_page_count in_row_reserved_page_count lob_used_page_count lob_reserved_page_count row_overflow_used_page_count row_overflow_reserved_page_count used_page_count reserved_page_count row_count
72057811627737088 74483344 1 1 209792 210722 210724 0 0 0 0 210722 210724 1854705
72057811627802624 874224848 1 1 210563 211493 211498 0 0 0 0 211493 211498 1854705

у меня нет идей кроме как вы может не по тем полям кластерный сделали во втором случае?
или может уникальность ему приписали?
5 май 15, 00:41    [17599740]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7480
 		o-o,

Вот прямо скопипастил из своего-же скрипта.
/****** Object:  Index [LedgerMiscWBS1WBS2WBS3IDX]    Script Date: 5/1/2015 4:48:00 PM ******/
CREATE CLUSTERED INDEX [LedgerMiscWBS1WBS2WBS3IDX] ON [dbo].[LedgerMisc]
(
	[WBS1] ASC,
	[WBS2] ASC,
	[WBS3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
5 май 15, 00:48    [17599751]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
o-o
Guest 		ну так из своего же скрипта там снова FF = 90,
и я не про исходную таблицу, а про вторую.

давайте еще глобальнее поступим, вот есть 2 процедуры,
взяты отсюда: sp_helpindex

можете оттуда взять или мое запустить (оно же)
+
/*============================================================================
  File:     sp_SQLskills_ExposeColsInIndexLevels

  Summary:  This procedure lists columns in the key vs. those in the leaf level
			of a nonclustered index. This is dependent on whether or not the 
			nonclustered is UNIQUE as well as whether or not the table has a 
			clustered index. It also changes based on whether or not the 
			clustering key is UNIQUE.
			
  Date:     May 2010

  Version:	SQL Server 2005/2008
------------------------------------------------------------------------------
  Written by Paul S. Randal and Kimberly L. Tripp, SQLskills.com

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by SQLskills instructors.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

USE master
go

if OBJECTPROPERTY(OBJECT_ID('sp_SQLskills_ExposeColsInIndexLevels'), 'IsProcedure') = 1
	drop procedure sp_SQLskills_ExposeColsInIndexLevels
go

create procedure sp_SQLskills_ExposeColsInIndexLevels 
(
	@object_id int,
	@index_id int,
	@ColsInTree nvarchar(2126) OUTPUT,
	@ColsInLeaf nvarchar(max) OUTPUT
)
AS
BEGIN
	declare @nonclus_uniq int
			, @column_id int
			, @column_name nvarchar(260)
			, @col_descending bit
			, @colstr	nvarchar (max);

	-- Get clustered index keys (id and name)
	select sic.column_id, QUOTENAME(sc.name, N']') AS column_name, is_descending_key
	into #clus_keys 
	from sys.index_columns AS sic
		JOIN sys.columns AS sc
			ON sic.column_id = sc.column_id AND sc.object_id = sic.object_id
	where sic.[object_id] = @object_id
	and [index_id] = 1;
	
	-- Get nonclustered index keys
	select sic.column_id, sic.is_included_column, QUOTENAME(sc.name, N']') AS column_name, is_descending_key
	into #nonclus_keys 
	from sys.index_columns AS sic
		JOIN sys.columns AS sc
			ON sic.column_id = sc.column_id 
				AND sc.object_id = sic.object_id
	where sic.[object_id] = @object_id
		and sic.[index_id] = @index_id;
		
	-- Is the nonclustered unique?
	select @nonclus_uniq = is_unique 
	from sys.indexes
	where [object_id] = @object_id
		and [index_id] = @index_id;

	if (@nonclus_uniq = 0)
	begin
		-- Case 1: nonunique nonclustered index

		-- cursor for nonclus columns not included and
		-- nonclus columns included but also clus keys
		declare mycursor cursor for
			select column_id, column_name, is_descending_key  
			from #nonclus_keys
			where is_included_column = 0
		open mycursor;
		fetch next from mycursor into @column_id, @column_name, @col_descending;
		WHILE @@FETCH_STATUS = 0
		begin
			select @colstr = ISNULL(@colstr, N'') + @column_name + CASE WHEN @col_descending = 1 THEN '(-)' ELSE N'' END + N', ';
			fetch next from mycursor into @column_id, @column_name, @col_descending;
		end
		close mycursor;
		deallocate mycursor;
		
		-- cursor over clus_keys if clustered
		declare mycursor cursor for
			select column_id, column_name, is_descending_key from #clus_keys
			where column_id not in (select column_id from #nonclus_keys
				where is_included_column = 0)
		open mycursor;
		fetch next from mycursor into @column_id, @column_name, @col_descending;
		WHILE @@FETCH_STATUS = 0
		begin
			select @colstr = ISNULL(@colstr, N'') + @column_name + CASE WHEN @col_descending = 1 THEN '(-)' ELSE N'' END + N', ';
			fetch next from mycursor into @column_id, @column_name, @col_descending;
		end
		close mycursor;
		deallocate mycursor;	
		
		select @ColsInTree = substring(@colstr, 1, LEN(@colstr) -1);
			
		-- find columns not in the nc and not in cl - that are still left to be included.
		declare mycursor cursor for
			select column_id, column_name, is_descending_key from #nonclus_keys
			where column_id not in (select column_id from #clus_keys UNION select column_id from #nonclus_keys where is_included_column = 0)
		open mycursor;
		fetch next from mycursor into @column_id, @column_name, @col_descending;
		WHILE @@FETCH_STATUS = 0
		begin
			select @colstr = ISNULL(@colstr, N'') + @column_name + CASE WHEN @col_descending = 1 THEN '(-)' ELSE N'' END + N', ';
			fetch next from mycursor into @column_id, @column_name, @col_descending;
		end
		close mycursor;
		deallocate mycursor;	
		
		select @ColsInLeaf = substring(@colstr, 1, LEN(@colstr) -1);
		
	end

	-- Case 2: unique nonclustered
	else
	begin
		-- cursor over nonclus_keys that are not includes
		select @colstr = ''
		declare mycursor cursor for
			select column_id, column_name, is_descending_key from #nonclus_keys
			where is_included_column = 0
		open mycursor;
		fetch next from mycursor into @column_id, @column_name, @col_descending;
		WHILE @@FETCH_STATUS = 0
		begin
			select @colstr = ISNULL(@colstr, N'') + @column_name + CASE WHEN @col_descending = 1 THEN '(-)' ELSE N'' END + N', ';
			fetch next from mycursor into @column_id, @column_name, @col_descending;
		end
		close mycursor;
		deallocate mycursor;
		
		select @ColsInTree = substring(@colstr, 1, LEN(@colstr) -1);
	
		-- start with the @ColsInTree and add remaining columns not present...
		declare mycursor cursor for
			select column_id, column_name, is_descending_key from #nonclus_keys 
			WHERE is_included_column = 1;
		open mycursor;
		fetch next from mycursor into @column_id, @column_name, @col_descending;
		WHILE @@FETCH_STATUS = 0
		begin
			select @colstr = ISNULL(@colstr, N'') + @column_name + CASE WHEN @col_descending = 1 THEN '(-)' ELSE N'' END + N', ';
			fetch next from mycursor into @column_id, @column_name, @col_descending;
		end
		close mycursor;
		deallocate mycursor;

		-- get remaining clustered column as long as they're not already in the nonclustered
		declare mycursor cursor for
			select column_id, column_name, is_descending_key from #clus_keys
			where column_id not in (select column_id from #nonclus_keys)
		open mycursor;
		fetch next from mycursor into @column_id, @column_name, @col_descending;
		WHILE @@FETCH_STATUS = 0
		begin
			select @colstr = ISNULL(@colstr, N'') + @column_name + CASE WHEN @col_descending = 1 THEN '(-)' ELSE N'' END + N', ';
			fetch next from mycursor into @column_id, @column_name, @col_descending;
		end
		close mycursor;
		deallocate mycursor;	

		select @ColsInLeaf = substring(@colstr, 1, LEN(@colstr) -1);
		select @colstr = ''
	
	end
	-- Cleanup
	drop table #clus_keys;
	drop table #nonclus_keys;
	
END;
GO

exec sys.sp_MS_marksystemobject 'sp_SQLskills_ExposeColsInIndexLevels'
go


-------------------
CREATE PROCEDURE [dbo].[sp_SQLskills_SQL2008_helpindex]
	@objname nvarchar(776)		-- the table to check for indexes
as

--November 2010: Added a column to show if an index is disabled.
--     May 2010: Added tree/leaf columns to the output - this requires the 
--               stored procedure: sp_SQLskills_ExposeColsInIndexLevels
--               (Better known as sp_helpindex8)
--   March 2010: Added index_id to the output (ordered by index_id as well)
--  August 2008: Fixed a bug (missing begin/end block) AND I found
--               a few other issues that people hadn't noticed (yikes!)!
--   April 2008: Updated to add included columns to the output. 


-- See my blog for updates and/or additional information
-- http://www.SQLskills.com/blogs/Kimberly (Kimberly L. Tripp)

	set nocount on

	declare @objid int,			-- the object id of the table
			@indid smallint,	-- the index id of an index
			@groupid int,  		-- the filegroup id of an index
			@indname sysname,
			@groupname sysname,
			@status int,
			@keys nvarchar(2126),	--Length (16*max_identifierLength)+(15*2)+(16*3)
			@inc_columns	nvarchar(max),
			@inc_Count		smallint,
			@loop_inc_Count		smallint,
			@dbname	sysname,
			@ignore_dup_key	bit,
			@is_unique		bit,
			@is_hypothetical	bit,
			@is_primary_key	bit,
			@is_unique_key 	bit,
			@is_disabled    bit,
			@auto_created	bit,
			@no_recompute	bit,
			@filter_definition	nvarchar(max),
			@ColsInTree nvarchar(2126),
			@ColsInLeaf nvarchar(max)

	-- Check to see that the object names are local to the current database.
	select @dbname = parsename(@objname,3)
	if @dbname is null
		select @dbname = db_name()
	else if @dbname <> db_name()
		begin
			raiserror(15250,-1,-1)
			return (1)
		end

	-- Check to see the the table exists and initialize @objid.
	select @objid = object_id(@objname)
	if @objid is NULL
	begin
		raiserror(15009,-1,-1,@objname,@dbname)
		return (1)
	end

	-- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
	declare ms_crs_ind cursor local static for
		select i.index_id, i.data_space_id, QUOTENAME(i.name, N']') AS name,
			i.ignore_dup_key, i.is_unique, i.is_hypothetical, i.is_primary_key, i.is_unique_constraint,
			s.auto_created, s.no_recompute, i.filter_definition, i.is_disabled
		from sys.indexes as i 
			join sys.stats as s
				on i.object_id = s.object_id 
					and i.index_id = s.stats_id
		where i.object_id = @objid
	open ms_crs_ind
	fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical,
			@is_primary_key, @is_unique_key, @auto_created, @no_recompute, @filter_definition, @is_disabled

	-- IF NO INDEX, QUIT
	if @@fetch_status < 0
	begin
		deallocate ms_crs_ind
		raiserror(15472,-1,-1,@objname) -- Object does not have any indexes.
		return (0)
	end

	-- create temp tables
	CREATE TABLE #spindtab
	(
		index_name			sysname	collate database_default NOT NULL,
		index_id			int,
		ignore_dup_key		bit,
		is_unique			bit,
		is_hypothetical		bit,
		is_primary_key		bit,
		is_unique_key		bit,
		is_disabled         bit,
		auto_created		bit,
		no_recompute		bit,
		groupname			sysname collate database_default NULL,
		index_keys			nvarchar(2126)	collate database_default NOT NULL, -- see @keys above for length descr
		filter_definition	nvarchar(max),
		inc_Count			smallint,
		inc_columns			nvarchar(max),
		cols_in_tree		nvarchar(2126),
		cols_in_leaf		nvarchar(max)
	)

	CREATE TABLE #IncludedColumns
	(	RowNumber	smallint,
		[Name]	nvarchar(128)
	)

	-- Now check out each index, figure out its type and keys and
	--	save the info in a temporary table that we'll print out at the end.
	while @@fetch_status >= 0
	begin
		-- First we'll figure out what the keys are.
		declare @i int, @thiskey nvarchar(131) -- 128+3

		select @keys = QUOTENAME(index_col(@objname, @indid, 1), N']'), @i = 2
		if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
			select @keys = @keys  + '(-)'

		select @thiskey = QUOTENAME(index_col(@objname, @indid, @i), N']')
		if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
			select @thiskey = @thiskey + '(-)'

		while (@thiskey is not null )
		begin
			select @keys = @keys + ', ' + @thiskey, @i = @i + 1
			select @thiskey = QUOTENAME(index_col(@objname, @indid, @i), N']')
			if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
				select @thiskey = @thiskey + '(-)'
		end

		-- Second, we'll figure out what the included columns are.
		select @inc_columns = NULL
		
		SELECT @inc_Count = count(*)
		FROM sys.tables AS tbl
		INNER JOIN sys.indexes AS si 
			ON (si.index_id > 0 
				and si.is_hypothetical = 0) 
				AND (si.object_id=tbl.object_id)
		INNER JOIN sys.index_columns AS ic 
			ON (ic.column_id > 0 
				and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) 
				AND (ic.index_id=CAST(si.index_id AS int) AND ic.object_id=si.object_id)
		INNER JOIN sys.columns AS clmns 
			ON clmns.object_id = ic.object_id 
			and clmns.column_id = ic.column_id
		WHERE ic.is_included_column = 1 and
			(si.index_id = @indid) and 
			(tbl.object_id= @objid)

		IF @inc_Count > 0
		BEGIN
			DELETE FROM #IncludedColumns
			INSERT #IncludedColumns
				SELECT ROW_NUMBER() OVER (ORDER BY clmns.column_id) 
				, clmns.name 
				FROM
				sys.tables AS tbl
				INNER JOIN sys.indexes AS si 
					ON (si.index_id > 0 
						and si.is_hypothetical = 0) 
						AND (si.object_id=tbl.object_id)
				INNER JOIN sys.index_columns AS ic 
					ON (ic.column_id > 0 
						and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) 
						AND (ic.index_id=CAST(si.index_id AS int) AND ic.object_id=si.object_id)
				INNER JOIN sys.columns AS clmns 
					ON clmns.object_id = ic.object_id 
					and clmns.column_id = ic.column_id
				WHERE ic.is_included_column = 1 and
					(si.index_id = @indid) and 
					(tbl.object_id= @objid)
			
			SELECT @inc_columns = QUOTENAME([Name], N']') FROM #IncludedColumns WHERE RowNumber = 1

			SET @loop_inc_Count = 1

			WHILE @loop_inc_Count < @inc_Count
			BEGIN
				SELECT @inc_columns = @inc_columns + ', ' + QUOTENAME([Name], N']') 
					FROM #IncludedColumns WHERE RowNumber = @loop_inc_Count + 1
				SET @loop_inc_Count = @loop_inc_Count + 1
			END
		END
	
		select @groupname = null
		select @groupname = name from sys.data_spaces where data_space_id = @groupid

		-- Get the column list for the tree and leaf level, for all nonclustered indexes IF the table has a clustered index
		IF @indid = 1 AND (SELECT is_unique FROM sys.indexes WHERE index_id = 1 AND object_id = @objid) = 0
			SELECT @ColsInTree = @keys + N', UNIQUIFIER', @ColsInLeaf = N'All columns "included" - the leaf level IS the data row, plus the UNIQUIFIER'
			
		IF @indid = 1 AND (SELECT is_unique FROM sys.indexes WHERE index_id = 1 AND object_id = @objid) = 1
			SELECT @ColsInTree = @keys, @ColsInLeaf = N'All columns "included" - the leaf level IS the data row.'
		
		IF @indid > 1 AND (SELECT COUNT(*) FROM sys.indexes WHERE index_id = 1 AND object_id = @objid) = 1
		exec sp_SQLskills_ExposeColsInIndexLevels @objid, @indid, @ColsInTree OUTPUT, @ColsInLeaf OUTPUT
		
		IF @indid > 1 AND @is_unique = 0 AND (SELECT is_unique FROM sys.indexes WHERE index_id = 1 AND object_id = @objid) = 0 
			SELECT @ColsInTree = @ColsInTree + N', UNIQUIFIER', @ColsInLeaf = @ColsInLeaf + N', UNIQUIFIER'
		
		IF @indid > 1 AND @is_unique = 1 AND (SELECT is_unique FROM sys.indexes WHERE index_id = 1 AND object_id = @objid) = 0 
			SELECT @ColsInLeaf = @ColsInLeaf + N', UNIQUIFIER'
		
		IF @indid > 1 AND (SELECT COUNT(*) FROM sys.indexes WHERE index_id = 1 AND object_id = @objid) = 0 -- table is a HEAP
		BEGIN
			IF (@is_unique_key = 0)
				SELECT @ColsInTree = @keys + N', RID'
					, @ColsInLeaf = @keys + N', RID' + CASE WHEN @inc_columns IS NOT NULL THEN N', ' + @inc_columns ELSE N'' END
		
			IF (@is_unique_key = 1)
				SELECT @ColsInTree = @keys
					, @ColsInLeaf = @keys + N', RID' + CASE WHEN @inc_columns IS NOT NULL THEN N', ' + @inc_columns ELSE N'' END
		END
			
		-- INSERT ROW FOR INDEX
		
		insert into #spindtab values (@indname, @indid, @ignore_dup_key, @is_unique, @is_hypothetical,
			@is_primary_key, @is_unique_key, @is_disabled, @auto_created, @no_recompute, @groupname, @keys, @filter_definition, @inc_Count, @inc_columns, @ColsInTree, @ColsInLeaf)

		-- Next index
    	fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical,
			@is_primary_key, @is_unique_key, @auto_created, @no_recompute, @filter_definition, @is_disabled
	end
	deallocate ms_crs_ind

	-- DISPLAY THE RESULTS
	
	select
		'index_id' = index_id,
		'is_disabled' = is_disabled,
		'index_name' = index_name,
		'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
				case when index_id = 1 then 'clustered' else 'nonclustered' end
				+ case when ignore_dup_key <>0 then ', ignore duplicate keys' else '' end
				+ case when is_unique=1 then ', unique' else '' end
				+ case when is_hypothetical <>0 then ', hypothetical' else '' end
				+ case when is_primary_key <>0 then ', primary key' else '' end
				+ case when is_unique_key <>0 then ', unique key' else '' end
				+ case when auto_created <>0 then ', auto create' else '' end
				+ case when no_recompute <>0 then ', stats no recompute' else '' end
				+ ' located on ' + groupname),
		'index_keys' = index_keys,
		'included_columns' = inc_columns,
		'filter_definition' = filter_definition,
		'columns_in_tree' = cols_in_tree,
		'columns_in_leaf' = cols_in_leaf
		
	from #spindtab
	order by index_id

	return (0) -- sp_SQLskills_SQL2008_helpindex
go

exec sys.sp_MS_marksystemobject 'sp_SQLskills_SQL2008_helpindex'
go

и потом по обоим своим кластерным результаты вывода второй процедуры(первая просто вспомогательная):
exec sp_SQLskills_SQL2008_helpindex 'LedgerMisc';
exec sp_SQLskills_SQL2008_helpindex 'LedgerMisc2';

посмотрю уже завтра
5 май 15, 01:00    [17599763]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7480
 		o-o,

exec sp_SQLskills_SQL2008_helpindex 'LedgerMisc2';

index_id	is_disabled	index_name	index_description	index_keys	included_columns	filter_definition	columns_in_tree	columns_in_leaf
1	0	[LedgerMiscWBS1WBS2WBS3IDX2]	clustered located on PRIMARY	[WBS1], [WBS2], [WBS3]	NULL	NULL	[WBS1], [WBS2], [WBS3], UNIQUIFIER	All columns "included" - the leaf level IS the data row, plus the UNIQUIFIER
5 май 15, 01:11    [17599780]     Ответить | Цитировать Сообщить модератору
 Re: Утрамбовать таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
 		Relic Hunter,

А ничего, что ваш TotalSpaceKB учитывает и все индексы? Если уж пользуетесь запросами из сети, хотя бы сначала разберитесь как они работают.

Выполните и сравните результаты:
exec sp_spaceused 'LedgerMisc', 'true';
exec sp_spaceused 'LedgerMisc2', 'true';
5 май 15, 02:06    [17599812]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl→      все
Все форумы / Microsoft SQL Server Ответить