4

I would like to set a maximum number of rows a SQL Server table I created can have. The thing is that I have a program that constantly writes new data in my datatable, but I only need to storage one value per column. So I'd like to set my maximum amount of possible rows for this datatable to one. Does anyone have an idea how to do this?

Otherwise as I haven't found a possibility to do this so far, I also thought about rewriting my code and using the SQL UPDATE statement.

I am not so sure about what would be better.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MaChaToc
  • 139
  • 1
  • 12
  • 2
    I don't think there's any built-in way to limit the number of rows for a table. You'd have to handle this yourself, e.g. in an `AFTER INSERT` trigger or something – marc_s May 05 '17 at 15:01
  • Assuming your row limit is something like 1000... once you run the insert then you can run a delete statement to remove the # of rows you need to in order to maintain your limit using a CTE and ROW_NUMBER among other ways – S3S May 05 '17 at 15:01
  • I think marc's suggestion is your best bet. If your _are_ able to set a maximum, you run the risk of breaking the application which writes to the database. – HoneyBadger May 05 '17 at 15:13
  • You could use [permissions](https://learn.microsoft.com/en-us/sql/t-sql/functions/permissions-transact-sql). You can limit the app, and its users, to updates only. This approach would need you to refactor the app. – David Rushton May 05 '17 at 15:22
  • @marc_s I guess this would be the way to go... – MaChaToc May 08 '17 at 13:47
  • @destination-data I'm not sure if the permission would interfere with other tables of my database. Or is it possible to set permissions for just one table? – MaChaToc May 08 '17 at 13:49
  • Yes, you can set permissions at the table level. If you combine this with an stored procedure that creates records when it needs to and updates at all other times you should be covered for all eventualities. – David Rushton May 08 '17 at 15:09

1 Answers1

1

Add an identity column to your table and constrain it to your limit.

create table dbo.MyTable (MyColumn varchar(10), i int identity(1,1) check(i <= 5));

insert into dbo.MyTable 
    select 'one' union 
    select 'two' union 
    select 'three' union 
    select 'four' union
    select 'five';


insert into dbo.MyTable 
    select 'nope';

Msg 547, Level 16, State 0, Line 7
The INSERT statement conflicted with the CHECK constraint

If it really is only one row, maybe something like this?

create table dbo.MyTable (MyColumn varchar(10), Limited bit not null default(1) unique);

insert into dbo.MyTable (MyColumn)
    select 'only one';

insert into dbo.MyTable (MyColumn)
    select 'nope';
nathan_jr
  • 9,092
  • 3
  • 40
  • 55