November 3, 2005 at 3:55 pm
Hi,
I have the following Table structure...
Column Data Type
Status_Code Int Not null
Request_Id Int Not Null Key
SequenceNum Int Not Null Key
Request Id & Sequence Number uniquely identifies a row.. Basically this table keeps history of multiple statuses of the request...
I want Sequence Number to be generated (start from 1 & incremented by 1) based on unique request Id entry... I meant for each request id = 1, sequence Numbers should be 1,2,3 for three status entrees... If Request id = 2 then its sequence number should again start from 1...
I am new to SQL Server, using MS SQL Enterprise Manager 8.0... Can somebody tell me whether it is possible in SQL Server... If yes, how???
Thanks in advance..
Sachin
November 3, 2005 at 8:23 pm
It's better to have DateChanged column with default GETDATE().
Column Data Type
Status_Code Int Not null
Request_Id Int Not Null Key
DateChanged smalldatetime Not Null Key
It gives you more information and stores some kind of log.
If you will need sequence number you can generate it on fly using subquery:
SELECT RequestId,
(SELECT COUNT(T1.DateChanged) FROM TheTable T1 WHERE T1.RequestrId = T.RequestId and T1.DateChanged <=T.DateChanged) as SequenceNum
FROM TheTable T
_____________
Code for TallyGenerator
November 3, 2005 at 11:10 pm
Date can't be used for this scenario because multiple statuses can be entered into the table simultaneously (Datetime less than 300th of a second apart) which will violate primary key constraint... We were already firing a query to get max sequence number but we want to avoid it... Want to know if some trigger can be written to take care of this scenario or something similar...
Thanks for your reply...
Sachin
November 3, 2005 at 11:39 pm
Sorry, it does not make any sence.
What does it mean - multiple statuses can be entered into the table simultaneously?
What's a point to apply status if it's already overwritten by another?
And how you suppose to distinguish which one comes first and which one second if they come simulteneously?
_____________
Code for TallyGenerator
November 4, 2005 at 5:14 am
Can you use something like this? This could handle both unknown new requests as well as incremented sequence requests. The @SequenceNum is returned as an output param. The Request_ID is returned when newly created. The Return_Value would indicate successful insert of the record.
Create Procedure MyTable_Insert (
@StatusCode INT = 1,
@RequestID INT = NULL BOTH,
@SequenceNum INT OUTPUT
)
AS
SET NOCOUNT ON
DECLARE @SequenceNum INT
IF @RequestID IS NULL
SELECT @RequestID = ISNULL( MAX(Request_ID), 0) + 1
FROM MyTable
SELECT @SequenceNum = ISNULL( MAX(SequenceNum), 0 ) + 1
FROM MyTable WITH (TABLOCKX, HOLDLOCK)
WHERE Request_ID = @RequestID
INSERT INTO MyTable (Request_ID, SequenceNum, Status_Code)
VALUES (@RequestID, @SequenceNum, @StatusCode)
IF @@ERROR = 0 GOTO ERROR_HANDLER
RETURN 0
ERROR_HANDLER:
SELECT @SequenceNum = -1
RETURN 1
SET NOCOUNT OFF
GO
-Mike Gercevich
November 4, 2005 at 5:29 am
It's much easier to have IDENTITY column.
_____________
Code for TallyGenerator
November 4, 2005 at 8:56 am
Yes it is much easier to use and IDENTITY but, based on this discussion, you can't. The SequenceNum must be based on a match to the Request_ID. It's a reasonable approach to lock the tables based on simultaneous arrival of requests for a number. Mike's approach is very reasonable given the circumstances stated. You can use the MAX operation and to find the last number assigned to the specific request_id begin updates but you could also select all the records of that request_id, sort by sequencenum desc and use the top 1 value + 1. I'm unsure which would have best performance which would be a consideration considering the volume of updates implied.
November 4, 2005 at 9:09 am
Sergiy,
Status is created by the system based on the data entered by the user in the Request master table for eg Status: Received, in process, on hold etc...
GUI is designed in such a way that user can enter all or part of the data... Thats why status can be created by the system simultaneously... Status is a process history of user request for eg.. Car loan processing...
Mike, How can that procedure activated/called?
Is it not possible to do something like:
before committing each new record,
Get request id for the current record...
Check if Request id exists
If exists, get max sequence number...
Enter (max Sequence # +1) into current sequence # field...
Commit
Thanks in advance
November 4, 2005 at 11:09 am
It seems that keys and indexes are getting very confused here. A key is something identify the row as unique. indexes are for accessing the data. Your table key can be an identity column alone. You can then apply indexes, composite - multi-column, or non-composite - single columns as you see fit.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply