How to set 'start with' of sequence to select query result in SQL server?

Question

I am trying to do something like in SQL server 2012

CREATE SEQUENCE item_seq
  START WITH (SELECT MAX(i_item_sk)
     FROM item) 
  INCREMENT BY 1;

Is it possible? What are the other ways if this is not possible? Can we do it like how we do it in PostgreSQL(given below)?

create sequence item_seq 
select setval('item_seq', (select max(i_item_sk)+1 from item), false);

I would be further using this sequence variable in Kettle 'Add sequence' step.

Answer 1

It does not look like you can declare a variable amount in the syntax. However, you can wrap it in an EXEC statement, like so:

DECLARE @max int;
SELECT @max = MAX(i_item_sk)
     FROM item

exec('CREATE SEQUENCE item_seq 
    START WITH ' + @max +
'   INCREMENT BY 1;')

select * from sys.sequences