Click here to Skip to main content
15,901,205 members
Home / Discussions / Database
   

Database

 
AnswerRe: begin tran in sql server 2008 Pin
Corporal Agarn19-Feb-14 8:22
professionalCorporal Agarn19-Feb-14 8:22 
QuestionSQL Server and IDENTITY Pin
gmhanna18-Feb-14 8:33
gmhanna18-Feb-14 8:33 
AnswerRe: SQL Server and IDENTITY Pin
David Mujica18-Feb-14 8:43
David Mujica18-Feb-14 8:43 
GeneralRe: SQL Server and IDENTITY Pin
gmhanna18-Feb-14 9:32
gmhanna18-Feb-14 9:32 
GeneralRe: SQL Server and IDENTITY Pin
Richard Deeming18-Feb-14 11:04
mveRichard Deeming18-Feb-14 11:04 
JokeRe: SQL Server and IDENTITY Pin
ZurdoDev18-Feb-14 15:31
professionalZurdoDev18-Feb-14 15:31 
AnswerRe: SQL Server and IDENTITY Pin
Mycroft Holmes18-Feb-14 11:46
professionalMycroft Holmes18-Feb-14 11:46 
AnswerRe: SQL Server and IDENTITY Pin
S Douglas26-Feb-14 8:24
professionalS Douglas26-Feb-14 8:24 
gmhanna wrote:
wonder what happens when I've added and deleted 4 Billion records


1) Instead of running a delete command, use truncate table. That will reseed the identity field, thus allowing you to regenerate the numbers. (Not that there is anything wrong with what others suggested of just using a BIGINT for the ID field. Once MAX INT has been reached you will no longer be able to insert records into that table.)

2) If you are unable to truncate the table, then you can reseed the key. More information can be found here http://blog.sqlauthority.com/2007/03/15/sql-server-dbcc-reseed-table-identity-value-reset-table-identity/[^]

A quick example

SQL
CREATE TABLE Test(id INT IDENTITY(1, 1), TestValue INT)
GO
INSERT INTO test VALUES(1)
INSERT INTO test VALUES(2)
INSERT INTO test VALUES(3)
INSERT INTO test VALUES(4)
INSERT INTO test VALUES(5)
GO
SELECT * FROM test
GO
DELETE test
GO
INSERT INTO test VALUES(1)
INSERT INTO test VALUES(2)
INSERT INTO test VALUES(3)
INSERT INTO test VALUES(4)
INSERT INTO test VALUES(5)
GO 
SELECT * FROM test
GO
TRUNCATE TABLE TEST
GO
INSERT INTO test VALUES(1)
INSERT INTO test VALUES(2)
INSERT INTO test VALUES(3)
INSERT INTO test VALUES(4)
INSERT INTO test VALUES(5)
GO 
SELECT * FROM test
GO
DBCC CHECKIDENT(test, reseed, 1)
GO
INSERT INTO test VALUES(1)
INSERT INTO test VALUES(2)
INSERT INTO test VALUES(3)
INSERT INTO test VALUES(4)
INSERT INTO test VALUES(5)
GO 
SELECT * FROM test
GO
DROP TABLE test



gmhanna wrote:
This is SQL Server, and I'm wondering if I should be doing a database REORGs like we do with DB2.


From IBM's website, looks like the same feature would exist in SQL Server as clustered index management (more information http://technet.microsoft.com/en-us/library/ms189858.aspx[^])

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.


QuestionOLAP Cubes in BIDS for Salesforce and PRISM Pin
Karina Notka18-Feb-14 6:30
Karina Notka18-Feb-14 6:30 
AnswerRe: OLAP Cubes in BIDS for Salesforce and PRISM Pin
S Douglas26-Feb-14 8:54
professionalS Douglas26-Feb-14 8:54 
QuestionDelete from MySQL + select affected Pin
Member 993503818-Feb-14 3:41
Member 993503818-Feb-14 3:41 
AnswerRe: Delete from MySQL + select affected Pin
Eddy Vluggen18-Feb-14 7:04
professionalEddy Vluggen18-Feb-14 7:04 
AnswerRe: Delete from MySQL + select affected Pin
ZurdoDev18-Feb-14 15:33
professionalZurdoDev18-Feb-14 15:33 
Questionoracle transaction with multi OracleConnection c # ? Pin
Member 1059782417-Feb-14 9:19
Member 1059782417-Feb-14 9:19 
AnswerRe: oracle transaction with multi OracleConnection c # ? Pin
Shameel18-Feb-14 0:00
professionalShameel18-Feb-14 0:00 
AnswerRe: oracle transaction with multi OracleConnection c # ? Pin
Simon_Whale18-Feb-14 0:46
Simon_Whale18-Feb-14 0:46 
QuestionHandling ISODate in mongoDB when Deserializing from JSON Pin
ThetaClear16-Feb-14 19:48
ThetaClear16-Feb-14 19:48 
QuestionWhere to do the processing Pin
Mycroft Holmes15-Feb-14 22:28
professionalMycroft Holmes15-Feb-14 22:28 
AnswerRe: Where to do the processing Pin
Eddy Vluggen15-Feb-14 23:25
professionalEddy Vluggen15-Feb-14 23:25 
GeneralRe: Where to do the processing Pin
Mycroft Holmes16-Feb-14 1:14
professionalMycroft Holmes16-Feb-14 1:14 
AnswerRe: Where to do the processing Pin
Kornfeld Eliyahu Peter15-Feb-14 23:30
professionalKornfeld Eliyahu Peter15-Feb-14 23:30 
GeneralRe: Where to do the processing Pin
Mycroft Holmes16-Feb-14 1:05
professionalMycroft Holmes16-Feb-14 1:05 
GeneralRe: Where to do the processing Pin
Kornfeld Eliyahu Peter16-Feb-14 1:14
professionalKornfeld Eliyahu Peter16-Feb-14 1:14 
GeneralRe: Where to do the processing Pin
Mycroft Holmes16-Feb-14 1:18
professionalMycroft Holmes16-Feb-14 1:18 
GeneralRe: Where to do the processing Pin
Kornfeld Eliyahu Peter16-Feb-14 1:27
professionalKornfeld Eliyahu Peter16-Feb-14 1:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.