Transact SQL :: Server Identity Or A Self Calculated Sequence
Jun 14, 2015
I am designing a database. I want to define a automatic sequence on a table primary key field. what is the best solution for it?I know I can enable identity property for a field, but it has some problems ( for example its seed jumps on restart and unsuccessful events).I also can use some calculated sequences. for example I can calculate max of the filed values and after incrementing use it as key for new inserted record.
View 4 Replies
ADVERTISEMENT
Dec 12, 2012
I've started using a SEQUENCE in a table instead of an identity.
I seem to be experiencing problems of the sequence getting reset to a lower value periodically. Inserting will work on the table, producing the next bigint in the sequence as the primary key, for days and then all of the sudden duplicate primary key errors show up. When I check, the last primary key value in the table is higher than the current value of the sequence.
For example: right now I have primary key values 6000 through 7032 contiguously in the table, all of which were generated with the sequence. Suddenly I'm getting duplicate primary key errors. A quick check of the sequence shows it's at 7002, but the last inserted row has a primary key of 7032!
I'm populating this table in one place (in the application layer), leaving the primary key null, which allows the default constraint to get the next sequence.
When the problem shows up, I've reset the sequence to the higher number in the past and all is well for many days, then the problem occurs again.
The definition for the sequence is:
CREATE SEQUENCE [dbo].[IntegrationQueueSEQ]
AS [bigint]
START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 9223372036854775807
CYCLE
CACHE 50
The default constraint for the primary key on the table is defined as:
ALTER TABLE [dbo].[IntegrationQueue] ADD CONSTRAINT [DF_IntegrationQueue_IntegrationQueueID] DEFAULT (NEXT VALUE FOR [dbo].[IntegrationQueueSEQ]) FOR [IntegrationQueueID]
View 5 Replies
View Related
Jun 21, 2015
So, I have some questions about best practice in SQL Server.
1.) I have PK like this (company TINYINT, store TINYINT, action TINYINT, invoice INT, sn SMALLINT). I know JOINS will work faster with surrogate key but I have only couple of JOINS on that table. I use members of PK in WHERE clause mainly, alone and combined for reporting purpose. Is it always better to have surrogate key because they don't have any meaning and context of data laying in current PK.
2.) In my PK from above I have two candidates for using Sequence object. Invoice start with 1 for every (company,store,action) combination. Sn start with 1 for every (company,store,action,invoice) combination. I would like to know can I implement Sequence object here knowing that Sequence don't support PARTITION BY in OVER clause. From what I red it cannot be done via Sequence but I have to ask.Here is data sample for this PK
company store action invoice sn
----------- ----------- ----------- ----------- -----------
1 1 1 2017 1
1 1 1 2018 1
1 1 1 2019 1
1 1 1 2019 2
1 1 1 2019 3
1 1 2 1 1
1 1 2 2 1
1 1 2 2 2
1 1 2 2 3
1 1 2 3 1
1 1 2 3 2
1 1 2 3 3
1 1 2 3 4
1 1 2 3 5
View 7 Replies
View Related
Jul 23, 2005
How do I get the next int value for a column before I do an insert inMY SQL Server 2000? I'm currently using Oracle sequence and doingsomething like:select seq.nextval from dual;Then I do my insert into 3 different table all using the same uniqueID.I can't use the @@identity function because my application uses aconnection pool and it's not garanteed that a connection won't be usedby another request so under a lot of load there could be major problemsand this doens't work:insert into <table>;select @@identity;This doesn't work because the select @@identity might give me the valueof an insert from someone else's request.Thanks,Brent
View 4 Replies
View Related
Nov 29, 2006
Recently I'm working on a multi-thread solution based on SQL-Server, now I'm facing such a problem:
Suppose I have process No.1(with multi-threads) inserting data to Table A, which has its identity column auto generated. And process No.2(also with multi-threads) retrieving data from Table A ,generate some records and insert the result into Table B. Both of these two processes are doing batch processing(batch retrieving and batch writing), and they are running parallelly.
Now since process No.2 retrieve data sequencely by the identity of Table A, it found there exists missing results. This is due to that records with bigger identities are not necessarily commited earlier than those who have smaller identities.
One direct solution is add one flag field in Table A indicating whether this record has been processed by process No.2, and each time it was processed , the field will be set. But unfortunatelly the table structure is not supposed to be modified.
So is there any other good solutions for this problem? Thanks.
View 7 Replies
View Related
Oct 27, 2015
Please find the necessary SQL to create the table with sample data down below.In this simple table, I'm keeping project status logs i.e. what project, what happened and when.I want to create a select statement that will give me the ProjectId and the time stamps from "Contract Signed" to "First Meeting". So, I'd like my data to look like this:
ProjectId - FromStatus - FromTimeStamp - ToStatus - ToTimeStamp
123 - 'Contract Signed' - 6/21/2015 3:33PM - 'First Meeting' - 6/27/2015 8:45AM
456 - 'Contract Signed' - 6/25/2015 9:00AM - 'First Meeting' - 6/29/2015 2:50PM
987 - 'Contract Signed' - 9/29/2015 11:00AM - 'First Meeting' - 7/11/2015 10:00AM
Please notice that in my SELECT statement, I don't want to see anything else. For example, I don't want ProjectId: 555 included because it never made it to "First Meeting". I also don't want to see any information about "Press Release" for ProjectId: 123. I only want to see those projects that went from "Contract Signed" to "First Meeting".
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ProjectStatus](
[LogId] [int] NOT NULL,
[code]...
View 3 Replies
View Related
Apr 30, 2015
I need a simple tSQL code that will produce an array sequence values that has gap with 2 or more consecutive values, which can vary - so 2 or 3 or 4 between sequence of number. For example, I want it to return a series with a gap or space of 2- So it should return 1, 2, 5, 6, 9, 10.. or a gap of 3 - 1,2 ,6,7,11,12.. etc I know a single gap is quite simple using a recursive cte, but I haven't been able to find how to get this kind of result.
View 14 Replies
View Related
Sep 21, 2015
I am trying to JOIN table on datetime column truncated to full minutes with other table, where I store time intervals (in order to have all minutes in result even when there was no event in main data table).
SELECT
dateadd(minute, datediff(minute, 0, StartTime), 0) as StartTimeMinute
,ApplicationName
,COUNT(*) as Requests
FROM dbo.Profiler as p INNER JOIN dbo.MinuteIntervals as i ON i.TIMEVALUE = p.StartTimeMinute
WHERE EventClass IN (10,12)
GROUP BY dateadd(minute, datediff(minute, 0, StartTime), 0), ApplicationName
ORDER BY StartTimeMinute
How to join on p.StartTimeMinute? I do understand why this doesn't work, as p.StartTimeMinute is calculated in my query, so JOIN cannot find it in original p table.
View 5 Replies
View Related
Sep 4, 2015
What is a more efficient way of doing the following such that DATEDIFF() does not have to calculated numerous times?
CASE
WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 0 THEN '<= 0D'
WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) > 0 AND DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 7 THEN '> 0D AND <= 7D'
WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) > 7 AND DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 30 THEN '> 7D AND <= 30D'
[code]....
View 5 Replies
View Related
Jul 18, 2015
use Northwind
Go
select
dbo.Orders.OrderID
,Cast(dbo.Orders.OrderDate As DATE)Order_Date
, dbo.Customers.CustomerID
, dbo.Customers.CompanyName
, dbo.Products.ProductName
[code].....
I cannot use the alias field names as part of additional calculations for new columns.
total_Amount and Grand_Total cannot be done with my skill level.
View 9 Replies
View Related
Nov 4, 2015
Setup:
Windows Server 2003 R2 - Enterprise - SP2 - 32 Bit
SQL Server 2014 Express - 32 Bit
Problem: I have a calculated field on a PO table that adds up item prices on an Item table to get the total PO value. This works as expected until there are at least 10 rows in the PO table. From the 10 row on the calculated field stops working and only shows 0.
I have experienced this before and it seems like calculated fields break on the 10th row of a table and onward.
My PO table
CREATE TABLE [dbo].[PO](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Quote_Number] [varchar](max) NULL,
[Customer] [varchar](max) NULL,
[CustomerPO] [varchar](max) NULL,
[PO_Received_Date] [datetime] NULL,
[Total_PO_Value] [decimal](18, 2) NULL,
[Code] ....
View 10 Replies
View Related
May 13, 2015
I've the following data:
<Hour>11</Hour><Hour>12</Hour><Hour>15</Hour><Hour>18</Hour>
And my goals is to the extract the following example:
11,12, 15, 18
How Can I do That?
View 6 Replies
View Related
Jul 29, 2015
In a t-sql 2012 sql update script listed below, it only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value of 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?
UPDATE LKC
SET LKC.combo = lockCombo2
FROM [LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
[Code] ....
View 10 Replies
View Related
Jun 15, 2015
I have a database that has entries that I want sorted by date order. Each entry has an auto ID number allocated (primary key auto sequencing), which I want to change to reflect the sorting (so the first date has the first auto ID number and so on).I've gone into the database and sorted the entries as I want them. Then I've gone into Design View to delete and restablish the primary key autosequence. However, it is not keeping the date order in the database (ie entry ID 3140 date is 12/06/2015, but 3141 is 02/02/2012). How do I get it to maintain the order?
View 3 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
Nov 21, 2015
I am new in sql server. I am using TSQL2012 database. I have added a new column in processedOrderCount in Hr.Employees table. And created a trigger on Sales.Orders table that whenever orderId is updated it automatically updated processedOrderCount in Hr.Employees. There is a problem that the orderId is the identity column I can't update it. How can we work with identity column? the code of my trigger is:
If Object_id ('Sales.trig_Calculate_OrderProcessed', 'tr') is not null
Drop Trigger Sales.trig_Calculate_OrderProcessed
go
Create Trigger Sales.trig_Calculate_OrderProcessed
ON Sales.Orders
[Code] ....
View 2 Replies
View Related
Oct 16, 2013
I would like to use sequences and triggers to update table identity field with int value from sequence via before insert trigger. I'm searching on google for a few days and there are no same or identical article about this subject.
Is there any sample how to create table with column Id, Name, Comment and sequence (for generate int numbers for Int field in table) and trigger which will fired before insert and check is inserted Id is NULL and update this field from sequence or nothing if id is set up.
View 15 Replies
View Related
Jun 1, 2015
By mistake i deleted a record form prod table.
how can i enter that record back? i get this error.
INSERT INTO item_details(ItemID,Item_Name,price) VALUES(201, bag,10)
ItemID is identity column. can i disable identity column and enter and then enable using sql statement?
View 3 Replies
View Related
May 27, 2015
Is there anyway I can change Identity specification on column to yes ? and If I do that it supposed to take data as 1,2,3,4 like that?
But mine is not changing it's come up all data as 0. Is there any other way to do?
View 6 Replies
View Related
Jun 26, 2015
In my database all columns have Identity Value as a PK.
Now today I check Index Fragmentation I saw that many cluster and Non cluster Index are avg.Fragmentation is around 99 % I thought that in Identity column record is always inserted at bottom so there is no fill factor assigned to it.
So in this case Do I need to set Fill factor for Cluster and Non Cluster Index?
If Yes Then For PK How much - 95 % or what? and same for Non cluster or It should around 85 to 90
View 4 Replies
View Related
Sep 9, 2015
My current proc updates(updates using joins of two or three tables) millions of records as per the condition provided for each department.
However, when the proc fails it writes to a ErrorTable, ERROR_MESSAGE(), ERROR_SEVERITY() and which department has failed.
Since the records are updated keeping the selected departments in loop, I get the department in a temp variable.Now, I was asked to log the specific record where the failure was occured.Something like log the identity column value or primary key value which record has failed.
View 2 Replies
View Related
Jun 19, 2015
I'm trying to insert records into "holding" table and write back identity column value (Entry_Key) to the original table. So my setup is I have two tables; tblEWPBulk and tbleFormsUploadEWP. Users will enter records into tblEWPBulk and use BatchID to group records, once batch entry has been completed (usually less than 30 records) user will click on UploadAll button and insert records (not all fields) into tbleFormsUploadEWP. One record in tblEWPBulk can be sent multiple times to the holding table but tblEWPBulk will need to have latest Entry_Key captured. Records are sent from holding table to DB2 z/VSE using SQL stored procedure and based on certain logic records are marked uploaded or certain error capture... that part works fine.
So for example I want to send
BatchID, AccountNumber, Period, ReceiveDate, AccountType, ReturnType, NetProfitOrLoss, TaxCredit FROM tblEWPBulk to the holding table and write back Entry_Key (identity column) back to the record in tblEWPBulk (field called UploadEntryKey). As I said one record could be sent to the holding table multiple times until uploaded or deleted and UploadEntryKey always needs to be updated so that when results are processed response from the DB2 can be inserted into table and presented to the user.
No foreign key relationship exists since records in the holding table get sent to the archive table and table is truncated and entry_key starting value reset back to 2000... just some DB2 restrictions.
View 5 Replies
View Related
Apr 24, 2015
I am writing an Instead of Insert trigger. I would like to fire an error when inserting into an 'Identity' column. Since UPDATE([ColumnName]) always returns TRUE for insert statements, is there an easy/fast way around this? I don't want to use:
IF(EXISTS(SELECT [i].[AS_ID] FROM [inserted] [i] WHERE [i].[AS_ID] IS NULL))
here is my pseudo-code...
CREATE VIEW [org].[Assets]
WITH SCHEMABINDING
[Code] .....
-- How does this statement need to be written to throw the error?
--UPDATE([AS_ID]) always returns TRUE
IF(UPDATE([AS_ID]))
RAISERROR('INSERT into the anchor identity column ''AS_ID'' is not allowed.', 16, 1) WITH NOWAIT;
-- Is there a faster/better method than this?
IF(EXISTS(SELECT [i].[AS_ID] FROM [inserted] [i] WHERE [i].[AS_ID] IS NOT NULL))
RAISERROR('INSERT into the anchor identity column ''AS_ID'' is not allowed.', 16, 1) WITH NOWAIT;
-- Do Stuff
END;
-- Should error for inserting into [AS_ID] field (which is an identity field)
INSERT INTO [org].[Assets]([AS_ID], [Tag], [Name])
VALUES(1, 'f451', 'Paper burns'),
(2, 'k505.928', 'Paper burns in Chemistry');
-- No error should occur
INSERT INTO [org].[Assets]([Tag], [Name])
VALUES('f451', 'Paper burns'),
('k505.928', 'Paper burns in Chemistry');
View 7 Replies
View Related
Jul 17, 2015
Table 1:
------- ----- ---- ----
Name Add No RowID
------- ----- ---- -------
aa #a-1,India 10
bb #a-1,India 11
aa #a-1,India 12
----------------------------------------------------
table 1 inserting to Table 2 (Using 1st Data flow)
Table 2:
------- ----- ----
Name Add ID(Note:Here Identity1,1)
------- ----- ----
aa #a-1,India 1
bb #a-1,India 2
aa #a-1,India 3
----------------------------------------------------
My Requirement is Update Table 1 set Column::No=Table 2.ID
based on Exact Match of
Table1.Name=Table2.Name and
Table1.Add=Table2.Add
It means Get back the Id for Source Table 1
2nd Data flow
Source(Table1:Name, Add,No)
|
--LOOKUP(Table2:Name, Add::Matched Look Columns Name, Add and
Tick Mark on ID)
|(Match)
-->OLEDB Command: update Table1 set N0=? where RowID=?(Here Param_0= NO ,Param_1=RowID)
Here My Issue is if Table 1 had Duplicates(same Name, Add, but Row Id is different it is Updating Same ID for Table 1.No It means Get Back ID correctly not updating Result::
Table 1:
------- ----- ---- ----
Name Add No RowID
------- ----- ---- -------
aa #a-1,India 1 10
bb #a-1,India 2 11
aa #a-1,India 1 12
----------------------------------------------------
My correct Output is 3 instead of Result:Table1 1.NO 1 where RowID =12
It caused by LOOKUP , It picking Top1 ID while Matching Look on fields.
How Should I update the (Identity) Get Back Table 2.ID to Source Table1. NO in Above logic in SSIS?
View 11 Replies
View Related
Oct 12, 2015
I have a table of raw data where each column can be null. The thought was to create an identity key (1,1) and set as primary for each row. (name/ address / zip/country/joindate/spending) with surrogate key: "pkid".However other queries will not use this primary key. So for instance they may count the # of folks at a zip, select all names, addresses etc. The queries may order by join date, or select all the people that joined on a specific date.No other code would logically use the primary key (surrogate primary id key), therefore would it still have any performance benefits? at this time the table would have no clustured or nonclustured indexes or keys. I'm curious if there are millions of records.
View 7 Replies
View Related
Apr 21, 2015
In my database table has auto Identity file which is (1,1) But Its Increasing 1000 Some time 100 I don't Understand why It is happening in my every table.
View 4 Replies
View Related
Apr 20, 2014
I have 4 tables involved here. The priority table is TABLE1:
NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15
[code]...
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
View 0 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
Mar 7, 2001
I am used to working in MS Access where you can return a value as in:
[date1]-[date2]=X
It will calculate that value provided "date1" and "date2" are fields in the recordset. One calc for each record.
I am getting an error message in SQL Server saying that neither "date1" nor "date2" are not contained in an aggregate function and there is no "group by" clause.
In Access this would not be a problem.
Can you help?
Thank you.
View 1 Replies
View Related
Feb 15, 2004
Hi
This may seem amazing and a stupid question but:
Consider there is a parent table A and child table B and we want to write a query that has some fields from A and a calculated field which indicates whether A has any child record in B or not. The Value 1 means Yes and 0 means No. Has anybody an idea to write this in SQL Server?
View 2 Replies
View Related
Oct 7, 2006
Hi, I'm new to sql server but have used oracle sequence numbers to create unique keys in my tables. I've been looking for examples on how to create a sequence type table in sql server and have also found that there is a column called identifier but am unsure how to use this... I created a table and used this column attempting to insert (thinking it would autoincrement) but it fails... Any insight into how I can accomplish a sequence type activity for unquie numeric ids would be helpful, Thank you for your help,Jay
View 2 Replies
View Related
Mar 25, 2004
Is is possible to Generate sequence numbers in a Query result in an SQL select Statement
like
Select sno,employee_name from empmaster
sno being a number with auto increments(this Field will not be in a Table).
I do not want to use Identity .....
This is Just to Provide a Serial Number to the Query Output...
thus the Out put should be something like
sno employeename
--- ------------------
1 abc
2 jhjk
3 kljl
View 4 Replies
View Related
Mar 27, 2008
Hi,
Does any one have information about how 'large' the SQL Server Sequence Number generator can go,
like 1 to 1 billion, or to 12 zeros, etc?
Thanks.
--John
View 1 Replies
View Related
