PrimaryKey consisting of two PKs with autoincrement?

I want to create a table where I can make a history of a file, while it's
processed by my application. The file will change and even get a new file, so
I created a Document table and a Journal table. The Journal table contains
the time, the event that did occure and a FK to the Document table. One line
represents a specific document (one unique file).
I added two primary keys to this table, one for the transaction and one for
the document itself. So always when a file changes (gets a new file) a new
line will be inserted with a new document key, but the same transaction key.
When a completly new file gets processed a new transaction should start and
the document key will again begin by zero.
I just would like to new if it is possible to make the document key
autoincremental, so that I don't have to worry about it in the application. I
don't really think that the transaction key can be made autoincremental, as
the database does not know, if it is a completly new file or just a new
version of an older file.
But could there be any problems with making the document key
autoincremental? I just want it to be zero again, every time a new
transaction key starts, but I think the database will just count on.
Hi
Take a look at an INDETITY property that is a good choice for artificial
keys.
"Stampede" <Stampede@.discussions.microsoft.com> wrote in message
news:F835C787-5BC8-4835-A31D-D38C4EA581A1@.microsoft.com...
> I want to create a table where I can make a history of a file, while it's
> processed by my application. The file will change and even get a new file,
so
> I created a Document table and a Journal table. The Journal table contains
> the time, the event that did occure and a FK to the Document table. One
line
> represents a specific document (one unique file).
> I added two primary keys to this table, one for the transaction and one
for
> the document itself. So always when a file changes (gets a new file) a new
> line will be inserted with a new document key, but the same transaction
key.
> When a completly new file gets processed a new transaction should start
and
> the document key will again begin by zero.
> I just would like to new if it is possible to make the document key
> autoincremental, so that I don't have to worry about it in the
application. I
> don't really think that the transaction key can be made autoincremental,
as
> the database does not know, if it is a completly new file or just a new
> version of an older file.
> But could there be any problems with making the document key
> autoincremental? I just want it to be zero again, every time a new
> transaction key starts, but I think the database will just count on.

PrimaryKey consisting of two PKs with autoincrement?

I want to create a table where I can make a history of a file, while it's
processed by my application. The file will change and even get a new file, so
I created a Document table and a Journal table. The Journal table contains
the time, the event that did occure and a FK to the Document table. One line
represents a specific document (one unique file).
I added two primary keys to this table, one for the transaction and one for
the document itself. So always when a file changes (gets a new file) a new
line will be inserted with a new document key, but the same transaction key.
When a completly new file gets processed a new transaction should start and
the document key will again begin by zero.
I just would like to new if it is possible to make the document key
autoincremental, so that I don't have to worry about it in the application. I
don't really think that the transaction key can be made autoincremental, as
the database does not know, if it is a completly new file or just a new
version of an older file.
But could there be any problems with making the document key
autoincremental? I just want it to be zero again, every time a new
transaction key starts, but I think the database will just count on.Hi
Take a look at an INDETITY property that is a good choice for artificial
keys.
"Stampede" <Stampede@.discussions.microsoft.com> wrote in message
news:F835C787-5BC8-4835-A31D-D38C4EA581A1@.microsoft.com...
> I want to create a table where I can make a history of a file, while it's
> processed by my application. The file will change and even get a new file,
so
> I created a Document table and a Journal table. The Journal table contains
> the time, the event that did occure and a FK to the Document table. One
line
> represents a specific document (one unique file).
> I added two primary keys to this table, one for the transaction and one
for
> the document itself. So always when a file changes (gets a new file) a new
> line will be inserted with a new document key, but the same transaction
key.
> When a completly new file gets processed a new transaction should start
and
> the document key will again begin by zero.
> I just would like to new if it is possible to make the document key
> autoincremental, so that I don't have to worry about it in the
application. I
> don't really think that the transaction key can be made autoincremental,
as
> the database does not know, if it is a completly new file or just a new
> version of an older file.
> But could there be any problems with making the document key
> autoincremental? I just want it to be zero again, every time a new
> transaction key starts, but I think the database will just count on.

PrimaryKey consisting of two PKs with autoincrement?

