CODEWITHSUNDEEP
sqldatabasesql-server-2012
Arthur Marçal Flores
Arthur Marçal Flores

Reputation: 37

Define maximum table size or rows per table - SQL Server 2012

Is there a way to define the maximum size per table or the maximum number of rows in a table? (for a SELECT INTO new_table operation)

I was working with SELECT INTO and JOINS in tables with approximately 70 million rows and it happened that I made a mistake in the ON condition. As a consequence, the result of this join operation created a table larger than the database size limit. The DB crashed and went into a recovery mode (which left for 2 days)

I would like to know how to avoid this kind of problem in the future. Is there any "good manners manual" when working with huge tables? Any kind of pre-defined configuration to prevent this problem?

I don't have the code but as I said, it was basically a left join and the result inserted in a new table through SELECT INTO.

PS: I don't have much experience with SQL SERVER or any other Relational database.

Thank you.

Upvotes: 0

Views: 188

Answers (1)

Ross Presser
Ross Presser

Reputation: 6255

SET ROWCOUNT 10000 would have made it so that no more than 10,000 rows would be inserted. However, while that can prevent damage, it would also mask the mistake you made with your SQL query.

I'd say that before running any SELECT INTO, I would do a SELECT COUNT(*) to see how many rows my JOIN and WHERE clauses are resulting in. If the count is huge, or if it spends hours even coming up with a count, then that's your warning sign.

Upvotes: 1

Related Questions