Les Cardwell (3/15/2010)
Also, this:
DECLARE @selectDate = getdate()-365
won't work. In SQL Server 2008 it needs to be like this:
DECLARE @selectDate datetime = getdate()-365
For what it's worth, it doesn't work in 2005 either.
Cannot assign a default value to a local variable.
Nope, it doesn't. Being able to assign a value to a variable when it is declared is new to SQL Server 2008. Guess what, we upgraded our PeopleSoft systems to SQL Server 2008 EE. Now, we just need to start upgrading our other systems.
Good catch on the 'type' 🙂
Actually, in 2005 it needs to be...
DECLARE @selectDate DATETIME
SET @selectDate = getdate() - 365
;
I'm jumping around between SQL2000, SQL2005, SQL2008, Oracle10g, and DB2... nutz.
Pretty sure.
Table/Index defs
USE [SandBox]
GO
/****** Object: Table [dbo].[JBMTest] Script Date: 03/15/2010 12:49:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[JBMTest](
[RowNum] [int] IDENTITY(1,1) NOT NULL,
[AccountID] [int] NOT NULL,
[Amount] [money] NOT NULL,
[Date] [datetime] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Index [IX_JBMTest] Script Date: 03/15/2010 12:49:16 ******/
CREATE CLUSTERED INDEX [IX_JBMTest] ON [dbo].[JBMTest]
(
[Date] 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
/****** Object: Index [IX_JBMTest_AccountID_Date] Script Date: 03/15/2010 12:49:16 ******/
CREATE NONCLUSTERED INDEX [IX_JBMTest_AccountID_Date] ON [dbo].[JBMTest]
(
[AccountID] ASC,
[Date] 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]
Simple query:
select * from dbo.JBMTest where Date > getdate() - 365
Actual execution plan attached.
There are 1,000,000 records in the test table.