I used a field as the record's number,how can I get a automaticly created
number field (it can inrease automaticly) when I insert a record into a
table?Refer to the IDENTITY property in BOL
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"authorking" <authorking2002@.hotmail.com> wrote in message
news:uKU01w2CFHA.3732@.TK2MSFTNGP14.phx.gbl...
>I used a field as the record's number,how can I get a automaticly created
>number field (it can inrease automaticly) when I insert a record into a
>table?
>|||First, let's clarify some concepts. In SQL, rows are identified by a Key not
by a "record number". In fact the concept of a record number is quite alien
to the relational database model. The Key is part of your data - it is some
subset of the attributes that uniquely identify a row.
What you are asking for is called a *surrogate* or *artificial* key. SQL
Server provides the IDENTITY feature as a mechanism for an artifically
generated, surrogate key so take a look at IDENTITY in Books Online.
IDENTITY is not a substitute for the natural key of your table. It is just a
surrogate for that key and may be used in foreign key references. Many times
you won't need IDENTITY at all. If you aren't familiar with some of these
key concepts then look them up in a book on relational database
fundamentals.
Hope this helps.
David Portas
SQL Server MVP
--
Showing posts with label number. Show all posts
Showing posts with label number. Show all posts
Tuesday, March 27, 2012
Tuesday, March 20, 2012
Automatic SQL db update at set time?
I would like to limit the number of pages a user can view on my website each day. The users logs in and I can count the number of pages viewd in a field but i want to know how i can set the page count field to reset to 0 at the end of the day (ie midnight). Is it possible to do this? and if so how? Thanks.You can set up a job to run a stored proc at that time every day using SQL Server Agent (find it in SQL Server Enterprise Manager)
A better way might be to log pageviews with a timestamp, and then you can allow X pages within any 24 hour period - simply count the pageviews in the log newer than getdate() - 1 and check it against the limit.
Does that help?|||The server will be a shared sql server and i don't have access to creating new jobs, so I think your second suggestion would be best but not sure how to implement it. Do you have an example or a link to where I can find an example? Thanks|||Normally that should not be a problem - I use a shared database server (one of those cheap .net hosters) and I can create jobs just fine.
Think about my other solution if you really can't create jobs - it's better (I believe) and it does not require a scheduled job.
Check BOL for examples of creating jobs.
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.
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
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
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:
>
Monday, March 19, 2012
automatic number problem
Hi, i have imported an access database into sqlserver 2000. In access,
i have some table fields which were using automatic number as
datatype. But i see that there is not a datatype in sqlserver like
automatic no. I have to enter id numbers to my tables for each records
automatically. Would anybody help me about solving this problem? Any
idea? Thanksfatih kayaalp (kaya_alp@.hotmail.com) writes:
> Hi, i have imported an access database into sqlserver 2000. In access,
> i have some table fields which were using automatic number as
> datatype. But i see that there is not a datatype in sqlserver like
> automatic no. I have to enter id numbers to my tables for each records
> automatically. Would anybody help me about solving this problem? Any
> idea? Thanks
i have some table fields which were using automatic number as
datatype. But i see that there is not a datatype in sqlserver like
automatic no. I have to enter id numbers to my tables for each records
automatically. Would anybody help me about solving this problem? Any
idea? Thanksfatih kayaalp (kaya_alp@.hotmail.com) writes:
> Hi, i have imported an access database into sqlserver 2000. In access,
> i have some table fields which were using automatic number as
> datatype. But i see that there is not a datatype in sqlserver like
> automatic no. I have to enter id numbers to my tables for each records
> automatically. Would anybody help me about solving this problem? Any
> idea? Thanks
You can assign a column the IDENTITY property:
CREATE TABLE a (a int IDENTITY(1, 1) NOT NULL,
To get idenity value the most recently inserted row, use scope_identity().
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I have some table fields [sic] which were using automatic number as
datatype [sic]. <<
You do not understand SQL or data modeling. Fields are not columns;
tables are not file; rows are not records. The entire concept of a
physical numbering of rows is not relational. Your original design
was wrong and you want to copy it into SQL. Why do you want to do
that again??
Take a course and take the time to learn to do it right.|||Joe, go back in the hole you came from, take an attitude course and then
come back. Not earlier.
"--CELKO--" <joe.celko@.northface.edu> wrote in message
news:a264e7ea.0401132113.38222a3a@.posting.google.c om...
> >> I have some table fields [sic] which were using automatic number as
> datatype [sic]. <<
> You do not understand SQL or data modeling. Fields are not columns;
> tables are not file; rows are not records. The entire concept of a
> physical numbering of rows is not relational. Your original design
> was wrong and you want to copy it into SQL. Why do you want to do
> that again??
> Take a course and take the time to learn to do it right.|||Martin Feuersteiner (theintrepidfox@.hotmail.com) writes:
> Joe, go back in the hole you came from, take an attitude course and then
> come back. Not earlier.
Joe on an attitude course? What a waste of time and money! That man is a
hopeless case!
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> Joe on an attitude course? What a waste of time and money! That man
is a hopeless case! <<
I was going to take a Dale Carnegie course, but the restraining order is
still in effect.
--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 ***
Don't just participate in USENET...get rewarded for it!
automatic number increment in ms sql 2005
will it be possible to increase number as below automatically
00000001
00000002
00000003
...
type.
news:1169681707.525338.145700@.13g2000cwe.googlegro ups.com...
NOTE: The numbers will be sequential but not necessarily contiguous.
>|||HandersonVA (handersonva@.hotmail.com) writes:
First of all: do you need the numbers to be contiguous. If you cannot
accept gaps, you need to roll your own:
00000001
00000002
00000003
...
whenever the row is inserted, number will be increased like above
format.
which data type should I select and do some other setting to record
like that?
thanksHanderson,
If you set up a column as an INTEGER IDENTITY column (see BOL), SQL Server
will not store leading zeros. You could get leading zeros out of your select
statments as follows:
select right('00000000' + cast(MyColumn as varchar(25)), 8)
But, the best thing to do is manage leading zeros at the application layer.
Don't have the database always doing that work for you.
-- Bill
"HandersonVA" <handersonva@.hotmail.comwrote in message
news:1169678553.956380.205080@.q2g2000cwa.googlegro ups.com...
Quote:
Originally Posted by
will it be possible to increase number as below automatically
00000001
00000002
00000003
...
>
whenever the row is inserted, number will be increased like above
format.
which data type should I select and do some other setting to record
like that?
thanks
>
type.
Thus (from BOL)
IF OBJECT_ID ('dbo.new_employees', 'U') IS NOT NULL
DROP TABLE new_employees
GO
CREATE TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
)
On Jan 24, 2:42 pm, "HandersonVA" <handerso...@.hotmail.comwrote:
Quote:
Originally Posted by
will it be possible to increase number as below automatically
00000001
00000002
00000003
...
>
whenever the row is inserted, number will be increased like above
format.
which data type should I select and do some other setting to record
like that?
thanks
news:1169681707.525338.145700@.13g2000cwe.googlegro ups.com...
Quote:
Originally Posted by
>
When creating the table, use the Identity keyword, and an int or bigint
type.
>
NOTE: The numbers will be sequential but not necessarily contiguous.
If you have a rollback for example the numbers wioll be "used" up.
Quote:
Originally Posted by
>
Thus (from BOL)
IF OBJECT_ID ('dbo.new_employees', 'U') IS NOT NULL
DROP TABLE new_employees
GO
CREATE TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
)
>
>
>
>
On Jan 24, 2:42 pm, "HandersonVA" <handerso...@.hotmail.comwrote:
Quote:
Originally Posted by
>will it be possible to increase number as below automatically
>00000001
>00000002
>00000003
>...
>>
>whenever the row is inserted, number will be increased like above
>format.
>which data type should I select and do some other setting to record
>like that?
>thanks
>|||HandersonVA (handersonva@.hotmail.com) writes:
Quote:
Originally Posted by
will it be possible to increase number as below automatically
00000001
00000002
00000003
...
>
whenever the row is inserted, number will be increased like above
format.
which data type should I select and do some other setting to record like
that? thanks
First of all: do you need the numbers to be contiguous. If you cannot
accept gaps, you need to roll your own:
BEGIN TRANSACTION
SELECT @.id = coalesce(MAX(id), 0) + 1 FROM tbl WITH (UPDLOCK)
INSERT tbl (id, ...)
VALUES (@.id, ...)
COMMIT TRANSACTION
If you want to includ the leading zeroes, I would recommend that you
add a computed column that you persist and can index:
idasstr AS replicate('0', 10 - len(ltrim(str(id))) + ltrim(str(id))
PERSISTED
(Note: the PERSISTED keyword is available in SQL 2005 only.)
If you don't contiguous numbers you can use IDENTITY instead, and this is
partiucularly important if you expect a high insertion frequency from
multiple clients, as the scheme above will incur a serialisation that
reduces throughput. Even with IDENTITY you can would have a computed
column with the leading zeroes.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Automatic Import of Emails
Is there a way to automatically import incoming emails into a SQL
Server Database?
I know it is inefficient, but we have to run a number of rules on the
email after it is received. Store it in a database and be able to
connect other things, meetings, contacts, etc. Moving it back and
forth in chunks can be difficult and the overhead could spiral out of
control.
So we were thinking of moving it to the DB as it was retrieved.
What do you think would be a better solution to retrieve it?
Does my email make sense?Am 14 Jul 2006 01:13:51 -0700 schrieb leeroy881:
Maybe not import, but read. See xp_readmail/xp_findnextmsg (in SQL2000 sp3
and up) in BOL. With this and ServerAgent you can implement an automatic
mail processing system.
>
Maybe not import, but read. See xp_readmail/xp_findnextmsg (in SQL2000 sp3
and up) in BOL. With this and ServerAgent you can implement an automatic
mail processing system.
>
bye, Helmut
Server Database?
I know it is inefficient, but we have to run a number of rules on the
email after it is received. Store it in a database and be able to
connect other things, meetings, contacts, etc. Moving it back and
forth in chunks can be difficult and the overhead could spiral out of
control.
So we were thinking of moving it to the DB as it was retrieved.
What do you think would be a better solution to retrieve it?
Does my email make sense?Am 14 Jul 2006 01:13:51 -0700 schrieb leeroy881:
Quote:
Originally Posted by
Is there a way to automatically import incoming emails into a SQL
Server Database?
Maybe not import, but read. See xp_readmail/xp_findnextmsg (in SQL2000 sp3
and up) in BOL. With this and ServerAgent you can implement an automatic
mail processing system.
bye, Helmut|||Thank you Helmut.
I'll check it out.
Helmut Woess wrote:
Quote:
Originally Posted by
Am 14 Jul 2006 01:13:51 -0700 schrieb leeroy881:
>
Quote:
Originally Posted by
Is there a way to automatically import incoming emails into a SQL
Server Database?
>
Maybe not import, but read. See xp_readmail/xp_findnextmsg (in SQL2000 sp3
and up) in BOL. With this and ServerAgent you can implement an automatic
mail processing system.
>
bye, Helmut
Wednesday, March 7, 2012
Automated Backup
Hi there,
I currently have a MSDE Server running with a number of databases on which i
would like to backup daily and weekly at a certain time. Is there anyway of
doing this? At the moment ive just been using a simple line of code and
executing when i remeber to, but an automated system would be great. The code
ive been using is:
BACKUP DATABASE dbname TO DISK = 'C:\dbname.bak'
Any help of links to sites that explain how todo this would be excellent!!
James
Hi James,
You need to schedule a job with the sql agent. First, make sure it is
running (it is a separate service). Then take a look at sp_addjob and
sp_addjobstep.
If that sounds messy, the MSDE Manager utility at our web site has options
to do that for you and is free for personal use.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"James Proctor" <JamesProctor@.discussions.microsoft.com> wrote in message
news:C6DBD816-25C1-4A0E-8E78-60FAB18A6940@.microsoft.com...
> Hi there,
> I currently have a MSDE Server running with a number of databases on which
> i
> would like to backup daily and weekly at a certain time. Is there anyway
> of
> doing this? At the moment ive just been using a simple line of code and
> executing when i remeber to, but an automated system would be great. The
> code
> ive been using is:
> BACKUP DATABASE dbname TO DISK = 'C:\dbname.bak'
> Any help of links to sites that explain how todo this would be excellent!!
> James
|||You can try SQLExecMS from www.laplas-soft.com
It allows to create maintenance plans for your databases.
"James Proctor" <JamesProctor@.discussions.microsoft.com> wrote in message
news:C6DBD816-25C1-4A0E-8E78-60FAB18A6940@.microsoft.com...
> Hi there,
> I currently have a MSDE Server running with a number of databases on which
> i
> would like to backup daily and weekly at a certain time. Is there anyway
> of
> doing this? At the moment ive just been using a simple line of code and
> executing when i remeber to, but an automated system would be great. The
> code
> ive been using is:
> BACKUP DATABASE dbname TO DISK = 'C:\dbname.bak'
> Any help of links to sites that explain how todo this would be excellent!!
> James
I currently have a MSDE Server running with a number of databases on which i
would like to backup daily and weekly at a certain time. Is there anyway of
doing this? At the moment ive just been using a simple line of code and
executing when i remeber to, but an automated system would be great. The code
ive been using is:
BACKUP DATABASE dbname TO DISK = 'C:\dbname.bak'
Any help of links to sites that explain how todo this would be excellent!!
James
Hi James,
You need to schedule a job with the sql agent. First, make sure it is
running (it is a separate service). Then take a look at sp_addjob and
sp_addjobstep.
If that sounds messy, the MSDE Manager utility at our web site has options
to do that for you and is free for personal use.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"James Proctor" <JamesProctor@.discussions.microsoft.com> wrote in message
news:C6DBD816-25C1-4A0E-8E78-60FAB18A6940@.microsoft.com...
> Hi there,
> I currently have a MSDE Server running with a number of databases on which
> i
> would like to backup daily and weekly at a certain time. Is there anyway
> of
> doing this? At the moment ive just been using a simple line of code and
> executing when i remeber to, but an automated system would be great. The
> code
> ive been using is:
> BACKUP DATABASE dbname TO DISK = 'C:\dbname.bak'
> Any help of links to sites that explain how todo this would be excellent!!
> James
|||You can try SQLExecMS from www.laplas-soft.com
It allows to create maintenance plans for your databases.
"James Proctor" <JamesProctor@.discussions.microsoft.com> wrote in message
news:C6DBD816-25C1-4A0E-8E78-60FAB18A6940@.microsoft.com...
> Hi there,
> I currently have a MSDE Server running with a number of databases on which
> i
> would like to backup daily and weekly at a certain time. Is there anyway
> of
> doing this? At the moment ive just been using a simple line of code and
> executing when i remeber to, but an automated system would be great. The
> code
> ive been using is:
> BACKUP DATABASE dbname TO DISK = 'C:\dbname.bak'
> Any help of links to sites that explain how todo this would be excellent!!
> James
Automate to get version number for a list of SQL Instances
We have a bunch of SQL Server instances in our domain. Some of them
are in SQL 2000 and some are in 2005. I need to be able to find out
what service pack, edition and version each of the instances are
running on, what will be the best way to go about it. I would prefer
not to do it manually, i have a list of instances all saved in a
table. I would like to loop through this table connect to each
instance and get the result I want and save it in the same table.
My challenge is none of the servers are linked and I am not able to
get openrowset to work with trusted connection, I am able to get what
I need using SQL user but that requires me to add SQL login to each of
the servers before I can go about my script. I am sure other DBA's
have gone through this, can someone please suggest or give ideas.
Any help in this reagrd will be greatly appreciated.
Thanks"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>
If you can access the file system on the target servers then you can obtain
the version number via VBScript:
Set objFSO = CreateObject("Scripting.FileSystemObject")
Wscript.Echo objFSO.GetFileVersion("C:\Program Files\Microsoft SQL
Server\MSSQL$SS2K\Binn\sqlservr.exe")
--
David Portas|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
The below is not reliable script. You will have to go throu each server and
run SERVERPROPRTY to get what you want.
CREATE TABLE #servers(sname VARCHAR(255))
INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
DELETE #servers WHERE sname='Servers:'
SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
DROP TABLE #servers
"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>|||I had to monitor several hundred instances before and I did monitoring each
day by creating linked servers through TSQL right before I executed my
monitoring scripts. After the scripts finished I would drop the link server
so I would not have hundreds of linked servers setting around.
Also try using OSQL to make a connection. You can put build the OSQL
connection string from your table for each server and then paste all the
connection strings into a batch job which points to a script file with your
monitoring code.
"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>|||The ideal approach is not to do this in T-SQL, but in a little client app
written in a real programming language. Typically, you would want to collect
a lot more info than just versions and build numbers. Having a little client
app gives you ultimate flexibility in whatever inventory information you may
fancy to collect.
Linchi
"shub" wrote:
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>
are in SQL 2000 and some are in 2005. I need to be able to find out
what service pack, edition and version each of the instances are
running on, what will be the best way to go about it. I would prefer
not to do it manually, i have a list of instances all saved in a
table. I would like to loop through this table connect to each
instance and get the result I want and save it in the same table.
My challenge is none of the servers are linked and I am not able to
get openrowset to work with trusted connection, I am able to get what
I need using SQL user but that requires me to add SQL login to each of
the servers before I can go about my script. I am sure other DBA's
have gone through this, can someone please suggest or give ideas.
Any help in this reagrd will be greatly appreciated.
Thanks"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>
If you can access the file system on the target servers then you can obtain
the version number via VBScript:
Set objFSO = CreateObject("Scripting.FileSystemObject")
Wscript.Echo objFSO.GetFileVersion("C:\Program Files\Microsoft SQL
Server\MSSQL$SS2K\Binn\sqlservr.exe")
--
David Portas|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
The below is not reliable script. You will have to go throu each server and
run SERVERPROPRTY to get what you want.
CREATE TABLE #servers(sname VARCHAR(255))
INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
DELETE #servers WHERE sname='Servers:'
SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
DROP TABLE #servers
"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>|||I had to monitor several hundred instances before and I did monitoring each
day by creating linked servers through TSQL right before I executed my
monitoring scripts. After the scripts finished I would drop the link server
so I would not have hundreds of linked servers setting around.
Also try using OSQL to make a connection. You can put build the OSQL
connection string from your table for each server and then paste all the
connection strings into a batch job which points to a script file with your
monitoring code.
"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>|||The ideal approach is not to do this in T-SQL, but in a little client app
written in a real programming language. Typically, you would want to collect
a lot more info than just versions and build numbers. Having a little client
app gives you ultimate flexibility in whatever inventory information you may
fancy to collect.
Linchi
"shub" wrote:
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>
Friday, February 24, 2012
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 :)
Sunday, February 19, 2012
AutoClose - How much resources are saved
We have a server with a number of relatively infrequently used DBs, one or
two are more archival.
If I set them to Autclose, any idea as to resources released ?
I know the users will suffer when they do access them, but some never really
are used.
KlK, MCSE
> I know the users will suffer when they do access them, but some never
really
> are used.
Then wouldn't it make more sense to detach them?
|||Here you go:
http://msdn.microsoft.com/library/de...ar_ts_1o4z.asp
Sincerely,
Anthony Thomas
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE
|||note that every time you access EM and open the Database tree/folder, all the
databases that were closed will re-open and it also gets logged... I would
follow Aaron advice.
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE
|||Well unfortunately, while I say they are really never used, the users would
not agree and want their data. So I guess we leave as is.
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE
|||> Well unfortunately, while I say they are really never used, the users
would
> not agree and want their data. So I guess we leave as is.
So what is the big concern about moving them or having them autoclose or not
then?
If having them on the same server as other databases is such a big concern,
why not detach them from that server and attach them to a different one?
two are more archival.
If I set them to Autclose, any idea as to resources released ?
I know the users will suffer when they do access them, but some never really
are used.
KlK, MCSE
> I know the users will suffer when they do access them, but some never
really
> are used.
Then wouldn't it make more sense to detach them?
|||Here you go:
http://msdn.microsoft.com/library/de...ar_ts_1o4z.asp
Sincerely,
Anthony Thomas
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE
|||note that every time you access EM and open the Database tree/folder, all the
databases that were closed will re-open and it also gets logged... I would
follow Aaron advice.
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE
|||Well unfortunately, while I say they are really never used, the users would
not agree and want their data. So I guess we leave as is.
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE
|||> Well unfortunately, while I say they are really never used, the users
would
> not agree and want their data. So I guess we leave as is.
So what is the big concern about moving them or having them autoclose or not
then?
If having them on the same server as other databases is such a big concern,
why not detach them from that server and attach them to a different one?
AutoClose - How much resources are saved
We have a server with a number of relatively infrequently used DBs, one or
two are more archival.
If I set them to Autclose, any idea as to resources released ?
I know the users will suffer when they do access them, but some never really
are used.
KlK, MCSE> I know the users will suffer when they do access them, but some never
really
> are used.
Then wouldn't it make more sense to detach them?|||Here you go:
http://msdn.microsoft.com/library/d...br />
1o4z.asp
Sincerely,
Anthony Thomas
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never real
ly
> are used.
> --
> KlK, MCSE|||note that every time you access EM and open the Database tree/folder, all th
e
databases that were closed will re-open and it also gets logged... I would
follow Aaron advice.
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never real
ly
> are used.
> --
> KlK, MCSE|||Well unfortunately, while I say they are really never used, the users would
not agree and want their data. So I guess we leave as is.
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never real
ly
> are used.
> --
> KlK, MCSE|||> Well unfortunately, while I say they are really never used, the users
would
> not agree and want their data. So I guess we leave as is.
So what is the big concern about moving them or having them autoclose or not
then?
If having them on the same server as other databases is such a big concern,
why not detach them from that server and attach them to a different one?
two are more archival.
If I set them to Autclose, any idea as to resources released ?
I know the users will suffer when they do access them, but some never really
are used.
KlK, MCSE> I know the users will suffer when they do access them, but some never
really
> are used.
Then wouldn't it make more sense to detach them?|||Here you go:
http://msdn.microsoft.com/library/d...br />
1o4z.asp
Sincerely,
Anthony Thomas
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never real
ly
> are used.
> --
> KlK, MCSE|||note that every time you access EM and open the Database tree/folder, all th
e
databases that were closed will re-open and it also gets logged... I would
follow Aaron advice.
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never real
ly
> are used.
> --
> KlK, MCSE|||Well unfortunately, while I say they are really never used, the users would
not agree and want their data. So I guess we leave as is.
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never real
ly
> are used.
> --
> KlK, MCSE|||> Well unfortunately, while I say they are really never used, the users
would
> not agree and want their data. So I guess we leave as is.
So what is the big concern about moving them or having them autoclose or not
then?
If having them on the same server as other databases is such a big concern,
why not detach them from that server and attach them to a different one?
AutoClose - How much resources are saved
We have a server with a number of relatively infrequently used DBs, one or
two are more archival.
If I set them to Autclose, any idea as to resources released ?
I know the users will suffer when they do access them, but some never really
are used.
--
KlK, MCSE> I know the users will suffer when they do access them, but some never
really
> are used.
Then wouldn't it make more sense to detach them?|||Here you go:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_1o4z.asp
Sincerely,
Anthony Thomas
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE|||note that every time you access EM and open the Database tree/folder, all the
databases that were closed will re-open and it also gets logged... I would
follow Aaron advice.
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE|||Well unfortunately, while I say they are really never used, the users would
not agree and want their data. So I guess we leave as is.
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE|||> Well unfortunately, while I say they are really never used, the users
would
> not agree and want their data. So I guess we leave as is.
So what is the big concern about moving them or having them autoclose or not
then?
If having them on the same server as other databases is such a big concern,
why not detach them from that server and attach them to a different one?
two are more archival.
If I set them to Autclose, any idea as to resources released ?
I know the users will suffer when they do access them, but some never really
are used.
--
KlK, MCSE> I know the users will suffer when they do access them, but some never
really
> are used.
Then wouldn't it make more sense to detach them?|||Here you go:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_1o4z.asp
Sincerely,
Anthony Thomas
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE|||note that every time you access EM and open the Database tree/folder, all the
databases that were closed will re-open and it also gets logged... I would
follow Aaron advice.
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE|||Well unfortunately, while I say they are really never used, the users would
not agree and want their data. So I guess we leave as is.
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE|||> Well unfortunately, while I say they are really never used, the users
would
> not agree and want their data. So I guess we leave as is.
So what is the big concern about moving them or having them autoclose or not
then?
If having them on the same server as other databases is such a big concern,
why not detach them from that server and attach them to a different one?
Monday, February 13, 2012
Auto populated field
Hello,
I have SQL Server Server Man Studio Express 2005, currently having a problem with an auto populated field.
Basically I have a number populated everytime a new asset is added to my database, but at the moment the firled does not increment by 1 as I would like it to. Seems to assign the same number as a item already in the database and I have to go into the back end and change it manually.
Anyone know how this is easly sorted, the asset ID is not the primary key. Just for your info at the moment 'Identity Spec' is set to 'NO'.
Many thanks, Andrew
How are you currently trying to auto populate the number if Identity is set to No?
--Uncle Pete
|||This is a good question, sorry i only recently started using this software as inherited it of another person so very new to it.|||I have just checked and it will not allow me to change the Identity Spec to 'Yes'? Default value or binding is set to ((0)).|||What is the datatype of the field?
If it is set to INT then you should be able to set the identity to yes.
|||Hello, yes it is set to INT but dosen't seem that I can alter it?
|||If you look at the identity field, you will see a plus sign, expand that. There you will be able to select Yes and set the seed value and increment. Be sure to set the seed value higher than what ever the highest current value is.
Also I see you said that default was set to (0), delete that, as it will conflict with the indentity.
|||Thank you for your reply but I still cannot change the Identity Spec field?
It is setup the follwoing way:
Allow Nulls: Yes
Datat Type: int
Value or binding: ((0))
Condensed data type: int
Deterministic: Yes
Indexable: Yes
Full text Spec: No
Identity Spec: NO
Size: 4
everything else set to No or blank.
Many thanks, Andrew
auto number?
Hi, I define a field as auto number field, usually how people deal with if
data is growing near 2147483647? Thanks.The simplest solution is to change the data type on the column to BigInt.
Thomas
"js" <js@.someone@.hotmail.com> wrote in message
news:%23BzLMyNUFHA.2096@.TK2MSFTNGP14.phx.gbl...
> Hi, I define a field as auto number field, usually how people deal with if
> data is growing near 2147483647? Thanks.
>
>|||Are you talking about access (--> autonumber) or SQl server (->identity)
Identities at sql serv can store up to
+-2^63-1 (9223372036854775807)
HTH, Jens SUessmeyer.
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:%23BzLMyNUFHA.2096@.TK2MSFTNGP14.phx.gbl...
> Hi, I define a field as auto number field, usually how people deal with if
> data is growing near 2147483647? Thanks.
>
>|||is it any archive function avaliable?
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:uPOzm2NUFHA.3344@.TK2MSFTNGP10.phx.gbl...
> The simplest solution is to change the data type on the column to BigInt.
>
> Thomas
> "js" <js@.someone@.hotmail.com> wrote in message
> news:%23BzLMyNUFHA.2096@.TK2MSFTNGP14.phx.gbl...
>|||That's big...
So I can just design it and forget it, assume not problem at all?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eE%23uB3NUFHA.4056@.TK2MSFTNGP15.phx.gbl...
> Are you talking about access (--> autonumber) or SQl server (->identity)
> Identities at sql serv can store up to
> +-2^63-1 (9223372036854775807)
> HTH, Jens SUessmeyer.
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:%23BzLMyNUFHA.2096@.TK2MSFTNGP14.phx.gbl...
>|||As far as you wont reach 9223372036854775807 and your client app can handle
that, no.
Jens Suessmeyer.
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:e1MXhCOUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> That's big...
> So I can just design it and forget it, assume not problem at all?
>
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:eE%23uB3NUFHA.4056@.TK2MSFTNGP15.phx.gbl...
>|||I'm think of archive the data and reset the seed? what other people handle
that? Thanks.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ebWIfGOUFHA.1796@.TK2MSFTNGP15.phx.gbl...
> As far as you wont reach 9223372036854775807 and your client app can
> handle that, no.
> Jens Suessmeyer.
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:e1MXhCOUFHA.3140@.TK2MSFTNGP14.phx.gbl...|||I would not recommend that solution. I would instead recommend using a BigIn
t
for the data type of your identity column. If that table gets big, then by a
ll
means archive some of the data into a different table. But I would not chang
e
the identity values nor the seed when I archived the data.
Thomas
"js" <js@.someone@.hotmail.com> wrote in message
news:%23C3l1JOUFHA.3532@.TK2MSFTNGP09.phx.gbl...
> I'm think of archive the data and reset the seed? what other people handle
> that? Thanks.
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote i
n
> message news:ebWIfGOUFHA.1796@.TK2MSFTNGP15.phx.gbl...
>|||> I'm think of archive the data and reset the seed? what other people handle
> that? Thanks.
What? How often do you plan on archiving? If you have three archives and
all have a row where idNumber = 1, which one is the one you're looking for?
If you are building a system where you really think you will need to reset
the IDENTITY value, perhaps you are going about this the wrong way
altogether.|||I agree with that now...
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%235ODlQOUFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I would not recommend that solution. I would instead recommend using a
>BigInt for the data type of your identity column. If that table gets big,
>then by all means archive some of the data into a different table. But I
>would not change the identity values nor the seed when I archived the data.
>
> Thomas
> "js" <js@.someone@.hotmail.com> wrote in message
> news:%23C3l1JOUFHA.3532@.TK2MSFTNGP09.phx.gbl...
>
data is growing near 2147483647? Thanks.The simplest solution is to change the data type on the column to BigInt.
Thomas
"js" <js@.someone@.hotmail.com> wrote in message
news:%23BzLMyNUFHA.2096@.TK2MSFTNGP14.phx.gbl...
> Hi, I define a field as auto number field, usually how people deal with if
> data is growing near 2147483647? Thanks.
>
>|||Are you talking about access (--> autonumber) or SQl server (->identity)
Identities at sql serv can store up to
+-2^63-1 (9223372036854775807)
HTH, Jens SUessmeyer.
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:%23BzLMyNUFHA.2096@.TK2MSFTNGP14.phx.gbl...
> Hi, I define a field as auto number field, usually how people deal with if
> data is growing near 2147483647? Thanks.
>
>|||is it any archive function avaliable?
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:uPOzm2NUFHA.3344@.TK2MSFTNGP10.phx.gbl...
> The simplest solution is to change the data type on the column to BigInt.
>
> Thomas
> "js" <js@.someone@.hotmail.com> wrote in message
> news:%23BzLMyNUFHA.2096@.TK2MSFTNGP14.phx.gbl...
>|||That's big...
So I can just design it and forget it, assume not problem at all?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eE%23uB3NUFHA.4056@.TK2MSFTNGP15.phx.gbl...
> Are you talking about access (--> autonumber) or SQl server (->identity)
> Identities at sql serv can store up to
> +-2^63-1 (9223372036854775807)
> HTH, Jens SUessmeyer.
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:%23BzLMyNUFHA.2096@.TK2MSFTNGP14.phx.gbl...
>|||As far as you wont reach 9223372036854775807 and your client app can handle
that, no.
Jens Suessmeyer.
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:e1MXhCOUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> That's big...
> So I can just design it and forget it, assume not problem at all?
>
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:eE%23uB3NUFHA.4056@.TK2MSFTNGP15.phx.gbl...
>|||I'm think of archive the data and reset the seed? what other people handle
that? Thanks.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ebWIfGOUFHA.1796@.TK2MSFTNGP15.phx.gbl...
> As far as you wont reach 9223372036854775807 and your client app can
> handle that, no.
> Jens Suessmeyer.
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:e1MXhCOUFHA.3140@.TK2MSFTNGP14.phx.gbl...|||I would not recommend that solution. I would instead recommend using a BigIn
t
for the data type of your identity column. If that table gets big, then by a
ll
means archive some of the data into a different table. But I would not chang
e
the identity values nor the seed when I archived the data.
Thomas
"js" <js@.someone@.hotmail.com> wrote in message
news:%23C3l1JOUFHA.3532@.TK2MSFTNGP09.phx.gbl...
> I'm think of archive the data and reset the seed? what other people handle
> that? Thanks.
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote i
n
> message news:ebWIfGOUFHA.1796@.TK2MSFTNGP15.phx.gbl...
>|||> I'm think of archive the data and reset the seed? what other people handle
> that? Thanks.
What? How often do you plan on archiving? If you have three archives and
all have a row where idNumber = 1, which one is the one you're looking for?
If you are building a system where you really think you will need to reset
the IDENTITY value, perhaps you are going about this the wrong way
altogether.|||I agree with that now...
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%235ODlQOUFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I would not recommend that solution. I would instead recommend using a
>BigInt for the data type of your identity column. If that table gets big,
>then by all means archive some of the data into a different table. But I
>would not change the identity values nor the seed when I archived the data.
>
> Thomas
> "js" <js@.someone@.hotmail.com> wrote in message
> news:%23C3l1JOUFHA.3532@.TK2MSFTNGP09.phx.gbl...
>
auto number via a query
Hello,
I am wondering if somone could provide me with a sample SELECT for an
auto number query. For example I have a table called People with two columns
first_name, and last_name. There isn't a unique id to correspond with the
table but would like to dynamically make one during the return of the query.
So if there was 5 rows in the table it would return
1 John Alpha
2 John Beta
3 John Cat
4 John Delta
5 John Echo
Where the query was an order by last_name. Thanks in advance.
Jake"Jake Smythe" <someone@.microsoft.com> wrote in message
news:O5zxrnZmGHA.4100@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I am wondering if somone could provide me with a sample SELECT for an
> auto number query. For example I have a table called People with two
> columns first_name, and last_name. There isn't a unique id to correspond
> with the table but would like to dynamically make one during the return of
> the query. So if there was 5 rows in the table it would return
> 1 John Alpha
> 2 John Beta
> 3 John Cat
> 4 John Delta
> 5 John Echo
> Where the query was an order by last_name. Thanks in advance.
Something like this will work, you'll need to add the first name to the
comparison also.
CREATE TABLE People (FirstName VARCHAR(100), LastName VARCHAR(100))
INSERT INTO People VALUES('John','Alpha')
INSERT INTO People VALUES('John','Beta')
INSERT INTO People VALUES('John','Cat')
INSERT INTO People VALUES('John','Delta')
INSERT INTO People VALUES('John','Echo')
SELECT *, (SELECT COUNT(*) FROM People AS P1 WHERE P1.LastName <=
People.LastName) FROM People
ORDER BY LastName, FirstName
DROP TABLE People
> Jake
>|||While this kludge may work for the immediate need, you must be warned that t
here is no certainly that the order will be static. Each time the query exec
utes, the order may be different. And if will have problems with perfectly d
uplicate names. Try adding duplicate names and watch what happens...
SELECT
( SELECT sum(1)
FROM People p
WHERE ( p.LastName + p.FirstName ) <= ( p.LastName + p.FirstName )
) AS rownum
, p2.LastName
, p2.FirstName
FROM People p2
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Michael C" <nospam@.nospam.com> wrote in message news:%23j%23u8yZmGHA.4052@.TK2MSFTNGP05.phx
.gbl...
> "Jake Smythe" <someone@.microsoft.com> wrote in message
> news:O5zxrnZmGHA.4100@.TK2MSFTNGP05.phx.gbl...
>
> Something like this will work, you'll need to add the first name to the
> comparison also.
>
> CREATE TABLE People (FirstName VARCHAR(100), LastName VARCHAR(100))
> INSERT INTO People VALUES('John','Alpha')
> INSERT INTO People VALUES('John','Beta')
> INSERT INTO People VALUES('John','Cat')
> INSERT INTO People VALUES('John','Delta')
> INSERT INTO People VALUES('John','Echo')
> SELECT *, (SELECT COUNT(*) FROM People AS P1 WHERE P1.LastName <=
> People.LastName) FROM People
> ORDER BY LastName, FirstName
> DROP TABLE People
>
>
>
>|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eQ0TcDamGHA.4076@.TK2MSFTNGP05.phx.gbl...
While this kludge may work for the immediate need, you must be warned that
there is no certainly that the order will be static. Each time the query
executes, the order may be different. And if will have problems with
perfectly duplicate names. Try adding duplicate names and watch what
happens...
I was going to add a warning that this method wasn't perfect but I had to
race off so just hit send. Of course the value will change if the order
changes :-) As for duplicates you'd just need to use the fields that make up
the primary key, if rows are duplicated then maybe they should have the same
values anyway. Maybe performance of this method might be a problem?
Michael|||Guys thanks for the responses. It's fine if the order changes each time I am
just looking for a identifier at run time.
"Michael C" <nospam@.nospam.com> wrote in message
news:e0x5fZbmGHA.4064@.TK2MSFTNGP02.phx.gbl...
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eQ0TcDamGHA.4076@.TK2MSFTNGP05.phx.gbl...
> While this kludge may work for the immediate need, you must be warned that
> there is no certainly that the order will be static. Each time the query
> executes, the order may be different. And if will have problems with
> perfectly duplicate names. Try adding duplicate names and watch what
> happens...
> I was going to add a warning that this method wasn't perfect but I had to
> race off so just hit send. Of course the value will change if the order
> changes :-) As for duplicates you'd just need to use the fields that make
> up the primary key, if rows are duplicated then maybe they should have the
> same values anyway. Maybe performance of this method might be a problem?
> Michael
>
I am wondering if somone could provide me with a sample SELECT for an
auto number query. For example I have a table called People with two columns
first_name, and last_name. There isn't a unique id to correspond with the
table but would like to dynamically make one during the return of the query.
So if there was 5 rows in the table it would return
1 John Alpha
2 John Beta
3 John Cat
4 John Delta
5 John Echo
Where the query was an order by last_name. Thanks in advance.
Jake"Jake Smythe" <someone@.microsoft.com> wrote in message
news:O5zxrnZmGHA.4100@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I am wondering if somone could provide me with a sample SELECT for an
> auto number query. For example I have a table called People with two
> columns first_name, and last_name. There isn't a unique id to correspond
> with the table but would like to dynamically make one during the return of
> the query. So if there was 5 rows in the table it would return
> 1 John Alpha
> 2 John Beta
> 3 John Cat
> 4 John Delta
> 5 John Echo
> Where the query was an order by last_name. Thanks in advance.
Something like this will work, you'll need to add the first name to the
comparison also.
CREATE TABLE People (FirstName VARCHAR(100), LastName VARCHAR(100))
INSERT INTO People VALUES('John','Alpha')
INSERT INTO People VALUES('John','Beta')
INSERT INTO People VALUES('John','Cat')
INSERT INTO People VALUES('John','Delta')
INSERT INTO People VALUES('John','Echo')
SELECT *, (SELECT COUNT(*) FROM People AS P1 WHERE P1.LastName <=
People.LastName) FROM People
ORDER BY LastName, FirstName
DROP TABLE People
> Jake
>|||While this kludge may work for the immediate need, you must be warned that t
here is no certainly that the order will be static. Each time the query exec
utes, the order may be different. And if will have problems with perfectly d
uplicate names. Try adding duplicate names and watch what happens...
SELECT
( SELECT sum(1)
FROM People p
WHERE ( p.LastName + p.FirstName ) <= ( p.LastName + p.FirstName )
) AS rownum
, p2.LastName
, p2.FirstName
FROM People p2
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Michael C" <nospam@.nospam.com> wrote in message news:%23j%23u8yZmGHA.4052@.TK2MSFTNGP05.phx
.gbl...
> "Jake Smythe" <someone@.microsoft.com> wrote in message
> news:O5zxrnZmGHA.4100@.TK2MSFTNGP05.phx.gbl...
>
> Something like this will work, you'll need to add the first name to the
> comparison also.
>
> CREATE TABLE People (FirstName VARCHAR(100), LastName VARCHAR(100))
> INSERT INTO People VALUES('John','Alpha')
> INSERT INTO People VALUES('John','Beta')
> INSERT INTO People VALUES('John','Cat')
> INSERT INTO People VALUES('John','Delta')
> INSERT INTO People VALUES('John','Echo')
> SELECT *, (SELECT COUNT(*) FROM People AS P1 WHERE P1.LastName <=
> People.LastName) FROM People
> ORDER BY LastName, FirstName
> DROP TABLE People
>
>
>
>|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eQ0TcDamGHA.4076@.TK2MSFTNGP05.phx.gbl...
While this kludge may work for the immediate need, you must be warned that
there is no certainly that the order will be static. Each time the query
executes, the order may be different. And if will have problems with
perfectly duplicate names. Try adding duplicate names and watch what
happens...
I was going to add a warning that this method wasn't perfect but I had to
race off so just hit send. Of course the value will change if the order
changes :-) As for duplicates you'd just need to use the fields that make up
the primary key, if rows are duplicated then maybe they should have the same
values anyway. Maybe performance of this method might be a problem?
Michael|||Guys thanks for the responses. It's fine if the order changes each time I am
just looking for a identifier at run time.
"Michael C" <nospam@.nospam.com> wrote in message
news:e0x5fZbmGHA.4064@.TK2MSFTNGP02.phx.gbl...
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eQ0TcDamGHA.4076@.TK2MSFTNGP05.phx.gbl...
> While this kludge may work for the immediate need, you must be warned that
> there is no certainly that the order will be static. Each time the query
> executes, the order may be different. And if will have problems with
> perfectly duplicate names. Try adding duplicate names and watch what
> happens...
> I was going to add a warning that this method wasn't perfect but I had to
> race off so just hit send. Of course the value will change if the order
> changes :-) As for duplicates you'd just need to use the fields that make
> up the primary key, if rows are duplicated then maybe they should have the
> same values anyway. Maybe performance of this method might be a problem?
> Michael
>
Auto Number SQL Server 2000
Hi all,
How can i generate auto Number in SQL Server 2000,like AutoNumber Datatype in MsAccess...
Thanx in advance
Sajjad
UseIDENTITY
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp
|||Hi
Here is how you can do that with WebMatrix, from theGuided Tour
Using IsIdentity
Auto Number Sequence
How can I create a number sequence starting at a certain number and continue on for the number of records I have.
For example I have 3000 records in my table and a field named I created called RecordId which I'd like to start at number 1 and goto 3000 (or maybe even start at 9000 and goto 12000 or however many records there are).
In my pseudo SQL code Im guessing it would be something like...
select * from Incident
update Incident
set RecordId( i=9000; i<=Number of Records in Table; i++)
Whats the easiest way to do this?
--1.
Alter TABLE Incident
DROP COLUMN RecordId
--2.
Alter TABLE Incident
ADD RecordId int IDENTITY(3000,1)--if you want start at 3000
--or 3.
Alter TABLE Incident
ADD RecordId int IDENTITY(3000,1)--if you want start at 1
You can look up this infomation Alter Table and Alter Column from Books Online.
|||hi,
you can use the identity function
make use use of its parameter seed=3000 to start from 3000
here's the syntaxt
identity(seed, increament)
here's your sample code
use northwind
select IDENTITY(int, 3000,1) AS ID_Num,
lastname,firstname into #temp from employeesselect * from #temp
regards,
joey
|||A potentially more expressive function you could use in place of identity is the row_number.select row_number over( partiton by ... order by ...) + startin_number,
One caveat of Row_Number implementation on SqlServer is that SS05 doesn't allow an empty order by, nor does it allow to sort on a constant.|||
hi if any body can tell me the solutions of my prob.
i have a table in Access in which prid is auto number , but the prob is that these prid is not in sequnce i.e, some number missing, like that after 8 its 12 , how will i arange it with delete data etc
|||If you have issues with this column only within this table, you can delete this column and recreate another Auto number field for your prid column in Access.|||You can do this by combining a variable with an update statement.
Here's an example. If you run this whole set of code, the records in the table end up with values 1,2,3,4.....
-- create a simple test table
create table testcounter (thefld int)
go
-- add four rows, all with the same value
insert into testcounter values (1)
insert into testcounter values (1)
insert into testcounter values (1)
insert into testcounter values (1)
go
-- Declare and initialize an int variable
DECLARE @.thecount int
set @.thecount = 0
-- update the table using the variable.
update testcounter SET
@.thecount = @.thecount + 1,
thefld = @.thecount
from testcounter
go
-- list the results
select * from testcounter
Auto Number Sequence
How can I create a number sequence starting at a certain number and continue on for the number of records I have.
For example I have 3000 records in my table and a field named I created called RecordId which I'd like to start at number 1 and goto 3000 (or maybe even start at 9000 and goto 12000 or however many records there are).
In my pseudo SQL code Im guessing it would be something like...
select * from Incident
update Incident
set RecordId( i=9000; i<=Number of Records in Table; i++)
Whats the easiest way to do this?
--1.
Alter TABLE Incident
DROP COLUMN RecordId
--2.
Alter TABLE Incident
ADD RecordId int IDENTITY(3000,1)--if you want start at 3000
--or 3.
Alter TABLE Incident
ADD RecordId int IDENTITY(3000,1)--if you want start at 1
You can look up this infomation Alter Table and Alter Column from Books Online.
|||hi,
you can use the identity function
make use use of its parameter seed=3000 to start from 3000
here's the syntaxt
identity(seed, increament)
here's your sample code
use northwind
select IDENTITY(int, 3000,1) AS ID_Num,
lastname,firstname into #temp from employeesselect * from #temp
regards,
joey
|||A potentially more expressive function you could use in place of identity is the row_number.select row_number over( partiton by ... order by ...) + startin_number,
One caveat of Row_Number implementation on SqlServer is that SS05 doesn't allow an empty order by, nor does it allow to sort on a constant.|||
hi if any body can tell me the solutions of my prob.
i have a table in Access in which prid is auto number , but the prob is that these prid is not in sequnce i.e, some number missing, like that after 8 its 12 , how will i arange it with delete data etc
|||If you have issues with this column only within this table, you can delete this column and recreate another Auto number field for your prid column in Access.|||You can do this by combining a variable with an update statement.
Here's an example. If you run this whole set of code, the records in the table end up with values 1,2,3,4.....
-- create a simple test table
create table testcounter (thefld int)
go
-- add four rows, all with the same value
insert into testcounter values (1)
insert into testcounter values (1)
insert into testcounter values (1)
insert into testcounter values (1)
go
-- Declare and initialize an int variable
DECLARE @.thecount int
set @.thecount = 0
-- update the table using the variable.
update testcounter SET
@.thecount = @.thecount + 1,
thefld = @.thecount
from testcounter
go
-- list the results
select * from testcounter
Auto Number PROBLEM
I have a table namely Product with the following identity modifiers:-
Column- "Num"
Identity-Yes
Identity Seed-1
Identity Increment-1
My problem is everytime i delete a row in the table Product, the Num column after the deleted row will not automatically replace the deleted Num column's data.
Let say:
Num Name
30 Apple
31 Orange
32 Pineapple
If I delete row with Num 31, the Num column after the deleted row will not change to 31 but remain 32.
Num Name
30 Apple
32 Pineapple
What can I do to ensure that the Num column (Num 32) will change to Num 31?
Thank you.what you could do is think very hard about what you seem to want to do
suppose you have an Orders table, which contains customer orders for all products you've sold
if you renumber the products in the products table, you must therefore renumber the Orders table too
this could shut your database down for a few hours
and why? what is the benefit?
autonumbers should never be reassigned
it's not as though you're going to run out of numbers
you could realistically set your identity seed at 183,527,426 and your increment at 3,743 and you would still not run out of numbers for several centuries|||you can add a new column to your table
RowNumber
Id Fruit RowNumber
31 Apple 100
32 Banana 101
33 Pineapple 102
If you add a new fruit
RowNumber = Count(*) of the table
If you delete a fruit
you update RowNumber=RowNumber-1 for all the fruits over the
RowNumber of the deleted fruit
If you delete the Banana
Update Table Fruits Set RowNumber=RowNumber-1
Where RowNumber>(Select RowNumber From Fruits Where Id=32)
Delete Fruits Where Id=32|||But some would say this is not a GREAT thing to do
'cause it's not performant.
But it will work|||i would say it is not a great thing to do because of (a) the impact it has on related tables, and (b) the total absence of ROI (http://searchwebservices.techtarget.com/sDefinition/0,,sid26_gci214270,00.html)
also, not only do you have to update the fruits table, you also have to update the desserts table, the pie table, and any other table which has foreign keys to the fruit table
and i repeat: what exactly is the benefit of doing it?
if it is important that there be no gap in numbers, then i strongly suggest that an autonumber is the wrong design choice|||in my case there is no impact on other tables
because I don't touch the ID number
but the new independant column ROWNUMBER !!!
Column- "Num"
Identity-Yes
Identity Seed-1
Identity Increment-1
My problem is everytime i delete a row in the table Product, the Num column after the deleted row will not automatically replace the deleted Num column's data.
Let say:
Num Name
30 Apple
31 Orange
32 Pineapple
If I delete row with Num 31, the Num column after the deleted row will not change to 31 but remain 32.
Num Name
30 Apple
32 Pineapple
What can I do to ensure that the Num column (Num 32) will change to Num 31?
Thank you.what you could do is think very hard about what you seem to want to do
suppose you have an Orders table, which contains customer orders for all products you've sold
if you renumber the products in the products table, you must therefore renumber the Orders table too
this could shut your database down for a few hours
and why? what is the benefit?
autonumbers should never be reassigned
it's not as though you're going to run out of numbers
you could realistically set your identity seed at 183,527,426 and your increment at 3,743 and you would still not run out of numbers for several centuries|||you can add a new column to your table
RowNumber
Id Fruit RowNumber
31 Apple 100
32 Banana 101
33 Pineapple 102
If you add a new fruit
RowNumber = Count(*) of the table
If you delete a fruit
you update RowNumber=RowNumber-1 for all the fruits over the
RowNumber of the deleted fruit
If you delete the Banana
Update Table Fruits Set RowNumber=RowNumber-1
Where RowNumber>(Select RowNumber From Fruits Where Id=32)
Delete Fruits Where Id=32|||But some would say this is not a GREAT thing to do
'cause it's not performant.
But it will work|||i would say it is not a great thing to do because of (a) the impact it has on related tables, and (b) the total absence of ROI (http://searchwebservices.techtarget.com/sDefinition/0,,sid26_gci214270,00.html)
also, not only do you have to update the fruits table, you also have to update the desserts table, the pie table, and any other table which has foreign keys to the fruit table
and i repeat: what exactly is the benefit of doing it?
if it is important that there be no gap in numbers, then i strongly suggest that an autonumber is the wrong design choice|||in my case there is no impact on other tables
because I don't touch the ID number
but the new independant column ROWNUMBER !!!
Subscribe to:
Posts (Atom)