I have a table with a primary key but it is not indexing by one number at a time. I have set up the primary keys identity specification to increment by one but it will sometimes increment by more than one and Im not sure why. Is there another setting I should have where it does not increment unless something is actually saved in the data row.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Synchronize record number with primary key
- Thread starter Saturn57
- Start date
Identity means only identity. Absolutely nothing else.
It does not guarantee a continuous sequence.
If you try to insert a record, but the insert fails, or if the transaction is rolled back after the insert has succeeded, the generated identities are lost.
I think you can't do anything about it, except write your own sequential number according to the business rules.
HTH
![[pipe]](/data/assets/smilies/pipe.gif "[pipe] [pipe]")
Daniel Vlas
Systems Consultant
It does not guarantee a continuous sequence.
If you try to insert a record, but the insert fails, or if the transaction is rolled back after the insert has succeeded, the generated identities are lost.
I think you can't do anything about it, except write your own sequential number according to the business rules.
HTH
Daniel Vlas
Systems Consultant
- Thread starter
- #3
Saturn, don't do that you will have concurrency problems. Just accept that there will be gaps. The record id should be a meaningless number anyway. Why does it matter if there are gaps?
"NOTHING is more important in a database than integrity." ESquared
"NOTHING is more important in a database than integrity." ESquared
As SQLSister poined out, you shouldn't do it just for the sake of having sequential numbers as a primary key.
But, if you need that to have some kind of document numbering, you must define the rules and then find a way to model them in the database.
For example, you want to number your invoices from 1 to whatever number over one year, then re-start the numbering. So, you need to work with the number and the date of the invoice. As a side note, in such case you should have a unique index InvNumber + InvYear, to keep you on the safe side.
In this case, you need to calculate
Select @NewNumber=coalesce(max(InvNumber), 0) + 1 from tblInvoices where Year(@InvDate)=Year(GetDate())
Then you will use this number in your individual insert.
Of course, everything should be wrapped in a transaction, otherwise you'll get duplicate entries sooner or later.
There are other options as well, but first you need to define exactly what you want and be prepared to change it in the future without affecting existing data.
HTH
Daniel Vlas
Systems Consultant
But, if you need that to have some kind of document numbering, you must define the rules and then find a way to model them in the database.
For example, you want to number your invoices from 1 to whatever number over one year, then re-start the numbering. So, you need to work with the number and the date of the invoice. As a side note, in such case you should have a unique index InvNumber + InvYear, to keep you on the safe side.
In this case, you need to calculate
Select @NewNumber=coalesce(max(InvNumber), 0) + 1 from tblInvoices where Year(@InvDate)=Year(GetDate())
Then you will use this number in your individual insert.
Of course, everything should be wrapped in a transaction, otherwise you'll get duplicate entries sooner or later.
There are other options as well, but first you need to define exactly what you want and be prepared to change it in the future without affecting existing data.
HTH
Daniel Vlas
Systems Consultant
- Thread starter
- #6
I don't get it?
Why do you care if there are gaps in that field?
That field doesn't care no information in it (as SQLSister already said). It is a field that make your record unique, nothing more. So what is the big problem if you have:
1
2
7
8
11
12
...
instead of
1
2
3
4
5
...
Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Why do you care if there are gaps in that field?
That field doesn't care no information in it (as SQLSister already said). It is a field that make your record unique, nothing more. So what is the big problem if you have:
1
2
7
8
11
12
...
instead of
1
2
3
4
5
...
Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Saturn57, I am not talking about the primary key
Leave your primary key as it is and think that way: primary key belongs to the programmer, document number belongs to the user.
The primary key has absolutely no meaning to the user and you shouldn't waste time to add 'features' to it or to make it visible to the users. You have an identity field, leave it that way and use it internally for relationships and joins. That's it, nothing more.
Now, to make your users happy: add one 'DocNumber' field to your table. This will be what users are going to see.
Do not use just Max, because it would yield Null when the year changes. That's what Coalesce does: turns the Null into 1.
Then, your Insert will be something like:
and you grab the ID by:
Unless you define what you really need, I'm totally with SQLSister and bborissov: you shouldn't care about the gaps in primary key numbering. Not because it's impossible, but because it's bad for your database.
Daniel Vlas
Systems Consultant
Leave your primary key as it is and think that way: primary key belongs to the programmer, document number belongs to the user.
The primary key has absolutely no meaning to the user and you shouldn't waste time to add 'features' to it or to make it visible to the users. You have an identity field, leave it that way and use it internally for relationships and joins. That's it, nothing more.
Now, to make your users happy: add one 'DocNumber' field to your table. This will be what users are going to see.
Code:
Select @NewNumber=coalesce(max(DocNumber), 0) + 1 from tblInvoices where Year(DocDate)=Year(@DocDate)Do not use just Max, because it would yield Null when the year changes. That's what Coalesce does: turns the Null into 1.
Then, your Insert will be something like:
Code:
Insert Into YourTable(DocNumber, DocDate, OtherFields)
Values(@NewNumber, @DocDate, @OtherParameters)and you grab the ID by:
Code:
Select @ID=scope_identity()Unless you define what you really need, I'm totally with SQLSister and bborissov: you shouldn't care about the gaps in primary key numbering. Not because it's impossible, but because it's bad for your database.
Daniel Vlas
Systems Consultant
- Thread starter
- #10
What is your current table structure?
How do you insert information in the table?
Is it some kind of Access application with ODBC linked tables and you enter data directly through a bound form?
If not, you must have some sort of Insert statement or a stored procedure.
If yes... then you seem to have a problem with database management and you're trying to accomplish something you're not yet ready for.
Daniel Vlas
Systems Consultant
How do you insert information in the table?
Is it some kind of Access application with ODBC linked tables and you enter data directly through a bound form?
If not, you must have some sort of Insert statement or a stored procedure.
If yes... then you seem to have a problem with database management and you're trying to accomplish something you're not yet ready for.
Daniel Vlas
Systems Consultant
- Thread starter
- #12
- Thread starter
- #14
Here is the structure of the two tables and I insert data by using a save button on the form in my program that we created using visual studio. Note the two tables are linked with the estimateno and estno fields.
Header Table
USE [Estimator]
GO
/****** Object: Table [dbo].[QuoteCalcHeader] Script Date: 03/25/2009 08:39:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[QuoteCalcHeader](
[estimateno] [int] IDENTITY(1,1) NOT NULL,
[filelockbit] [bit] NULL,
[date] [datetime] NULL CONSTRAINT [DF__QuoteCalcH__Date__398D8EEE] DEFAULT (CONVERT([datetime],CONVERT([varchar],getdate(),(1)),(1))),
[customer] [nvarchar](50) NULL,
[contact] [nvarchar](50) NULL,
[partno] [nvarchar](50) NULL,
[partname] [nvarchar](50) NULL,
[englev] [nvarchar](50) NULL,
[materialname] [nvarchar](50) NULL,
[mattype] [nchar](100) NULL,
[matthick] [decimal](8, 3) NULL CONSTRAINT [DF__QuoteCalc__Mater__3A81B327] DEFAULT ((0)),
[blankl] [decimal](18, 2) NULL CONSTRAINT [DF__QuoteCalc__Blank__3B75D760] DEFAULT ((0)),
[blankw] [decimal](18, 2) NULL CONSTRAINT [DF__QuoteCalc__Blank__3C69FB99] DEFAULT ((0)),
[blankp] [decimal](18, 2) NULL CONSTRAINT [DF__QuoteCalc__Blank__3D5E1FD2] DEFAULT ((0)),
[makesnoparts] [int] NULL CONSTRAINT [DF__QuoteCalc__Makes__3E52440B] DEFAULT ((0)),
[oem] [nvarchar](50) NULL,
[program] [nvarchar](50) NULL,
[partdepth] [decimal](10, 2) NULL CONSTRAINT [DF__QuoteCalc__Model__3F466844] DEFAULT ((0)),
[tapeno] [nvarchar](50) NULL,
[revcomments] [nvarchar](3000) NULL,
[terms] [nvarchar](100) NULL CONSTRAINT [DF__QuoteCalc__Terms__403A8C7D] DEFAULT ('10%Design,25%Material,55%After Tryout At Saturn Prior To Delivery,10% Net 30 Days'),
[currency] [nvarchar](50) NULL,
[exchange] [decimal](18, 2) NULL CONSTRAINT [DF__QuoteCalc__Excha__412EB0B6] DEFAULT ((0)),
[delivery] [int] NULL CONSTRAINT [DF__QuoteCalc__Deliv__4222D4EF] DEFAULT ((0)),
[fob] [nvarchar](50) NULL CONSTRAINT [DF__QuoteCalcHe__FOB__4316F928] DEFAULT ('Saturn'),
[jobno] [nvarchar](50) NULL,
[address] [nvarchar](50) NULL,
[city] [nvarchar](50) NULL,
[postalcode] [nvarchar](50) NULL,
[rfqno] [nvarchar](50) NULL,
[comments] [nvarchar](3000) NULL,
[partpic] [image] NULL,
[homelinecost] [money] NULL,
[transport] [money] NULL,
[carrycost] [money] NULL,
[discount] [decimal](5, 2) NULL CONSTRAINT [DF_QuoteCalcHeader_discount] DEFAULT ((0)),
[english] [bit] NULL,
[procpic1] [image] NULL,
[procpic2] [image] NULL,
[procpic3] [image] NULL,
[procpic4] [image] NULL,
[procpic5] [image] NULL,
[procpic6] [image] NULL,
CONSTRAINT [PK_QuoteCalcHeader] PRIMARY KEY CLUSTERED
(
[estimateno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Detail Table
USE [Estimator]
GO
/****** Object: Table [dbo].[QuoteCalcBody] Script Date: 03/25/2009 08:40:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[QuoteCalcBody](
[estno] [int] NOT NULL CONSTRAINT [DF__QuoteCalc__Estim__7C8480AE] DEFAULT ((1.45)),
[operno] [int] IDENTITY(1,1) NOT NULL,
[filelockbit] [bit] NULL,
[quantity] [int] NULL CONSTRAINT [DF__QuoteCalc__Quant__7D78A4E7] DEFAULT ((0)),
[englishunits] [bit] NULL,
[diename] [nvarchar](50) NULL,
[diel] [decimal](10, 3) NULL CONSTRAINT [DF__QuoteCalc__Die L__7E6CC920] DEFAULT ((0)),
[diew] [decimal](10, 3) NULL CONSTRAINT [DF__QuoteCalc__Die W__7F60ED59] DEFAULT ((0)),
[diesh] [decimal](10, 3) NULL CONSTRAINT [DF__QuoteCalc__Die S__00551192] DEFAULT ((0)),
[density] [decimal](5, 2) NULL CONSTRAINT [DF__QuoteCalc__Densi__014935CB] DEFAULT ((0)),
[system] [nvarchar](20) NULL,
[construction] [nvarchar](20) NULL,
[noofops] [int] NULL CONSTRAINT [DF__QuoteCalc__No Of__023D5A04] DEFAULT ((0)),
[optyp] [nvarchar](20) NULL,
[dod] [nvarchar](20) NULL CONSTRAINT [DF__QuoteCalcBo__DOD__03317E3D] DEFAULT ((0)),
[cnccomplex] [nvarchar](20) NULL CONSTRAINT [DF__QuoteCalc__DSFac__0425A276] DEFAULT ((0)),
[pins] [int] NULL,
[wear] [int] NULL,
[dieworkareapercentage] [decimal](5, 2) NULL CONSTRAINT [DF_QuoteCalcBody_dieworkareapercentage] DEFAULT ((1)),
[lengthoftrim] [decimal](7, 2) NULL,
[diesections] [int] NULL,
[nitrogenupper] [bit] NULL,
[nitrogenlower] [bit] NULL,
[aircushion] [bit] NULL,
[noofsections] [int] NULL,
[noofholes] [int] NULL CONSTRAINT [DF__QuoteCalc__# Of __07F6335A] DEFAULT ((0)),
[qtyacs] [int] NULL CONSTRAINT [DF__QuoteCalc__# Of __09DE7BCC] DEFAULT ((0)),
[qtyacm] [int] NULL,
[qtyacl] [int] NULL,
[qtydmcs] [int] NULL CONSTRAINT [DF__QuoteCalc__# Of __0BC6C43E] DEFAULT ((0)),
[qtydmcm] [int] NULL,
[qtydmcl] [int] NULL,
[coatingcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_coatingcost] DEFAULT ((0)),
[specialitems] [nvarchar](50) NULL,
[specialitemcost] [money] NULL CONSTRAINT [DF__QuoteCalc__Speci__0DAF0CB0] DEFAULT ((0)),
[basicmatcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_basicmatcost] DEFAULT ((0)),
[patternmatcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_patternmatcost] DEFAULT ((0)),
[pinmatcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_pinmatcost] DEFAULT ((0)),
[wearmatcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_wearmatcost] DEFAULT ((0)),
[holematcost] [money] NULL CONSTRAINT [DF__QuoteCalc__Hole __08EA5793] DEFAULT ((0)),
[dmcmatcost] [money] NULL CONSTRAINT [DF__QuoteCalc__Die M__0CBAE877] DEFAULT ((0)),
[acmatcost] [money] NULL CONSTRAINT [DF__QuoteCalc__Aeria__0AD2A005] DEFAULT ((0)),
[nitrogenmatcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_nitrogenmatcost] DEFAULT ((0)),
[sectionmatcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_sectionmatcost] DEFAULT ((0)),
[totalmaterialcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_totalmaterialcost] DEFAULT ((0)),
[basicprochrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basicprochrs] DEFAULT ((0)),
[totalprochrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totalprochrs] DEFAULT ((0)),
[basicsimhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basicsimhrs] DEFAULT ((0)),
[totalsimhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totalsimhrs] DEFAULT ((0)),
[basicdsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basicdsgnhrs] DEFAULT ((0)),
[pindsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_pindsgnhrs] DEFAULT ((0)),
[weardsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_weardsgnhrs] DEFAULT ((0)),
[nitrodsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_nitrodsgnhrs] DEFAULT ((0)),
[holedsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_holedsgnhrs] DEFAULT ((0)),
[acdsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_acdsgnhrs] DEFAULT ((0)),
[dmcdsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_dmcdsgnhrs] DEFAULT ((0)),
[sectiondsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_sectiondsgnhrs] DEFAULT ((0)),
[totaldsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totaldsgnhrs] DEFAULT ((0)),
[basic2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basic2chrs] DEFAULT ((0)),
[pin2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_pin2chrs] DEFAULT ((0)),
[wear2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_wear2chrs] DEFAULT ((0)),
[nitro2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_nitro2chrs] DEFAULT ((0)),
[hole2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_hole2chrs] DEFAULT ((0)),
[ac2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_ac2chrs] DEFAULT ((0)),
[dmc2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_dmc2chrs] DEFAULT ((0)),
[sec2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_sec2chrs] DEFAULT ((0)),
[trim2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_trim2chrs] DEFAULT ((0)),
[total2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_total2chrs] DEFAULT ((0)),
[basic2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basic2dhrs] DEFAULT ((0)),
[pin2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_pin2dhrs] DEFAULT ((0)),
[wear2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_wear2dhrs] DEFAULT ((0)),
[nitro2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_nitro2dhrs] DEFAULT ((0)),
[hole2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_hole2dhrs] DEFAULT ((0)),
[ac2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_ac2dhrs] DEFAULT ((0)),
[dmc2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_dmc2dhrs] DEFAULT ((0)),
[section2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_section2dhrs] DEFAULT ((0)),
[trim2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_trim2dhrs] DEFAULT ((0)),
[total2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_total2dhrs] DEFAULT ((0)),
[basic3chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basic3chrs] DEFAULT ((0)),
[total3chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_total3chrs] DEFAULT ((0)),
[basic3dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basic3dhrs] DEFAULT ((0)),
[total3dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_total3dhrs] DEFAULT ((0)),
[basicassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basicassyhrs] DEFAULT ((0)),
[pinassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_pinassyhrs] DEFAULT ((0)),
[wearassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_wearassyhrs] DEFAULT ((0)),
[nitroassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_nitroassyhrs] DEFAULT ((0)),
[holeassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_holeassyhrs] DEFAULT ((0)),
[acassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_acassyhrs] DEFAULT ((0)),
[dmcassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_dmcassyhrs] DEFAULT ((0)),
[sectionassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_sectionassyhrs] DEFAULT ((0)),
[totalassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totalassyhrs] DEFAULT ((0)),
[basicspothrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basicspothrs] DEFAULT ((0)),
[holespothrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_holespothrs] DEFAULT ((0)),
[totalspothrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totalspothrs] DEFAULT ((0)),
[basictryhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basictryhrs] DEFAULT ((0)),
[holetryhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_holetryhrs] DEFAULT ((0)),
[totaltryhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totaltryhrs] DEFAULT ((0)),
[totalhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totalhrs] DEFAULT ((0)),
[prochradj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_prochradj] DEFAULT ((0)),
[simhradj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_simhradj] DEFAULT ((0)),
[dsgnhradj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_dsgnhradj] DEFAULT ((0)),
[hrs2cadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_hrs2cadj] DEFAULT ((0)),
[hrs2dadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_hrs2dadj] DEFAULT ((0)),
[hrs3cadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_hrs3cadj] DEFAULT ((0)),
[hrs3dadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_hrs3dadj] DEFAULT ((0)),
[assyadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_assyadj] DEFAULT ((0)),
[spotadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_spotadj] DEFAULT ((0)),
[tryadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_tryadj] DEFAULT ((0)),
[processprice] [money] NULL CONSTRAINT [DF_QuoteCalcBody_processprice] DEFAULT ((0)),
[simulationprice] [money] NULL CONSTRAINT [DF_QuoteCalcBody_simulationprice] DEFAULT ((0)),
[designprice] [money] NULL CONSTRAINT [DF_QuoteCalcBody_designprice] DEFAULT ((0)),
[price2c] [money] NULL CONSTRAINT [DF_QuoteCalcBody_price2c] DEFAULT ((0)),
[price2d] [money] NULL CONSTRAINT [DF_QuoteCalcBody_price2d] DEFAULT ((0)),
[price3c] [money] NULL CONSTRAINT [DF_QuoteCalcBody_price3c] DEFAULT ((0)),
[price3d] [money] NULL CONSTRAINT [DF_QuoteCalcBody_price3d] DEFAULT ((0)),
[assyprice] [money] NULL CONSTRAINT [DF_QuoteCalcBody_assyprice] DEFAULT ((0)),
[spotprice] [money] NULL CONSTRAINT [DF_QuoteCalcBody_spotprice] DEFAULT ((0)),
[tryprice] [money] NULL CONSTRAINT [DF_QuoteCalcBody_tryprice] DEFAULT ((0)),
[diediscount] [decimal](18, 2) NULL CONSTRAINT [DF__QuoteCalcBod__Ds__1367E606] DEFAULT ((0)),
[pricecdnprediscount] [money] NULL CONSTRAINT [DF__QuoteCalc__Price__0EA330E9] DEFAULT ((0)),
[pricecdnpostdiscount] [money] NULL CONSTRAINT [DF_QuoteCalcBody_pricecdnpostdiscount] DEFAULT ((0)),
[priceforeignpostdiscount] [money] NULL CONSTRAINT [DF_QuoteCalcBody_priceforeignpostdiscount] DEFAULT ((0)),
[cdndolperhr] [money] NULL,
[matpercofprice] [decimal](5, 2) NULL,
CONSTRAINT [PK_QuoteCalcBody_1] PRIMARY KEY CLUSTERED
(
[operno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Header Table
USE [Estimator]
GO
/****** Object: Table [dbo].[QuoteCalcHeader] Script Date: 03/25/2009 08:39:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[QuoteCalcHeader](
[estimateno] [int] IDENTITY(1,1) NOT NULL,
[filelockbit] [bit] NULL,
[date] [datetime] NULL CONSTRAINT [DF__QuoteCalcH__Date__398D8EEE] DEFAULT (CONVERT([datetime],CONVERT([varchar],getdate(),(1)),(1))),
[customer] [nvarchar](50) NULL,
[contact] [nvarchar](50) NULL,
[partno] [nvarchar](50) NULL,
[partname] [nvarchar](50) NULL,
[englev] [nvarchar](50) NULL,
[materialname] [nvarchar](50) NULL,
[mattype] [nchar](100) NULL,
[matthick] [decimal](8, 3) NULL CONSTRAINT [DF__QuoteCalc__Mater__3A81B327] DEFAULT ((0)),
[blankl] [decimal](18, 2) NULL CONSTRAINT [DF__QuoteCalc__Blank__3B75D760] DEFAULT ((0)),
[blankw] [decimal](18, 2) NULL CONSTRAINT [DF__QuoteCalc__Blank__3C69FB99] DEFAULT ((0)),
[blankp] [decimal](18, 2) NULL CONSTRAINT [DF__QuoteCalc__Blank__3D5E1FD2] DEFAULT ((0)),
[makesnoparts] [int] NULL CONSTRAINT [DF__QuoteCalc__Makes__3E52440B] DEFAULT ((0)),
[oem] [nvarchar](50) NULL,
[program] [nvarchar](50) NULL,
[partdepth] [decimal](10, 2) NULL CONSTRAINT [DF__QuoteCalc__Model__3F466844] DEFAULT ((0)),
[tapeno] [nvarchar](50) NULL,
[revcomments] [nvarchar](3000) NULL,
[terms] [nvarchar](100) NULL CONSTRAINT [DF__QuoteCalc__Terms__403A8C7D] DEFAULT ('10%Design,25%Material,55%After Tryout At Saturn Prior To Delivery,10% Net 30 Days'),
[currency] [nvarchar](50) NULL,
[exchange] [decimal](18, 2) NULL CONSTRAINT [DF__QuoteCalc__Excha__412EB0B6] DEFAULT ((0)),
[delivery] [int] NULL CONSTRAINT [DF__QuoteCalc__Deliv__4222D4EF] DEFAULT ((0)),
[fob] [nvarchar](50) NULL CONSTRAINT [DF__QuoteCalcHe__FOB__4316F928] DEFAULT ('Saturn'),
[jobno] [nvarchar](50) NULL,
[address] [nvarchar](50) NULL,
[city] [nvarchar](50) NULL,
[postalcode] [nvarchar](50) NULL,
[rfqno] [nvarchar](50) NULL,
[comments] [nvarchar](3000) NULL,
[partpic] [image] NULL,
[homelinecost] [money] NULL,
[transport] [money] NULL,
[carrycost] [money] NULL,
[discount] [decimal](5, 2) NULL CONSTRAINT [DF_QuoteCalcHeader_discount] DEFAULT ((0)),
[english] [bit] NULL,
[procpic1] [image] NULL,
[procpic2] [image] NULL,
[procpic3] [image] NULL,
[procpic4] [image] NULL,
[procpic5] [image] NULL,
[procpic6] [image] NULL,
CONSTRAINT [PK_QuoteCalcHeader] PRIMARY KEY CLUSTERED
(
[estimateno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Detail Table
USE [Estimator]
GO
/****** Object: Table [dbo].[QuoteCalcBody] Script Date: 03/25/2009 08:40:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[QuoteCalcBody](
[estno] [int] NOT NULL CONSTRAINT [DF__QuoteCalc__Estim__7C8480AE] DEFAULT ((1.45)),
[operno] [int] IDENTITY(1,1) NOT NULL,
[filelockbit] [bit] NULL,
[quantity] [int] NULL CONSTRAINT [DF__QuoteCalc__Quant__7D78A4E7] DEFAULT ((0)),
[englishunits] [bit] NULL,
[diename] [nvarchar](50) NULL,
[diel] [decimal](10, 3) NULL CONSTRAINT [DF__QuoteCalc__Die L__7E6CC920] DEFAULT ((0)),
[diew] [decimal](10, 3) NULL CONSTRAINT [DF__QuoteCalc__Die W__7F60ED59] DEFAULT ((0)),
[diesh] [decimal](10, 3) NULL CONSTRAINT [DF__QuoteCalc__Die S__00551192] DEFAULT ((0)),
[density] [decimal](5, 2) NULL CONSTRAINT [DF__QuoteCalc__Densi__014935CB] DEFAULT ((0)),
[system] [nvarchar](20) NULL,
[construction] [nvarchar](20) NULL,
[noofops] [int] NULL CONSTRAINT [DF__QuoteCalc__No Of__023D5A04] DEFAULT ((0)),
[optyp] [nvarchar](20) NULL,
[dod] [nvarchar](20) NULL CONSTRAINT [DF__QuoteCalcBo__DOD__03317E3D] DEFAULT ((0)),
[cnccomplex] [nvarchar](20) NULL CONSTRAINT [DF__QuoteCalc__DSFac__0425A276] DEFAULT ((0)),
[pins] [int] NULL,
[wear] [int] NULL,
[dieworkareapercentage] [decimal](5, 2) NULL CONSTRAINT [DF_QuoteCalcBody_dieworkareapercentage] DEFAULT ((1)),
[lengthoftrim] [decimal](7, 2) NULL,
[diesections] [int] NULL,
[nitrogenupper] [bit] NULL,
[nitrogenlower] [bit] NULL,
[aircushion] [bit] NULL,
[noofsections] [int] NULL,
[noofholes] [int] NULL CONSTRAINT [DF__QuoteCalc__# Of __07F6335A] DEFAULT ((0)),
[qtyacs] [int] NULL CONSTRAINT [DF__QuoteCalc__# Of __09DE7BCC] DEFAULT ((0)),
[qtyacm] [int] NULL,
[qtyacl] [int] NULL,
[qtydmcs] [int] NULL CONSTRAINT [DF__QuoteCalc__# Of __0BC6C43E] DEFAULT ((0)),
[qtydmcm] [int] NULL,
[qtydmcl] [int] NULL,
[coatingcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_coatingcost] DEFAULT ((0)),
[specialitems] [nvarchar](50) NULL,
[specialitemcost] [money] NULL CONSTRAINT [DF__QuoteCalc__Speci__0DAF0CB0] DEFAULT ((0)),
[basicmatcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_basicmatcost] DEFAULT ((0)),
[patternmatcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_patternmatcost] DEFAULT ((0)),
[pinmatcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_pinmatcost] DEFAULT ((0)),
[wearmatcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_wearmatcost] DEFAULT ((0)),
[holematcost] [money] NULL CONSTRAINT [DF__QuoteCalc__Hole __08EA5793] DEFAULT ((0)),
[dmcmatcost] [money] NULL CONSTRAINT [DF__QuoteCalc__Die M__0CBAE877] DEFAULT ((0)),
[acmatcost] [money] NULL CONSTRAINT [DF__QuoteCalc__Aeria__0AD2A005] DEFAULT ((0)),
[nitrogenmatcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_nitrogenmatcost] DEFAULT ((0)),
[sectionmatcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_sectionmatcost] DEFAULT ((0)),
[totalmaterialcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_totalmaterialcost] DEFAULT ((0)),
[basicprochrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basicprochrs] DEFAULT ((0)),
[totalprochrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totalprochrs] DEFAULT ((0)),
[basicsimhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basicsimhrs] DEFAULT ((0)),
[totalsimhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totalsimhrs] DEFAULT ((0)),
[basicdsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basicdsgnhrs] DEFAULT ((0)),
[pindsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_pindsgnhrs] DEFAULT ((0)),
[weardsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_weardsgnhrs] DEFAULT ((0)),
[nitrodsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_nitrodsgnhrs] DEFAULT ((0)),
[holedsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_holedsgnhrs] DEFAULT ((0)),
[acdsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_acdsgnhrs] DEFAULT ((0)),
[dmcdsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_dmcdsgnhrs] DEFAULT ((0)),
[sectiondsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_sectiondsgnhrs] DEFAULT ((0)),
[totaldsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totaldsgnhrs] DEFAULT ((0)),
[basic2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basic2chrs] DEFAULT ((0)),
[pin2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_pin2chrs] DEFAULT ((0)),
[wear2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_wear2chrs] DEFAULT ((0)),
[nitro2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_nitro2chrs] DEFAULT ((0)),
[hole2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_hole2chrs] DEFAULT ((0)),
[ac2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_ac2chrs] DEFAULT ((0)),
[dmc2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_dmc2chrs] DEFAULT ((0)),
[sec2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_sec2chrs] DEFAULT ((0)),
[trim2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_trim2chrs] DEFAULT ((0)),
[total2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_total2chrs] DEFAULT ((0)),
[basic2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basic2dhrs] DEFAULT ((0)),
[pin2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_pin2dhrs] DEFAULT ((0)),
[wear2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_wear2dhrs] DEFAULT ((0)),
[nitro2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_nitro2dhrs] DEFAULT ((0)),
[hole2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_hole2dhrs] DEFAULT ((0)),
[ac2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_ac2dhrs] DEFAULT ((0)),
[dmc2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_dmc2dhrs] DEFAULT ((0)),
[section2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_section2dhrs] DEFAULT ((0)),
[trim2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_trim2dhrs] DEFAULT ((0)),
[total2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_total2dhrs] DEFAULT ((0)),
[basic3chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basic3chrs] DEFAULT ((0)),
[total3chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_total3chrs] DEFAULT ((0)),
[basic3dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basic3dhrs] DEFAULT ((0)),
[total3dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_total3dhrs] DEFAULT ((0)),
[basicassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basicassyhrs] DEFAULT ((0)),
[pinassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_pinassyhrs] DEFAULT ((0)),
[wearassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_wearassyhrs] DEFAULT ((0)),
[nitroassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_nitroassyhrs] DEFAULT ((0)),
[holeassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_holeassyhrs] DEFAULT ((0)),
[acassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_acassyhrs] DEFAULT ((0)),
[dmcassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_dmcassyhrs] DEFAULT ((0)),
[sectionassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_sectionassyhrs] DEFAULT ((0)),
[totalassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totalassyhrs] DEFAULT ((0)),
[basicspothrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basicspothrs] DEFAULT ((0)),
[holespothrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_holespothrs] DEFAULT ((0)),
[totalspothrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totalspothrs] DEFAULT ((0)),
[basictryhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basictryhrs] DEFAULT ((0)),
[holetryhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_holetryhrs] DEFAULT ((0)),
[totaltryhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totaltryhrs] DEFAULT ((0)),
[totalhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totalhrs] DEFAULT ((0)),
[prochradj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_prochradj] DEFAULT ((0)),
[simhradj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_simhradj] DEFAULT ((0)),
[dsgnhradj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_dsgnhradj] DEFAULT ((0)),
[hrs2cadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_hrs2cadj] DEFAULT ((0)),
[hrs2dadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_hrs2dadj] DEFAULT ((0)),
[hrs3cadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_hrs3cadj] DEFAULT ((0)),
[hrs3dadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_hrs3dadj] DEFAULT ((0)),
[assyadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_assyadj] DEFAULT ((0)),
[spotadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_spotadj] DEFAULT ((0)),
[tryadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_tryadj] DEFAULT ((0)),
[processprice] [money] NULL CONSTRAINT [DF_QuoteCalcBody_processprice] DEFAULT ((0)),
[simulationprice] [money] NULL CONSTRAINT [DF_QuoteCalcBody_simulationprice] DEFAULT ((0)),
[designprice] [money] NULL CONSTRAINT [DF_QuoteCalcBody_designprice] DEFAULT ((0)),
[price2c] [money] NULL CONSTRAINT [DF_QuoteCalcBody_price2c] DEFAULT ((0)),
[price2d] [money] NULL CONSTRAINT [DF_QuoteCalcBody_price2d] DEFAULT ((0)),
[price3c] [money] NULL CONSTRAINT [DF_QuoteCalcBody_price3c] DEFAULT ((0)),
[price3d] [money] NULL CONSTRAINT [DF_QuoteCalcBody_price3d] DEFAULT ((0)),
[assyprice] [money] NULL CONSTRAINT [DF_QuoteCalcBody_assyprice] DEFAULT ((0)),
[spotprice] [money] NULL CONSTRAINT [DF_QuoteCalcBody_spotprice] DEFAULT ((0)),
[tryprice] [money] NULL CONSTRAINT [DF_QuoteCalcBody_tryprice] DEFAULT ((0)),
[diediscount] [decimal](18, 2) NULL CONSTRAINT [DF__QuoteCalcBod__Ds__1367E606] DEFAULT ((0)),
[pricecdnprediscount] [money] NULL CONSTRAINT [DF__QuoteCalc__Price__0EA330E9] DEFAULT ((0)),
[pricecdnpostdiscount] [money] NULL CONSTRAINT [DF_QuoteCalcBody_pricecdnpostdiscount] DEFAULT ((0)),
[priceforeignpostdiscount] [money] NULL CONSTRAINT [DF_QuoteCalcBody_priceforeignpostdiscount] DEFAULT ((0)),
[cdndolperhr] [money] NULL,
[matpercofprice] [decimal](5, 2) NULL,
CONSTRAINT [PK_QuoteCalcBody_1] PRIMARY KEY CLUSTERED
(
[operno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
- Status
Similar threads
- Locked
- Question