CODEWITHSUNDEEP
sqlindexingsql-server-2008-r2
ElHaix
ElHaix

Reputation: 12986

Largest table size with SQL Server 2008 R2?

For example, a website offers the ability to create mobile surveys. Each survey ID is a FK in the survey response table, which contains ALL of the survey responses.

What is the size limitation of this table in a SQL Server 2008 db, if the table contains, say 20 varchar(255) fields including the bigint PK & FK?

I realize this would depend on the file size limitation as well, but I would like some more of an educated answer rather than my guess on this.

In terms of searchability, some fields that contain geo-related details such as the survey ID, city, state, and two commends fields would have to be searchable, and thus indexed ... index only these fields?

Also, aged responses would expire after a given amount of time - thus deleted from the table. Does the table, at this point being very large, need to be re-indexed/cleaned up, after the deletions (which would be an automated process)?

Thanks.

Upvotes: 0

Views: 2228

Answers (3)

Magnus
Magnus

Reputation: 46929

Maximum Capacity Specifications for SQL Server

Bytes per row: 8,060
Rows per table: Limited by available storage

Note

SQL Server supports row-overflow storage which enables variable length columns to be pushed off-row. Only a 24-byte root is stored in the main record for variable length columns pushed out of row; because of this, the effective row limit is higher than in previous releases of SQL Server. For more information, see the "Row-Overflow Data Exceeding 8 KB" topic in SQL Server Books Online

Upvotes: 2

user596075
user596075

Reputation:

As per this Reference, the max size of a table is limited by the available storage.

It sounds like you are going to have a high traffic and high content table. You should consider performance and storage enhancements like Table Partitioning. Also, because this table will be the victim of often INSERTS/UPDATES/DELETES, carefully plan out your indexing, as indexes add overhead for DML statements on the table.

Upvotes: 0

p.campbell
p.campbell

Reputation: 100577

You mention 'table size' -- does this mean number of rows?

Maximum Capacity Specifications for SQL Server

Rows per table : Limited by available storage

Upvotes: 0

Related Questions