Ad-hoc INSERT of new Identity Value

Hello,
I have a table with a primary key having IDENTITY (ID).
In case of an INSERT I want to fill another Column C1
with the new Identity value.
How can I achive this? By a Trigger maybe?
Thank you very much
JoachimTry
create table #t (c1 int not null identity(1,1),c2 char(1),c3 as c1)
insert into #t (c2) values ('a')
insert into #t (c2) values ('b')
insert into #t (c2) values ('c')
select * from #t
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:OysGn1beGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table with a primary key having IDENTITY (ID).
> In case of an INSERT I want to fill another Column C1
> with the new Identity value.
> How can I achive this? By a Trigger maybe?
> Thank you very much
> Joachim|||What would be the point of having redundant data in the table? Anyway, much
easier with a computed column than with a trigger, but this won't work if
your intention is to later update C1.
CREATE TABLE dbo.foo
(
id INT IDENTITY(1,1),
blat VARCHAR(32),
C1 AS id
);
INSERT dbo.foo(blat) SELECT 'bar';
SELECT * FROM dbo.foo;
DROP TABLE dbo.foo;
Or just use a view:
CREATE VIEW dbo.View_foo
AS
SELECT id, C1 = id
FROM dbo.foo;
But it's tough to provide a good answer if we have no idea. It's usually
better to describe the intended goal than to ask us how to accomplish the
solution you've already determined is the only way to do it...
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:OysGn1beGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table with a primary key having IDENTITY (ID).
> In case of an INSERT I want to fill another Column C1
> with the new Identity value.
> How can I achive this? By a Trigger maybe?
> Thank you very much
> Joachim

Adding Row Numbers to Flat File Source

Hi,

I was wondering if it was possible to add an identity column to a flat file data source as it is being processed in a data flow. I need to know the record number of each row in the file. Can this be done with the derived column task or is it possible to return the value of row count on each row of the data?

Any help on this is greatly recieved.

Cheers,

Grant

Generating Surrogate Keys
(http://www.sqlis.com/default.aspx?37)

-Jamie

|||

You may have more than one option here; I would try with a script task in control flow that uses a variable to generate and adds it as a column to the pipeline.

Create a SSIS variable Int32. eg MyIdentityColSeed that is set to zero.

Then, in your data flow, after the flat file source create a script task with 1 output column (named for example MyIdentityCol) and use and script like this:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Row.MyIdentityCol = Variables.MyIdentityColSeed + Counter

Counter += 1

End Sub

Even when the code is very simple; I did not tested it, so take the time to debug it.

|||Rafael, Jamie,

Thanks for both your posts on this matter. I'm looking into Rafael's suggestion at present and see that the only way to access variables in the data flow section is within the PostExecute phase. That problem with this is that it doesn't seem to update the variable as i move through the rows in the dataflow. Is this because of how SSIS has been developped. Can you not set a variable anywhere else within the script. I'll look at Jamie's suggestion once this one has been exhausted.

Thanks,

Grant|||

The Pre and Post execute limitation is just that. It also makes more sense as you only need to read and write the variables pre/post, which is faster than accessing them for every row. Use local variables in the actual main execution code.

You may want to take a quick look at the Row Number Transformation, it may do exactly what you want with seed and increment settings already- http://www.sqlis.com/default.aspx?93

|||Got Rafaels suggestion to work. Didn't need to use a variable in the end just a counter that was initialized on the preexecute event which was incremented for each row. Many thanks for everyone's help.

Yet more new things learned today :) .

Thank you all.

Grant|||

Grant Swan wrote:

Rafael, Jamie,

Thanks for both your posts on this matter. I'm looking into Rafael's suggestion at present and see that the only way to access variables in the data flow section is within the PostExecute phase. That problem with this is that it doesn't seem to update the variable as i move through the rows in the dataflow. Is this because of how SSIS has been developped. Can you not set a variable anywhere else within the script. I'll look at Jamie's suggestion once this one has been exhausted.

