Create index on the Date Part of DATETIME COLUMN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Create index on the Date Part of DATETIME COLUMN

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2010-10-09 : 01:59:25

This is my table

CREATE TABLE [dbo].[Testng](
[ID] [int] IDENTITY(1,1) NOT NULL,
[EventOn] [datetime] NULL CONSTRAINT [DF03317E3F] DEFAULT (getdate()),
) ON [PRIMARY]

EventOn column is DATETIME. I need to index on this column only for Date Part.

How to ?.

Thanks and Regards,
Babu R.k.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-09 : 02:13:26

You can't create a partial index like that. In SQL Server 2008, we've now got filtered indexes, however that's filtering on rows and not on portion of a column.

What problem are you trying to solve that you think you need this?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-09 : 02:13:58

Here's a regular ol' index on it:

CREATE INDEX idx_EventOn ON Testng(EventOn)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources