Identy Number

I am using an Identy number to generate a Unique ID for records.
The process is
a. Save header record (identy ID created)
b. Retrieve Identy No using a select statement via odbc
c. Dave many data recors with Identy Number as reference to the Header
Record.
How can i retrieve the Identy No with a select statement.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004Use the SCOPE_IDENTITY() function.
--
David Portas
SQL Server MVP
--|||To expand David's response... Use a select that returns the identity ie
select @.@.scope_identity
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:OHc5aqz8DHA.2308@.TK2MSFTNGP11.phx.gbl...
> I am using an Identy number to generate a Unique ID for records.
> The process is
> a. Save header record (identy ID created)
> b. Retrieve Identy No using a select statement via odbc
> c. Dave many data recors with Identy Number as reference to the Header
> Record.
> How can i retrieve the Identy No with a select statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004
>

Identy Number

I am using an Identy number to generate a Unique ID for records.
The process is
a. Save header record (identy ID created)
b. Retrieve Identy No using a select statement via odbc
c. Dave many data recors with Identy Number as reference to the Header
Record.
How can i retrieve the Identy No with a select statement.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004Use the SCOPE_IDENTITY() function.
David Portas
SQL Server MVP
--|||To expand David's response... Use a select that returns the identity ie
select @.@.scope_identity
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:OHc5aqz8DHA.2308@.TK2MSFTNGP11.phx.gbl...
> I am using an Identy number to generate a Unique ID for records.
> The process is
> a. Save header record (identy ID created)
> b. Retrieve Identy No using a select statement via odbc
> c. Dave many data recors with Identy Number as reference to the Header
> Record.
> How can i retrieve the Identy No with a select statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004
>sql

Identity problem. (Need generate on subscriber new identity values)

If you change the publication to queued updating, use
automatic identity range management and don't run the
queue reader, you should achieve what you require.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
But where in that case subcribers updates will be stored? On distributor?
Is it known bug with incorrect handling identity values in replications? Or
it is my own issue?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:050b01c50853$c6e94010$a601280a@.phx.gbl...
> If you change the publication to queued updating, use
> automatic identity range management and don't run the
> queue reader, you should achieve what you require.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||The subscriber updates will be held on the queue table at
the subscriber, or you could remove the subscriber
triggers. It is assumed that subscribers are read only in
normal circumstances, so any issues regarding data
changes at a subscriber and problems with identities on
the subscriber are not really catered for. Identities are
only coded for if the subscriber uses immediate updating
or queued updating.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Identity Increment

Hello, I need some help writing a script to generate Identity keys. I cannot use the row number generator because I would like to start the identity at a package level variable. Is this possible?
Thank you in advance

I just find this using a web search engine:

http://www.sqljunkies.com/WebLog/sqlbi/archive/2005/05/30/15684.aspx

I know that has been also discussed in this forum; I recommned you tu use the search functionality of the forum

|||http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/

identity in the resultset

