[quoted text, click to view]
always in order, even after removing elements from the middle. <<

Yes, with a self-join that counts the number of keys less than the key
of the current row.

But can you tell us what that ever-changing number would mean in terms
of a LOGICAL DATA MODEL? What attribute is being modeled by it? This is
like identifying a car by its current parking space number rather than
its VIN.

I think you might be confusing a sequential file system with an RDBMS
and are imitating a record number, just like we did 40 yearrs ago when
we deleted records from mag tape files.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

Hi everyone,

Is there a way in SQL to define an Auto Increment column which is always in
order, even after removing elements from the middle.

For instance, currently if you define something as Auto Increment it will be
like
1,2,3,4,5,6,7,8,9,10 however, if you remove rows 4 and 9 then it will be
1,2,3,5,6,7,8,10

I want it to always remain as Ex. ID=1,2,3,4,5,6,7,8,9 and when you delete
row 4 I want to read 1,2,3,4,5,6,7,8

Also, What is the purpose of defining a #Temp table vs. regular table? And
also how do you define it in SQL Server Enterprise Manager?

Thank you
Maz.

[quoted text, click to view]

Do it when you SELECT data from the table. Here's an example from the Pubs
database:

SELECT
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS id
,*
FROM Authors AS A


[quoted text, click to view]

Temp tables are typically used to store intermediate results in a stored
procedure before returning data to the client. You create temp tables in
TSQL using a CREATE TABLE statement, which you can do from Query Analyzer. I
don't believe it's possible from the Enterprise Manager interface.


--
David Portas
SQL Server MVP
--

Maziar Aflatoun (maz00@rogers.com) writes:
[quoted text, click to view]

SQL Server MVP Fernando Guerrero had an article about this in SQL Server
Magazine a couple of years back. You can search the archives at
www.sqlmag.com, although you have to be a subscriber to access the article.

However, I would ask to think twice before you implement anything like
this. It will cost some machine power to maintain this, and it might
be only moderately useful.

You are probably better off numbering when you select as David Portas
suggested.

[quoted text, click to view]

A temp table goes away when the scope you created it in, goes away.
That is, if you create a temp table in a stored procedure the table
goes away when the procedure exists. The same happens if you create it
in a piece of dynamic SQL. If you submit a CREATE TABLE command directly
from the client, the table goes away when you disconnect.

Temp tables are mainly used to store intermediary results for data that
is not to be persisted.

[quoted text, click to view]

I have no idea if you can (I never create tables from EM), but it seems
like a pointless thing to do, for the reasons I described above.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

Hi,

Thank you for your quick response. What I'm trying to do it Custom Paging
in ASP.NET (C#) based on the following article (Check: Custom Paging with
Autoincrement Data Model).

http://authors.aspalliance.com/aspxtreme/sys/Web/UI/WebControls/DataGridClassAllowCustomPaging.aspx

However, this way assumes that the PlanNo is an autoincrement data model
without any of the rows ever being deleted which is not the usual case.

Any suggestions?

Thank you
Maz.



[quoted text, click to view]

Here are some techniques for paging dynamically without a physical sequence
column.

http://www.aspfaq.com/2120

--
David Portas
SQL Server MVP
--

[quoted text, click to view]

And dont forget to wish Aron Happy B'day.