I want to create a table where I can make a history of a file, while it's
processed by my application. The file will change and even get a new file, s
o
I created a Document table and a Journal table. The Journal table contains
the time, the event that did occure and a FK to the Document table. One line
represents a specific document (one unique file).
I added two primary keys to this table, one for the transaction and one for
the document itself. So always when a file changes (gets a new file) a new
line will be inserted with a new document key, but the same transaction key.
When a completly new file gets processed a new transaction should start and
the document key will again begin by zero.
I just would like to new if it is possible to make the document key
autoincremental, so that I don't have to worry about it in the application.
I
don't really think that the transaction key can be made autoincremental, as
the database does not know, if it is a completly new file or just a new
version of an older file.
But could there be any problems with making the document key
autoincremental? I just want it to be zero again, every time a new
transaction key starts, but I think the database will just count on.Hi
Take a look at an INDETITY property that is a good choice for artificial
keys.
"Stampede" <Stampede@.discussions.microsoft.com> wrote in message
news:F835C787-5BC8-4835-A31D-D38C4EA581A1@.microsoft.com...
> I want to create a table where I can make a history of a file, while it's
> processed by my application. The file will change and even get a new file,
so
> I created a Document table and a Journal table. The Journal table contains
> the time, the event that did occure and a FK to the Document table. One
line
> represents a specific document (one unique file).
> I added two primary keys to this table, one for the transaction and one
for
> the document itself. So always when a file changes (gets a new file) a new
> line will be inserted with a new document key, but the same transaction
key.
> When a completly new file gets processed a new transaction should start
and
> the document key will again begin by zero.
> I just would like to new if it is possible to make the document key
> autoincremental, so that I don't have to worry about it in the
application. I
> don't really think that the transaction key can be made autoincremental,
as
> the database does not know, if it is a completly new file or just a new
> version of an older file.
> But could there be any problems with making the document key
> autoincremental? I just want it to be zero again, every time a new
> transaction key starts, but I think the database will just count on.

Primary keys

my PKs are out of order. my application relies on the order of these keys.
How do i put back on order ex.
current: desired:
1 1
2 2
5 3
6 4
7 5
Thanks...
drop and recreate the column as PK.
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:A7B43C62-8E2A-4100-AD63-09E4B8DB5139@.microsoft.com...
> my PKs are out of order. my application relies on the order of these keys.
> How do i put back on order ex.
> current: desired:
> 1 1
> 2 2
> 5 3
> 6 4
> 7 5
> Thanks...
|||This is a common misunderstanding. A table, by definition, has no order.
You cannot rely on any physical ordering of rows; a clustered index is not a
guarantee. The only safe way to guarantee order in a query is to use an
order by clause.
Beware of small test data sets and simplistic queries; these will appear to
support your invalid assumption.
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:A7B43C62-8E2A-4100-AD63-09E4B8DB5139@.microsoft.com...
> my PKs are out of order. my application relies on the order of these keys.
> How do i put back on order ex.
> current: desired:
> 1 1
> 2 2
> 5 3
> 6 4
> 7 5
> Thanks...
|||Use ORDER BY when you query the table. A table has no pre-determined order.
All you have done wrong is to fail to specify the order you require.
David Portas
SQL Server MVP
|||"Scott Morris" <bogus@.bogus.com> wrote in message
news:u4def5AzEHA.2036@.TK2MSFTNGP12.phx.gbl...
> This is a common misunderstanding. A table, by definition, has no order.
> You cannot rely on any physical ordering of rows; a clustered index is not
a
> guarantee. The only safe way to guarantee order in a query is to use an
> order by clause.
I thnk what mmc actually means is they are out of sequence.
My guess is he's using an IDENTITY value.

> Beware of small test data sets and simplistic queries; these will appear
to[vbcol=seagreen]
> support your invalid assumption.
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:A7B43C62-8E2A-4100-AD63-09E4B8DB5139@.microsoft.com...
keys.
>
|||As per Greg's suggestion, it looks like you may have an IDENTITY column with
gaps in the sequence (not "out of order" at all). Don't rely on the IDENTITY
column being sequential without gaps. You can't prevent gaps from happening
with IDENTITY so you should change your code to work correctly with this in
mind.
I would also suggest that it's unwise to rely on the sequential nature of
IDENTITY at all. There are situations where this will cause you problems and
the best rule is to assume NOTHING about the IDENTITY sequence and write
your code accordingly. Don't even assume an IDENTITY column will be unique
unless you have a unique constraint on it. Adding a DATETIME column to the
table is a better way to track the order of insertion, if that is your goal.
David Portas
SQL Server MVP
|||Thank you, David.
"David Portas" wrote:

