SQL Server Performance

Create index on the Date Part of DATETIME Column

Discussion in 'SQL Server 2008 General Developer Questions' started by baburk, Oct 8, 2010.

  1. baburk New Member

    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.
    baburk, Oct 8, 2010
    #2

  2. Luis Martin Moderator

    Well, in SQL Server 2008, there's a new datatype called "DATE" - you could use that column and create an index on that.
    Luis Martin, Oct 10, 2010
    #3

  3. Adriaan New Member

    Depends - the default constraint is GETDATE(). Does the EventOn column actually need the time of day?
    If it doesn't, and you always rely on the default to enter the date for EventOn, change the default constraint so it returns the date at 0:00:
    DATEADD(DAY, DATEDIFF(DAY,GETDATE(), 0), 0)
    Memory isn't too clear right now - could be this as well:
    DATEADD(DAY, 0, DATEDIFF(DAY,GETDATE(), 0))
    ... make that:
    DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
    Adriaan, Oct 10, 2010
    #4

  4. FrankKalis Moderator

    Just throwing in some more ideas:
    -You could use a computed column on the expression Adriaan gave, persist it, and put an index on it.
    - Leave it as it is and derive the date-only information at runtime of a query.
    What do you want to use it for?
    FrankKalis, Oct 12, 2010
    #5

Share This Page