Thanks,

Grant

Errr...both Rafael's and my suggestions were exactly the same

Glad you got it working anyhow!

-Jamie

|||

Grant,

I think Jamie is right; the approaches are identical. I do not keep records of web resources I used; so I was unable, like Jamie, to post a link to the original sorce. I guess a owe some credits to author of the script

Rafael Salas

|||Jamie,

Sorry, I'd had a previous link open from the same website and was looking at this which had some resemblence to what i wanted to do:

http://www.sqlis.com/default.aspx?93

As apposed to the link i'd clicked from your post. Apologies and i'll mark your post as an answer also.

Its been a bad day and i've got so many SSIS things in my head :)

Cheers,

Grant|||

Grant Swan wrote:

Jamie,

Sorry, I'd had a previous link open from the same website and was looking at this which had some resemblence to what i wanted to do:

http://www.sqlis.com/default.aspx?93

As apposed to the link i'd clicked from your post. Apologies and i'll mark your post as an answer also.

Its been a bad day and i've got so many SSIS things in my head :)

Cheers,

Grant

Ha ha. Cool. I'm not bothered about getting my "answer count" up, as long as the thread has at least one reply marked as the answer. thanks anyway tho!

-Jamie

Adding Identity column to existing table.

I am trying to add a server managed ranged Identity column to an existing
table in my database and then have SQL Server provide the management of
subscribers ranges.
I have had success with this using a test database (a book walk-though
example) but now I want to add this to a real table in my production
environment (actually a copy of the database) and then test with several
Pocket PC Sql Server CE subscribers using merge replication.
I added a column named MyIdentity as a 'int' type and choose the 'Not for
Replication' option.
I have it set up with horizonal filtering using suser_sname() which is
correct giving each subscriber only the appropriate records.
The problem is that under 'Articles', 'Identity Ranges', the settings are
disabled as if they are not an option with the article I added the
MyIdentity column to.
Any ideas as to how to get this working. Your help is greatly appreciated.
Bill Mitchell
Bill,
when you create the publication there is a checkbox which gives the option
to 'automatically assign and maintain a unique identity range for each
subscription'. If this is not checked when you create the subscription then
it is assumed you will manually set the ranges and the option is then greyed
out. Please can you recreate the publication to check this is the case.
BTW setting it manually can also be useful (see
http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Paul,
Thanks, I think maybe I understand what was wrong earier. I tried your
suggestion but there was no tab on the article properties dialog as I was
creating the publication. Finally I added in a column as 'int' with the
Replicated set to "Yes". Then when I created the publication, the tab was
there and allowed me to choose for the server to manage the ranges.
If I understand correctly (I'm quite new to SQL Server), each table in a
merge replication solution in which the subscribers will be adding records
will need a single "int" column with the replication set to "Yes" prior to
creating the publication. Then during the creation of the publication, I
need to make sure and check for each of these tables (articles) ranges to be
managed. Is this correct and am I missing anything in this statement?
Thanks again,
Bill
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uKT3J1IREHA.2408@.tk2msftngp13.phx.gbl...
> Bill,
> when you create the publication there is a checkbox which gives the option
> to 'automatically assign and maintain a unique identity range for each
> subscription'. If this is not checked when you create the subscription
then
> it is assumed you will manually set the ranges and the option is then
greyed
> out. Please can you recreate the publication to check this is the case.
> BTW setting it manually can also be useful (see
> http://www.mssqlserver.com/replicati...h_identity.asp).
> HTH,
> Paul Ibison
>
|||Bill,
for merge you need the column specified as Yes Not For Replication on the
publisher. When creating the publication there is the checkbox to have SQL
Server manage identity ranges we talked about. On the subscriber there
should be the same Identity Yes Not For Replication attribute. This allows
the replication process to do identity inserts. The ranges avoid clashes. If
you are not going to have SQL Server manage the ranges then you can manually
do it yourself. This is required if you are doing a nosync subscription ie
the subscriber already has the data so the initialization process won't send
it down. There are details on Michael Hotek's site of nice algorithms to
avoid identity clashes (http://www.mssqlserver.com/replication/).
HTH,
Paul Ibison
|||Thanks for all of your help.
Bill
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eMO9NgMREHA.3420@.TK2MSFTNGP11.phx.gbl...
> Bill,
> for merge you need the column specified as Yes Not For Replication on the
> publisher. When creating the publication there is the checkbox to have SQL
> Server manage identity ranges we talked about. On the subscriber there
> should be the same Identity Yes Not For Replication attribute. This allows
> the replication process to do identity inserts. The ranges avoid clashes.
If
> you are not going to have SQL Server manage the ranges then you can
manually
> do it yourself. This is required if you are doing a nosync subscription ie
> the subscriber already has the data so the initialization process won't
send
> it down. There are details on Michael Hotek's site of nice algorithms to
> avoid identity clashes (http://www.mssqlserver.com/replication/).
> HTH,
> Paul Ibison
>
|||Never mind...I found it.
It's when I add an article I needed to go into this article's properties to set it before generating the snapshot.
Vince
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.

Adding Identity Column to BIG table

I've got a table with 36+ million rows. I've been asked to modify the
table and add in an identity column. The code I used caused SQL to
lock up and it maxed out the log files. :)

The code I used is:

Begin Transaction
Alter Table ODS_DAILY_SALES_POS
ADD ODS_DAILY_SALES_POS_ID BigInt NOT NULL IDENTITY (1,1)
Commit

Is there a way to break up the code? Maybe only do a few million
records at a time? Or is there a way to do this without locking
anything up?

Thanks,
Jennifer>> I've been asked to modify the table and add in an identity column.
The code I used caused SQL to lock up and it maxed out the log files.
<<

Do you have any idea why anyone would want to do this in the first
place? The idiot does not seem to understand that IDENTITY has no
meaning in a data model?|||[posted and mailed, please reply in news]

Jennifer (jennifer1970@.hotmail.com) writes:
> I've got a table with 36+ million rows. I've been asked to modify the
> table and add in an identity column. The code I used caused SQL to
> lock up and it maxed out the log files. :)
> The code I used is:
> Begin Transaction
> Alter Table ODS_DAILY_SALES_POS
> ADD ODS_DAILY_SALES_POS_ID BigInt NOT NULL IDENTITY (1,1)
> Commit
> Is there a way to break up the code? Maybe only do a few million
> records at a time? Or is there a way to do this without locking
> anything up?

The alternative is to rename the table and all its constraints,
create the table and new with constraints, triggers and indexes
and insert the data into that table. You can then do a loop which
takes a reasonable number of rows at a time. That requires, however,
that you somehow, can identify which rows you have copied and which
you have not. An advice is to perform the loop on the clustered of
the table. Once data has been copied, move referencing foreigh keys
to point to the new table, and then drop the old table.

The advantage of this approach is that the strain on the log is less,
particularly, if you permit yourself to switch to simple recovery
while you are running the move.

Note: above I said that you should recreate triggers and indexes. It
may be a good idea to do that after the copying is completed, but
just don't forget it. (You should package everything in a script
and first test in database where the table is smaller.)

A variation is to bulk out the data, and then bulk it in when the
table has no indexes. If you have bulk_logged recovery, this load will
be very fast. Personally, I prefer to create the clustered index first,
before I load, since building the index takes its time too.

Yet a variation is to use SELECT INTO (with which you can use
the IDENTITY function). SELECT INTO is also minimally logged when
you have bulk_logged recovery.

Finally, you have set your colunm to bigint. With 36 million rows, you
have a long way to go, before you 31 bits become too few for you.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

adding identity column

I'm adding an Identity column to a table like this:
SELECT DISTINCT
T.NAME_LAST,
T.NAME_FIRST,
T.DOB,
RowID = IDENTITY(INT, 1, 1)
INTO #TempResultSet
FROM #TempPopulation T
LEFT JOIN #TempDateOfService B
ON B.KEY_ID = T.KEY_ID
ORDER BY NAME_LAST, NAME_FIRST
The problem I'm having is that the RowID isn't added to the table after it's
Ordered, which is what I want/need. Is there a way to do this, without
having to put the data into another temp table, where it's already ordered?
Thanks, AndreSELECT INTO does not guarantee order.
See this KB article:
http://support.microsoft.com/kb/273586
And yesterday's thread in this group:
"Row numbering unpredictable"
If you know the order by is NAME_LAST, NAME_FIRST then what difference does
it make what the identity value is? Never mind that if someone changes
their last name, suddenly your identity values are all messed up.
"Andre" <no@.spam.com> wrote in message
news:OGoht0oXGHA.3740@.TK2MSFTNGP03.phx.gbl...
> I'm adding an Identity column to a table like this:
> SELECT DISTINCT
> T.NAME_LAST,
> T.NAME_FIRST,
> T.DOB,
> RowID = IDENTITY(INT, 1, 1)
> INTO #TempResultSet
> FROM #TempPopulation T
> LEFT JOIN #TempDateOfService B
> ON B.KEY_ID = T.KEY_ID
> ORDER BY NAME_LAST, NAME_FIRST
> The problem I'm having is that the RowID isn't added to the table after
> it's
> Ordered, which is what I want/need. Is there a way to do this, without
> having to put the data into another temp table, where it's already
> ordered?
> Thanks, Andre
>|||Thanks Aaron. The reason I need the identity column in the order of
name_last, name_first is because I'm using the identity column in a sproc
that handles paging. I get my resultset, in this case it's 2000 records,
and I only want to return 25 records to the front-end. I need my data
ordered so I know which 25 to send in. I've got it working by putting my
ordered data into a temp table, then into another temp where I add the ident
column. It just seemed like an extra step, and an extra temp table.
Perhaps not the most optimal solution but it works.
Andre|||> Thanks Aaron. The reason I need the identity column in the order of
> name_last, name_first is because I'm using the identity column in a sproc
> that handles paging.
THERE ARE BETTER SOLUTIONS THAN THIS!
http://www.aspfaq.com/2120|||I'm basically doing exactly what the article showed, but just having to make
use of the temp table to order my data. My method is precisely what I've
seen in SQL Magazine previously.
Thanks Aaron.
Andre

Adding Identity

how can i change a column attribute using alter table in sql server 2005. i want to add an identity property to my column userid , which deosn't have an identity property defined when created.

i tried this.

alter table userlookup alter column userid int identity(1,1) not null

i got this error:

Incorrect syntax near the keyword 'identity'.

As far as I know (I might be wrong), you can't do that if you already have some values in that column.

Anyway, if you particular table's requirements permit this (stuff like foreign keys pointing to it, etc.), you can just drop it and re-create it anew, this time as an identity column.

|||Open management studio
go to the table you need to alter
open the Modify context menu
edit changes
now right click and use the last menu item: Generate script for changes

You can use this way to know scripting template for any change you need.

In that case you will see that SSMS will create a temp table, performs a bulk insert, drops the original table and then renames the temp table to the original name.
something like this:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_TRACE01
(
RowNumber int NOT NULL,
EventClass int NULL,
TextData ntext NULL,
NTUserName nvarchar(128) NULL,
ClientProcessID int NULL,
ApplicationName nvarchar(128) NULL,
LoginName nvarchar(128) NULL,
SPID int NULL,
Duration bigint NOT NULL IDENTITY (1, 1),
StartTime datetime NULL,
Reads bigint NULL,
Writes bigint NULL,
CPU int NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
DECLARE @.v sql_variant
SET @.v = cast(N'760' as int)
EXECUTE sp_addextendedproperty N'Build', @.v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_TRACE01', NULL, NULL
SET @.v = cast(N'8' as int)
EXECUTE sp_addextendedproperty N'MajorVer', @.v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_TRACE01', NULL, NULL
SET @.v = cast(N'0' as int)
EXECUTE sp_addextendedproperty N'MinorVer', @.v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_TRACE01', NULL, NULL
GO
SET IDENTITY_INSERT dbo.Tmp_TRACE01 ON
GO
IF EXISTS(SELECT * FROM dbo.TRACE01)
EXEC('INSERT INTO dbo.Tmp_TRACE01 (RowNumber, EventClass, TextData, NTUserName, ClientProcessID, ApplicationName, LoginName, SPID, Duration, StartTime, Reads, Writes, CPU)
SELECT RowNumber, EventClass, TextData, NTUserName, ClientProcessID, ApplicationName, LoginName, SPID, Duration, StartTime, Reads, Writes, CPU FROM dbo.TRACE01 WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_TRACE01 OFF
GO
DROP TABLE dbo.TRACE01
GO
EXECUTE sp_rename N'dbo.Tmp_TRACE01', N'TRACE01', 'OBJECT'
GO
ALTER TABLE dbo.TRACE01 ADD CONSTRAINT
PK__TRACE01__317735E1 PRIMARY KEY CLUSTERED
(
RowNumber
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

Adding data to database with other application not SQL

Hi,
I have a database that it have and int type with identity (1,1).when I add
data from sql everything is ok but when I want to add data from fro example
Delphi it says that field ID must have a value and auto numbering doesn't
work.
Please tell me how can I solve my problem.
Regards
Ali"Ali.M" <AliM@.discussions.microsoft.com> wrote in message
news:4D01366E-E296-4C7E-84FF-58EA93329088@.microsoft.com...
> Hi,
> I have a database that it have and int type with identity (1,1).when I add
> data from sql everything is ok but when I want to add data from fro
example
> Delphi it says that field ID must have a value and auto numbering doesn't
> work.
> Please tell me how can I solve my problem.
> Regards
> Ali
It sounds to me like Delphi is trying to be helpful and wants to generate a
value for each column that you pass in. If that is the case and you are
picking a table to update from Delphi, you may try creating a View in SQL
Server for the table that leaves off the Identity column. Point Delphi at
the View instead and see if that doesn't solve your problem.
Example:
CREATE TABLE Foo (
IDCol int IDENTITY(1, 1) NOT NULL,
FirstName varchar(20) NULL,
LastName varchar(20) NOT NULL
SSN varchar(9) NOT NULL
)
CREATE VIEW vFoo AS
SELECT FirstName, LastName, SSN FROM Foo
Rick Sawtell
MCT, MCSD, MCDBA|||Instead of accessing your tables from applications directly, make it a
standard practice to use stored procedures.
When in doubt search for 'procedure' in this newsgroup. And of course Books
Online will provide lots of help.
ML

adding an identity column to existing table

I removed all constraints in order to load a bunch of data into a table, now I'm wondering if I can add an identity column to this table which does contain data or if I have to create a new table with the identity column and insert the data into that.

thx

Kat

The alter table didn't work. Just created a new table with the identity column and inserted into that. So never mind... Unless there is a different way to do this. I know, it has been a while for me.

Kat

|||

It should:

create table test
(
value varchar(10)
)
go
insert into test
select 'a'
union all
select 'b'
union all
select 'c'
go
select *
from test
/*
value
-
a
b
c
*/

alter table test
add testId int identity
go
select *
from test

Can you post the syntax/error you got if it doesn't work for you?

|||

Hi Louis,

This did work. I think my syntax was the problem when altering the table, which I discarded. I was trying to add a primary key constraint at the same time and my syntax was obviously messed up. I followed the BOL syntax for altering a table, adding an identity column along with a primary key constraint. It didn't appear to say that this was not possible unless I read the 'alter table' section incorrectly. Can you tell me if this is possible and what the correct 'alter table' add identitiy, and make it a primary key at the same time?

thx,

Kathleen

|||

Just add PRIMARY KEY:

create table test
(
value varchar(10)
)
go
insert into test
select 'a'
union all
select 'b'
union all
select 'c'
go
select *
from test
/*
value
-
a
b
c
*/

alter table test
add testId int constraint PKTest identity primary key
go
select *
from test

The constraintPKTest in bold italics is optional

|||

Even though we allow the syntax below:

alter table test
add testId int constraint PKTest identity primary key

I think it is a bug to allow the IDENTITY keyword in between the constraint definition. The correct syntax is:

alter table test
add testId int identity constraint PKTest primary key

I will file a bug internally for the invalid syntax.

|||That was a mistake. I can't believe I didn't notice that. I also can't believe it compiled :)

Adding an identity column solely for the benefit of the clustering

- Instead having indexes:
ParentID, SomeValue (non-clustered)
ParentID, SomeOtherValue (non-clustered)
SomeValue, ParentID (non-clustered)
SomeOtherValue, ParentID (non-clustered)
Create just three indexes:
ParentID
SomeValue
SomeOtherValue
SQL Server is smart enough to use them when necessary.

> This table could end up containing 20+ million records.
> Less so on offline clients, which would only contain a subset
> of the master database, but which would be unable to spread
> the tables and indexes across multiple disk systems.
Is there any other column that you can use for a clustered index (remember,
a good candidate will be one that you use in range queries)?
If so, then you do not need to add the identity column.
AMB
"Joergen Bech @. post1.tele.dk>" wrote:

> Let's say I have a table containing string values:
> ValueTable:
> --
> ValueID guid
> ParentID guid
> SomeValue nvarchar(1000)
> SomeOtherValue nvarchar(1000)
> with indexes on
> ValueID (clustered)
> ParentID, SomeValue (non-clustered)
> ParentID, SomeOtherValue (non-clustered)
> SomeValue, ParentID (non-clustered)
> SomeOtherValue, ParentID (non-clustered)
> Note: GUIDs are required for this application. The table does
> not actually look like this, but is a simplification for the sake
> of the example.
> This table could end up containing 20+ million records.
> Less so on offline clients, which would only contain a subset
> of the master database, but which would be unable to spread
> the tables and indexes across multiple disk systems.
> Now the question is: Would it make sense to add an
> identity column (8-byte long), make this column the clustered
> index, and change the ValueID index to non-clustered?
> The idea is to reduce the size of the non-clustered indexes,
> seeing that the clustered column would only be half the size
> of the original - and force insertion of new records to the end
> of the table, rather than all over the place.
> But besides some savings in space, would I actually gain anything
> in terms of performance, seeing that each insertion would require
> several non-clustered GUID indexes to be updated?
> Pros and cons of adding an identity column in the above scenario?
> TIA,
> Joergen Bech
>
>On Fri, 25 Mar 2005 05:55:03 -0800, "Alejandro Mesa"
<AlejandroMesa@.discussions.microsoft.com> wrote:

>- Instead having indexes:
>ParentID, SomeValue (non-clustered)
>ParentID, SomeOtherValue (non-clustered)
>SomeValue, ParentID (non-clustered)
>SomeOtherValue, ParentID (non-clustered)
>Create just three indexes:
>ParentID
>SomeValue
>SomeOtherValue
>SQL Server is smart enough to use them when necessary.
Sorry. That would require bookmark lookups. By having "Value, ID"
and "ID, Value", I basically have covering indexes for all IDs
satisfying a specific value, as well as all values (typically 20-50)
for a specific ID. I tried the single-column index approach, but
this - though a great space-saver - requires a bit more work for
the server.

>Is there any other column that you can use for a clustered index (remember,
>a good candidate will be one that you use in range queries)?
>If so, then you do not need to add the identity column.
Not really. As I mentioned in the first post, the purpose of adding
the identity column was
1) to have a narrow clustered index, thereby saving space in the non-
clustered indexes.
2) to force insertion of new records to take place at the end of the
table, rather than causing splits all over the place, which would
happen when basing it on a GUID.
Then again: Even though the clustered index (the table itself) never
needs defragging - being identity-based and all - is probably not of
any use at all performance-wise, if it is not used for anything but
saving NC-space (i.e. it is not even used for joins of any kind).
/JB
>
>AMB
>
>"Joergen Bech @. post1.tele.dk>" wrote:
>|||
>Then again: Even though the clustered index (the table itself) never
>needs defragging - being identity-based and all - is probably not of
>any use at all performance-wise, if it is not used for anything but
>saving NC-space (i.e. it is not even used for joins of any kind).
To correct myself: They are, of course, used for bookmark lookups,
in which case an always-defragged clustering index is nice to have.
Though - if most high-performance queries are served by covering
indexes, bookmark lookups won't be needed anyway.
Oh well. As the identity index won't be referenced by any T-SQL
code, I can always do all the tweaking and testing I like later.
/JB

adding an identity column retrospectively

I've inherited a database that has a series of tables that have primary keys
that SHOULD also be identity columns, but don't.
e.g.
CREATE TABLE [dbo].[test] (
[id] [int] NOT NULL ,
[fieldA] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[test] ADD
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
They've now got loads of data in them.
These are updated by a set of stored procedures that first identify the
max(id) value and then insert the id as max(id) + 1.
As you can imagine, there's lots of errors where, having identified the
max(id) value, another instance has nabbed the incremented value, causing
the database to generate an error where it's attempting to create a
duplicate primary key.
What I want to do is to update the database tables so that they have an
identity (1,1) set on them.
Bizarrely, I can't get the syntax right...
Secondly (and more importantly)
Could adding this identity column (and of course updating all the related
stored procedures) create any problems? Could it damage any of the data
already in the database?
Thanks in advance
Griffhttp://www.windowsitpro.com/Article...2080/22080.html
"Griff" <howling@.the.moon> wrote in message
news:eLeJ8jtPGHA.2992@.tk2msftngp13.phx.gbl...
> I've inherited a database that has a series of tables that have primary
> keys that SHOULD also be identity columns, but don't.
> e.g.
> CREATE TABLE [dbo].[test] (
> [id] [int] NOT NULL ,
> [fieldA] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[test] ADD
> CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
> (
> [id]
> ) ON [PRIMARY]
> GO
> They've now got loads of data in them.
> These are updated by a set of stored procedures that first identify the
> max(id) value and then insert the id as max(id) + 1.
> As you can imagine, there's lots of errors where, having identified the
> max(id) value, another instance has nabbed the incremented value, causing
> the database to generate an error where it's attempting to create a
> duplicate primary key.
> What I want to do is to update the database tables so that they have an
> identity (1,1) set on them.
> Bizarrely, I can't get the syntax right...
> Secondly (and more importantly)
> Could adding this identity column (and of course updating all the related
> stored procedures) create any problems? Could it damage any of the data
> already in the database?
> Thanks in advance
> Griff
>

Adding an autonumber column after the fact?

I have a csv file of quotes. I need to IMPORT the quotes into my SQL Server database. I created the table with an autonumber identity column, but the IMPORT fails because I can't INSERT NULL into my identity column. There is no option to autonumber or anything when I click the TRANSFORM button on the IMPORT wizard. So, I have decided to allow NULLS on the ID column and add the numbering later. I'd rather not have to manually number it, is there a way to do it with a query? I treid just setting it to not allow NULLS and to auto-incrememnt, but that didn't work...I would have asked you to use temp table for the import and then do INSERT INTO a table with IDENTITY SET. Now you do ANSI SQL ALTER TABLE to SET IDENTITY on the table. Run a search of ALTER table in the BOL (books online). Hope this helps.|||Thank-you for your help :)|||I am glad I could help.