CREATE TABLE [dbo].[CategoriesNews] (
    [Id]       INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    [Name]                 VARCHAR (100)  NOT NULL,
    [Description]          NTEXT          NOT NULL
  
);

CREATE TABLE [dbo].[CategoriesNews] (
    [Id]       INT NOT NULL PRIMARY KEY ,
    [Name]                 VARCHAR (100)  NOT NULL,
    [Description]          NTEXT          NOT NULL
  
);

CREATE TRIGGER [NewId]
	ON [dbo].[CategoriesNews]
	FOR  INSERT
	AS
	BEGIN
		как тут сказать максимальный id+1
	END

use tempdb
go

create table #a ( id int identity(1,1), flag int )
insert into #a( flag ) values( 1 )
select * from #a

begin tran

insert into #a( flag ) 
select 2 as flag
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10

rollback tran

insert into #a( flag ) values( 11 )
select * from #a

go

drop table #a
go