Question On Design With Identity Columns
Aug 23, 2005
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
View 46 Replies
ADVERTISEMENT
Apr 17, 2007
I don't know if this is the right forum but...
In a parent/child table structure (order/orderdetail) I have used identity columns for the orderdetail or compund primary keys. I find a single identity column on the detail table easier to manage (with a fk to the parent) but what ends up bieng easiest for the user is to have an order (say #3456) and detail items listed sequentially from 1 to n. This reflects a compound key structure but generating the 2nd field is a pain. Is there any way to tie an identity field to the parent key so that it will generate this number for me automatically?
View 3 Replies
View Related
Sep 16, 2006
Hi,
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).
Any help would be appreciated.
Thanks,
-Niraj
View 1 Replies
View Related
Feb 13, 2006
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.
View 1 Replies
View Related
Sep 3, 2015
We want to add a new int identity column as a primary key to an already existing table that has a primary key on Guid. Here is the DDL:
CREATE TABLE [dbo].[VRes](
[VResID] [uniqueidentifier] NOT NULL,
[Mes] [varchar](max) NOT NULL,
[PID] [uniqueidentifier] NOT NULL,
[Segt] [int] NOT NULL,
[code]....
Also we currently have 3 million rows on this table. Is having an integer column as identity column and primary key better or shd I consider using BigInt?
View 4 Replies
View Related
Nov 16, 2007
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
View 2 Replies
View Related
Oct 7, 1999
First off I am NOT a DBA.
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.
TIA
Nate
View 1 Replies
View Related
Jan 7, 2005
Where do I find the seed and increment values of the identity column in database.
If possile can u suggest query for that Pl.
Thanx in advance
-ali
View 2 Replies
View Related
Jul 23, 2005
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?
View 5 Replies
View Related
Jun 28, 2007
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).
Regards,
Oskar Berggren
View 3 Replies
View Related
Dec 31, 2007
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
View 3 Replies
View Related
May 28, 2006
Hello there,
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.
View 1 Replies
View Related
Feb 13, 2004
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.
Any pro's con's appreciated,
Dan
View 8 Replies
View Related
Jan 16, 2001
(Long post)
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.
Dave
View 3 Replies
View Related
Sep 8, 1999
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?
Thanks,
Brad
View 1 Replies
View Related
Jan 17, 2000
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
View 1 Replies
View Related
Apr 14, 2004
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?
View 2 Replies
View Related
May 12, 2004
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.
The contents of the table would look like this
ClientID OrderID
------- -------
1 0
1 1
2 0
1 2
2 1
2 2
1 3
and so on....
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...
Thanks in advance
View 14 Replies
View Related
May 14, 2004
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);
ATable will now contain the following values:
A___B___
1___39__
2___51__
3___62__
4___93__
5___10__
Now let's say I do this:
BEGIN TRAN MyTranName;
INSERT INTO ATable (B) VALUES(44);
INSERT INTO ATable (B) VALUES(32);
INSERT INTO ATable (B) VALUES(11);
ROLLBACK TRAN MyTranName;
The three new rows won't (obviously) be in the table as my TRANSACTION was rolled back.
However, if I do this :
INSERT INTO ATable (B) VALUES (28);
INSERT INTO ATable (B) VALUES (52);
...My table will now contain the following values:
A___B___
1___39__
2___51__
3___62__
4___93__
5___10__
<I>NEW ENTRIES</I>
9___28__
10__52__
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)?
Thanks.
View 7 Replies
View Related
Oct 8, 2007
Hello,
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
View 2 Replies
View Related
Nov 1, 2007
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?
many thanks for help
View 7 Replies
View Related
Nov 3, 2006
Even though I select "Column Names" in Design View when creating a query (or view), only "* (All Columns)" appears in the table box.
In InfoPath, when I connect a combo-box, err drop down box, to the database, I am unable to connect directly to a table... no tables are shown. If I select a different database, these problems do not exist.
I can not find any setting to allow these columns to be shown in the design view or any setting that will "expose" the tables in InfoPath.
I tried creating a new database and exporting the data, tables and data, from the troubled DB to the new DB; however, the new DB exhibited the same behaviour. The system tables, Master and Model, have the same behaviour. Please help me with your ideas and suggestions... thank you very much for your time.
This database was upsized from Access 2003 to SQL Server 2000 SP4.
rogge
View 7 Replies
View Related
Jun 9, 2015
I would like to alter view to add columns. My base table is populated by application and the base table name is always change. Is there any way to add some extra columns to this view.
One way is to create a sp to add columns and use the view inside the sp. But I would like to know is there any way to alter the view.Â
View 4 Replies
View Related
Jun 3, 2015
I am adding a table within my vb.net program using New datatable(tblname) function, then adding 22 columns (col01 to col22) to this table using .columns.add (colname) function without any error.The program however throws an exception when trying to assign a value to column number 14 (col14) saying this column does not belong to table tblname. Assigning a value from col01 to col13 is working fine.Is there a limitation on number of columns can be added to a table using code?
View 2 Replies
View Related
Jun 17, 2006
I am working with a SQL database that was migrated from MS Access and adapted for full-text search which involved creating a new table with a different design. I would like to copy three columns (YearGiven, MonthGiven, DayGiven) from the Documents table to the newly-created FullDocuments table so that I can delete the Documents table and four other tables with redundant data. Here are the two tables involved in the column copy:
Documents Table
DocID (Primary Key)
DocNo
SequenceNo
YearGiven
MonthGiven
DayGiven
FullDocuments Table
FullDocID (Primary Key)
DocNo
SequenceNo
SectionText
YearGiven (empty €“ no data)
MonthGiven (empty €“ no data)
DayGiven (empty €“ no data)
After the copy column procedure I want the FullDocuments table structure to look like this:
FullDocuments Table
FullDocID (Primary Key)
DocNo
SequenceNo
SectionText
YearGiven (full of transferred data)
MonthGiven (full of transferred data)
DayGiven (full of transferred data)
The problem is that the FullDocuments table contains approximately 4x as many rows as the Documents table. This is because each document has four types €“ the primary Text document and three supplemental documents (Background, Report, and Index). Whereas the Documents table has one row for each Document, the Full Documents table has four rows per document (the four document types). Instead of simply doing a copy and paste of columns based on a primary key and foreign key relationship (as described in the MSDN online books), I need to copy and paste based on the DocNo and SequenceNo columns. Thus the same date data (Year, Month, Day) will need to occupy four consecutive rows for each document in the FullDocuments table (as is now the case with the DocNo and SequenceNo).
I will end up with one table that meets the requirements for the full-text search (primary key and all the text to be searched) and eliminate five tables (Documents and the four document type text tables). This is a static database consisting of historical records so I am not concerned about input errors that can be associated with denormalization.
I tried exporting the data using the Export Wizard using the €œCopy data from one or more tables€? option. The transfer failed. The most relevant lines of the Error Report are:
·Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'FullDocumentID', table 'Documents.dbo.FullDocuments'; column does not allow nulls. INSERT fails.".
(SQL Server Import and Export Wizard)
·Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - FullDocuments" (61) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
(SQL Server Import and Export Wizard)
·Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0202009.
(SQL Server Import and Export Wizard)
I then tried the €œWrite a query to specify the data to transfer€? option focusing on just the YearGiven column using various FETCH, INSERT AND UPDATE commands without any luck. Questions:
1. Is the query approach the best way to achieve my objective, or did I miss something in the €œCopy data€? option in the Export Wizard?
2. If the query approach is the best way, any suggestions on what the query will look like?
Thanks for any help you can provide.
View 5 Replies
View Related
Apr 23, 2015
I have two columns "Project Name" and "Flag".Â
Now i want to find out all the project names which contain a particular substring in it, like say - "SRM" and update the "Flag" column to value 1 for all those projects.
View 15 Replies
View Related
Jun 29, 2006
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.
View 14 Replies
View Related
Jul 21, 2000
We have a high load site that requires some redesign. We would like to move the PK generation from a custom scheme to IDENTITY columns.
What are the any scalability issues for using IDENTITY? Will contention issues arise because of row locking?
Thanks in advance.
View 7 Replies
View Related
Mar 23, 2006
Is there a query I can write against an INFORMATION_SCHEMA or against the system tables to determine if a column is an identity column?
View 8 Replies
View Related
Mar 19, 2008
Can a View include an identity column? If a view can include an identity column, how do you go about setting a column as an identity column.
Also how do you go about removing an identity property that may exist on column in a View
I am using SQL Server 2005.
View 7 Replies
View Related
Mar 19, 2008
How do i Find all Columns that have Identity Property in a database. That is i will like to know all columns in a database that are identity columns.
I am using SQL Server 2005. Thanks
View 4 Replies
View Related
Mar 28, 2007
Dear all,
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.
- Have you ever encounter this type of issue?
- What can create it?
- Is our approach fully inappropriate?
Please let me know if you need more information,
Best regards,
View 3 Replies
View Related
Mar 6, 2015
I have a ipaddress column is there where i need to split the column into two columns because of values like below
172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874
I have written the below query but it will throuh some error.
 select SUBSTRING(IPAddress0, 1, CHARINDEX(',', IPAddress0) - 1) as IPAddress0Â
  from IPADDRESS
error:
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
View 16 Replies
View Related
