How can I set a maximum number of rows in SQL Server table?

Question

I need to set the maximum number of rows in SQL Server Compact 3.5 Database tables: the database consists of some tables and each table should have a different maximum number of rows.

If the answer is yes, what is the default rule when the tables are full? Can I set a custom rule (for example I would delete the row with minimum ID, where ID is a column)?

This is very rarely a feature built into a RDBMS... You might just have to do it manually with count() etc; nosql often supports this type of thing more directly (for example LTRIM on redis), but is a different paradigm — Marc Gravell
– Marc Gravell, Commented Mar 3, 2012 at 18:58
+1, I'd be interested to know why you need a maximum number of rows, it's an unusual request for a non-static table. — Ben
– Ben, Commented Mar 3, 2012 at 19:06
@Ben: I would like to keep under control the size of database file. — enzom83
– enzom83, Commented Mar 3, 2012 at 20:06

Jared Shaver · Accepted Answer · 2012-03-03 19:40:13Z

1

Without triggers, you could have a table with one column of integers 1 through max row#, and then in the table where you want to only allow up to max row# define a row number column with a UNIQUE constraint and a foreign key constraint pointing to the table with 1 through max row#. You would need to handle keeping the next row number at the right value, and you would need to write code to handle the foreign key constraint exception once you reach the max row#. Obviously, this is a pretty big hack and will not be good to maintain.

answered Mar 3, 2012 at 19:40

Jared Shaver

1,3398 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yahia · Accepted Answer · 2012-03-03 19:03:03Z

1

IMHO the answer is no - SQL Server does not have a configuration option for what you ask.

With SQL Server editions supporting triggers you could achieve what you want with BEFORE INSERT triggers (one per table)... which would allow for implementing whatever you need as a rule for "table full" situation...

Since SQL Server Compact does NOT support triggers I don't see a way to implement what you ask...

answered Mar 3, 2012 at 19:03

Yahia

70.5k9 gold badges117 silver badges147 bronze badges

Collectives™ on Stack Overflow

How can I set a maximum number of rows in SQL Server table?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related