i want to generate a column in the resultset
that works like an identity column.
one way i thought is creating a temp table with an identity column
and inserting resultset to that table and select again.
is there any other way that works serverside?
thanks...Using a temp table or a table variable *is* done on the server.
Is there a special reason for using identity? Are you trying to sort or rank
the rows in the result-set? If so, there are built-in functions in SQL2005
and several custom options for SQL2000.
ML|||Always specify SQL Server version you are using when posting questions.
With SQL 2005, you can use ROW_NUMBER() as described in the Books Online.
Another method with older versions is with a subquery that includes a unique
column(s) and the same criteria as the main table query:
SELECT MyData,
(SELECT COUNT(*) + 1 FROM MyTable t2 WHERE t2.Id < t1.Id) As
MyRowNumber,
FROM MyTable t1
ORDER BY Id
Personally, I'd assign the numbers in the client application.
Hope this helps.
Dan Guzman
SQL Server MVP
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:OiAOowO9FHA.472@.TK2MSFTNGP15.phx.gbl...
>i want to generate a column in the resultset
> that works like an identity column.
> one way i thought is creating a temp table with an identity column
> and inserting resultset to that table and select again.
> is there any other way that works serverside?
> thanks...
>
>|||i wanted to say , is there any other way that works serverside too?
can you tell about custom options for SQL2000?
thanks
"ML" <ML@.discussions.microsoft.com> wrote in message
news:02F1360F-284E-4DCD-A8FF-A6EDD07825CD@.microsoft.com...
> Using a temp table or a table variable *is* done on the server.
> Is there a special reason for using identity? Are you trying to sort or
> rank
> the rows in the result-set? If so, there are built-in functions in SQL2005
> and several custom options for SQL2000.
>
> ML|||http://www.aspfaq.com/2427
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:uUIfc6O9FHA.3636@.TK2MSFTNGP09.phx.gbl...
>i wanted to say , is there any other way that works serverside too?
> can you tell about custom options for SQL2000?
> thanks
>
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:02F1360F-284E-4DCD-A8FF-A6EDD07825CD@.microsoft.com...
>|||
> Another method with older versions is with a subquery that includes a
> unique
> column(s) and the same criteria as the main table query:
> SELECT MyData,
> (SELECT COUNT(*) + 1 FROM MyTable t2 WHERE t2.Id < t1.Id) As
> MyRowNumber,
> FROM MyTable t1
> ORDER BY Id
thanks for that.|||Dan posted one, a few can be found here:
Row-numbering:
http://www.aspfaq.com/show.asp?id=2427
Paging:
http://www.aspfaq.com/show.asp?id=2120
ML

Identity Columns - Design Question

Is it a good practice to use identty columns as primary keys? or should
we generate our own keys using a seed table?
Does using Identity give any performance advantages?
Thank you,Hi

> Is it a good practice to use identty columns as primary keys? or should
> we generate our own keys using a seed table?
If you don't care about gaps you can use an IDENTITY propertry as a
PFRIMARY KEY , just thinking about your business requirements which I don't
know , actuallty there was lots of discussions about this in this newsgpoup

> Does using Identity give any performance advantages?
Yes, it is , especially if it has a CLUSTERED INDEX .
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1149591940.115554.74460@.y43g2000cwc.googlegroups.com...
>
> Is it a good practice to use identty columns as primary keys? or should
> we generate our own keys using a seed table?
> Does using Identity give any performance advantages?
> Thank you,
>|||There is a slight performance gain on large tables.
But I don't use them anymore due to re-design issues on upgrades and third
party ware and a number of other integrations.
"Uri Dimant" wrote:

