In my first attempts at building my code, I strictly went with either native or on-disk code. I specifically wrote the on-disk code to only use features that worked in-memory. This lead to one majorly silly bit of code, used to create system assigned key values. How would I create a customer number that was unique. We can\u2019t use the Max(value) + 1 approach because it will be very hideous with MVCC isolation levels, since 100 connections might see the same value, leading to lots of duplication. You can\u2019t see other connections, so you would duplicate data quickly. I was also limited to not using sequence objects because they too are not allowed in native code.<\/p>\n
So, I used a random number generator, like this:<\/p>\n
DECLARE @CustomerNumber CHAR(10)
WHILE 1=1
BEGIN
SET @customerNumber = ‘CU’ + RIGHT(‘00000000’ + CAST(CAST(100000000 * RAND() AS INT) AS VARCHAR(8)),8)
IF NOT EXISTS (SELECT * FROM Customers.Customer WHERE CustomerNumber = @CustomerNumber)
BREAK<\/p>\n
END <\/p>\n
This actually worked quite well for the tens of thousand rows I generated. The likelihood of two connections getting the same random value, and getting past the NOT EXISTS block was very unlikely. Now, obviously the probability of clashes will rise greatly as you approach the limits of the 8 digit number, so you would have to monitor usage and change the CU to something else to increment the value. This is demo code, and as this blog is about, there are easier ways. But it was fun to write and test.<\/p>\n
Of course the worst part of this code isn\u2019t the random number generator, or even the looping (oh, the dreaded looping), no this code is not very optimal, because of the NOT EXISTS subquery. Best case we have to do one probe into the table to see if that value doesn\u2019t exist. While it was sub optimal in interpreted code, in native code, it got more silly looking because you can\u2019t break out of a while loop, and you can\u2019t use subqueries (nor the RIGHT function). So the code changed to the following awkward (yet operational) bit of code:<\/p>\n
DECLARE @customerNumber CHAR(10), @rowcount INT, @keepGoing BIT = 1, @baseNumber VARCHAR(30)
WHILE @keepGoing = 1
BEGIN
SET @baseNumber = ‘00000000’ + CAST(CAST(100000000 * RAND() AS INT) AS VARCHAR(8))
SET @customerNumber = ‘CU’ + SUBSTRING(@baseNumber,LEN(@baseNumber) – 8,8)
SELECT @rowcount = COUNT(*) FROM Customers.Customer WHERE CustomerNumber = @CustomerNumber
IF @rowcount = 0
SET @keepGoing = 0<\/p>\n
END <\/p>\n
The inefficiency of this code is the same as the interpreted code: that query on CustomerNumber. Of course, the more typical solution to the problem of a system generated key would be to use a SEQUENCE object (possibly as a default), and format the number somehow. But you can\u2019t use sequences in native code, so instead of going fully native code, I am using a hybrid approach.<\/p>\n
First, I built a procedure that generated CustomerNumbers, it has a simple checkdigit appended to a 7 digit number (preceded by CU) (and sloughing off any customer numbers with 666 in the value from the string just to show what can be done).<\/p>\n
CREATE SEQUENCE Customers.Customer$CustomerNumber$Sequence
AS INT
START WITH 1
go<\/p>\n
–use a sequence and format the output a bit to avoid lots of duplication
CREATE PROCEDURE Customers.Customer$getNextCustomerNumber
@customerNumber char(10) OUTPUT
AS <\/p>\n
–doing it in a loop is the simplest method when complex requirements.
WHILE (1=1)
BEGIN
–Get the base account number, which is just the next value from the stack
SET @customerNumber = ‘CU’ + right(replicate (‘0’,8) +
CAST(NEXT VALUE FOR Customers.Customer$CustomerNumber$Sequence as varchar(7)), 6)<\/p>\n
–add a check digit to the account number (take some digits add together, take the first number)
SELECT @customerNumber = CAST(@customerNumber AS varchar(8)) +
RIGHT(CAST(
CAST(SUBSTRING(@customerNumber, 3,1) AS TINYINT) +
POWER(CAST(SUBSTRING(@customerNumber, 5,1) AS TINYINT),2) +
CAST(SUBSTRING(@customerNumber, 8,1) AS TINYINT) * 3 +
CAST(SUBSTRING(@customerNumber, 9,1) AS TINYINT) * 2 +
CAST(SUBSTRING(@customerNumber, 10,1) AS TINYINT) +
CAST(SUBSTRING(@customerNumber, 11,1) AS TINYINT) * 3 AS VARCHAR(10)),1)<\/p>\n
–if the number doesn’t have these character string in it (including check digit)
if @customerNumber NOT LIKE ‘%00000%’
AND @customerNumber NOT LIKE ‘%666%’
BREAK — we are done
END
GO<\/p>\n
Pretty simple, just loops until a good number is found. Not looping through rows, but looping through sequence values, which is very fast and can be done by many simultaneous callers with almost no contention. If contention is an issue, you can choose your caching for a sequence object, which can avoid a couple of writes when it persists that you have burned through the previously cached values.<\/p>\n
So now, the code simply says:<\/p>\n
DECLARE @CustomerNumber CHAR(10)
EXEC Customers.Customer$getNextCustomerNumber @CustomerNumber OUTPUT<\/p>\n
Rather than that loop. But I can\u2019t use that in a natively compiled procedure, so we create an interpreted procedure that calls this procedure, then calls the native procedure:<\/p>\n
CREATE PROCEDURE Customers.Customer$CreateAndReturn
@FirstName NVARCHAR(30),
@LastName NVARCHAR(30),
@MiddleName NVARCHAR(30),
@EmailAddress NVARCHAR(200)
AS <\/p>\n
BEGIN
SET NOCOUNT ON<\/p>\n
–see if the customer exists\u2026 We don\u2019t do updates here
DECLARE @customerId INT = (SELECT CustomerId
FROM Customers.Customer
WHERE EmailAddress = @EmailAddress) –we are assuming validation is done elsewhere<\/p>\n
IF @customerId IS NULL
BEGIN
DECLARE @customerNumber CHAR(10)
EXEC Customers.Customer$getNextCustomerNumber @customerNumber OUTPUT<\/p>\n
EXEC @CustomerId = Customers.Customer$SimpleInMemCreate
@CustomerNumber = @CustomerNumber,
@FirstName = @FirstName,
@LastName = @LastName,
@MiddleName = @MiddleName,
@EmailAddress = @EmailAddress<\/p>\n
END<\/p>\n
RETURN COALESCE(@customerId, -100)
END
GO<\/p>\n
I haven\u2019t added error handling just yet, but this is nearly the final version. The procedure to do the actual insert is just a simple insert using native compilation:<\/p>\n
CREATE PROCEDURE Customers.Customer$SimpleInMemCreate
@CustomerNumber CHAR(10),
@FirstName NVARCHAR(30),
@LastName NVARCHAR(30),
@MiddleName NVARCHAR(30),
@EmailAddress NVARCHAR(200)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N’us_english’
)
DECLARE @customerId int
INSERT INTO Customers.Customer
( FirstName , MiddleName ,LastName ,
CustomerNumber , EmailAddress,
RowCreateTime, RowLastModifiedTime
)
VALUES ( @FirstName , @MiddleName ,@LastName ,
@CustomerNumber , @EmailAddress,
SYSDATETIME(), SYSDATETIME()
)
SELECT @customerId = SCOPE_IDENTITY()<\/p>\n
RETURN isnull(@customerId, -100)
END
GO <\/p>\n
So we get the benefits of the compiled procedure (if there is any in the actual case, my demo code is fairly simplistic) coupled with anything in the interpreted code that could not be done in native mode. <\/p>\n","protected":false},"excerpt":{"rendered":"
In my first attempts at building my code, I strictly went with either native or on-disk code. I specifically wrote the on-disk code to only use features that worked in-memory. This lead to one majorly silly bit of code, used to create system assigned key values. How would I create a customer number that was……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-82165","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82165","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82165"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82165\/revisions"}],"predecessor-version":[{"id":82254,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82165\/revisions\/82254"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82165"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82165"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82165"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82165"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}