how to create COMPUTED columns inSQL server 2008 for DATEADD(yy, -1, GETDATE())

  • HI All,

    Can any one help me how to create COMPUTED columns on DATEADD(yy, -1, GETDATE()) ?

    Existing where clause :

    WHERE [BirthDate] > DATEADD(yy, -1, GETDATE())

    Required OUTPUT :

    1. Instead of this DATEADD(yy, -1, GETDATE()) i want to use COMPUTED column to improve the performance, 2. Also create the NON CLUSTERED index on this new computed column.

    CREATE TABLE [dbo].[CompCol](

    [ID] [int] NOT NULL,

    [FirstName] [varchar](100) NULL,

    [LastName] [varchar](100) NULL,

    [BirthDate] [datetime] NULL,

    CONSTRAINT [PK_CompCol] PRIMARY KEY CLUSTERED

    ([ID] ASC)

    )

    GO

    -- Insert One Hundred Thousand Records

    INSERT INTO CompCol (ID,FirstName,LastName, BirthDate)

    SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,

    'Bob',

    CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'

    ELSE 'Brown' END,

    CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN DATEADD(yy,-2, '2010-08-13 14:20:24.853')

    WHEN ROW_NUMBER() OVER (ORDER BY a.name)%3 = 1 THEN DATEADD(yy,-3, '2010-08-13 14:20:24.853')

    WHEN ROW_NUMBER() OVER (ORDER BY a.name)%5 = 1 THEN DATEADD(yy,-5, '2010-08-13 14:20:24.853')

    WHEN ROW_NUMBER() OVER (ORDER BY a.name)%7 = 1 THEN DATEADD(yy,-7, '2010-08-13 14:20:24.853')

    ELSE GETDATE() END

    FROM sys.all_objects a

    CROSS JOIN sys.all_objects b

    GO

    -- Select specific year data

    SELECT ID, FirstName

    FROM CompCol

    WHERE [BirthDate] > DATEADD(yy, -1, GETDATE())

    GO

  • kbhanu15 (1/17/2014)


    HI All,

    Can any one help me how to create COMPUTED columns on DATEADD(yy, -1, GETDATE()) ?

    What have you tried so far?

    1. Instead of this DATEADD(yy, -1, GETDATE()) i want to use COMPUTED column to improve the performance,

    Replacing a computation with exactly the same computation done in a different place isn't going to do anything for performance. Why do you think that the DATEADD is a performance problem?

    2. Also create the NON CLUSTERED index on this new computed column.

    Not possible, because it's not a deterministic function. See 'Creating Indexes on Computed Columns' in Books Online

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply