locked
Sequence, Identity, checksum/checkdigit RRS feed

  • Question

  • Even back in 90s when there was only Btrieve and very primitive data manipulation tools, we were able to incorporate some security and check digit in numeric key column to prevent random id and user typo.

    A very common procedure was to calculate the checksum 11 and concatenate it to the key and easily prevent the user from typo.

    I am wondering how come 30 years later still Microsoft has not provided such functionality.

    In MySql and Oracle, I can write FUNCTION to generate my next number which has this sort of checksum in it.

    But in MSSQL:

    1. If I use function, it does not allow updating my base table that keeps the sequence number

    2. If I use SEQUENCE, does not have such property, and If I try to work around it and use it in my function again, SQL does not allow use of SEQUENCE in function.

    3. The only way is to write my code in a stored procedure, which can not be used in a SELECT or UPDATE query.

    I am wondering how others handle this? 

    Is everyone goes through loop and cursors to do the job?

    Is everyone just dismisses the concept and simply uses IDENTITY or SEQUENCE as is without any checksum?

    Am I such an out dated and old school guy?

    Thursday, July 27, 2017 3:50 AM

Answers

All replies

  • Hi,

    The auto-incerement fields in SQL server are using IDENTITY.

    SQL server will handle it you dont need need to put an process to maintain this.

    And you can even reset this identity to start from another INT.

    Good luck.

    Thursday, July 27, 2017 12:24 PM
  • In this way why store a sequence in the table at all, just generate it as  needed

    CREATE TABLE T1
    (
      id VARCHAR(10) NOT NULL PRIMARY KEY,
      pos INT NOT NULL UNIQUE
    )

    INSERT INTO T1 VALUES('A', 1)
    INSERT INTO T1 VALUES('B', 2)
    INSERT INTO T1 VALUES('C', 3)
    INSERT INTO T1 VALUES('D', 4)
    INSERT INTO T1 VALUES('E', 5)
    INSERT INTO T1 VALUES('F', 6)

    DECLARE
      @id AS VARCHAR(10),
      @newpos AS INT

    SET @id = 'd'
    SET @newpos = 1

    SELECT T1.*
    FROM T1, (SELECT pos AS idpos FROM T1 WHERE id = @id) AS P
    ORDER BY
      CASE
        WHEN id = @id THEN @newpos
        WHEN pos BETWEEN idpos AND @newpos THEN pos - 1
        WHEN pos BETWEEN @newpos AND idpos THEN pos + 1
        ELSE pos
      END
    ---
    UPDATE T1 SET pos =CASE
        WHEN id = @id THEN @newpos
        WHEN pos BETWEEN @oldpos AND @newpos THEN pos - 1
        WHEN pos BETWEEN @newpos AND @oldpos THEN pos + 1
        ELSE pos
      END

    If it does not help , please post sample data and desired result


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Sunday, July 30, 2017 5:58 AM
  • The objective is to run following query and set each of records with a identity which has checksum for TYPO error proofing purpose. 

    Update Table1 set Column1=NEXT_GENERATED_NUMBER() Where Column1 is null

    NEXT_GENERATED_NUMBER should return a value that will be unique in the table as Column1 should be Unique.

    Uri you missed following : Concurrent access to the function and also more importantly MSSQL does not allow use some of your command inside FUNCTION

    Sunday, July 30, 2017 1:55 PM
  • How about it

    create table seq(val int not null);
    insert into seq values(0);
    go
    select * from seq
    create proc usp_getseq @val as int output
    as

    update seq set @val = val = val + 1;
    go

    -- usage
    declare @i as int;
    exec usp_getseq @i output;
    select @i;

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, July 30, 2017 2:25 PM
  • What is the logic here? You have a table like this 

    create table #t (id int null, col1 char(1))

    insert into #t (col1) values ('a')
    insert into #t (col1) values ('b')
    insert into #t (col1) values ('c')
    insert into #t (col1) values ('d')

    now, you need to generate an ids?

    insert into #t (col1) values ('e')
    and now you look for a null value for id and generate a next sequence?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, July 30, 2017 2:35 PM