January 17, 2014 at 1:27 am
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
January 17, 2014 at 1:50 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply