[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 72
  • Last Modified:

Increasing Identity length in sql server

I have a table in my SQL that currently has 900+ records and I noticed that the ID length is 4. I'm worried that when it reaches 9999 it will give me error and no longer write data.

How do I increase the length?

I know that I can reset the seed with "DBCC CHECKIDENT ([WIP], RESEED, 0); GO" however that will cause duplicates.
0
Jaime Martinez
Asked:
Jaime Martinez
  • 2
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Just for kicks and giggle generate the CREATE TABLE schema for this table and paste it into this question.

In SSMS, click on table, right-click > Script Table As > Create To > New Query Editor Window
0
 
Jaime MartinezERP System AnalystAuthor Commented:
Jim here is the schema

USE [UE_Internal]
GO

/****** Object:  Table [dbo].[WIP]    Script Date: 12/1/2016 2:48:26 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[WIP](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [CompID] [nvarchar](255) NULL,
      [ShipStatus] [nvarchar](243) NULL,
      [Factory] [nvarchar](255) NULL,
      [OrdNumber] [nvarchar](255) NULL,
      [OrdCust] [nvarchar](255) NULL,
      [OrdPO] [nvarchar](255) NULL,
      [OrdState] [nvarchar](255) NULL,
      [OrdWhse] [nvarchar](255) NULL,
      [OrdStartShip] [datetime] NULL,
      [OrdCancelDate] [datetime] NULL,
      [OrdProdID] [nvarchar](255) NULL,
      [OrdProdDesc] [nvarchar](255) NULL,
      [OrdProdQTY] [nvarchar](255) NULL,
      [OrdStyleNotes] [ntext] NULL,
      [PrdOrdDate] [datetime] NULL,
      [PrdBuyNote] [nvarchar](255) NULL,
      [PrdPiDate] [datetime] NULL,
      [PrdTesting] [nvarchar](255) NULL,
      [PrdTestingState] [nvarchar](255) NULL,
      [PrdTestingDate] [datetime] NULL,
      [PrdInnerLabel] [nvarchar](255) NULL,
      [PrdHangtags] [nvarchar](255) NULL,
      [PrdCareLabels] [nvarchar](255) NULL,
      [PrdPriceTickets] [nvarchar](255) NULL,
      [PrdPriceTicketsOrdDate] [datetime] NULL,
      [PrdPriceTicketsSentDate] [datetime] NULL,
      [PrdPriceTicketsTracking] [nvarchar](255) NULL,
      [PrdDeliveryDate] [datetime] NULL,
      [ItsNumber] [nvarchar](255) NULL,
      [ItsEta] [datetime] NULL,
      [ItsVessalDate] [datetime] NULL,
      [ItsInHouseDate] [datetime] NULL,
      [ItsReference] [nvarchar](255) NULL,
      [ItsContainer] [nvarchar](255) NULL,
      [ItsArriveDate] [datetime] NULL,
      [VpoNumber] [nvarchar](255) NULL,
      [PP] [datetime] NULL,
      [PhotoSamples] [datetime] NULL,
      [2ndPP] [datetime] NULL,
      [Top] [datetime] NULL,
      [WipNotes] [ntext] NULL,
      [ReadyOrPending] [nvarchar](510) NULL,
      [ReworkVendor] [nvarchar](510) NULL,
      [WIPStatus] [nvarchar](510) NULL,
      [ToWhse] [nvarchar](510) NULL,
      [WhseNote] [nvarchar](510) NULL,
      [Field1] [nvarchar](510) NULL,
      [Field2] [nvarchar](510) NULL,
      [Field3] [nvarchar](510) NULL,
      [Field4] [nvarchar](510) NULL,
      [PPAprroved] [datetime] NULL,
      [TopApproved] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I'm worried that when it reaches 9999 it will give me error and no longer write data.
>      [ID] [int] IDENTITY(1,1) NOT NULL,
Since ID is an int it will continue incrimenting by 1 until the max allowable value for an int data type = 2,147,483,647

So, no 9999 issues.
0
 
Jaime MartinezERP System AnalystAuthor Commented:
Thanks Jim, I didn't want to run into any issues later.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now