hi all

i wants to generate linear sequence number  like 1,2,3,.............1000000

,are there any function like  NEWID() ( this return unique guide, i want to get integer)

 

thanks 

IndikaD 

Hi Indika,

If you are inserting into a new table using SELECT.. INTO, you can think of using IDENTITY function as part of select statement.

the syntax for it is like this:

SELECT IDENTITY(int, 1,1) AS ID_Num
INTO NewTable
FROM OldTable

I am thinking whether you want to generate a sequence number at database level. If that is what you want,  there is no direct method for it. But you can have a workaround.  Keep a table with identity column and insert each time you want to generate it.

CREATE TABLE dbo.Sequence (SequenceID int IDENTITY(1,1) )

GO

INSERT INTO dbo.Sequence Default Values

SELECT SCOPE_IDENTITY()

You a have a lot of problems with this approach. Firstly you can't use this routine like a function. (you can think of ROW_NUMBER() Ranking function for it, but in a multi user environment you may face duplicates on that approach as well).  You you want to use this against a heavily used environment,  you may see the table as a bottleneck. As this table grows it may eat soem space of your database.  You can  have a job to remove old data periodically.

 

 

 

 

 

---

G.R.Preethiviraj Kulasingham MCITP: DBA
http://preethiviraj.blogspot.com/
Plan Your Work and Work Your Plan!