> Hi
>
> If you don't care about gaps you can use an IDENTITY propertry as a
> PFRIMARY KEY , just thinking about your business requirements which I don
't
> know , actuallty there was lots of discussions about this in this newsgpo
up
>
>
> Yes, it is , especially if it has a CLUSTERED INDEX .
>
>
> "S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
> news:1149591940.115554.74460@.y43g2000cwc.googlegroups.com...
>
>|||This is an altered version of an older response on the same subject, with
emphasis on fundamentals:
A key is an attribute or set of attributes that can uniquely identify an
entity in the conceptual model. At the corresponding logical level, a column
or set of columns that can uniquely identify a row in a table is defined as
a key. In reality, an entity may have more than one candidate that provides
such identification ( hence are called candidate keys ), however due to
obvious reasons one of those candidate keys should be treated as primal (
hence called primary key ) based on practical considerations. Such
considerations should include:
i. Familiarity - meaningful to the user.
ii. Stability - non-volatile, should not be altered frequently.
iii. Simplicity - so that queries are easy to express and optimize.
iv. Irreducibility - no proper subset of the key should uniquely identify a
row in the table. ( some treat this also as a derivation to 2NF, which
prohibits partial key dependencies )
Having stated the above guidelines, regardless of the complexity in your
business model & data requirements, an intelligent database design can find
a middle ground by trading off certain characteristics without compromising
the integrity of the data. And one such compromise is the logical surrogate
key which usually defies the characteristic of familiarity but provides
excellent stability and simplicity. Simple and stable keys are often
recommended over composite/volatile keys esp. when they are used in
referential integrity constraints.
http://www.datraverse.com/technology/sql.php
One of the fundamental principles guiding relational design, is a concept
called physical data independence, which can be simply put as: the users
must be presented with a logical view of the data & need not deal with the
physical implementation and storage details. That is primarily why we deal
with tables, columns, keys etc ( which is logical) instead of files, disk
indices, row positions etc (which is physical). Thus you will see why and
how a surrogate in a clean logical model, should also conform to physical
data independence.
Features like identity, GUID etc provide uniqueness, but there are questions
regarding whether they provide sufficient data independence esp. with
ordering of identity column values at the time of generation. Some aspects
of this issue were addressed here:
http://blogs.msdn.com/sqltips/default.aspx?p=2
The non-updateability of identity columns arguably invalidates relational
assignments and therefore the information principle. Since the internal
mechanism that generates identity values are mostly unknown to the user,
practical data design and quality guidelines involving data verification and
validation cannot be applied to such DBMS generated values either.
At the logical level, performance should be least of your considerations in
key selection, since the primary goal of keys is entity identification that
preserves data integrity. Performance is purely dependent upon the physical
implementation of a database. Obviously values generated and tied closely
and directly to the physical model tend to be better performing. Also values
that are smaller in physical size can improve certain query performance due
to faster disk access i/o etc. Given that identity values are of numeric
datatypes and often smaller in physical size, queries utilizing identity
columns tend to have the same performance benefits.
Anith|||I have read ("i have never tried it out myself") -- Identity values cannot
be used in merge replication.
Best Regards
Vadivel
http://vadivel.blogspot.com
"S Chapman" wrote:

>
> Is it a good practice to use identty columns as primary keys? or should
> we generate our own keys using a seed table?
> Does using Identity give any performance advantages?
> Thank you,
>|||If you have no natural primary key then using the IDENTITY property is
significantly better than roling your own because you won't get the same
contention (blocking and serialisation) you get with other roled methods.
IDENTITY does give a performance advantage - there is no locking to get the
next number.
The one divantage is that you can get gaps because the calculation for
next number is not transaction aware but that doesn't matter in most cases
i've seen in business.
You really need to PRIMARY KEY using your natural key and then use a
surrogate key (the IDENTIITY) everywhere else - in sps, joins etc... the
primary key is then just meta data.
eg.
create table sector (
sector_name nvarchar(100) not null constraint pk_sector primary
key nonclustered,
id int not null constraint sk_sector unique clustered
)
create table sector_coverage (
sector_id int not null references sector( id ),
individual_id int not null references individual( id )
)
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1149591940.115554.74460@.y43g2000cwc.googlegroups.com...
>
> Is it a good practice to use identty columns as primary keys? or should
> we generate our own keys using a seed table?
> Does using Identity give any performance advantages?
> Thank you,
>|||Agreed Tony
"Tony Rogerson" wrote:

> If you have no natural primary key then using the IDENTITY property is
> significantly better than roling your own because you won't get the same
> contention (blocking and serialisation) you get with other roled methods.
> IDENTITY does give a performance advantage - there is no locking to get th
e
> next number.
> The one divantage is that you can get gaps because the calculation for
> next number is not transaction aware but that doesn't matter in most cases
> i've seen in business.
> You really need to PRIMARY KEY using your natural key and then use a
> surrogate key (the IDENTIITY) everywhere else - in sps, joins etc... the
> primary key is then just meta data.
> eg.
> create table sector (
> sector_name nvarchar(100) not null constraint pk_sector primary
> key nonclustered,
> id int not null constraint sk_sector unique clustered
> )
> create table sector_coverage (
> sector_id int not null references sector( id ),
> individual_id int not null references individual( id )
> )
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a S
QL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
> news:1149591940.115554.74460@.y43g2000cwc.googlegroups.com...
>
>