0

I want to create indexed view with such t-sql:

    Select
   Table1_ID,
   cast(CONVERT(varchar(8),
   t2.Object_CreationDate, 112)AS DateTime) as Object_CreationDate ,
   Count_BIG(*) as ObjectTotalCount 
   from
       [dbo].Table2 t2 inner join [dbo].Table1 t1 on ...   
   Group BY
       Table1_ID, CONVERT(varchar(8), t2.Object_CreationDate, 112))

I need to make group by only by datepart of column Object_CreationDate (type datetime2 ).

Also I want to set index on columns Theme_Id AND Object_CreationDate in the derived view.
If I use cast(CONVERT(varchar(8), m.Mention_CreationDate, 112)AS DateTime) in SELECT - I'll get problems with index on this column. Because this column (Object_CreationDate) is not deterministic.

I wonder if it is possible to solve a problem.

gbn
  • 422,506
  • 82
  • 585
  • 676
arena-ru
  • 990
  • 2
  • 12
  • 25

1 Answers1

3

replace ...

CONVERT(varchar(8), t2.Object_CreationDate, 112))

... with 

DATEADD(day, DATEDIFF(day, 0, t2.Object_CreationDate), 0)
--OR
CAST(t2.Object_CreationDate AS date)

The 2nd format is SQL Server 2008+ only, the 1st is more general

This removes the time component from a datetime value in the date/datetime datatype domain without any intermediate locale dependent datetime formats

See these answers: One and Two(comments)

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Thanks, it helps. But when I try to set index with command create unique index IX_Theme_ID_Object_CreationDate on [View](Theme_ID,Object_CreationDate) - sql give an error that because of i don't have unique clustered index on this view, i can't add this index. – arena-ru Dec 10 '10 at 13:24
  • You need to identify a "unique clustered index" exactly as the error message says. perhaps "Table1_ID". How else will SQL Server know what row is what if there is no unique key for the indexed viw? – gbn Dec 10 '10 at 13:28