Anyone know if MS-SQL Server supports IDENTITY columns that are incremented for each new value of the column it depends on.
For exameple:
Let's say I have a client table with a ClientID column as it's PRIMARY KEY.
This column can be an auto-incrementing IDENTITY column.
Then I have an orders table. The PRIMARY KEY for the orders table is composed of (ClientID, OrderID). I would like the OrderID to be an IDENTITY field that increments by an arbitrary value (1 in this case) for every new value of ClientID...therefore creating a unique PRIMARY KEY.
MySQL (and maybe other RDMS's...I haven't checked) seems to do this automatically when you set a column as AUTOINCREMENT and then define a composite PRIMARY KEY on two fields.
I know this can be done manually using triggers, but I was wondering if there was a better way...
I am planning to use transacational replication (instead of merge replication) on my SQL server 2000. My application is already live and is being used by real users.
How can I ensure that replicated data on different server would have exact same values of identity columns and date columns (where every I set default date to getdate())?
It is very important for me to have a mirror image of data (without using clustering servers).
Just to confirm, do identity columns and XML columns work OK with database mirroring ? That is, all data types are supported with mirroring, and identities aren't an issue ?
Transactional replication with identity columns was a right pain in the **** in SQL 2000. I'm assuming that mirroring doesn't have these issues, but want to be sure.
Can anyone describe how SQLServer calculates identity columns? Does it use some internal counter when generating the next identity, or something a little more mundane such as gets the highest existing identity value at the point of the insertion and increments it by the IDENT_INCR value of the identity column?I’m not worrying about reliability or gaps in values, but i am wondering if it would be less efficient for me to manually manage the identity/primary key in the form of a counter in another table used to generate the new identity, or simply let the DB do it for me. I dont mind if there are gaps in the sequence etc. so would it be less efficient for me to calculate the field than SQLServer itself? Basically, is the overhead to the DB of me doing it greater than the overhead of the app doing it...Thanks
I have a question about IDENTITY columns. I am working for a client that has an entire employee database that uses IDENTITY columns without any Primary keys defined. I have never seen this done. Is it ok or should I recommend that it be changed to use Primary keys?
The DBA that built the database is no longer with the company and the client has no DBA. Where can I get some information on how to use IDENTITY columns? I did not get much from the help file.
I have been using the following query to identify the IDENTITY columnsin a given table. (The query is inside an application.)select column_namefrom information_schema.columnswhere table_schema = 'user_a' andtable_name = 'tab_a' andcolumnproperty(object_id(table_name), column_name, 'IsIdentity') = 1This works. When "user_a" performs the query, everything is OK.Now, another user wanted to use the same application. So, "user_b"clicks on a button, and the exact same query as above is run. (Nosubstitutions are made; user_b is trying to see the identity column in[user_a].[tab_a]). However, the query returns null, instead of theidentity column name. User_b can read the table and select from itjust fine.Why am I getting two different results against the same query? Do Ineed to rewrite the query to go against different information schemaviews?
I'm seeing some weird behavior regarding identity columns in MSSQL 2000.In a specific client database we have this table: ID Name SecLevDG Flags ----------- ----------------- ----------- ----------- 1029528 xxx 0 01029529 xxx 0 01049676 xxx 0 0 While upgrading this database to a later version of our product, some schema changes are necessary. For this particular table, the changes are
alter table Authority drop constraint apkAuthorityId alter table Authority drop column SecLevDg alter table Authority add new_id integer identity
This code has worked perfectly for years, and even in this particular database, there are no error messages. However, the result isn't quite the expected:
ID Name Flags new_id ----------- ---------------- ----------- ----------- 1029528 xxx 0 167772201029529 xxx 0 167772201049676 xxx 0 16777220
Notice that the new column did NOT get unique values 1, 2, 3, etc... In other tests I manage to get different values, but still not the expected ones. Is this a bug in MSSQL 2000?
DBCC CHECKIDENT returns: Checking identity information: current identity value '1', current column value '1'.
DBCC CHECKDB returns no errors before running the above statement. Afterwards it returns this (only relevant messages included): Server: Msg 8970, Level 16, State 1, Line 1 Row error: Object ID 293576084, index ID 0, page ID (1:277), row ID 0. Column 'new_id' was created NOT NULL, but is NULL in the row. Server: Msg 8970, Level 16, State 1, Line 1 Row error: Object ID 293576084, index ID 0, page ID (1:277), row ID 1. Column 'new_id' was created NOT NULL, but is NULL in the row. Server: Msg 8970, Level 16, State 1, Line 1 Row error: Object ID 293576084, index ID 0, page ID (1:1145), row ID 0. Column 'new_id' was created NOT NULL, but is NULL in the row. [...] CHECKDB found 0 allocation errors and 3 consistency errors in table 'Authority' (object ID 293576084).
I have an app that has multiple users inserting and updating from a couple key tables using SQL Server 2005. In my previous SQL coding life, I was able to make use of sequence.NextVal to find and lock the next available sequence value for a table. Currently I am in the SQL Server 2005 world and cannot do this. I have found all sorts of help for Ident_currentand Identity columns, but nothing on how to accomplish the same as a NextVal did. I can add one to the Ident_Curr but I think I run the risk of that value being used by another user before the current user can get his update in. Is that correct? Is there a way to accomplish what I am trying to do? Basically what I need to do is when a user inserts a record in table "Loads" I need to insert behind the scenes to table "Comments" with the ID of the row created in the "Loads" table. Thank you in advance, Garth
I am using sql 2000 dts package to migrate a databse to SQL Server 2005, everything works except for identity columns, SQL Server reorder the columns, and this breaks the referential integrity. Is there a way to stop that? Your help is appreciated.
I am running a CLR stored proc that goes to an EDS (Novell) server with LDAP and returns records into a SQL table.
I am using the Novell ldap library.
I want to do this with SSL so my code referneces the Mono security library as well.
However when I make the call to the stored proc to run in SSL, I get an object not found error. I do not think that the the Novell assembly can "find" the Mono assembly.
Two points: 1/ I can do the SSL if I run it as an asp.net page (so I know the SSL works) 2/ The proc runs and pulls all the records in non-SSL (so I know the proc works)
I use the identity = yes for my unique columns in most of my tables that need it. I am trying to decide if I should change identity = no, and instead manually update my unique number column myself by adding one when I insert new rows.
The reason I want to do this is for maintainabilty and ease of transfering data for backup to other sql servers. I always have trouble keeping the identity numbers to stay the same as they are in the original database because when they are transfered to a db that has identity = yes, the numbers get rearranged.
It will also make it easier to transfer data from original db to another sql server db and use the data right away without having to configure the destination db to disable identity and then enable it, etc.
I have a production database with about half the tables using IDENTITY columns for PRIMARY KEYS. This system is configured as both a Publisher and a Distributor. We are using Transactional Replication without updates. The SQL Server Agent runs every hour to pick up any changes and replicate them to the Subscriber (another SQL Server machine configured as our failover server).
Both servers are running SQL Server 7.0 (original, no service packs) under Windows NT 4.0 (SP4).
The failover server (the replication Subscriber) will only be used if the primary server goes down. And hopefully, only for a short time before the primary server comes back online. During the time that the failover is actually being used, the application will not make any changes to the database.
The IDENTITY values that are replicated need to stay with their original values. The replication process CANNOT assign new values to these columns when there are inserted into the database on the Publisher (i.e. failover server)
My question is: According to the documention, I can add 'NOT FOR REPLICATION' to the IDENTITY columns and the values will be preserved. But a collegue of mine says that resets the IDENTITY sequence on the subscriber and the 2nd time a row gets inserted on the Publisher, the values get messed up. On his system, he calls a stored procedure for the tables with IDENTITY columns, and in the stored procedure, he executes a 'SET IDENTITY_INSERT OFF', then INSERTS the row, then 'SET IDENTITY_INSERT ON'. He claims that this approach solved his issues with IDENTITY columns.
Who is right? Do I have to create a stored procedure for replication for every table with an IDENTITY column, or can I just add 'NOT FOR REPLICATION' and SQL Server will handle the rest?
NOTE: Upgrading to SQL Server 2000 is NOT an option right now. Although, if a Service Pack for 7 fixes this, that might be an option.
Thanks in advance for any help you can shed on this issue.
We are trying to run replication from one server to another. Most of the tables in the publishing database have identity columns set. At one point the subscribing database had a match of identity columns and primary keys matching the publishing database. Obviously with a primary key set on the subscriber there would be conflicts with duplicate key inserts. We disabled the identity columms and droped the indexes and the data was able to replicate over. However, we discovered that the identity columns, which are used to generate id's on many of our tables, were not replicating over to the subscriber. In fact a null value was being inserted into the subsriber database.
Anyone seen this before? What, if there is one, is the solution?
hi there. I was wondering is there any way that you can use an Identity Column on both a subcribing and Publishing table in Replication, I am receiving errors when I run Replication with Identity Columns, Thanks in advance Fin
Hi folks! I've a merge replication setup b/w two servers. Published tables have columns (INT IDENTITY SEED 1 INCREMENT[NOT FOR REPLICATION]). Whenever i apply the SNAPSHOT, i have to run DBCC CHECKIDENT('table' RESEED) for each table at the subscriber twice, for the values in the columns are almost always greater than the ID-Seed value. For example the last Identity value in the column is 999 but whenever i insert a new row; i get error; couldn't insert duplicate value into the table. When i run the dbcc check i see the following message: "Checking identity information: current identity value '1', current column value '999'." How do i square this away?
Hi, I noticed that tables containing IDENTITY columns skip IDENTITY values when a transaction is rolled back. Is there any way to avoid this? By "avoid" I mean make the table continue generating IDENTITY values from where the last COMMITTED insert took place.
Example:
CREATE TABLE ATable (A INT IDENTITY, B INT);
INSERT INTO ATable (B) VALUES (39); INSERT INTO ATable (B) VALUES (51); INSERT INTO ATable (B) VALUES (62); INSERT INTO ATable (B) VALUES (93); INSERT INTO ATable (B) VALUES (10);
Is there a way to have SQL-Server continue from where it left off (IDENTITY VALUES 6, 7, 8) without having to generate the numbers manually or occasionnally turning on IDENTITY_INSERT on the relevant table(s)?
In my application I am using Identity columns. When some rows are deleted from table, This identity values are not filling the gap. I mean My current identity is 5. That means 1 to 5 rows sequentially i inserted. If I am deleting 3rd and 4th rows, next identity will still continue with 6. So is there any method to fill the gap between rows
I have a stored procedure which gets data from several tables in database A and inserts them into the same tables in database B. Before the inserts are done, the data in database B is removed currently by using the TRUNCATE statement.
Unfortunately these tables are now being used for replication and you cannot TRUNCATE a table used for replication.
The issue is that these tables contain an Identity column each and using DELETE means the Identity columns will be incremented from the last value each time. I do not want this to happen.
Is there any way of reseeding an Identity column without using the DBCC CHECKIDENT statement because I do not want the procedures to run under the "sa" context if a DBCC statement was to be incorporated into the stored procedure with a DELETE statement?
I posted a similar question in the SQL7 forum but am not getting any replys.
"I need to move 65 tables onto their own filegroup. Is there anyway to do this without scripting the tables first and BCP etc? My concern is the dependant stored procedures, I don't want to have to script these also as there are hundreds of them."
If I wanted to drop and recreate some tables, how would I do so without upsetting the dependant stores procedures on these tables? If I inserted the names into a table and wrote a cursor to pull out the dependant id's from the sysdepends table, then put them back after the drop and recreate (matching the old dependants with the new object ids) what kind of trouble am getting myself into? Thanks Patrick
hi.i am using ms sql server 2000. can somebody tell me what the code would be to remove all the values in a given column and replace them with the associated number of the row with each execution. so, if i have a column:nums|1||2||3||4|and somebody deletes record |2|i would like the nums colum to update to|1||2||3|not:|1||3||4|it seems simple but i am having a hard time with this. how is it done?thanks.
Normally when I have a "Many-toMany" or linkage table where the primary key consists of a foreign key from two different tables, I do not bother to make a separate identiy column instead.Does anyone see a reason why an identity column would be more or less desireable ? For exampleTable Person PK - PersonIDTable Car PK - CarIDTable PersonCar PK (PersonID, CarID)Or would it be better to make an Identity Column such as PersonCarID so then the table would look like the following:Table PersonCarPK - (PersonCarID)FK - PersonIDFK - CarIDCreate Unique Constraint on Person and CarIDAny feedback is appreciated
We have an application that use merge replication between MSDE and Devices with SQL CE.
Due to a major application changes, we have to change our replication on all our workstation. Our process is the following:
- drop current replication
- recreate our replication
After first check our replication seems to work but after some test we have identify that all identity ranges have been reset on the workstation. As side effect, device start to reuse existing range and also existing value in the range.
Hi,I have a 2 tables called 1.tblRisk which consists of Ref(pk), subject, status, staff & Dept(fk)2.tblDept which has Ref(Pk) & DepartmentHow do i get it to populate Department, when tblRisk Ref's Dept matches the Ref in tblDept i am using SQL Server 2000best regards
Hi All, It seems I have been requested to carry out a complex query and the best way I think I can do this is with the use of a stored procedure. The problem is that I am not quite sure whether my SP is stated correctly and also how I would go about stating the SP in my VB.net code!
I would be ever so grateful if somebody could look over my SP code and possibly recommend a way of stating my code. My ability is limited so I would appreciate it if examples could be used with possible relations to my problem.
The SP should state that Department should appear as the end result of the query when the page is loaded. So when a row is selected in tblRisk, dependant upon what the Dept is in that table, it then populates the department in which it is associated with from tblDept. I have left the SP below.
Many Thanks, Kunal
CREATE PROCEDURE dbo.ShowMe @yourInputValue INTAS SELECT tblDept.Department FROM tblDept JOIN tblRisk ON tblDept.Ref = tblRisk.Dept WHERE tblDept.Ref = @yourInputValue RETURN 0GO
Hi all, Sorry if it looks a little cluttered!I have these two tables: CAMPAIGN(MEDIACODE varchar(10) UncheckedSPECIALOFFERCODE varchar(15) UncheckedLAYOUT varchar(10) UncheckedHEADERTEXT varchar(100) CheckedSORTORDER varchar(10) UncheckedSORTORDERCOLUMN varchar(50) UncheckedWIDTH varchar(50) Checked)andPROMORATEVIEW(MEDIACODE varchar(10) CheckedSPECIALOFFERCODE varchar(15) CheckedCAMPAIGNCODE varchar(15) CheckedNUMBEROFISSUES smallint CheckedRATE decimal(9, 0) CheckedDESPATCHMETHODCODE varchar(50) Checked)CAMPAIGN HAS ONLY ONE ROW:(CE DG8398 GRID ASC NUMBEROFISSUES NULL)ANDPROMORATEVIEWTOO MANY AND HERE A VERY SMALL RANGE OF RECORDSMEDIACODE SPECIALOFFERCODE CAMPAIGNCODE NUMBEROFISSUES RATE DESPATCHMETHODCODE(...CE CER1R02 CER1 12 429 WCE CER1R03 CER1 24 829 WCE CER1R03 CER1 12 429 WCE DG8398 DG8398 12 411 FCE DG8398 DG8398 12 405 1CE DG8399 DG8399 12 399 WCE DG8399 DG8399 12 735 1CE DG8399 DG8399 12 756 ACE DG8400 DG8400 12 756 ACE DG8400 DG8400 12 396 W...)Now the question:Why these two OUTER JOINS RETURN the same number of rows in My Sql2000 & 2005 express???1.SELECT DISTINCT P.MEDIACODE, P.SPECIALOFFERCODE,CD.MEDIACODEFROM CAMPAIGN AS CD RIGHT OUTER JOINPROMORATEVIEW AS P ON P.MEDIACODE = CD.MEDIACODE AND P.SPECIALOFFERCODE = CD.SPECIALOFFERCODE2.SELECT DISTINCT P.MEDIACODE, P.SPECIALOFFERCODE,CD.MEDIACODEFROM CAMPAIGN AS CD RIGHT OUTER JOINPROMORATEVIEW AS P ON P.MEDIACODE = CD.MEDIACODE AND P.SPECIALOFFERCODE = CD.SPECIALOFFERCODE AND CD.SORTORDER IS NULLIf you still with me here is what I am trying to do:to right outer join the campaign with promorateview and the if it's not overriden in campaign( there is no record for MEDIACODE, SPECIALOFFERCODE in campaign ) I will use that view to override the default values.I suspect that the last statement makes no "influence" on right outer join.Another work around for me now is that if I create a view that has these values and then filter it with VIEW.SORTORDER IS NULL it does the job but I am trying to get rid of this extra view.Thanks for your time, avarair
Hello, i am trying to subscribe to a report that has dependant parameters, for example Country->City and Year-> Month. Those values are coming from a Analysis Services cube dimensions. when i come to it in the subscription window, some of the parameters are populated and some are not. I cannot detect a pattern. Does anyone know what can be the problem? thank you.
I need gurus input on the pros and cons of Seeded(int) Identity colums vs. UniqueIdentifier(GUIDs) columns for my db design.
As I understand it, merging the data of 2 independent db's both using IDENTITY columns would be very hairy because of the possibility of overlap. GUIDs are much more likely to be unique across different servers.
What I'd like to hear from others are the other pros and cons of the situation. I of course understand the performance hit suffered at the hands of the GUID.
Can a Primary Key column also be a Identity column? The reason I am asking this question is because I have created a table and each time I insert data into the Address Table I am also inserting the AddressID, how do I get the Primary Key (AddressID column) to self generate ID values.
