Hi,
Is it possible to have SQL server automatically generate a sequence number
based on another column, both forming the table's primary key. So another
kind of auto-increment field.
What is mean is something like this:
Code Seq Name ...
A100 1 a
A100 2 b
A100 3 c
G432 1 x
G432 2 y
H008 1 p
H008 2 q
H008 3 r
...
Thanks a lot for your help.
Edgar
Hi
In SQL 2005 when retrieving data you can use the ROWNUMBER function see
http://msdn2.microsoft.com/en-us/library/ms189798.aspx, but you could not
store them. You could use a subquery when inserting the records
e.g.
CREATE TABLE mytable ( [Code] CHAR(4) NOT NULL, [Seq] INT NOT NULL, [Name]
CHAR(1) )
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'A100', 1, 'a'
UNION ALL SELECT 'A100', 2, 'b'
UNION ALL SELECT 'A100', 3, 'c'
UNION ALL SELECT 'G432', 1, 'x'
UNION ALL SELECT 'G432', 2, 'y'
UNION ALL SELECT 'H008', 1, 'p'
UNION ALL SELECT 'H008', 2, 'q'
SELECT * FROM MyTable
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'H008', ISNULL( ( SELECT COUNT(*)+1 FROM mytable WHERE [Code] =
'H008'),0), 'r'
SELECT * FROM MyTable
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'H010', ISNULL( ( SELECT COUNT(*)+1 FROM mytable WHERE [Code] =
'H010'),0), 'g'
SELECT * FROM MyTable
This could be incorporated into an INSTEAD OF TRIGGER
John
"Edgar" wrote:
> Hi,
> Is it possible to have SQL server automatically generate a sequence number
> based on another column, both forming the table's primary key. So another
> kind of auto-increment field.
> What is mean is something like this:
> Code Seq Name ...
> A100 1 a
> A100 2 b
> A100 3 c
> G432 1 x
> G432 2 y
> H008 1 p
> H008 2 q
> H008 3 r
> ...
>
> Thanks a lot for your help.
> Edgar
|||Why does this data need to be stored, when you could always retrieve Seq at
query time?
The problem with storing it in the table is that now it has to be
maintained. DELETE table WHERE Code = 'A100' AND Name = 'a' and now you are
mising Seq=1 for that combination. If A100 has 80,000 rows and you need to
decrease all of their Seq values by 1, that becomes a very, very, very
expensive delete operation.
A
"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:8178F22E-B631-413B-8BF1-32CB0E6B06B3@.microsoft.com...
> Hi,
> Is it possible to have SQL server automatically generate a sequence number
> based on another column, both forming the table's primary key. So another
> kind of auto-increment field.
> What is mean is something like this:
> Code Seq Name ...
> A100 1 a
> A100 2 b
> A100 3 c
> G432 1 x
> G432 2 y
> H008 1 p
> H008 2 q
> H008 3 r
> ...
>
> Thanks a lot for your help.
> Edgar
|||I need the sequence numbers, because they indicate the order of the records
related to their parent record.
Thanks,
Edgar
"Aaron Bertrand [SQL Server MVP]" wrote:
> Why does this data need to be stored, when you could always retrieve Seq at
> query time?
> The problem with storing it in the table is that now it has to be
> maintained. DELETE table WHERE Code = 'A100' AND Name = 'a' and now you are
> mising Seq=1 for that combination. If A100 has 80,000 rows and you need to
> decrease all of their Seq values by 1, that becomes a very, very, very
> expensive delete operation.
> A
>
> "Edgar" <Edgar@.discussions.microsoft.com> wrote in message
> news:8178F22E-B631-413B-8BF1-32CB0E6B06B3@.microsoft.com...
>
>
|||"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:ADBF8CBC-78F0-4E89-AF0A-318BC806A56B@.microsoft.com...
>I need the sequence numbers, because they indicate the order of the records
> related to their parent record.
>
> --
Ok, that's legit. You can just use an IDENTITY column for the sequence
numbers. They won't be sequential, and they won't start over for each
parent, but they will give you the relative ordering
EG
Code Seq Name ...
A100 1132 a
A100 1314 b
A100 5991 c
G432 7202 x
G432 82929 y
H008 1002 p
H008 89231 q
H008 999231 r
David
|||David,
Thanks for your help. Good suggestion.
I will create the normal 1, 2, 3 when i retrieve the data.
(But it would be a nice addition to the product :-))
Thanks,
Edgar
"David Browne" wrote:
>
> "Edgar" <Edgar@.discussions.microsoft.com> wrote in message
> news:ADBF8CBC-78F0-4E89-AF0A-318BC806A56B@.microsoft.com...
>
> Ok, that's legit. You can just use an IDENTITY column for the sequence
> numbers. They won't be sequential, and they won't start over for each
> parent, but they will give you the relative ordering
> EG
> Code Seq Name ...
> A100 1132 a
> A100 1314 b
> A100 5991 c
> G432 7202 x
> G432 82929 y
> H008 1002 p
> H008 89231 q
> H008 999231 r
> David
>
|||Hi Edgar
If you do that then the sequencing may not reflect the true order in which
they were inserted, for example if an entry is deleted subsequent entries
will be moved up. If you are ok with this then using the identity is ok, you
may also want to only allocate the sequence number on the client which would
save you doing the subquery.
John
"Edgar" wrote:
[vbcol=seagreen]
> David,
> Thanks for your help. Good suggestion.
> I will create the normal 1, 2, 3 when i retrieve the data.
> (But it would be a nice addition to the product :-))
> Thanks,
> Edgar
>
> "David Browne" wrote:
sql
Showing posts with label key. Show all posts
Showing posts with label key. Show all posts
Tuesday, March 20, 2012
automatic sequence number by id
Hi,
Is it possible to have SQL server automatically generate a sequence number
based on another column, both forming the table's primary key. So another
kind of auto-increment field.
What is mean is something like this:
Code Seq Name ...
A100 1 a
A100 2 b
A100 3 c
G432 1 x
G432 2 y
H008 1 p
H008 2 q
H008 3 r
...
Thanks a lot for your help.
EdgarHi
In SQL 2005 when retrieving data you can use the ROWNUMBER function see
http://msdn2.microsoft.com/en-us/library/ms189798.aspx, but you could not
store them. You could use a subquery when inserting the records
e.g.
CREATE TABLE mytable ( [Code] CHAR(4) NOT NULL, [Seq] INT NOT NULL,
[Name]
CHAR(1) )
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'A100', 1, 'a'
UNION ALL SELECT 'A100', 2, 'b'
UNION ALL SELECT 'A100', 3, 'c'
UNION ALL SELECT 'G432', 1, 'x'
UNION ALL SELECT 'G432', 2, 'y'
UNION ALL SELECT 'H008', 1, 'p'
UNION ALL SELECT 'H008', 2, 'q'
SELECT * FROM MyTable
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'H008', ISNULL( ( SELECT COUNT(*)+1 FROM mytable WHERE [Code]
=
'H008'),0), 'r'
SELECT * FROM MyTable
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'H010', ISNULL( ( SELECT COUNT(*)+1 FROM mytable WHERE [Code]
=
'H010'),0), 'g'
SELECT * FROM MyTable
This could be incorporated into an INSTEAD OF TRIGGER
John
"Edgar" wrote:
> Hi,
> Is it possible to have SQL server automatically generate a sequence number
> based on another column, both forming the table's primary key. So another
> kind of auto-increment field.
> What is mean is something like this:
> Code Seq Name ...
> A100 1 a
> A100 2 b
> A100 3 c
> G432 1 x
> G432 2 y
> H008 1 p
> H008 2 q
> H008 3 r
> ...
>
> Thanks a lot for your help.
> Edgar|||Why does this data need to be stored, when you could always retrieve Seq at
query time?
The problem with storing it in the table is that now it has to be
maintained. DELETE table WHERE Code = 'A100' AND Name = 'a' and now you are
mising Seq=1 for that combination. If A100 has 80,000 rows and you need to
decrease all of their Seq values by 1, that becomes a very, very, very
expensive delete operation.
A
"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:8178F22E-B631-413B-8BF1-32CB0E6B06B3@.microsoft.com...
> Hi,
> Is it possible to have SQL server automatically generate a sequence number
> based on another column, both forming the table's primary key. So another
> kind of auto-increment field.
> What is mean is something like this:
> Code Seq Name ...
> A100 1 a
> A100 2 b
> A100 3 c
> G432 1 x
> G432 2 y
> H008 1 p
> H008 2 q
> H008 3 r
> ...
>
> Thanks a lot for your help.
> Edgar|||I need the sequence numbers, because they indicate the order of the records
related to their parent record.
Thanks,
Edgar
"Aaron Bertrand [SQL Server MVP]" wrote:
> Why does this data need to be stored, when you could always retrieve Seq a
t
> query time?
> The problem with storing it in the table is that now it has to be
> maintained. DELETE table WHERE Code = 'A100' AND Name = 'a' and now you a
re
> mising Seq=1 for that combination. If A100 has 80,000 rows and you need t
o
> decrease all of their Seq values by 1, that becomes a very, very, very
> expensive delete operation.
> A
>
> "Edgar" <Edgar@.discussions.microsoft.com> wrote in message
> news:8178F22E-B631-413B-8BF1-32CB0E6B06B3@.microsoft.com...
>
>|||"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:ADBF8CBC-78F0-4E89-AF0A-318BC806A56B@.microsoft.com...
>I need the sequence numbers, because they indicate the order of the records
> related to their parent record.
>
> --
Ok, that's legit. You can just use an IDENTITY column for the sequence
numbers. They won't be sequential, and they won't start over for each
parent, but they will give you the relative ordering
EG
Code Seq Name ...
A100 1132 a
A100 1314 b
A100 5991 c
G432 7202 x
G432 82929 y
H008 1002 p
H008 89231 q
H008 999231 r
David|||David,
Thanks for your help. Good suggestion.
I will create the normal 1, 2, 3 when i retrieve the data.
(But it would be a nice addition to the product :-))
Thanks,
Edgar
"David Browne" wrote:
>
> "Edgar" <Edgar@.discussions.microsoft.com> wrote in message
> news:ADBF8CBC-78F0-4E89-AF0A-318BC806A56B@.microsoft.com...
>
> Ok, that's legit. You can just use an IDENTITY column for the sequence
> numbers. They won't be sequential, and they won't start over for each
> parent, but they will give you the relative ordering
> EG
> Code Seq Name ...
> A100 1132 a
> A100 1314 b
> A100 5991 c
> G432 7202 x
> G432 82929 y
> H008 1002 p
> H008 89231 q
> H008 999231 r
> David
>|||Hi Edgar
If you do that then the sequencing may not reflect the true order in which
they were inserted, for example if an entry is deleted subsequent entries
will be moved up. If you are ok with this then using the identity is ok, you
may also want to only allocate the sequence number on the client which would
save you doing the subquery.
John
"Edgar" wrote:
[vbcol=seagreen]
> David,
> Thanks for your help. Good suggestion.
> I will create the normal 1, 2, 3 when i retrieve the data.
> (But it would be a nice addition to the product :-))
> Thanks,
> Edgar
>
> "David Browne" wrote:
>
Is it possible to have SQL server automatically generate a sequence number
based on another column, both forming the table's primary key. So another
kind of auto-increment field.
What is mean is something like this:
Code Seq Name ...
A100 1 a
A100 2 b
A100 3 c
G432 1 x
G432 2 y
H008 1 p
H008 2 q
H008 3 r
...
Thanks a lot for your help.
EdgarHi
In SQL 2005 when retrieving data you can use the ROWNUMBER function see
http://msdn2.microsoft.com/en-us/library/ms189798.aspx, but you could not
store them. You could use a subquery when inserting the records
e.g.
CREATE TABLE mytable ( [Code] CHAR(4) NOT NULL, [Seq] INT NOT NULL,
[Name]
CHAR(1) )
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'A100', 1, 'a'
UNION ALL SELECT 'A100', 2, 'b'
UNION ALL SELECT 'A100', 3, 'c'
UNION ALL SELECT 'G432', 1, 'x'
UNION ALL SELECT 'G432', 2, 'y'
UNION ALL SELECT 'H008', 1, 'p'
UNION ALL SELECT 'H008', 2, 'q'
SELECT * FROM MyTable
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'H008', ISNULL( ( SELECT COUNT(*)+1 FROM mytable WHERE [Code]
=
'H008'),0), 'r'
SELECT * FROM MyTable
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'H010', ISNULL( ( SELECT COUNT(*)+1 FROM mytable WHERE [Code]
=
'H010'),0), 'g'
SELECT * FROM MyTable
This could be incorporated into an INSTEAD OF TRIGGER
John
"Edgar" wrote:
> Hi,
> Is it possible to have SQL server automatically generate a sequence number
> based on another column, both forming the table's primary key. So another
> kind of auto-increment field.
> What is mean is something like this:
> Code Seq Name ...
> A100 1 a
> A100 2 b
> A100 3 c
> G432 1 x
> G432 2 y
> H008 1 p
> H008 2 q
> H008 3 r
> ...
>
> Thanks a lot for your help.
> Edgar|||Why does this data need to be stored, when you could always retrieve Seq at
query time?
The problem with storing it in the table is that now it has to be
maintained. DELETE table WHERE Code = 'A100' AND Name = 'a' and now you are
mising Seq=1 for that combination. If A100 has 80,000 rows and you need to
decrease all of their Seq values by 1, that becomes a very, very, very
expensive delete operation.
A
"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:8178F22E-B631-413B-8BF1-32CB0E6B06B3@.microsoft.com...
> Hi,
> Is it possible to have SQL server automatically generate a sequence number
> based on another column, both forming the table's primary key. So another
> kind of auto-increment field.
> What is mean is something like this:
> Code Seq Name ...
> A100 1 a
> A100 2 b
> A100 3 c
> G432 1 x
> G432 2 y
> H008 1 p
> H008 2 q
> H008 3 r
> ...
>
> Thanks a lot for your help.
> Edgar|||I need the sequence numbers, because they indicate the order of the records
related to their parent record.
Thanks,
Edgar
"Aaron Bertrand [SQL Server MVP]" wrote:
> Why does this data need to be stored, when you could always retrieve Seq a
t
> query time?
> The problem with storing it in the table is that now it has to be
> maintained. DELETE table WHERE Code = 'A100' AND Name = 'a' and now you a
re
> mising Seq=1 for that combination. If A100 has 80,000 rows and you need t
o
> decrease all of their Seq values by 1, that becomes a very, very, very
> expensive delete operation.
> A
>
> "Edgar" <Edgar@.discussions.microsoft.com> wrote in message
> news:8178F22E-B631-413B-8BF1-32CB0E6B06B3@.microsoft.com...
>
>|||"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:ADBF8CBC-78F0-4E89-AF0A-318BC806A56B@.microsoft.com...
>I need the sequence numbers, because they indicate the order of the records
> related to their parent record.
>
> --
Ok, that's legit. You can just use an IDENTITY column for the sequence
numbers. They won't be sequential, and they won't start over for each
parent, but they will give you the relative ordering
EG
Code Seq Name ...
A100 1132 a
A100 1314 b
A100 5991 c
G432 7202 x
G432 82929 y
H008 1002 p
H008 89231 q
H008 999231 r
David|||David,
Thanks for your help. Good suggestion.
I will create the normal 1, 2, 3 when i retrieve the data.
(But it would be a nice addition to the product :-))
Thanks,
Edgar
"David Browne" wrote:
>
> "Edgar" <Edgar@.discussions.microsoft.com> wrote in message
> news:ADBF8CBC-78F0-4E89-AF0A-318BC806A56B@.microsoft.com...
>
> Ok, that's legit. You can just use an IDENTITY column for the sequence
> numbers. They won't be sequential, and they won't start over for each
> parent, but they will give you the relative ordering
> EG
> Code Seq Name ...
> A100 1132 a
> A100 1314 b
> A100 5991 c
> G432 7202 x
> G432 82929 y
> H008 1002 p
> H008 89231 q
> H008 999231 r
> David
>|||Hi Edgar
If you do that then the sequencing may not reflect the true order in which
they were inserted, for example if an entry is deleted subsequent entries
will be moved up. If you are ok with this then using the identity is ok, you
may also want to only allocate the sequence number on the client which would
save you doing the subquery.
John
"Edgar" wrote:
[vbcol=seagreen]
> David,
> Thanks for your help. Good suggestion.
> I will create the normal 1, 2, 3 when i retrieve the data.
> (But it would be a nice addition to the product :-))
> Thanks,
> Edgar
>
> "David Browne" wrote:
>
Automatic refresh
If there a way to have reports automatically refesh in in print preview mode - without hitting the refresh key or cmd5 key on the toolbar. CR VER 8.5 or 9.0Is there a way to automatically refresh a screen in the preview mode (without hitting cmd 5 or refresh key on the toolbar)
Saturday, February 25, 2012
Auto-Increment Primary key Sqlce Problem
Hello,
I am using Remote data access, passing a copy of one database on SQL Server 2055 to another database SqlCe Mobile server.
I've got 4 entries on one table on SQL Server 2005, then i use RDA and i have now that 4 entries on my pda database.
The problem is that when i want to insert another entry on that table the Id autoincrement starts from the beginning (from 1).
Example:
Table "Colmos" on First State after the copy using RDA:
ColmoID Zona
2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1
Then i try to make an insert with de pda database to the "Colmo" table and i do it successful at first.
I get:
ColmoID Zona
2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1
1 Zona 1 (note that the increment counter start again from 1)
When i want to do another insert i get this error:
A duplicate value cannot be inserted into a unique index. [ Table name = Colmo,Constraint name = PK__Colmo__00000000000000F3 ]
The problem is that the next id that the sqlce want to insert is number 2, and that id already exists than i got that error.
The code that i am using is:
Dim sql As String = "INSERT INTO Colmo(Zona) VALUES('Zona 1')"
Dim c As SqlCeCommand = New SqlCeCommand(sql, connection)
connection.Open()
c.ExecuteNonQuery()
connection.Close()
If anyone could help me..
Thanks!
Hello,
I am using Remote data access, passing a copy of one database on SQL Server 2055 to another database SqlCe Mobile server.
I've got 4 entries on one table on SQL Server 2005, then i use RDA and i have now that 4 entries on my pda database.
The problem is that when i want to insert another entry on that table the Id autoincrement starts from the beginning (from 1).
Example:
Table "Colmos" on First State after the copy using RDA:
ColmoID Zona
2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1
Then i try to make an insert with de pda database to the "Colmo" table and i do it successful at first.
I get:
ColmoID Zona
2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1
1 Zona 1 (note that the increment counter start again from 1)
When i want to do another insert i get this error:
A duplicate value cannot be inserted into a unique index. [ Table name = Colmo,Constraint name = PK__Colmo__00000000000000F3 ]
The problem is that the next id that the sqlce want to insert is number 2, and that id already exists than i got that error.
The code that i am using is:
Dim sql As String = "INSERT INTO Colmo(Zona) VALUES('Zona 1')"
Dim c As SqlCeCommand = New SqlCeCommand(sql, connection)
connection.Open()
c.ExecuteNonQuery()
connection.Close()
If anyone could help me..
Thanks!
|||Hello,
I am using Remote data access, passing a copy of one database on SQL Server 2055 to another database SqlCe Mobile server.
I've got 4 entries on one table on SQL Server 2005, then i use RDA and i have now that 4 entries on my pda database.
The problem is that when i want to insert another entry on that table the Id autoincrement starts from the beginning (from 1).
Example:
Table "Colmos" on First State after the copy using RDA:
ColmoID Zona
2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1
Then i try to make an insert with de pda database to the "Colmo" table and i do it successful at first.
I get:
ColmoID Zona
2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1
1 Zona 1 (note that the increment counter start again from 1)
When i want to do another insert i get this error:
A duplicate value cannot be inserted into a unique index. [ Table name = Colmo,Constraint name = PK__Colmo__00000000000000F3 ]
The problem is that the next id that the sqlce want to insert is number 2, and that id already exists than i got that error.
The code that i am using is:
Dim sql As String = "INSERT INTO Colmo(Zona) VALUES('Zona 1')"
Dim c As SqlCeCommand = New SqlCeCommand(sql, connection)
connection.Open()
c.ExecuteNonQuery()
connection.Close()
If anyone could help me..
Thanks!
AutoIncrement Primary Key
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- GabeYou could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>
access but for SQL 2000? Thanks,
- GabeYou could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>
AutoIncrement Primary Key
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
You could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>
access but for SQL 2000? Thanks,
- Gabe
You could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>
AutoIncrement Primary Key
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
You could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>
access but for SQL 2000? Thanks,
- Gabe
You could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>
AutoIncrement Primary Key
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- GabeYou could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>
access but for SQL 2000? Thanks,
- GabeYou could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>
Auto-Increment of varchar primary key
Hi All
I am looking for a bit of advice.
I am in the process of creating a database in which it has been decided that
all primary keys are going to varchar(40). Not my decision, but anyway.
When inserting into each table it will be possible to specify a value for
the primary, but if not specified a value should be auto-generated. That
means that the values in the primary key field can be a mixture of both
numbers and letters, but if auto-generated it should just be a number.
What be the best way to make this autogenerated values if no value is being
specified in the insert?
TIA
KlausDepends really, if auto-generated does it just need to be a number? Any old
number, or a specific format and range?
I always recommend putting a surrogate key on the tables and use that as the
foriegn key and inside the application (not for display purposes, but for
use as the value in a listbox for instance), that can be a int column with
the IDENTITY property, not null and have a unique constraint on it.
You could set the value of the primary key to that if not specified, that
would save calculating a new unique number.
Otherwise, you could use an 'instead of' trigger, for example...
Instead of using MAX, you could take the value from a table that holds the
last number used.
create table testtrg (
mycol int not null unique
)
go
insert testtrg ( mycol ) values ( 1 )
go
create trigger trgTestTrg on testtrg instead of insert
as
begin
if @.@.rowcount = 0
return
declare @.nextid int
begin tran
set @.nextid = ( select max( mycol )
from testtrg with (tablockx) )
set @.nextid = isnull( @.nextid, 0 ) + 1
insert testtrg values( @.nextid )
commit tran
end
go
-- Note, inserting 1 but it already exists so should give a key violation,
-- but the instead of trigger code kicks in and gives the next id.
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
go
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Klaus" <Klaus@.discussions.microsoft.com> wrote in message
news:FE28E558-F88F-4A9F-9AAA-40837A9966E9@.microsoft.com...
> Hi All
> I am looking for a bit of advice.
> I am in the process of creating a database in which it has been decided
> that
> all primary keys are going to varchar(40). Not my decision, but anyway.
> When inserting into each table it will be possible to specify a value for
> the primary, but if not specified a value should be auto-generated. That
> means that the values in the primary key field can be a mixture of both
> numbers and letters, but if auto-generated it should just be a number.
> What be the best way to make this autogenerated values if no value is
> being
> specified in the insert?
> TIA
> Klaus
>|||Thanks a lot, Tony. That was very helpfull.
I will create a unique field on each of my tables. The value for this will
be auto-generated using identity. A trigger will then keep an eye on the
inserts. If no value is being specified for the Primary key, the Identity
value will be copied into the varchar(40) primary key field.
-- Klaus
"Tony Rogerson" wrote:
> Depends really, if auto-generated does it just need to be a number? Any ol
d
> number, or a specific format and range?
> I always recommend putting a surrogate key on the tables and use that as t
he
> foriegn key and inside the application (not for display purposes, but for
> use as the value in a listbox for instance), that can be a int column with
> the IDENTITY property, not null and have a unique constraint on it.
> You could set the value of the primary key to that if not specified, that
> would save calculating a new unique number.
> Otherwise, you could use an 'instead of' trigger, for example...
> Instead of using MAX, you could take the value from a table that holds the
> last number used.
> create table testtrg (
> mycol int not null unique
> )
> go
>
> insert testtrg ( mycol ) values ( 1 )
> go
>
> create trigger trgTestTrg on testtrg instead of insert
> as
> begin
> if @.@.rowcount = 0
> return
>
> declare @.nextid int
>
> begin tran
>
> set @.nextid = ( select max( mycol )
> from testtrg with (tablockx) )
>
> set @.nextid = isnull( @.nextid, 0 ) + 1
>
> insert testtrg values( @.nextid )
>
> commit tran
>
> end
> go
>
> -- Note, inserting 1 but it already exists so should give a key violation
,
> -- but the instead of trigger code kicks in and gives the next id.
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> go
>
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Klaus" <Klaus@.discussions.microsoft.com> wrote in message
> news:FE28E558-F88F-4A9F-9AAA-40837A9966E9@.microsoft.com...
>
>|||The second part of Tony's point should not be lost. You should use that int
key as the FK for relationships with other tables. If you need to show your
client the benefit of using an int instead of a varchar(40). Load up a
couple of tables with some test data. Perform join's using varchar(40) as
the keys and then the same using int as the key. The performance difference
is noticable.
So use an int (or even bigint) PK, put a unique constraint on the
varchar(40) column and for all business logic purposes, the varchar(40) fiel
d
is the "key". But behind the scenes in the database the far more efficient
int is the key.
John Scragg
"Klaus" wrote:
> Thanks a lot, Tony. That was very helpfull.
> I will create a unique field on each of my tables. The value for this will
> be auto-generated using identity. A trigger will then keep an eye on the
> inserts. If no value is being specified for the Primary key, the Identity
> value will be copied into the varchar(40) primary key field.
> -- Klaus
> "Tony Rogerson" wrote:
>|||Also, if you need the data in a varchar(40) field you can use a calculated
column (if they dont need to enter it).
I concurr with Tony & John, if you're doing joins, definately use the INT
field as the joining field, joining on varchar fields gets very slow at
medium to high data volumes.
create table ( id int identity(1,1) primary key , myPK AS cast( ID as
varchar(40)) )
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:EDB86798-0F96-415A-9D8D-733ED2E0CA02@.microsoft.com...
> The second part of Tony's point should not be lost. You should use that
int
> key as the FK for relationships with other tables. If you need to show
your
> client the benefit of using an int instead of a varchar(40). Load up a
> couple of tables with some test data. Perform join's using varchar(40) as
> the keys and then the same using int as the key. The performance
difference
> is noticable.
> So use an int (or even bigint) PK, put a unique constraint on the
> varchar(40) column and for all business logic purposes, the varchar(40)
field
> is the "key". But behind the scenes in the database the far more
efficient
> int is the key.
> John Scragg
> "Klaus" wrote:
>
will
Identity
Any old
as the
for
with
that
the
violation,
decided
anyway.
value for
That
both
number.
is
I am looking for a bit of advice.
I am in the process of creating a database in which it has been decided that
all primary keys are going to varchar(40). Not my decision, but anyway.
When inserting into each table it will be possible to specify a value for
the primary, but if not specified a value should be auto-generated. That
means that the values in the primary key field can be a mixture of both
numbers and letters, but if auto-generated it should just be a number.
What be the best way to make this autogenerated values if no value is being
specified in the insert?
TIA
KlausDepends really, if auto-generated does it just need to be a number? Any old
number, or a specific format and range?
I always recommend putting a surrogate key on the tables and use that as the
foriegn key and inside the application (not for display purposes, but for
use as the value in a listbox for instance), that can be a int column with
the IDENTITY property, not null and have a unique constraint on it.
You could set the value of the primary key to that if not specified, that
would save calculating a new unique number.
Otherwise, you could use an 'instead of' trigger, for example...
Instead of using MAX, you could take the value from a table that holds the
last number used.
create table testtrg (
mycol int not null unique
)
go
insert testtrg ( mycol ) values ( 1 )
go
create trigger trgTestTrg on testtrg instead of insert
as
begin
if @.@.rowcount = 0
return
declare @.nextid int
begin tran
set @.nextid = ( select max( mycol )
from testtrg with (tablockx) )
set @.nextid = isnull( @.nextid, 0 ) + 1
insert testtrg values( @.nextid )
commit tran
end
go
-- Note, inserting 1 but it already exists so should give a key violation,
-- but the instead of trigger code kicks in and gives the next id.
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
go
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Klaus" <Klaus@.discussions.microsoft.com> wrote in message
news:FE28E558-F88F-4A9F-9AAA-40837A9966E9@.microsoft.com...
> Hi All
> I am looking for a bit of advice.
> I am in the process of creating a database in which it has been decided
> that
> all primary keys are going to varchar(40). Not my decision, but anyway.
> When inserting into each table it will be possible to specify a value for
> the primary, but if not specified a value should be auto-generated. That
> means that the values in the primary key field can be a mixture of both
> numbers and letters, but if auto-generated it should just be a number.
> What be the best way to make this autogenerated values if no value is
> being
> specified in the insert?
> TIA
> Klaus
>|||Thanks a lot, Tony. That was very helpfull.
I will create a unique field on each of my tables. The value for this will
be auto-generated using identity. A trigger will then keep an eye on the
inserts. If no value is being specified for the Primary key, the Identity
value will be copied into the varchar(40) primary key field.
-- Klaus
"Tony Rogerson" wrote:
> Depends really, if auto-generated does it just need to be a number? Any ol
d
> number, or a specific format and range?
> I always recommend putting a surrogate key on the tables and use that as t
he
> foriegn key and inside the application (not for display purposes, but for
> use as the value in a listbox for instance), that can be a int column with
> the IDENTITY property, not null and have a unique constraint on it.
> You could set the value of the primary key to that if not specified, that
> would save calculating a new unique number.
> Otherwise, you could use an 'instead of' trigger, for example...
> Instead of using MAX, you could take the value from a table that holds the
> last number used.
> create table testtrg (
> mycol int not null unique
> )
> go
>
> insert testtrg ( mycol ) values ( 1 )
> go
>
> create trigger trgTestTrg on testtrg instead of insert
> as
> begin
> if @.@.rowcount = 0
> return
>
> declare @.nextid int
>
> begin tran
>
> set @.nextid = ( select max( mycol )
> from testtrg with (tablockx) )
>
> set @.nextid = isnull( @.nextid, 0 ) + 1
>
> insert testtrg values( @.nextid )
>
> commit tran
>
> end
> go
>
> -- Note, inserting 1 but it already exists so should give a key violation
,
> -- but the instead of trigger code kicks in and gives the next id.
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> go
>
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Klaus" <Klaus@.discussions.microsoft.com> wrote in message
> news:FE28E558-F88F-4A9F-9AAA-40837A9966E9@.microsoft.com...
>
>|||The second part of Tony's point should not be lost. You should use that int
key as the FK for relationships with other tables. If you need to show your
client the benefit of using an int instead of a varchar(40). Load up a
couple of tables with some test data. Perform join's using varchar(40) as
the keys and then the same using int as the key. The performance difference
is noticable.
So use an int (or even bigint) PK, put a unique constraint on the
varchar(40) column and for all business logic purposes, the varchar(40) fiel
d
is the "key". But behind the scenes in the database the far more efficient
int is the key.
John Scragg
"Klaus" wrote:
> Thanks a lot, Tony. That was very helpfull.
> I will create a unique field on each of my tables. The value for this will
> be auto-generated using identity. A trigger will then keep an eye on the
> inserts. If no value is being specified for the Primary key, the Identity
> value will be copied into the varchar(40) primary key field.
> -- Klaus
> "Tony Rogerson" wrote:
>|||Also, if you need the data in a varchar(40) field you can use a calculated
column (if they dont need to enter it).
I concurr with Tony & John, if you're doing joins, definately use the INT
field as the joining field, joining on varchar fields gets very slow at
medium to high data volumes.
create table ( id int identity(1,1) primary key , myPK AS cast( ID as
varchar(40)) )
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:EDB86798-0F96-415A-9D8D-733ED2E0CA02@.microsoft.com...
> The second part of Tony's point should not be lost. You should use that
int
> key as the FK for relationships with other tables. If you need to show
your
> client the benefit of using an int instead of a varchar(40). Load up a
> couple of tables with some test data. Perform join's using varchar(40) as
> the keys and then the same using int as the key. The performance
difference
> is noticable.
> So use an int (or even bigint) PK, put a unique constraint on the
> varchar(40) column and for all business logic purposes, the varchar(40)
field
> is the "key". But behind the scenes in the database the far more
efficient
> int is the key.
> John Scragg
> "Klaus" wrote:
>
will
Identity
Any old
as the
for
with
that
the
violation,
decided
anyway.
value for
That
both
number.
is
Auto-increment my primary key: why 2 instead of 1?
Hi all,
I have a table where I have my ProdPK set up as Primary key, turned on "Is Identity" and set the Identity increment to 1. But each time I add a new item, the number incremented by 2... I have couple of other tables and they are all fine, just this particular table increased twice as it should. I check the setting against other tables and everything seems to be the same.
By the way, this is adding the data to the table inside MS SQL Server Management Studio manually. I haven't done anything in the ASP.NET page yet.
Thank you very much,
Kenny.
That's weird. I would double check the identity column settings, and maybe check for triggers on the table?
|||Thanks. I did checked the table's setting and the auto-increment was set to "1". Other settings are identical to my other tables. Any suggestions?
Thanks again,
Kenny.
|||
Can you post the table script and the results you observe by executing "dbcc checkident ( YourTableName )" ? Have you made sure that there are no triggers defined on the table ?
Here is the table's script:
1USE [C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.2\MSSQL\DATA\MTRENZ.MDF]2GO3/****** Object: Table [dbo].[T_PRODUCTS] Script Date: 12/22/2007 11:58:52 ******/4SET ANSI_NULLS ON5GO6SET QUOTED_IDENTIFIER ON7GO8SET ANSI_PADDING ON9GO10CREATE TABLE [dbo].[T_PRODUCTS](11[PROD_ID] [int] IDENTITY(1,1) NOT NULL,12[ACC_TYPE_ID_FK] [int] NOT NULL,13[PROD_NAME] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,14[PROD_DESCR] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,15[PROD_MODEL] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,16[PROD_STATUS] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,17[PROD_PRICE] [smallmoney] NULL,18[PROD_DATE_ADDED] [datetime] NOT NULL CONSTRAINT [DF_T_PRODUCTS_PROD_DATE_ADDED] DEFAULT (getdate()),19[PROD_ADDED_BY] [uniqueidentifier] NULL,20[PROD_NO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,21[PROD_NOTES] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,22[PROD_PIC_S] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,23[PROD_PIC_L] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,24[PROD_NEW] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,25 CONSTRAINT [PK_T_PRODUCTS] PRIMARY KEY CLUSTERED26(27[PROD_ID] ASC28)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]29) ON [PRIMARY]3031GO32SET ANSI_PADDING OFF33GO34USE [C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.2\MSSQL\DATA\MTRENZ.MDF]35GO36ALTER TABLE [dbo].[T_PRODUCTS] WITH NOCHECK ADD CONSTRAINT [ACT_TYPE_ID_FK] FOREIGN KEY([ACC_TYPE_ID_FK])37REFERENCES [dbo].[T_ACC_TYPES] ([ACC_TYPE_ID])38NOT FOR REPLICATION
This is what it return when I run the dbcc:
Checking identity information: current identity value '4', current column value '4'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Currently there are only two rows in my database, with the primary key values 2 and 4. It skipped 1 and 3.
Thank you,
Kenny.
|||I don't know what exactly is the problem, but my database primary key started at 2, then 4, then 8... After 8, everything then become normal, meaning they increased correctly by 1!!!
I still don't know why, but it solved the problem itself I guess!
Thanks all,
Kenny.
Auto-increment key id field help
I have a key field ID (int) for a table. How can I auto incremnt the key so that when I insert a new record I can get a new key. I should not have to know the next available number in my table. What settings do I use for the field.
Thanks in advance
MoonWa
Check out the IDENTITY property.
AutoIncrement Fields
I am trying to upsize a Microsoft Access database.
I have used Autoincrement/random to generate some key fields
Is there a way to duplicate this fuctionality in MSDE? ( I can find the
ability to increment starting with a seed)
thanks
Ed Warren.
Hi,
See IDENTITY property in SQL Server books online. Usage is
CREATE TABLE TESTTABLE(i int IDENTITY(1,1), Name Varchar(10))
So the value for i start with 1 and increment by 1
Thanks
Hari
SQL Server MVP
"Ed Warren" <eowarren@.fakeaddress.zzz> wrote in message
news:exV1d5rNFHA.1500@.TK2MSFTNGP09.phx.gbl...
>I am trying to upsize a Microsoft Access database.
> I have used Autoincrement/random to generate some key fields
> Is there a way to duplicate this fuctionality in MSDE? ( I can find the
> ability to increment starting with a seed)
> thanks
> Ed Warren.
>
|||Ed,
You can use a trigger to give you a random auto increment - the upsizing
wizard can do this for you or you can add your own similar to
CREATE TRIGGER [StudentsTrig] ON dbo.Students
FOR INSERT
AS
SET NOCOUNT ON
Declare @.randc int, @.newc int
SET @.randc=0
WHILE @.randc=0
BEGIN
SELECT @.randc = (SELECT convert(int,(rand()*4294967295)-2147483648))
END
SELECT @.newc = (SELECT [Stud ID] FROM inserted)
UPDATE Students SET [Stud ID]=@.randc WHERE [Stud ID]=@.newc
You can modify the select statement if you want to allow negative numbers
(like Access).
Russ Stevens
|||That's what I'm looking for, thanks a lot
Ed Warren
"Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
news:e3FZJAtNFHA.3156@.TK2MSFTNGP15.phx.gbl...
> Ed,
> You can use a trigger to give you a random auto increment - the upsizing
> wizard can do this for you or you can add your own similar to
> CREATE TRIGGER [StudentsTrig] ON dbo.Students
> FOR INSERT
> AS
> SET NOCOUNT ON
> Declare @.randc int, @.newc int
> SET @.randc=0
> WHILE @.randc=0
> BEGIN
> SELECT @.randc = (SELECT convert(int,(rand()*4294967295)-2147483648))
> END
> SELECT @.newc = (SELECT [Stud ID] FROM inserted)
> UPDATE Students SET [Stud ID]=@.randc WHERE [Stud ID]=@.newc
> You can modify the select statement if you want to allow negative numbers
> (like Access).
> Russ Stevens
>
I have used Autoincrement/random to generate some key fields
Is there a way to duplicate this fuctionality in MSDE? ( I can find the
ability to increment starting with a seed)
thanks
Ed Warren.
Hi,
See IDENTITY property in SQL Server books online. Usage is
CREATE TABLE TESTTABLE(i int IDENTITY(1,1), Name Varchar(10))
So the value for i start with 1 and increment by 1
Thanks
Hari
SQL Server MVP
"Ed Warren" <eowarren@.fakeaddress.zzz> wrote in message
news:exV1d5rNFHA.1500@.TK2MSFTNGP09.phx.gbl...
>I am trying to upsize a Microsoft Access database.
> I have used Autoincrement/random to generate some key fields
> Is there a way to duplicate this fuctionality in MSDE? ( I can find the
> ability to increment starting with a seed)
> thanks
> Ed Warren.
>
|||Ed,
You can use a trigger to give you a random auto increment - the upsizing
wizard can do this for you or you can add your own similar to
CREATE TRIGGER [StudentsTrig] ON dbo.Students
FOR INSERT
AS
SET NOCOUNT ON
Declare @.randc int, @.newc int
SET @.randc=0
WHILE @.randc=0
BEGIN
SELECT @.randc = (SELECT convert(int,(rand()*4294967295)-2147483648))
END
SELECT @.newc = (SELECT [Stud ID] FROM inserted)
UPDATE Students SET [Stud ID]=@.randc WHERE [Stud ID]=@.newc
You can modify the select statement if you want to allow negative numbers
(like Access).
Russ Stevens
|||That's what I'm looking for, thanks a lot
Ed Warren
"Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
news:e3FZJAtNFHA.3156@.TK2MSFTNGP15.phx.gbl...
> Ed,
> You can use a trigger to give you a random auto increment - the upsizing
> wizard can do this for you or you can add your own similar to
> CREATE TRIGGER [StudentsTrig] ON dbo.Students
> FOR INSERT
> AS
> SET NOCOUNT ON
> Declare @.randc int, @.newc int
> SET @.randc=0
> WHILE @.randc=0
> BEGIN
> SELECT @.randc = (SELECT convert(int,(rand()*4294967295)-2147483648))
> END
> SELECT @.newc = (SELECT [Stud ID] FROM inserted)
> UPDATE Students SET [Stud ID]=@.randc WHERE [Stud ID]=@.newc
> You can modify the select statement if you want to allow negative numbers
> (like Access).
> Russ Stevens
>
Friday, February 24, 2012
Autogenerating Numbers for a primary key field, "studyId," in a tablebut with a few
I have a question on autogenerating numbers for a primary key field, "studyID," in a table—but with a few twists.
We want studyID to be automatically generated as a 5-digit number. Additionally, we have two study sites and would like the studyIDs pertaining to the first site to begin with a 1 and StudyIDs associated with our second site to start with a 2. When we begin entering data, we will enter either a 1 or 2 in a field called, "Site." Upon entering that 1 or 2, we would like at that moment for Access to instantly autogenerate the appropriate studyID for that site and put it in the "StudyID" field. We want the very first number generated for each site to end in a 1 (10001 and 20001).
Here’s the range of values we want our StudyIDs to be (this is to be our validation rule as well):
10001-19999 for Site 1
20001-29999 for Site 2
Your suggestions are VERY VERY WELCOME! THANKS!
If all sites were in seprate databases or at least tables it would be easy just set "identity increment" =1 and "identity seed" = [side prefix]0001 during table creation, but you need maintain all of this in one table so probably trigger is the only solution.
Tomek
|||This is the fundamental problem with autoincrementing pk fields. You can't really do this in one table. You could create 2 tables and use the post above, then join them into 1 table for output; but, realistically you can't accomplish this goal in 1 table with 1 autogenerated number with arbitrary insert order and more than one logical grouping of keyspaces.
It is generally suggested that if you can possibly avoid it, you should not use autoincrementing pk fields -- but rather use primary keys or clustered keys based on the content of the data itself if you can guarantee uniqueness. If you cannot, it may be advantageous in the long run to avoid duplicate rows by using a count in your table.
e.g. First Last
John Gordon
John Gordon
becomes
First Last Count
John Gordon 2
In the short run, you could split your table into two and join them to report, but in the long run, you may want to consider the limitations this design imposes on the index space of your data and the possibility for growth.
Hope that helps,
John
Autogenerate Primary Key
Hi all
How do you autogenerate your own primary key in SQL.
Instead of SQL generating an IDENTIY number which would be 1, 2 ,3..etc
I was wanting to give it my own sequence of numbers, how exactly do I do that can anyone help??why? what do these sequence of numbers look like? do they have special meanings?|||Humour me for a second - why?|||why? what do these sequence of numbers look like? do they have special meanings?Humour him too ^^^^^^^ :)|||The number would look like this 07-0000
The first two digits are the year, the others are in numerical secquence.. like 07-0001, 07-0002, 07-0003 (JP IR#)and so on, they normally log this in a journal but now they want a database to log this in where the database would generate these numbers and they have to do is put in the type, the amount and whether its a hold or not. its for our surveillance department and they have IR numbers that help in their reports|||smart numbers are stupid for many many reasons. I would just store those 2 parts in seperate fields and bring them together in the UI so they can see the number they want.|||Sorry I didnt realize they would be stupid, I just thought I would ask. Doesnt hurt to ask. I wasnt sure how to create something like|||Heh. The problem with semi intelligent (and also dumb) bespoke keys is when it comes to serialisation. This is not much of an issue if you add one row at a tme and do not have high concurrency but if either of these are not true then performance really suffers. Do you need to reset the count each year? SQL Server does not support this internally. I think other RDBMSs (like MySQL) do but that is not much help :)
How do you autogenerate your own primary key in SQL.
Instead of SQL generating an IDENTIY number which would be 1, 2 ,3..etc
I was wanting to give it my own sequence of numbers, how exactly do I do that can anyone help??why? what do these sequence of numbers look like? do they have special meanings?|||Humour me for a second - why?|||why? what do these sequence of numbers look like? do they have special meanings?Humour him too ^^^^^^^ :)|||The number would look like this 07-0000
The first two digits are the year, the others are in numerical secquence.. like 07-0001, 07-0002, 07-0003 (JP IR#)and so on, they normally log this in a journal but now they want a database to log this in where the database would generate these numbers and they have to do is put in the type, the amount and whether its a hold or not. its for our surveillance department and they have IR numbers that help in their reports|||smart numbers are stupid for many many reasons. I would just store those 2 parts in seperate fields and bring them together in the UI so they can see the number they want.|||Sorry I didnt realize they would be stupid, I just thought I would ask. Doesnt hurt to ask. I wasnt sure how to create something like|||Heh. The problem with semi intelligent (and also dumb) bespoke keys is when it comes to serialisation. This is not much of an issue if you add one row at a tme and do not have high concurrency but if either of these are not true then performance really suffers. Do you need to reset the count each year? SQL Server does not support this internally. I think other RDBMSs (like MySQL) do but that is not much help :)
Autoexist when queying
Hi all,
I think it's a simple question but i can't seem to get it right:
I have a parent child dimension, with key Object, and an attribute Type. Every Object has a type, consider this example
Object Type
--
Top Model
Child 1 Portfolio
Child 2 PortFolio
When i run this query:
select
time.month.members on 0,
objects.object.members * objects.[type].members on 1
from [Dream2007]
I get this on Axis 0:
Top Model
Top Portfolio
Child 1 Model
Child 1 Portfolio
Child 2 Model
Child 2 Portfolio
How can i het the following result with only the existing combinations?:
Top Model
Child 1 Portfolio
Child 2 PortFolio
I tried non empty, but that doesn't work because they can be null. I also have an attribute relationship between Object and Type.
Any ideas?
TIA,
GJ
Maybe there's some issue with the attribute relationships - you can compare with the Adventure Works Organization parent-child dimension, where each Organization has a Currency Code:
>>
select {} on 0,
[Organization].[Organization].[Organization].Members
* [Organization].[Currency Code].[Currency Code].Members on 1
from [Adventure Works]
-
AdventureWorks Cycle USD
Australia AUD
Canadian Division CAD
Central Division USD
European Operations EUR
France EUR
Germany EUR
North America Operations USD
Northeast Division USD
Northwest Division USD
Pacific Operations AUD
Southeast Division USD
Southwest Division USD
USA Operations USD
>>
|||
I tried to set the Cardinality of the relationship to one, but this did not change it.
Actually, when i looked at it more closely, higher members have their own type, and the types of their children too. So the top object has all the types of all members, the leaf members for the PC hierarchy have only their own type (and the [all] type if i set IsAggretable to true) I only checked the top members at first, so it seemd as if it was the crossproduct of all members from both hierarchies.
Is there any way to disable this behaviour, so a member would only have the type it gets from the dimtabel row, not all its children? Or is this conceptually impossible?
I have a lot of caluculations that need to apply to a specific type of member only. I could scope on Level.Ordinal, but then i would have to keep track of which types of objects exist on which levels.
Regards,
GJ
|||Going back to the Adventure Works Organization dimension, I think this query illustrates the issue which you raise:
>>
select {} on 0,
[Organization].[Organizations].Members
* [Organization].[Currency Code].[Currency Code].Members on 1
from [Adventure Works]
AdventureWorks Cycle AUD
AdventureWorks Cycle CAD
AdventureWorks Cycle EUR
AdventureWorks Cycle USD
European Operations EUR
France EUR
Germany EUR
North America Operations CAD
North America Operations USD
Canadian Division CAD
USA Operations USD
Central Division USD
Northeast Division USD
Northwest Division USD
Southeast Division USD
Southwest Division USD
Pacific Operations AUD
Australia AUD
>>
Comparing it to the earlier query, each member is located in both the dimension key attribute hierarchy: [Organization].[Organization], and in the parent-child hierarchy: [Organization].[Organizations]. And in the attribute hierachy, each member is only associated with a single currency. Can you give an idea of what scoped calculations you need; and could these be translated to the Adventure Works Organization dimension?
|||I tried it with the key hierarchy, not the PC, and indead now get the results i expected.
I guess it would be logical for members in a PC hierarchy to have the attributes of it's descendants too for rolling up additive measures, because else there would be nowhere for these aggregates to go, or they wouldn't show up in queries.
The problem we have is we're looking to migrate a forecasting app we built on AS2000 to 2005. we have loads of calculated members like this one that all build upon each other
CREATE MEMBER CURRENTCUBE.[PP].[Calcs200].[WO_component_ML_maandbedrag] AS
iif(Object.CurrentMember.Level Is Object.Levels(7),
Iif(Object.CurrentMember.Properties("ObjectType") = "WO_component",
Iif(Object.CurrentMember.Properties("ObjectLevel") = "8",
iif(Tijd.CurrentMember Is Tijd.Members.Item(Val(Object.Currentmember.Properties("ObjectStartDateOrdinal"))) ,
0 + (0 + Val([Object].Properties("Component_Maandbedrag"))),
0 + (Tijd.Members.Item(Val(Object.Currentmember.Properties("ObjectStartDateOrdinal"))), [PP].[WO_component_ML_maandbedrag] )
),
0),
0),
iif(Object.CurrentMember.Level.Ordinal < 7,
0 + SUM(Filter(Descendants(Object.CurrentMember, Object.levels(7)), Object.CurrentMember.Properties("ObjectLevel") = "8" AND Object.CurrentMember.Properties("ObjectType") = "WO_component"), ([PP].[WO_component_ML_maandbedrag]) ) ,
0 + (Ancestor(Object.CurrentMember, Object.levels(7)), [PP].[WO_component_ML_maandbedrag] )
)
), SOLVE_ORDER= 11 , FORMAT_STRING='#,##.##' ;
Where we use iif(Object.Currentmember.Level or Object.Currentmember.Properties to make sure the calcs get done (only) on the right spot.
So we were pretty excited about Scope, because then we wouldn't have to use Currentmember and Filter all the time, this would eliminate all the extra things we have to do to solve performace.
Now i'm trying to understand how this works exactly, but i'm still having trouble getting my head around 2005. 2000 was alot easier in this respect.
I don't think adventureworks has anything that comes near, but i'll look into it.
Regards, GJ
|||Unfortunately, I can't quite comprehend all the logic in the calculated member above. But for the problem of filtering members of a parent-child hierarchy with a specific property value, using "Autoexist" behavior - here's an Adventure Works query which returns members of the [Organization].[Organizations] hierarchy whose Currency Code is "CAD" - and it excludes any ancestors, since their own Currency Code isn't "CAD":
>>
select {} on 0,
Generate([Organization].[Organizations].Members,
{([Organization].[Organizations].CurrentMember,
StrToMember("[Organization].[Organization].&["
+ [Organization].[Organizations].Properties("Key")
+ "]"),
[Organization].[Currency Code].[CAD])}) on 1
from [Adventure Works]
Canadian Division Canadian Division CAD
>>
|||PS: maybe I did learn something at last month's SQL PASS in Seattle - my memory just flashed back to a session where the use of LinkMember() in lieu of StrToMember() was mentioned (blog link below); so here's a cleaner version of the query:
select {} on 0,
Generate([Organization].[Organizations].Members,
{([Organization].[Organizations].CurrentMember,
LinkMember([Organization].[Organizations].CurrentMember,
[Organization].[Organization]),
[Organization].[Currency Code].[CAD])}) on 1
from [Adventure Works]
http://sqljunkies.com/WebLog/mosha/archive/2006/11/16.aspx
>>
SQL PASS in Seattle: What has the UDM Done For You Lately by Dan Bulos
.. 'bad' MDX functions for AS2005. StrToMember is one of them..we agreed that in that particular scenario the script indeed could've been rewritten to use LinkMember..
>>
Sunday, February 19, 2012
Auto_increment
Hello my name is Elisa and I have a problem with this line of code:
CREATE TABLE Film
(IdFilm int not null auto_increment PRIMARY KEY,
Titolo VARCHAR2(20) NOT NULL,
Regista VARCHAR2(20) NOT NULL,
Personaggi_Principali VARCHAR2(50) NOT NULL,
Personaggi_Secondari VARCHAR2(50) NOT NULL,
Nazione VARCHAR2(20) NOT NULL,
Durata NUMBER(3) NOT NULL,
Genere VARCHAR2(10) NOT NULL,
Trama VARCHAR2(500) NOT NULL,
Critica VARCHAR2(10) NOT NULL,
Locandina VARCHAR2(20) NOT NULL,
Note VARCHAR2(100) NOT NULL)
when I execute the result is:
Error: ORA-00922: missing or invalid option.
It's very urgent.
Thank you Elisaauto_increment does not exist in Oracle!
You have to create a sequence:
CREATE SEQUENCE FILM_SEQ;
and then you can either use it during the insert as in:
INSERT INTO Film VALUES (FILM_SEQ.NEXTVAL, ...);
or create a trigger:
CREATE OR REPLACE TRIGGER BI_FILM_TRG
BEFORE INSERT ON FILM FOR EACH ROW
BEGIN
SELECT FILM_SEQ.NEXTVAL INTO :NEW.IdFilm FROM DUAL;
END;
/
NOTE: DO NOT USE IT ON BOTH THE INSERT AND TRIGGER!!!
:cool:|||Thank you very much for your help
Elisa
CREATE TABLE Film
(IdFilm int not null auto_increment PRIMARY KEY,
Titolo VARCHAR2(20) NOT NULL,
Regista VARCHAR2(20) NOT NULL,
Personaggi_Principali VARCHAR2(50) NOT NULL,
Personaggi_Secondari VARCHAR2(50) NOT NULL,
Nazione VARCHAR2(20) NOT NULL,
Durata NUMBER(3) NOT NULL,
Genere VARCHAR2(10) NOT NULL,
Trama VARCHAR2(500) NOT NULL,
Critica VARCHAR2(10) NOT NULL,
Locandina VARCHAR2(20) NOT NULL,
Note VARCHAR2(100) NOT NULL)
when I execute the result is:
Error: ORA-00922: missing or invalid option.
It's very urgent.
Thank you Elisaauto_increment does not exist in Oracle!
You have to create a sequence:
CREATE SEQUENCE FILM_SEQ;
and then you can either use it during the insert as in:
INSERT INTO Film VALUES (FILM_SEQ.NEXTVAL, ...);
or create a trigger:
CREATE OR REPLACE TRIGGER BI_FILM_TRG
BEFORE INSERT ON FILM FOR EACH ROW
BEGIN
SELECT FILM_SEQ.NEXTVAL INTO :NEW.IdFilm FROM DUAL;
END;
/
NOTE: DO NOT USE IT ON BOTH THE INSERT AND TRIGGER!!!
:cool:|||Thank you very much for your help
Elisa
Thursday, February 16, 2012
auto return of primary key of row just entered
Hello vmrocha,
Our records indicate that you have never posted to our site before. We hope you find the help you need.
If you need to make a post, we're always happy to help.Hello vmrocha,
Our records indicate that you have never posted to our site before. We hope you find the help you need.
If you need to make a post, we're always happy to help.|||Hello vmrocha,
Our records indicate that you have never posted to our site before. We hope you find the help you need.
If you need to make a post, we're always happy to help.|||perhaps this the golden rule being acted out in a web forum|||actually, seems more like a bug. post says it has two pages, but it doesn't really...|||it's a b0rken thread, the forum database has integrity problems
http://www.dbforums.com/showthread.php?t=1613785
kind of ironic for a database forum, eh
:)
Our records indicate that you have never posted to our site before. We hope you find the help you need.
If you need to make a post, we're always happy to help.Hello vmrocha,
Our records indicate that you have never posted to our site before. We hope you find the help you need.
If you need to make a post, we're always happy to help.|||Hello vmrocha,
Our records indicate that you have never posted to our site before. We hope you find the help you need.
If you need to make a post, we're always happy to help.|||perhaps this the golden rule being acted out in a web forum|||actually, seems more like a bug. post says it has two pages, but it doesn't really...|||it's a b0rken thread, the forum database has integrity problems
http://www.dbforums.com/showthread.php?t=1613785
kind of ironic for a database forum, eh
:)
Monday, February 13, 2012
Auto Primary Key with fill in
I want a table to have an int auto primary key, that will fill in deleted rows.
EG
1
2
4
5
8
EG
1
2
4
5
8
The next primary key should be 3 (NOT 9)
This table changes often, so just adding 1 to the last number used, will fast run out of numbers.
The table is accessed by many users, so it can not be manually generated.
Using the IDENTITY property will successfully and correctly manage autonumbering. However, as you noted, deletions will leave gaps. I suggest that you use a datatype sufficient to allow both growth and allow for the deletions. A bigint datatype is a very, very large number -up to 9,223,372,036,854,775,807, I couldn't imagine that you are adding so much data to the database that a bigint would be inadequate.
It would be possible to create a 'homegrown' solution to manage this, but in my experience, with many users, that is a mistake. It would require a lot of table locking and/or data contention, placing unneeded stress on the database.
There are occassionally, real business needs for managing a numbering sequence, for example check numbers. But in those cases, deletions are usually not allowed.
|||Yes and after about 1 year or so when it flips over to 1 again, and starts over writing data.Its a very nasty bug.
Its happened before.|||
Hi:
Please refer to the following article, and see if it would help with your concern. :-)
http://www.sqlteam.com/item.asp?ItemID=765
Thanks.
|||That changes existing data.
but can be expanded upon, to use a multi sql statement to defrag a database.
but then how to reset the auto number to the next number.
So far the only way, i can do what i want, is one of two ways
1. Run all sql through a service, that can sync me a new auto fill in number.
2. Have unacceptable down time, and run a program to defrag the tables.
Auto numbering field similar to
I've been using MS Access 2000 for a while and have recently switched to MS
SQL 2000. When creating a primary key I am used to MS Access ability to auto
matically enter a number in the ID field when I enter data into my tables.
Does MS SQL have a feature similar to this? I checked all the data types and
the only thing that I see that is close to autonumber is uniqueidentifier. Is
that the same thing?
Walker_Michael wrote:
> I've been using MS Access 2000 for a while and have recently switched
> to MS SQL 2000. When creating a primary key I am used to MS Access
> ability to auto matically enter a number in the ID field when I enter
> data into my tables. Does MS SQL have a feature similar to this? I
> checked all the data types and the only thing that I see that is
> close to autonumber is uniqueidentifier. Is that the same thing?
No, not really. What you want is an IDENTITY column (attached to a
numeric data type) as in:
Create Table Customers (
CustID INT IDENTITY NOT NULL )
Unique identifiers can be used as well, but you need to generate the
number manually using the newid() function. They consists of a 16-byte
hexadecimal number (GUID). Some SQL Server users use them as keys. They
are used frequently in replication. The INT IDENTITY can accommodate
more than 2 Billion values and is only 4-bytes as opposed to 16.
The return the last identity value inserted, you should use
scope_identity(). From a stored procedure, you could use:
Create Proc dbo.UpdateCustomer
@.CustID INT OUTPUT,
@.CustName VARCHAR(50
as
Begin
If @.CustID IS NOT NULL
Update dbo.Customers
Set CustName = @.CustName
Where CustID = @.CustID
Else
Begin
Insert dbo.Customers (
CustName)
Values (
@.CustName )
Set @.CustID = SCOPE_IDENTITY()
End
End
To call this procedure:
Declare @.CustID INT
Exec dbo.UpdateCustomer @.CustID OUTPUT, 'David Gugick'
Select @.CustID
David Gugick
Imceda Software
www.imceda.com
SQL 2000. When creating a primary key I am used to MS Access ability to auto
matically enter a number in the ID field when I enter data into my tables.
Does MS SQL have a feature similar to this? I checked all the data types and
the only thing that I see that is close to autonumber is uniqueidentifier. Is
that the same thing?
Walker_Michael wrote:
> I've been using MS Access 2000 for a while and have recently switched
> to MS SQL 2000. When creating a primary key I am used to MS Access
> ability to auto matically enter a number in the ID field when I enter
> data into my tables. Does MS SQL have a feature similar to this? I
> checked all the data types and the only thing that I see that is
> close to autonumber is uniqueidentifier. Is that the same thing?
No, not really. What you want is an IDENTITY column (attached to a
numeric data type) as in:
Create Table Customers (
CustID INT IDENTITY NOT NULL )
Unique identifiers can be used as well, but you need to generate the
number manually using the newid() function. They consists of a 16-byte
hexadecimal number (GUID). Some SQL Server users use them as keys. They
are used frequently in replication. The INT IDENTITY can accommodate
more than 2 Billion values and is only 4-bytes as opposed to 16.
The return the last identity value inserted, you should use
scope_identity(). From a stored procedure, you could use:
Create Proc dbo.UpdateCustomer
@.CustID INT OUTPUT,
@.CustName VARCHAR(50
as
Begin
If @.CustID IS NOT NULL
Update dbo.Customers
Set CustName = @.CustName
Where CustID = @.CustID
Else
Begin
Insert dbo.Customers (
CustName)
Values (
@.CustName )
Set @.CustID = SCOPE_IDENTITY()
End
End
To call this procedure:
Declare @.CustID INT
Exec dbo.UpdateCustomer @.CustID OUTPUT, 'David Gugick'
Select @.CustID
David Gugick
Imceda Software
www.imceda.com
auto numbering
plz tell me how to make a column(id)that is a primary key to be auto numbered .
Hi,
you have to create a new column with the identity property and copy the primary key values over to the identity column, then drop the old non-identity column.
There is no ALTER Column or ALTER table to do this.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Subscribe to:
Posts (Atom)