> As per Greg's suggestion, it looks like you may have an IDENTITY column with
> gaps in the sequence (not "out of order" at all). Don't rely on the IDENTITY
> column being sequential without gaps. You can't prevent gaps from happening
> with IDENTITY so you should change your code to work correctly with this in
> mind.
> I would also suggest that it's unwise to rely on the sequential nature of
> IDENTITY at all. There are situations where this will cause you problems and
> the best rule is to assume NOTHING about the IDENTITY sequence and write
> your code accordingly. Don't even assume an IDENTITY column will be unique
> unless you have a unique constraint on it. Adding a DATETIME column to the
> table is a better way to track the order of insertion, if that is your goal.
> --
> David Portas
> SQL Server MVP
> --
>
>

Primary keys

my PKs are out of order. my application relies on the order of these keys.
How do i put back on order ex.
current: desired:
1 1
2 2
5 3
6 4
7 5
Thanks...drop and recreate the column as PK.
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:A7B43C62-8E2A-4100-AD63-09E4B8DB5139@.microsoft.com...
> my PKs are out of order. my application relies on the order of these keys.
> How do i put back on order ex.
> current: desired:
> 1 1
> 2 2
> 5 3
> 6 4
> 7 5
> Thanks...|||This is a common misunderstanding. A table, by definition, has no order.
You cannot rely on any physical ordering of rows; a clustered index is not a
guarantee. The only safe way to guarantee order in a query is to use an
order by clause.
Beware of small test data sets and simplistic queries; these will appear to
support your invalid assumption.
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:A7B43C62-8E2A-4100-AD63-09E4B8DB5139@.microsoft.com...
> my PKs are out of order. my application relies on the order of these keys.
> How do i put back on order ex.
> current: desired:
> 1 1
> 2 2
> 5 3
> 6 4
> 7 5
> Thanks...|||Use ORDER BY when you query the table. A table has no pre-determined order.
All you have done wrong is to fail to specify the order you require.
--
David Portas
SQL Server MVP
--|||"Scott Morris" <bogus@.bogus.com> wrote in message
news:u4def5AzEHA.2036@.TK2MSFTNGP12.phx.gbl...
> This is a common misunderstanding. A table, by definition, has no order.
> You cannot rely on any physical ordering of rows; a clustered index is not
a
> guarantee. The only safe way to guarantee order in a query is to use an
> order by clause.
I thnk what mmc actually means is they are out of sequence.
My guess is he's using an IDENTITY value.
> Beware of small test data sets and simplistic queries; these will appear
to
> support your invalid assumption.
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:A7B43C62-8E2A-4100-AD63-09E4B8DB5139@.microsoft.com...
> > my PKs are out of order. my application relies on the order of these
keys.
> > How do i put back on order ex.
> > current: desired:
> > 1 1
> > 2 2
> > 5 3
> > 6 4
> > 7 5
> > Thanks...
>|||As per Greg's suggestion, it looks like you may have an IDENTITY column with
gaps in the sequence (not "out of order" at all). Don't rely on the IDENTITY
column being sequential without gaps. You can't prevent gaps from happening
with IDENTITY so you should change your code to work correctly with this in
mind.
I would also suggest that it's unwise to rely on the sequential nature of
IDENTITY at all. There are situations where this will cause you problems and
the best rule is to assume NOTHING about the IDENTITY sequence and write
your code accordingly. Don't even assume an IDENTITY column will be unique
unless you have a unique constraint on it. Adding a DATETIME column to the
table is a better way to track the order of insertion, if that is your goal.
--
David Portas
SQL Server MVP
--|||Thank you, David.
"David Portas" wrote:
> As per Greg's suggestion, it looks like you may have an IDENTITY column with
> gaps in the sequence (not "out of order" at all). Don't rely on the IDENTITY
> column being sequential without gaps. You can't prevent gaps from happening
> with IDENTITY so you should change your code to work correctly with this in
> mind.
> I would also suggest that it's unwise to rely on the sequential nature of
> IDENTITY at all. There are situations where this will cause you problems and
> the best rule is to assume NOTHING about the IDENTITY sequence and write
> your code accordingly. Don't even assume an IDENTITY column will be unique
> unless you have a unique constraint on it. Adding a DATETIME column to the
> table is a better way to track the order of insertion, if that is your goal.
> --
> David Portas
> SQL Server MVP
> --
>
>

