Concatinated default value from sequence on a non primary key column

Question

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?

Answer 1

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.