December 21, 2011 at 1:36 am
I have a partitioned transaction table whose structure is shown below:
CREATE TABLE [dbo].[Transaction](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[TransactionId] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Archived] [int] NOT NULL,
[TransactionTypeId] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AccountId] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LinkedAccountId] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TransactionDate] [datetime] NOT NULL,
[DetailCodeId] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DTCode] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Details] [varchar](1250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Reference] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Amount] [money] NULL,
[Business] [int] NULL,
[Branch] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WIPAmount] [money] NULL,
[ThirdAllowance] [money] NULL,
[NextTransactionId] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MemoType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Debit] [int] NULL,
[FeeEarner] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AuditTrail] [int] NULL,
[VAT] [money] NULL,
[InvoiceNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirstMovement] [datetime] NULL,
[PostingDate] [datetime] NULL,
[AdditionalReference] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Period] [datetime] NULL,
[SysUserCreated] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SysDateCreated] [datetime] NULL,
[SysUserUpdated] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SysDateUpdated] [datetime] NULL,
[SysAdditionalReference] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CalculatedAmount] [money] NULL,
CONSTRAINT [PK_Transaction] PRIMARY KEY CLUSTERED
(
[TransactionDate] ASC,
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [TransactionDatePScheme]([TransactionDate])
) ON [TransactionDatePScheme]([TransactionDate])
The primary key is a combination of the Transactiondate and the Id field. The Database Tuning Adviser generated a non-clustered index below for a query that i ran that joins the transactiontable to a second table that holds a set of accountids, and needs to retrieve transaction details, the dtcodes and the references for those accounts
CREATE NONCLUSTERED INDEX [IX_DTDet] ON [dbo].[Transaction]
(
[AccountId] ASC,
[TransactionDate] ASC,
[Id] ASC
)
INCLUDE ( [DTCode],
[Details],
[Reference]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [TransactionDatePScheme]([TransactionDate]).
Should i drop the clustered index and only keep the non-clustered one as the table is partitioned? Or do i keep both indexes.
A lot of users write queries joining the transaction table to manually created tables with accountid info for the purpose of retrieving transaction details, references and dtcodes specific to these accounts.
December 21, 2011 at 2:19 am
Keep both. They have a different leading column, so they are completely different indexes. The primary key can't be seeked for accountID filters or joins, those would have to scan the table without that new nonclustered.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2011 at 7:51 am
Thanks for the response. Your point has been noted.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply