Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I would like know what the max no of rows is in a clustered index (non-unique) on a datetime column table in SQL Server 2008R2.

share|improve this question
I'm curious myself. I'd like to think there wasn't one. – iGanja Mar 7 at 4:15

migrated from stackoverflow.com Mar 7 at 9:08

1 Answer

up vote 7 down vote

The number of rows is limited only by available storage. i.e. there isn't one.

Maximum Capacity Specifications for SQL Server

I suspect it might actually be 9,223,372,036,854,775,807 rows (maximum size of a bigint). To put that number of rows into context, if you inserted a billion rows per day, it would take approximately 25,269,512 years to exhaust.

Update: please read comments below, as they talk specifically about the uniqueifier.

share|improve this answer
Thanks, am in under impression that only 2 billion records because it internally creates integer(4 byte key size) range is -ve 2,147,483,648 To +ve 2,147,483,647 . since index key use positive number – rmdussa Mar 7 at 4:39
Is clustered index(non-unique) on datetime column key size is Bigint? – rmdussa Mar 7 at 4:51
3  
Accounting for the uniquifier it is actually 2^32*2^64 records which far exceeds the amount of data which you can store in the maximum allowed database size of 524,272 terabytes. I don't feel like going through the exact calculations but I think when you account for all overhead you could store in the range of 15 to 25 quadrillion rows in a database in this scenario IF the only thing being stored was the datetime value. – Glenn Stevens Mar 7 at 5:31
2  
Basically, it's more rows than you can shake a stick at! – Mitch Wheat Mar 7 at 5:33
1  
@GlennStevens - The uniqueifier only appears on rows that are actually duplicates. Though I suppose given datetimes precison of 300 ticks per second and the range '17530101' to '99991231' there are "only" 78,075,005,760,000 unique possibilities so most would be dupes. – Martin Smith Mar 7 at 9:34
show 7 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.