I am using SQL Server 2005 Developer Edition. I am working on a conversion project from Oracle to SQL Server 2005.
As part of this, in order to replicate Oracle sequence functionality in SQL Server, the design called for writing a function that would accept a parameter , say sequence name. There will be a user table, say, SEQUENCE_GENERATOR with the following structure.
Column Name Data Type Nullability
-- -
Sequence_Name varchar(50) N
Last_Number int N
Everytime before generating the next sequence this function would be called. It would do the following : -
-
Select @.Last_Number = Last_Number
from SEQUENCE_GENERATOR WITH (UPDLOCK)
WHERE Sequence_Name = <func parameter passed i.e. Sequence Name>
SET @.Last_Number = @.Last_Number +1
UPDATE SEQUENCE_GENERATOR
SET Last_Number = @.Last_Number
WHERE Sequence_Name = <func parameter passed i.e. Sequence Name>
RETURN @.Last_Number
Since we are fetching the value of Last_Number(by calling the function) before inserting into our tables, we do not want users simultaneously accessing the same sequence to end up with PK violations and hence the use of UPDLOCK hint.
Now the real problem: -
Since the above three steps are a DML operation excepting the RETURN clause, we cannot do it in a function. However we can call an external stored procedure that does the above UML operations to accept the parameter and then return an output value. This output value would then be returned by the function.
Now I would like to know if the above approach is possible with external sps as I have no prior experience in this. Also, can someone point important resources where I can code the above thing in C++...
Thanks & Regards
Imtiaz
Before I get to the answer...
1. In practice sequence tables are a huge headache in any database design, I find they're more common in the Oracle world than any other, especially in large database designs that required a federated/distributed/replication model. If you can avoid a sequence table, consider using uniqueidentifier columns for PKs instead, they will remove a lot of headache (read: labor hours) and simplify software design at all levels.
2. Locking (table or otherwise) just creates artificial contention, if 1000 clients all decided to create a record at the same time they'd all have to stand in line. *owe*
3. In your sequence table you might consider using a seeded identity column for the PK, and leave the 'sequence name' as a regular data value. You could then write a UDF (function) in sql server that simply inserted the sequence name into the sequence table, and upon completion you acquire the generated number via @.@.IDENTITY (look this up in Books Online if you're not familiar with it already). This will help avoid the locking (which is hell on performance) and still get everything contained within sql server (no need to go the xp route). This is an option so long as the rest of the solution doesn't rely on a contigious set of numbers for a given sequence name (table?).
To answer your question, though, an extended stored procedure (xp/xsp) is implemented as a standard C dll, so you could implement it in C++ so long as you had cdecl exports sql server could consume.
Also, the registration process is tacky (look up sp_addextendedproc) since it requires access to the master table, but yes you can do what you want using an xp and a little elbow grease.
See: Creating an Extended Stored Procedure (MSDN)
Hope that helps,
Shaun
|||The logic that you have below is not quite correct unless you enclose the statements in a transaction. Without a transaction, the UPDLOCK will be released after the SELECT statement completes.
Select @.Last_Number = Last_Number
from SEQUENCE_GENERATOR WITH (UPDLOCK)
WHERE Sequence_Name = <func parameter passed i.e. Sequence Name>
SET @.Last_Number = @.Last_Number +1
UPDATE SEQUENCE_GENERATOR
SET Last_Number = @.Last_Number
WHERE Sequence_Name = <func parameter passed i.e. Sequence Name>
RETURN @.Last_Number
A more concise way of writing this in SQL Server is to use the UPDATE statement extension like:
UPDATE SEQUENCE_GENERATOR
SET @.Last_Number = Last_Number = Last_Number + 1
WHERE Sequence_Name = <sequence name>
Having said this, it is still not possible to perform data modifying operations from within UDFs. You can write an extended stored procedure. But that is more trouble actually. It can easily lead to concurrency issues, poor performance etc. You will also have to use bound connections. You could still go this route but be aware of the risks especially in a OLTP environment. You can use Visutal Studio templates to create the stubs required for implementing extended stored procedures. In any case, I would recommend against converting Oracle code verbatim. Identity provides a much faster and efficient mechanism to generate sequence numbers. If you need the sequence mechanism then why not implement it as a stored procedure and call it from within the proc requiring it. This is much more scalaeble way to implement it in TSQL.|||Shaun and Uma
The solutions you have given me instead of using external stored procedures I am aware of that. The application would run on Oracle or SQL Server backedn and the idea is to have most of the things as close as possible. I know this can be achieved by SPs but we desperately need functions to do that.
Uma
The reason we have UPDLOCK hint is for concurrency issues.
Now how can we write T-SQL code in C++ is my question. where do I start with?|||I am still waiting for some inputs on this...The problem is how do I start a xaction, access the database in extended procedure.
Regards
Imtiaz|||
 Shaun Wilson wrote: |
|
| See: Creating an Extended Stored Procedure (MSDN) |
Also
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odssql/ods_6_con_01_22sz.asp
and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odssql/ods_6_con_01_22sz.asp
Sync the toc on their website, all the docs you need are there. If all else fails you might consider using MSDTC as a transaction coordinator between the 'client', your xp and sql server.
