Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lorenae
Starting Member
3 Posts |
 Posted - 2006-05-19 : 14:12:05
|
| Hi!!I need to create something like rowid in SQL Server 2000I've tried with CHECKSUM(*) and it works fine with small tables, but I'm working now with a table that has more than 600.000 records and I've got duplicate values of CHECKSUM().Any ideas??thanks in advancelorena |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-19 : 15:12:12
|
| Use a temp table with an identity?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-05-19 : 15:38:41
|
| As always ... why are you doing this? Paging? |
|
|
|
cmdr_skywalker
Posting Yak Master
159 Posts |
Posted - 2006-05-19 : 18:05:02
|
| use IDENTITY column. If you need to have the column values unique throughout the company, use the UNIQUEIDENTIFIER.May the Almighty God bless us all! |
|
|
|
lorenae
Starting Member
3 Posts |
Posted - 2006-05-24 : 11:15:06
|
Ok, I'll try to explain the situation:I have a database already loaded and I won't insert records.I'm trying to measure data quality at record level so I've implemented several procedures and functions to measure it that will produce a value for each record of each table....I don't want to change the design of the database, I want to store all this calculated values in another table "Error_Measure"So I need a way to uniquely identify each record of each table (and although every table has a primary key constraint It doesn´t help because some tables have composed primary keys).I've thought that checksum(*) would work, and it works in most cases but I have a table with more than 600000 records and checksum(*) return duplicate values for diferent records.  The question reformulated is.... is there a way to generate an unique identifier for each record in a already loaded database?? It seems to me that the unique value should be generated with some kind of function applied to the record.I've attached the design of the table that I'm using to store the calculated values (Error_Measure) and the table that stores what kind of error I'm calculating (Error_Atribute)Thanks in advanceLorenaCREATE TABLE [Error_Measure] ( [Id_Error] [int] NOT NULL , [Id_Record] [int] NOT NULL , [Value] [numeric](4, 3) NOT NULL , CONSTRAINT [PK_Error_Measure] PRIMARY KEY CLUSTERED ( [Id_Error], [Id_Record] ) ON [PRIMARY] , CONSTRAINT [FK_Error_Measure_Error_Atribute] FOREIGN KEY ( [Id_Error] ) REFERENCES [Error_Atribute] ( [Id_Error] )) ON [PRIMARY]GO |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-05-24 : 12:02:27
|
| If Error_Measure needs a FK reference your existing table, and your existing table's PK consists of composite columns, then simply include those composite columns in your Error_Measure table and include them in your FK reference. Don't try to create "fake" primary keys that aren't needed! Remember that a foreign key constraint can use more than 1 column, and indeed it should if the table being referenced has more than 1 column as the primary key. |
|
|
|
lorenae
Starting Member
3 Posts |
Posted - 2006-05-24 : 13:09:35
|
| >If Error_Measure needs a FK reference your existing table, and your >existing table's PK consists of composite columns, then simply include >those composite columns in your Error_Measure table and include them in >your FK reference. Don't try to create "fake" primary keys that aren't >needed!>Remember that a foreign key constraint can use more than 1 column, and >indeed it should if the table being referenced has more than 1 column >as the primary key.I know that FK can have several columns, that isn't the problem. The problem is that Error_Measure contains values calculated over records from DIFFERENT tables, so I can't include a foreign key because this should vary depending on which table I'm referencing...I'm calculating values for records of different tables and I want to store this calculated values in only one table and I need a way to reference the record that corresponds to the value (and that record could be anywhere in the database)Hope that this clarifies what I'm trying to doThankslorena |
|
|
|
cmdr_skywalker
Posting Yak Master
159 Posts |
Posted - 2006-05-24 : 20:26:24
|
| It seems you have to have a standard data domain across tables that will be included on your Error_Measure table. The only thing I can think of is to add the object id to the identity value of the record (ie. allocate bigint datatype with the five digit allocated to ids appended to max rec cnt).May the Almighty God bless us all! |
|
|
|
|
|
|
|
|
ROWid or similar