Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to create COMPUTED columns inSQL server 2008 for DATEADD(yy, -1, GETDATE()) Expand / Collapse
Author
Message
Posted Friday, January 17, 2014 1:27 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, October 9, 2014 7:25 AM
Points: 988, Visits: 292
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
Post #1531952
Posted Friday, January 17, 2014 1:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
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

Post #1531956
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse