Sql-server – Resetting a SQL Server 2012 sequence

sequencesql serversql-server-2012

I'm in the process of testing and populating a specific table that leverages the SEQUENCE object. In this process I'm testing populating the table with tens of thousands of insert lines (as I'm unfamiliar with how to program this). The problem I'm seeing with this specific table is that when I start another population test, the SEQUENCE does not reset back to the first number I want (which is 1).

When I wish to re-run a new test I delete the table in question then run the following:

DROP SEQUENCE foo.fee;
GO

DROP SCHEMA foo;
GO

When I want to re-run the test I run the following SCHEMA & SEQUENCE commands, which are fired in the order below:

CREATE SCHEMA foo;
GO

CREATE SEQUENCE foo.fee
START WITH 1
INCREMENT BY 1
NO CYCLE
NO CACHE;

GO

I then create the table:

CREATE TABLE foo.sample_table_with_data
(order_number bigint PRIMARY KEY NOT NULL,
sample_column_one nvarchar(max) NULL,
sample_column_two nvarchar(max) NULL,
sample_column_three nvarchar(max) NULL)

GO

Once that is completed I run the following insert command 50,000 times:

INSERT INTO [foo].[sample_table_with_data] 
(
    [order_number],
    [sample_column_one], 
    [sample_column_two], 
    [sample_column_three]
) 
VALUES 
(
    NEXT VALUE FOR foo.fee, 
    'Blah', 
    'Blah Blah', 
    'Blah Blah Blah'
)

Now there is absolutely no problem with the data entering into the table. The challenge I'm encountering is that when I delete the table, drop the schema and sequence then re-create the table, sequence, and schema the SEQUENCE picks up from the last number in the previous database incarnation and not reset back to one.

For example, if the last number in the sequence is say 634,534 the next sequence number in the new table is 634,535.

After deleting the table and dropping the schema and sequence I run the following to verify removal of the sequence and schema:

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
GO

SELECT * FROM sys.sequences
GO

I'm stumped as to why this is happening. Is there another command that I'm missing here that would help me localize what exactly is going on here?

I should note that this table belongs to a database with 7 other tables all running the SEQUENCE command correctly.

This is a SQL 2012 SP1 Enterprise Edition installation.

Best Answer

Try

ALTER SEQUENCE foo.fee
RESTART

Or:

ALTER SEQUENCE foo.fee
RESTART WITH 1

http://msdn.microsoft.com/en-us/library/ff878572.aspx

Related Solutions

Limiting Generated Sequence Values in Multi-table Insert

This can be done in a single statement, but you have to wrap the sequence call in a function.

By calling a function in the select (as opposed to the sequence directly), you overcome the problem of getting ORA-02287 errors. However, the function will be called once for each row in A, which is not what you want.

This can be overcome by defining the function as deterministic. This allows Oracle to optimize the function so it is only executed once for each distinct parameter value passed in. To make this work in this case, you'll need to pass in ceil(x/2):

create or replace function f(p integer) return number deterministic  as
  l_retval pls_integer;
begin
  select my_seq.nextval into l_retval from dual;
  return l_retval;
end;
/

insert all 
    when mod(x,2) = 0 then
        into b (val) values (seq)
        into c (val) values (seq)
    else 
        into c (val) values (seq)
select x, f(ceil(x/2)) seq from a;


select * from b;

VAL
---
  1 
  2 

select * from c;

VAL
---
  1 
  2 
  1 
  2

Sql-server – Sql Server 2012 Change Data Capture weird behaviour

Here is the MSDN

Large Object Data Types Columns of data type image, text, and ntext are always assigned a NULL value when __$operation = 1 or __$operation = 3. Columns of data type varbinary(max), varchar(max), or nvarchar(max) are assigned a NULL value when __$operation = 3 unless the column changed during the update. When __$operation = 1, these columns are assigned their value at the time of the delete. Computed columns that are included in a capture instance always have a value of NULL.

By default, the maximum size that can be added to a captured column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement is 65,536 bytes or 64 KB. To increase this size to support larger LOB data, use the Configure the max text repl size Server Configuration Option to specify a larger maximum size. For more information, see Configure the max text repl size Server Configuration Option.

You are using a nvarchar(max). I've got the same problem. I fix it by changing my varchar(max) with varchar(255). For an address 255 should be enough.

Hope this help

Best Answer

Related Solutions

Limiting Generated Sequence Values in Multi-table Insert

Sql-server – Sql Server 2012 Change Data Capture weird behaviour

Related Question