0

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.

Improve this question
1
  • I'm curious myself. I'd like to think there wasn't one.
    – iGanja
    Mar 7, 2013 at 4:15

1 Answer 1

Reset to default
7

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.

Improve this answer
11
  • 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, 2013 at 4:39
  • Is clustered index(non-unique) on datetime column key size is Bigint?
    – rmdussa
    Mar 7, 2013 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, 2013 at 5:31
  • 2
    Basically, it's more rows than you can shake a stick at!
    – Mitch Wheat
    Mar 7, 2013 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, 2013 at 9:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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