Generating Sequence with no gap

I have one table say City having one or more record, now i need to generate the contiguous sequence for each city to be stored in a different table. Is there any way apart from maintaining a separate table to record the next available number or so? This introduces the chances of getting the duplicate numbers being generated. Is there other batter way of implementing the same scenario.

more ▼

asked Oct 16, 2012 at 12:34 PM in Default

avatar image

40 2 2 2

e.g. City Master with identity as City ID, now the other table needs to have the records in sequence for each City ID each day City Master City ID Name 1, ABC 2, BCD 3, CDE

The other table A City ID, Visitor ID, Seq NO 1,1,1 1,2,2 2,3,1 2,1,2

This Seq No needs to be unique. One can define the unique index on City ID and Seq No to generate the error in case of duplicate Seq No, is there any other way apart from catching the error and resubmitting it again.

Oct 16, 2012 at 01:40 PM vipin001
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

If you mean at the table level then you need to review the column property IDENTITY - http://msdn.microsoft.com/en-us/library/ms186775.aspx

If you want to create a contiguous list of numbers for a recordset then use ROW_NUMBER - http://msdn.microsoft.com/en-us/library/ms186734.aspx

more ▼

answered Oct 16, 2012 at 12:52 PM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

Ah, forgot about ROW_NUMBER. I was just reading about that in a SQL Joes2Pros book.

Oct 16, 2012 at 12:54 PM Shawn_Melton
(comments are locked)
10|1200 characters needed characters left

If the number just needs to be auto generated you can look at setting the column's [IDENTITY][1] property. This would allow you to set the starting value and then the increment of that value for each new record.

CREATE TABLE myTable ( id int IDENTITY(1000,100) city varchar(50) )

INSERT myTable (city) VALUES ('Montgomery') VALUES ('Birmingham')

If you query the above table after inserting one record you should see an ID of 1000 and 1100, then the City set to 'Montgomery' and 'Birmingham'.

[1]: http://msdn.microsoft.com/en-us/library/ms186775.aspx

more ▼

answered Oct 16, 2012 at 12:53 PM

avatar image

6.4k 21 25 34

(comments are locked)
10|1200 characters needed characters left

Forgive me, I'm not sure that I fully understand the question. Do your tables have an identity column on it? Can you post the DDL for the tables?

Not sure if this applies, but you can use a function such as ROW_NUMBER which would allow you to generate a distinct list of row numbers based off of some partition or order by value.

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

Depending on the table structure, you could also use a unique non-clustered index or a primary key on the tables to help prevent duplication of values.

Just throwing it out there. Hope this helps!

more ▼

answered Oct 16, 2012 at 12:56 PM

avatar image

13.1k 3 7 14

(comments are locked)
10|1200 characters needed characters left

I don't remember the code exactly.... giving you only the idea

Create a table with only one column defined as identity column

Create a procedure that inserts a new record in that table

The procedure returns output of the inserted identity

Use that output as the input in the required table in a procedure

To keep the values unique, use APPLICATION LOCK on the procedure

I hope it will give you the basic idea .......

more ▼

answered Oct 16, 2012 at 02:10 PM

avatar image

Sacred Jewel
1.7k 3 7 10

But it will need one table per City ID and there can be n no of cities, i need to generate the unique Sequence for a given City ID. If i would use the single table it will leave the gap between the sequence of a given City ID.

Oct 16, 2012 at 03:49 PM vipin001
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 16, 2012 at 12:34 PM

Seen: 2979 times

Last Updated: Oct 16, 2012 at 03:49 PM

Copyright 2016 Redgate Software. Privacy Policy