Is there a SQL Server function equivalent to AutoNumber() of QlikView?

Question

First of all: this is not a kind of a IDENTITY() field.

In QlikView, it is used to generate a number based on parameters send to function. See its documentation here: https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/CounterFunctions/autonumber.htm

In short, you send a parameter to it and it returns an integer that will identify the same arguments for the rest of script. If you send...

   AutoNumber('Name 900') -> returns 1
   AutoNumber('Name 300') -> returns 2
   AutoNumber('Name 001') -> returns 3
   AutoNumber('Name 900') -> returns 1 ... again 

and because the parameter is already in the intern list of AutoNumber

I tried to build some like that in SQL Server, but is not possible use SELECTs inside scalar functions.

My need is to get something like...

INSERT INTO FacSales (SumaryID, InvoiceID, InvoiceDate
                    , ProductID, SaleValue, CustomerID, VendorID)
SELECT AutoNumber(sale.VendorID, sale.CustomerID, sale.ProductID)
     , sale.InvoiceID
     , sale.SaleDate
     , details.ProductID
     , etc, etc, etc.

Is there, inside SQL Server, a "native" function that perform this? Or, is there a way to build this using a procedure/function?

Thanks.

Answer 1

You could use DENSE_RANK (Transact-SQL)

Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

declare @T table
(
  ID int identity,
  VendorID int,
  CustomerID int,
  ProductID int
);

insert into @T values
(1, 2, 3),
(1, 2, 3),
(1, 2, 4),
(1, 2, 3);

select sale.ID,
       sale.VendorID,
       sale.CustomerID,
       sale.ProductID,
       dense_rank() over(order by sale.VendorID, 
                                  sale.CustomerID, 
                                  sale.ProductID) as AutoNumber
from @T as sale
order by sale.ID;

Result:

ID          VendorID    CustomerID  ProductID   AutoNumber
----------- ----------- ----------- ----------- --------------------
1           1           2           3           1
2           1           2           3           1
3           1           2           4           2
4           1           2           3           1

Answer 2

You basically want a key value store. There are lots of ways to make one.

Here is a possible solution. It uses a stored procedure.

However, you did not say if the values are retained indefinitely or if they are just for a single call. This example shows how to do it indefinitely.

It could be modified to be for a single call or connection via careful use of temporary tables. If it is other than a call or connection then the autoNumber.AutoNumber table and the autoNumber.NextAutoNumber will need to be cleaned up on what ever that schedule is.

-- Create the table, sequence and sproc
-- Create a schema to hold our autonumber table and sequence
CREATE SCHEMA autoNumber
GO

-- Create a sequence.  This just gives us a new number when ever we want.
-- This could be replaced with an identity column.
CREATE SEQUENCE autoNumber.NextAutoNumber AS [bigint]
 START WITH 1
 INCREMENT BY 1 
 NO CACHE 
GO

-- Create a table to hold the auto number key value pairs.
CREATE TABLE autoNumber.AutoNumber(KeyValue varchar(255), Number bigint)
go

-- This is the stored procedure that actually does the work of getting the autonumber
CREATE PROCEDURE autoNumber.GetAutoNumber @KeyValue varchar(255), @AutoNumber bigint = -1 output  AS
BEGIN
    DECLARE @Number bigint = null

    -- See if we already have an autonumber created for this keyvalue
    -- If we do, then set @Number to that value
    SELECT  @Number = autoNum.Number
    FROM    autoNumber.AutoNumber autoNum
    WHERE   autoNum.KeyValue = @KeyValue

    IF (@Number is null)
    BEGIN
        -- If @Number was not changed, then we did not find one
        -- in the table for this @KeyValue.  Make a new one
        -- and insert it.
        SET @Number = NEXT VALUE FOR autonumber.NextAutoNumber

        INSERT INTO autoNumber.AutoNumber ( KeyValue, Number)
        VALUES (@KeyValue, @Number)
    END

    -- Return our number to the caller.
    -- This uses either an output parameter or a select.
    IF (@AutoNumber = -1)
    BEGIN
        select @Number        
    END ELSE
    BEGIN
        set @AutoNumber = @Number    
    END
END
GO
-- End Create

-- Testing with "select"
EXEC autoNumber.GetAutoNumber 'Name 900'
EXEC autoNumber.GetAutoNumber 'Name 300'
EXEC autoNumber.GetAutoNumber 'Name 001'
EXEC autoNumber.GetAutoNumber 'Name 900'

-- Testing with output parameter
DECLARE @AutoNumber bigint
EXEC autoNumber.GetAutoNumber 'Name 900', @AutoNumber OUTPUT
SELECT @AutoNumber
EXEC autoNumber.GetAutoNumber 'Name 300', @AutoNumber OUTPUT
SELECT @AutoNumber
EXEC autoNumber.GetAutoNumber 'Name 001', @AutoNumber OUTPUT
SELECT @AutoNumber
EXEC autoNumber.GetAutoNumber 'Name 900', @AutoNumber OUTPUT
SELECT @AutoNumber

-- End Testing

-- Clean up 
DROP PROCEDURE autoNumber.GetAutoNumber
GO 

DROP TABLE autoNumber.AutoNumber
GO

drop SEQUENCE autoNumber.NextAutoNumber

DROP SCHEMA autoNumber
GO 
-- End Cleanup