STATS_DATE return NULL

Question

STATS_DATE return NULL

Hardy21

SSCrazy Eights

Points: 9708
More actions
November 12, 2010 at 12:17 am

#242792

Hello,
When I fire following code, few records has StatisticsDate as NULL though it is primary key:
select object_name(i.object_id), i.name, STATS_DATE(i.[object_id], i.index_id) AS StatisticsDate
FROM sys.indexes i
What is the reason for that? Becuase it is not used yet?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 1

Hardy21 (11/12/2010)
Hello,
When I fire following code, few records has StatisticsDate as NULL though it is primary key:
select object_name(i.object_id), i.name, STATS_DATE(i.[object_id], i.index_id) AS StatisticsDate
FROM sys.indexes i
What is the reason for that? Becuase it is not used yet?

Hi

See this

Create table temp (i int primary key,j int)

Select * from temp

SELECT name AS index_name,

STATS_DATE(object_id, index_id) AS statistics_update_date

FROM sys.indexes where object_id in(

Select id from sys.sysobjects where name='temp')

insert into temp

Select 1,1

UPDATE STATISTICS temp

Select * from temp

SELECT name AS index_name,

STATS_DATE(object_id, index_id) AS statistics_update_date

FROM sys.indexes where object_id in(

Select id from sys.sysobjects where name='temp')

Drop table temp

Go

STATS_DATE Returns the date that the statistics for the specified table were last updated.

If you did not insert a value and do

UPDATE STATISTICS temp

and select and see the STATS_DATE you will be getting NULL only,Statistic needs to be updated.

Thanks

Parthi

Thanks
Parthi

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 2

Thanks for response.

I have executed sp_updatestats stored procedure still same records have NULL for statistic date.

Shall I need to update the statistic for NULL records manually?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Are there any rows in the table?

What's the index_id for that item?

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

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 4

GilaMonster (11/12/2010)
Are there any rows in the table?
What's the index_id for that item?

Yes there are 6 rows in the table and index_id = 1 (its primary key). In same table, there are other indexes that has statistic date and their index ids are 3,4,5,6.

Thanks

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 5

Hardy21 (11/12/2010)
Yes there are 6 rows in the table and index_id = 1 (its primary key). In same table, there are other indexes that has statistic date and their index ids are 3,4,5,6.

What are the other indexes can you have table design here

Thanks

Parthi

Thanks
Parthi

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 6

parthi-1705 (11/12/2010)
Hardy21 (11/12/2010)
Yes there are 6 rows in the table and index_id = 1 (its primary key). In same table, there are other indexes that has statistic date and their index ids are 3,4,5,6.
What are the other indexes can you have table design here
Thanks
Parthi

They are non-unique, non-clustered. I not able put table design here due to client's database.

Thanks

sumit.agrawal SSCommitted Points: 1616 More actions · Answer 7

Try to rebuild one/all indexes on the table and check.... it should come... By the way, Why indexes are required if there are only 6 rows in the table....

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 8

sumit.agrawal (11/12/2010)
Try to rebuild one/all indexes on the table and check.... it should come... By the way, Why indexes are required if there are only 6 rows in the table....

Thanks I will check by rebuilding it. It is my testing database so it has only 6 rows but in production, it may ne in millions.

Thanks