Identity Column Jumped 1.8 Billion Numbers
Aug 19, 1999
After moving our database between servers, one table had a hiccup in it's
identity column. The number jumped from 761 to 1886863475 on the next
insert. This is a production server and I didn't catch it until yesterday.
So I have several days worth of numbers in a central table. I can't clean
them out, but I wondered if I could do the following:
DBCC CHECKIDENT ('table_name', RESEED, 800)
I tested it on our development server and it doesn't seem to cause any
problems. I know that when we get to 1.8 billion on the identity column
again we'll get an error, but I'm willing to risk it.
A few questions. 1) Am I insane for trying this? 2) Has anyone else ran into
a similar problem and what did you do to fix it? 3) If I do this, is there
anything in particular to watch out for? 4) What caused the jump in the
first place?
My other option is to change the datatype from int to decimal(19) or
something along those lines. This will upset our programmers and I'll have
to change all my foreign keys (not a big deal, just a pain).
Any input at all here would be appreciated.
Thanks,
Grant
View 1 Replies
ADVERTISEMENT
Mar 4, 2008
Hello,
I have an ID column which is an Identity column, and it counts from 1 upwards.
If I have 10 records, I have ID's number 1 to 10.
If I then add another record, it will give the ID number 11.
Imagine I then delete ID number 9
Is it possible to get the next record I create to take number 9 instead of 12 ?
View 5 Replies
View Related
Feb 1, 2007
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
View 1 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
Sep 4, 2004
Hi All
I am using SQL 2000 on win 2003 server and I have been using the query analyzer and a stored procedure to add records to a particular table.
For some reason the ID column which is an identity column and is seeded to increment by 1, skips numbers every once in a while. I haven't used any deletes and there have been no errors when loading the info.
Has this happened to anyone else? Where do I start looking for possible causes
Thanks
P
View 3 Replies
View Related
Jun 2, 2015
I have inherited a system which uses Identity numbers for primary key fields, but also displays them for the user to see. The user has noticed missing numbers in the sequence, sometimes just a few numbers and sometimes as many as 1000 numbers missing.
As far as I can tell the few numbers missing could possibly be explained by users deleting records or the number being generated on a save, but the save then failing. But I can't explain the huge jump of 1000.
I can find no reference to the RESEED in the code...
View 9 Replies
View Related
Sep 26, 2007
Is there a way to check for missing identity numbers in a Primary Key column? I have some databases that are not fully normalized and want to check on tables that might have had some records deleted. Thank you.
View 3 Replies
View Related
Aug 24, 2006
I guess this is a fairly common topic but couldn't find the right words to find anything in a search.
What I'm getting at, is there any tsql functions or combination of commands for the following.
You have identity columns in your tables, if you set the a seed and autoincrement, I enter in rows 1 -10 and then I delete 4, 6, 7, 8.
My next new record uses 11. Is there any logic that allows you to check and reuse 4, 6, 7 & 8 described above? Not looking for something that consists of having to create an extra ID table for each table and handle configuring what the next available number is everytime an Insert or delete is called.
Thanks.
View 4 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
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
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
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
Mar 21, 2002
Hi
I'm trying to find a decent way of multiplying a set of numbers in a column without using a cursor in T-SQL.
There is no 'Product' aggregate function that I'm aware of in SQL 7 or 2000. The workaround I'm currently using is this :
SELECT EXP(SUM(LOG(ColumnName))) FROM tblName
This works fine, except when negative numbers are introduced. The LOG function does not allow negative numbers and therefore returns a domain error and the negative number is eliminated from the aggregate.
I could use a cursor to do the multiplication, however, this is proving too slow for the bulk calculations involved.
If anyone has any ideas or suggestions, then that would be much appreciated.
thanks....Tom
View 1 Replies
View Related
Oct 25, 2013
I have a table with 13,000 rows, in one column called Prioirty each row has a value of 1.
Is it possible to use SQL to replace all of these '1' values with a sequential list. Example 1,2,3,4,5,6,7....all the way to 13,000?
View 3 Replies
View Related
Jul 19, 2007
Hello.
Whats the easiest way to add a column with a numeration to my table?
I want the users to have an easier life finding on which row they are
talking.
I preffer of course doing it in reporting service and not adding a
column in my dataset.
Thanks.
View 1 Replies
View Related
Apr 23, 2008
OK I HAVE AN INTERESTING SITUATION HERE.
I HAVE AN SSN COLUMN.
SSN NUMBER HAS 9 NUMERIC DIGITS.
NOW IN THE SSN COLUMN I HAVE A LOT OF BAD SSN WHICH CONTAIN ONLY 3 OR ONLY 4 OR ONLY 5 AND SO ON DIGITS.
HOW CAN I FILTER THESE ONES OUT SO THAT I CAN MAKE AN EXCELL REPORT OF THIS DATA.
I AM THINKGING OF USING CONDITIONAL SPLIT.
BUT HOW SHOULD I GO ABOUT IT.
PLEASE LET ME KNOW THE COMMAND
View 1 Replies
View Related
Oct 11, 2007
Hi,
I have the following tables :
Code Block
Create table #EmployeeList(empname nvarchar(20), emptype char(5))
Insert INTO #EmployeeList('Cary zzz',null);
Insert INTO #EmployeeList('01 Jack',null);
Insert INTO #EmployeeList('02 Tommy',null);
Insert INTO #EmployeeList('03 Ricardo',null);
Insert INTO #EmployeeList('04 Jack',null);
Insert INTO #EmployeeList('Les zzz',null);
Insert INTO #EmployeeList('05 Tim',null);
The final data looks like this :
Cary zzz NULL
01 Jack NULL
02 Tommy NULL
03 Ricardo NULL
04 Jack NULL
Les zzz NULL
05 Tim NULL
1. I want to delete all rows which have 'zzz' in it.
2. I want to remove the numbers from the empname column
Code Block
Expected Output :
Jack NULL
Tommy NULL
Ricardo NULL
Jack NULL
Tim NULL
Can anyone help me please with the query?
thanks.
View 4 Replies
View Related
Mar 20, 2008
HELP. I've inherited some less-than-stellar dessign.
I have a table:
tbMD
ObjectID uniqueidentifier not null
Tag nvarchar(50) not null
Data nvarchar(400) not null
This table has 1.6Billion records and has filled up the drive (nearlly 1 TB in the DB, with about 600GB in this one table).
I need to move this table to another drive with ZERO down time.
I have created a new filegroup on a second drive array and created a new table (tbMD_TEMP) and my thought was to copy all the data from tbMD into tbMD_Temp then drop tbMD, and rename tbMD_Temp.
So, anyone have any great ideas on how to get this done very quickly?
View 5 Replies
View Related
Aug 27, 2004
HI all;
I have a simple question to ask; I need to create a column with the data from my DOB column (which has the smalldatetime type attached to it). I know how to do that but I am not too sure how to convert the data from that column int normal character for example when I copy it into my newly created column and change the type to varchar I get this jan 16 1979 from this date 1979/01/16. But I actually want the data to look like this 19790116, so in effect I just want to take out the slashes.
Any help would be highly appreciated, thanks all.
View 1 Replies
View Related
Oct 8, 2013
I have a Column which has both numeric values and Alphabets in them.I want to sort them in descending with numbers first and then Alphabets later.
Example:
Source column:
2008
2005
2010
2013
All-Year
Month-year
Required Output:
Source Column:
2013
2010
2008
2005
Month-Year
All-year
View 4 Replies
View Related
Jul 20, 2005
I have a temp table that's populated with an insert query in as toredprocedure. The temp table has a uniqueID as the primary key.In that table I have a column SortOrder.What I want to do is to create a sequential number in SortOrder butonly for records matching a WHERE statement, for example:(pardon the shorthand...)Insert *.tblPermanent into tblTempIf myField = 1 thenSortOrder = 1(2,3,4,5,.....etc.)elseSortOrder = 0Thankslq
View 1 Replies
View Related
Nov 22, 2007
how do I gradually add o substract numbers in a column for example if I have
total
deposito
test
120
120
80
40
77
117
7
124
4
120
i need in the test column:
if the number is in the total column the number in test is added
if the number is in the deposito column the number in test is substracted
i have this query:
SELECT gir_cantidad as total,null as deposito,'operation' as test
FROM giros inner join corresponsales on cor_corresponsal_id = gir_corresponsal_id where gir_fecha >= '11/21/2007' and gir_fecha <= '11/22/2007 23:59:59' and gir_fecha_anul is null and gir_agencia_id = 1
UNION
select null as total,paa_valor as deposito,'operation' as test from pagosagencia where paa_fecha >= '11/21/2007' and paa_fecha <= '11/22/2007 23:59:59' and paa_agencia_id= 1 order by gir_fecha
In column test i need the functionality to add and subtract the columns total and deposito
Please help
View 4 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
Jul 30, 2007
Hi;
I want to know, if a table has 1 billion recoreds then it will work eaisly with .net objects like datasets etc?
if not, what is the normal amount of data for sql server which is easy to manage for system.
Adnan
View 2 Replies
View Related
Mar 28, 2008
I will be receiving data from a company called Loan Performance, that has one file/table that will hold 1 billion rows. They send data by period, and I plan to load the data via BCP via NT/DOS scripts. The 1 billion rows represents data for 200+ periods.Are the following design plans feasible1. Partition table by period value, I'm not sure of the max number of partitions per table in 2005, but I think we have periods data back to 1992 and a new one gets created every month, so the possibility of having > 1000 partitions exists. I plan on just pre-creating partitions for future data, instead of dynamically creating when a new period is sent.2. Load data via BCP in DOS shell scripts that will drop index (by partition), BCP in data, and they re-create indexes by partition, is this possible ? and will I see a performance increase as opposed to one huge table (I'm pretty much sure I will). There is usually one periods data present per day, but sometimes the vendor resends all data (would get loaded on weekend).I'm a bit unsure of where to start being I never worked with this amount of data. I worked with partitioning in Oracle a long time ago.I plan on having an 2XQuadCore 2.66Ghz CPU with 32GB of RAM and SQL2005EE 64Bit connected to 1 Terabyte SAN Disk.Thanks all,PMA
View 7 Replies
View Related
Feb 18, 2014
I'm trying to create a column of numbers that increment by one.
I'm not able to use a #temptable in the application I'm using so I cannot use IDENTITY(int,1,1).
I want to add an Id column to this query:
Select distinct sd.name,ic.TABLE_SCHEMA,ic.TABLE_NAME from sys.databases sd
cross join INFORMATION_SCHEMA.COLUMNS ic
where sd.name = 'ODS1stage'
order by TABLE_SCHEMA,TABLE_NAME
How can I accomplish this without creating a temp table? I would just alter the table and insert the numbers but there are 2000 rows.
View 7 Replies
View Related
Jul 17, 2013
I have two tables. One table has empty column (ID) and I want to generate serial numbers in that column based two tables columns(LoanNum) condition.
Table A
ID LoanNum
Null 1234
Null 2345
Null 3456
Null 4567
Null 5678
Null 6789
Table B
LoanNum
1234
2345
3456
4567
5678
6789
2324
4352
4235
Id is not primary key, but should not duplicates keys, it is just a column want to generate serial number (1,2,3,4...etc) How to generate?
View 5 Replies
View Related
Aug 29, 2013
I have a table of id numbers that I wish to mask. My thought was to create a new column for this new id number and populate it with a unique sequential value - start at 1 and go as high as needed. My problem is that I cannot recall how to populate that column with a number...
View 4 Replies
View Related
Jul 1, 2014
I have the following SQL which i want to convert to a stored procedure having dynamic SQL to generate column numbers (1 to 52) for Sale_Week.Also, I want to call this stored procedure from Excel using VBA, passing 2 parameters to stored procedure in SQL Server
e.g,
DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation' DECLARE @Country nvarchar(MAX) = 'UK'
I want to grab the resultant pivoted table back into excel. how to do it?
USE [Database_ABC]
GO
DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation'
DECLARE @Country nvarchar(MAX) = 'UK'
[code]...
View 1 Replies
View Related
Jul 1, 2015
example:
column: ID numbercar before ID numbercar after
1 00122011 1 122011
2 00042010 2 42010
3 03102012 3 3102012
View 5 Replies
View Related
Feb 18, 2008
Here is my problem. I have a table with 4 columns id1,id2,id3,boxnum. Here is some sample data.
id1 id2 id3 boxnum
1 1 1
1 1 1
1 2 1
1 2 1
1 2 1
2 2 2
2 3 4
What I need is to be able to update boxnum with sequential numbers based on the unique set of values from the id columns. So my output would be this.
id1 id2 id3 boxnum
1 1 1 1
1 1 1 2
1 2 1 1
1 2 1 2
1 2 1 3
2 2 2 1
2 3 4 1
View 6 Replies
View Related
Nov 13, 2007
Hello,
One more question about this Custom Calendar table I'm creating. I have a column called "IsWorkdays" which indicates if the day represented by a row is a workday or not. For our purposes, I also need to create a row that accumulates those numbers by month. So, if it is the 3rd workday of the month, this column would have a 3. This is beyond my current T-SQL ability. Does anyone know how to do this?
Thanks a lot,
Andy
SQL version: 2005, Standard edition.
View 1 Replies
View Related
