CODEWITHSUNDEEP
sqlsql-servert-sql
Vitaly Borisov
Vitaly Borisov

Reputation: 1193

TSQL BINARY_CHECKSUM as a default value

There is a column in a table, which should store Checksum for list of columns, but I'd like to create it as a default value for this table:

CREATE TABLE tblVitalyTest (id BIGINT IDENTITY(1,1), startTime DATETIME, value1 INT, value2 INT, value3 INT, value4 DATETIME, BinCheckSum BIGINT)
GO
ALTER TABLE tblVitalyTest ADD  DEFAULT (BINARY_CHECKSUM(value1, value2, value3, value4)) FOR [BinCheckSum]
GO

So, it should be used like that:

INSERT INTO tblVitalyTest (startTime,value1,value2,value3,value4) VALUES ('2015-05-25',1,1,1,'2015-11-11')

As a result it should be something like that:

id, startTime, value1, value2, value3, value4, BinCheckSum

1, "2015-05-25", 1, 1, 1, "2015-11-11", 46173

But table "default" table creation fails unfortunately.

There is a query to play around:

DECLARE @a TABLE (id BIGINT IDENTITY(1,1), startTime DATETIME, value1 INT, value2 INT, value3 INT, value4 DATETIME)

INSERT INTO @a (startTime,value1,value2,value3,value4) 
    VALUES ('2015-05-25 08:00',1,1,1,'2015-11-11'),('2015-05-25 09:00',1,2,1,'2015-11-11')
          ,('2015-05-25 10:00',null,null,null,'2015-11-11'),('2015-05-25 11:00',2,1,1,'2015-11-11'),('2015-05-25 11:00',null,null,null,'2015-11-12')

SELECT a.*,BINARY_CHECKSUM(a.value1,a.value2,a.value3,a.value4) [BinCheckSum]
FROM @a a

Please let me know is there any way to create a Binary_Checksum as a default value for the column?

Upvotes: 2

Views: 1827

Answers (3)

JDawg
JDawg

Reputation: 9500

Post SQL Server 2005

Rather than creating a trigger, a cleaner solution is to create a persisted computed column.

Adding to BICube's response, the CREATE TABLE statement is:

CREATE TABLE tblVitalyTest
 (id BIGINT IDENTITY(1,1), 
     startTime DATETIME, 
     value1 INT,
     value2 INT,
     value3 INT, 
     value4 DATETIME, 
     cksum AS BINARY_CHECKSUM(value1,value2,value3,value4) PERSISTED
)
    GO

When the computed column is persisted, you take the computation hit on insert and update, but every read is free.

Upvotes: 1

BICube
BICube

Reputation: 4681

You can create the column as a computed column. Try this

CREATE TABLE tblVitalyTest
 (id BIGINT IDENTITY(1,1), 
     startTime DATETIME, 
     value1 INT,
     value2 INT,
     value3 INT, 
     value4 DATETIME, 
     cksum AS BINARY_CHECKSUM(value1,value2, value3, value4 ))
    GO

Or you can ALTER and existing table as such 

ALTER TABLE tblVitalyTest
ADD cksum AS BINARY_CHECKSUM(value1, value2, value3, value4);
GO

Results of this query (based on your INSERT example):

      select id, cksum
      from tblVitalyTest

would be:

   id   cksum
    1   46173
    2   46941
    3   -44486
    4   33885
    5   -44487

Upvotes: 4

JohnS
JohnS

Reputation: 2052

Basically you can't. But you can do something similar with a trigger. See below

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER dbo.tblVitalyTestUpdateInsertTrigger ON dbo.tblVitalyTest
  AFTER INSERT, UPDATE
AS
  BEGIN

    SET NOCOUNT ON;
    UPDATE
      vt
    SET
      vt.BinCheckSum = BINARY_CHECKSUM(vt.value1, vt.value2, vt.value3,
                                       vt.value4)
    FROM
      dbo.tblVitalyTest vt
      INNER JOIN INSERTED i
        ON vt.id = i.id

  END
GO

Upvotes: 4

Related Questions