Primary keys

my PKs are out of order. my application relies on the order of these keys.
How do i put back on order ex.
current: desired:
1 1
2 2
5 3
6 4
7 5
Thanks...drop and recreate the column as PK.
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:A7B43C62-8E2A-4100-AD63-09E4B8DB5139@.microsoft.com...
> my PKs are out of order. my application relies on the order of these keys.
> How do i put back on order ex.
> current: desired:
> 1 1
> 2 2
> 5 3
> 6 4
> 7 5
> Thanks...|||This is a common misunderstanding. A table, by definition, has no order.
You cannot rely on any physical ordering of rows; a clustered index is not a
guarantee. The only safe way to guarantee order in a query is to use an
order by clause.
Beware of small test data sets and simplistic queries; these will appear to
support your invalid assumption.
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:A7B43C62-8E2A-4100-AD63-09E4B8DB5139@.microsoft.com...
> my PKs are out of order. my application relies on the order of these keys.
> How do i put back on order ex.
> current: desired:
> 1 1
> 2 2
> 5 3
> 6 4
> 7 5
> Thanks...|||Use ORDER BY when you query the table. A table has no pre-determined order.
All you have done wrong is to fail to specify the order you require.
David Portas
SQL Server MVP
--|||"Scott Morris" <bogus@.bogus.com> wrote in message
news:u4def5AzEHA.2036@.TK2MSFTNGP12.phx.gbl...
> This is a common misunderstanding. A table, by definition, has no order.
> You cannot rely on any physical ordering of rows; a clustered index is not
a
> guarantee. The only safe way to guarantee order in a query is to use an
> order by clause.
I thnk what mmc actually means is they are out of sequence.
My guess is he's using an IDENTITY value.

> Beware of small test data sets and simplistic queries; these will appear
to
> support your invalid assumption.
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:A7B43C62-8E2A-4100-AD63-09E4B8DB5139@.microsoft.com...
keys.[vbcol=seagreen]
>|||As per Greg's suggestion, it looks like you may have an IDENTITY column with
gaps in the sequence (not "out of order" at all). Don't rely on the IDENTITY
column being sequential without gaps. You can't prevent gaps from happening
with IDENTITY so you should change your code to work correctly with this in
mind.
I would also suggest that it's unwise to rely on the sequential nature of
IDENTITY at all. There are situations where this will cause you problems and
the best rule is to assume NOTHING about the IDENTITY sequence and write
your code accordingly. Don't even assume an IDENTITY column will be unique
unless you have a unique constraint on it. Adding a DATETIME column to the
table is a better way to track the order of insertion, if that is your goal.
David Portas
SQL Server MVP
--|||Thank you, David.
"David Portas" wrote:

> As per Greg's suggestion, it looks like you may have an IDENTITY column wi
th
> gaps in the sequence (not "out of order" at all). Don't rely on the IDENTI
TY
> column being sequential without gaps. You can't prevent gaps from happenin
g
> with IDENTITY so you should change your code to work correctly with this i
n
> mind.
> I would also suggest that it's unwise to rely on the sequential nature of
> IDENTITY at all. There are situations where this will cause you problems a
nd
> the best rule is to assume NOTHING about the IDENTITY sequence and write
> your code accordingly. Don't even assume an IDENTITY column will be unique
> unless you have a unique constraint on it. Adding a DATETIME column to the
> table is a better way to track the order of insertion, if that is your goa
l.
> --
> David Portas
> SQL Server MVP
> --
>
>