0

I'm trying to create a table with some kind of generated default business-key. The value may be overridden, but may not be null. For the column I want to generate a string like "<tablename>_<sequence_id>": for example user_1, user_2,... the sequence is shared over all tables and looks like:

create sequence sq_business_key start with 1 increment by 1

and the datatable-sql is like:

CREATE TABLE [User] (
    guid CHAR(32) NOT NULL DEFAULT (REPLACE(CONVERT([char](36),newid()), '-', '')),
    name VARCHAR(255) NOT NULL,
    description VARCHAR(max) NULL,
    [businessKey] VARCHAR(50) NULL DEFAULT ('user_' + convert(varchar(10), NEXT VALUE FOR sq_business_key)),
    PRIMARY KEY (guid)
);

(don't wonder about the primary-key thingy: sqlalchemy, alembic and sql server together don't work as great as expected for me)

When I'm trying to insert a row in SSMS-Designer, I'm getting following error:

The data in row 1 was not committed. Error Source: Framework Microsoft SqlClient Data Provider. Error Message: NEXT VALUE FOR function cannot be used in a default constraint if ROWCOUNT option has been set, or the query contains TOP or OFFSET.

how can I disable the row-count option? SET ROWCOUNT 0 didn't do the trick (and I guess would've been a too easy fix). Any idea on how to fix this?

Matthias Burger
  • 5,549
  • 7
  • 49
  • 94
  • When are you getting this error, exactly? During the creation of the table? How are you creating the table? SSMS? ADS? Are you using the designer? Entity Framework Code first? Something else? – Thom A Apr 03 '23 at 12:39
  • @ThomA in SSMS when I add a new row and I think, when it possibly tries to insert the value for BusinessKey column – Matthias Burger Apr 03 '23 at 12:42
  • Try to Edit Top 200 rows... – Vojtěch Dohnal Apr 03 '23 at 12:45
  • 3
    So in the designer, perhaps using the "Edit Top 200 rows" '*feature*'? If so, forget that feature exists, it's buggy, quirky, and lacks functionality. Just `INSERT` into the table. – Thom A Apr 03 '23 at 12:46
  • @ThomA Edit doesn't work, an insert statement does... wtf... :D – Matthias Burger Apr 03 '23 at 12:48
  • 4
    Because the designer is buggy, quirky, and lacks functionality, as I mentioned. Just don't use it, forget it exists, purge it from your memory other than to know that it is a terrible thing and best avoided. – Thom A Apr 03 '23 at 12:50
  • 1
    I'm curious of why this limitation exists. What makes rowcount so hard to use with sequences – siggemannen Apr 03 '23 at 13:12
  • @siggemannen imho, the microsoft-guys are geniuses - but sometimes they give you a square peg and tell you to push it through a circle hole. – Matthias Burger Apr 03 '23 at 13:31
  • Yeah, or as i suspect, they had some unit test failing and instead of fixing they just slapped unsupported label on it. Looking at you: nested insert execs, NOT IN in filtered indexes and many many more – siggemannen Apr 03 '23 at 13:37
  • 1
    I have to ask why you want to append the table name in your value like this? That seems like a strange design decision that is now violating 1NF because you are storing multiple values in a single tuple. Additionally you are trying to apply some meaning to a sequential number which goes against most good design patterns. – Sean Lange Apr 03 '23 at 19:50
  • @SeanLange this is just an autogenerated business-key to view in frontend. we got guids as primary keys, but makes it difficult to tell your developer "could you take a look at user 631afd53-69c7-...?" instead of "could you take a look at User_5"? That's why we use it. and wasn't my decision :D – Matthias Burger Apr 04 '23 at 06:03
  • I hope for performance sake your guids are not also your clustered index. For the topic at hand I can see where that was going but I would just store the number and append the prefix in the front end. There is no benefit in storing that. – Sean Lange Apr 04 '23 at 14:24
  • 1
    Ouch. Looking at your table definition your guid is in fact your clustered index. But you are storing it in a varchar. Why? You should use uniqueidentifier. – Sean Lange Apr 04 '23 at 14:26
  • @SeanLange yeah I tried using uniqueidentifier, but I'm using alembic on python - it's hard to get a working model on mssql using python. I'll check about the clustered index. (I used to be .NET dev - and I completely understand your points) – Matthias Burger Apr 04 '23 at 15:33

1 Answers1

0

The solution is not to use the "Edit top 200 rows" in SSMS, because it's using a select top 200... statement. When executing an insert into statement directly, then it works.

Matthias Burger
  • 5,549
  • 7
  • 49
  • 94