Difference Between 2 Columns Key And A Third-party Column With Identity
Jul 22, 2007
Hi all,
Just a silly wonder I had a few days ago:
I have a table named 'CustomerOrders' and I ponder between the posibilities of PrimaryKey(s) i can set :
1. CustomerId + OrderID - a two columns key providing exactly what i need (representing the actual relation).
2. CustOrderId - one column key with identity insert (so i could directly delete/update the record).
Which one should I choose and why? Would i gain anything by choosing one over the other?
Thanks in advance,
iTaY.
View 6 Replies
ADVERTISEMENT
Jun 15, 2015
Given the attached report, is there an easy way of calculating the difference between the Today and QTR Start column? Because of the Account Group, the report looks like the sample shown on the second image.
Sample report:
View 4 Replies
View Related
Nov 9, 2006
Morning...
I've attempted several searches to find my answer but they're not quite the answer Im looking for.
We have a large VB6 system that currently sits on an informix database and I've been given the task of shifting it
onto SQL Server (Note: with as little impact to the code as possible - runtime speed will not be an issue at this early stage)
To cut a long story short, I'm hoping to find where relevent columns are shown as being an identity column.
I expected to see it in INFORMATION_SCHEMA.columns but its not in there (at least not that I can see)
Anyone point me in the right direction?
Cheers
Aaron.
View 3 Replies
View Related
Feb 24, 2015
I have the following 2 Query's - case when Table has no Identity Column and other with identity Column . I am planning to make it to single Query .
Query 1:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE object_id =
(
SELECT sys.objects.object_id
FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing1' AND sys.schemas.Name ='dbo'
)
Query2:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE is_identity != 1 AND object_id =
(SELECT sys.objects.object_id FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing2' AND sys.schemas.Name ='dbo'
)
View 8 Replies
View Related
Jun 19, 2008
Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun
View 6 Replies
View Related
Aug 12, 2009
when i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.
View 2 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
Jan 25, 2015
I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.
Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?
View 6 Replies
View Related
Sep 19, 2005
Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint
View 2 Replies
View Related
Feb 12, 2014
To calculate how many months are between the current date minus the First_Post_Date
For example
The First_Post_Date is displayed as follows following “25/07/2012”
Current date is 12-02-2014
The difference between the two dates is approx 20 months ..To make the calculation easier, it might be easier to default the day of First_Post_Date to 01 and do the same with the currentdate
So it would be 01/07/12 and 01/02/14
View 3 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
Jan 24, 2014
I am trying to produce a report in Application express. I want to find records where there is a difference between 2 columns eg
Debt_amount - billed_amount
where debt >billed amount
I only want to see records where the debt is more than the amount billed
View 1 Replies
View Related
Feb 6, 2007
Hi
Difference between xtype and type columns in sysobjects table?
Thanks in advance...
Regards
Magesh
View 2 Replies
View Related
Apr 14, 2008
I have two columns in my table. Both the columns contains datetime datatypes. I need to write a stored procedure which will calculate the date difference between the two columns and if it exceeds more than 5 days then that record should get deleted. How to do it?Thanx
View 2 Replies
View Related
Aug 14, 2014
How would I compare 2 rows to find the difference in the columns?
Example:
ID Column1 Column2 Column3
1 Text1 Text4
2 Text1 Text2 Text3
Result:
Column1: Text2 New
Column3: Text4 Old Text3 New
View 6 Replies
View Related
Sep 22, 2015
Here is the table:
empid lastname firstname title titleofcourtesy ModifiedOROriginal
1 Davis1 Sara CEO Ms. Modified
1 Davis Sara CEO Ms. Original
We need an output like this:
empid
1
1
lastname
Davis1
Davis
firstname
Sara
Sara
title
CEO
CEO
titleofcourtesy
Ms.
Ms.
ModifiedOROriginal
Modified
Original
View 2 Replies
View Related
Aug 1, 2014
I'm working with a third-party database (SQL Server 2005) and the problem here is the following:
- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).
- The PK of T is an Integer.
Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.
Considering that I can't change the PK, what is the best approach to solve this problem?
To sum up:
* I need to have processes in parallel inserting on T
* I can't change anything on T
* The PK is NOT an Identity
View 4 Replies
View Related
Mar 30, 2007
I used this query to get a result
select round(cast(DateDiff(ss, convert(datetime,rf.RECVD_DTTM), convert(datetime,con.ARRIVED_DTTM))/60 as float)/60,2) as LengthOfTime
from customer rf
but i am getting an error ?
"Difference of two datetime columns caused overflow at runtime."
Any idea ?
View 10 Replies
View Related
Sep 23, 2005
At my job is a dts package that is failing in SQL 2005. I am not a SQLexpert. I am just trying to fix. I put the query in Query Analyzerand get this error:(4322 row(s) affected)Server: Msg 535, Level 16, State 1, Line 1Difference of two datetime columns caused overflow at runtime.I am just trying to understand what this means, what I should belooking for and what could be wrong. Here is the query:SELECT i.SerialNumber, '' AS mac_number, DATEDIFF([second], 'Jan 1,1970', s.DateOrdered) AS Support_StartDt, DATEDIFF([second], 'Jan 1,1970',s.Warranty_Enddate) AS Support_EndDt,DATEDIFF([second], 'Jan 1, 1970', c.Registration_Date) ASRegistration_Date, c.FirstName AS enduser_fname,c.LastName AS enduser_lname, c.CompanyName ASenduser_companyname, c.ContactEmail AS enduser_email, c.Address ASenduser_address1,c.Address2 AS enduser_address2, c.City ASenduser_city, c.State AS enduser_state, c.Zip AS enduser_zip,c.WorkPhone AS enduser_phone,c.Fax AS enduser_fax, d.DealerName ASdealer_companyname, d.ContactFirstName AS dealer_fname,d.ContactLastName AS dealer_name,d.Address1 AS dealer_address, d.City ASdealer_city, d.State AS dealer_state, d.Zip AS dealer_zip,d.ContactPhone AS dealer_phone,d.ContactFax AS dealer_fax,ISNULL(SUBSTRING(p.ProductName, 11, LEN(p.ProductName) - 10), 'unknownIWP product') AS product_type, '' AS extra1,'' AS extra2, '' AS extra3, '' AS extra4, '' ASextra5, '' AS extra6, '' AS extra7FROM tblInventory i full outer JOINtblDealers d ON i.DealerID = d.DealerID fullOUTER JOINtblSupport s ON i.InventoryID = s.InventoryIDfull outer JOINtblCustomers c ON s.InventoryID = c.InventoryIDLEFT OUTER JOINtblProducts p ON LEFT(i.SerialNumber,PATINDEX('%-%', i.SerialNumber)) = p.SerialPrefixWHERE i.SerialNumber <> ''Any ideas would be greatly appreciated.
View 2 Replies
View Related
Oct 16, 2006
Hi all,
The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..
i cannot implement this constraint, it throws the error when i execute the below Alter query,
ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id)
REFERENCES parent_table (parent_id) ON DELETE CASCADE
the error thrown is :
Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT
fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE
CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be
created where the referencing column 'child_table.child_id' is an identity column.
any workarounds for this ?
View 3 Replies
View Related
Sep 4, 2015
IF Object_id('GoldenSecurity') IS NOT NULL DROP TABLE dbo.GoldenSecurity;
IF Object_id('GoldenSecurityRowVersion') IS NOT NULL DROP TABLE dbo.GoldenSecurityRowVersion;
CREATE TABLE dbo.GoldenSecurity (securityID INT, CompanyId INT, Securityname VARCHAR(50), issuedate SMALLDATETIME, currencyID INT)
[Code] ......
View 6 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
Sep 5, 2015
I am really puzzled by an apparent difference between table index key column order and its statistics order. I was under understanding that index statistics mirror index definition. However, in my db 2470 index ordinal definitions match statistics definition but 66 do not. I also can reproduce such discrepancy in 2008 R2, 2012 and 2014.
As per definition,
stats_column_id
int
1-based ordinal within set of stats columns
This script duplicates this for me.
BEGIN TRAN
GO
use tempdb
GO
CREATE TABLE [dbo].[ItemProperties](
[itmID] [int] NOT NULL,
[cpID] [smallint] NOT NULL,
[ipuID] [tinyint] NOT NULL,
[Code] ....
The result I get is this:
object_id stats_name
stats_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, cpID, ipuID,
and
object_id index_name
index_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, ipuID, cpID,
Also a query I used to discover this in my db is:
WITH stat AS
(
SELECT
s.object_id
,s.name as stats_name
,(
SELECT
c.name + ', ' as [data()]
FROM sys.stats_columns as sc
[Code] .....
View 6 Replies
View Related
Sep 7, 2007
Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance,
Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
View 18 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
