Sql-server – Unable to update or insert values table with DateTime column defaulted to GETDATE

alter-tabledatetimesql serversynonymstrigger

I have atlered table by adding Datetime column. This column is not null and also default value from GETDATE().

ALTER TABLE myTable ADD myDateColun DATETIME NOT NULL DEFAULT (GETDATE())

I'm trying to update, I'm unable to update or insert values table

update  myTable set myDateColun = GETDATE() where id = someNumber

Its throwing me SQL error

Column name or number of supplied values does not match table
definition.

Note: This table has trigger defined on insert and delete scenario for data, which updates synonym table linked to this table in other database.

Any ideas what can be reason for SQL errors in above scenario?

Best Answer

My guess is that your trigger has a "insert into ... select * from myTable" kind of structure, and now it's broken because there's one more column in the table. Change your trigger so that it lists the columns instead of using *.

Related Solutions

Sql-server – GETDATE() uniqueness when used on INSERT

GETDATE() is not guaranteed to be unique, no. Especially if it is a datetime, where milliseconds are rounded up or down, and of course when not all data is coming from the same user, you are almost guaranteed to have collisions.

Of course, a clustered index does not need to be unique, since SQL Server will make it so if it isn't (but only when it needs to). If you need to identify a specific row yourself (as opposed to just the uniquifier for SQL Server's internal usage), and there are no other candidate key columns (this is possible with things like event logging tables), you could add a non-clustered primary key that is, say, an IDENTITY column. Or if you really want web scale - and care more about insert performance than storage or any subsequent use of the data - you could use a uniqueidentifier column populated with NEWID().

Example

Let's take a look at an example, and see the differences.

USE tempdb;
GO

-- rely on uniqifier

CREATE TABLE dbo.Test1
(
  g DATETIME
);
CREATE CLUSTERED INDEX x ON dbo.Test1(g);


-- use an IDENTITY column

CREATE TABLE dbo.Test2
(
  i INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
  g DATETIME
);
CREATE CLUSTERED INDEX x ON dbo.Test2(g);


-- use a GUID

CREATE TABLE dbo.Test3
(
  n UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID()
    PRIMARY KEY NONCLUSTERED,
  g DATETIME
);
CREATE CLUSTERED INDEX x ON dbo.Test3(g);
GO

Insert speed

I populated all three tables with about 500,000 rows, using the following script:

SET NOCOUNT ON;
GO
SELECT SYSDATETIME();
GO
INSERT dbo.Test1(g) 
  SELECT DATEADD(SECOND, ABS([object_id])/1000, GETDATE()) 
  FROM sys.all_columns;
GO 100

SELECT SYSDATETIME();
GO
INSERT dbo.Test2(g) 
  SELECT DATEADD(SECOND, ABS([object_id])/1000, 
  GETDATE()) FROM sys.all_columns;
GO 100

SELECT SYSDATETIME();
GO
INSERT dbo.Test3(g) 
  SELECT DATEADD(SECOND, ABS([object_id])/1000, 
  GETDATE()) FROM sys.all_columns;
GO 100

SELECT SYSDATETIME();

Results:

Uniquifier:    2.26 seconds
IDENTITY:      3.89 seconds
GUID:          5.06 seconds

Scan Speed

SELECT SYSDATETIME();
DBCC DROPCLEANBUFFERS;
SELECT * FROM dbo.Test1;
SELECT SYSDATETIME();
DBCC DROPCLEANBUFFERS;
SELECT * FROM dbo.Test2;
SELECT SYSDATETIME();
DBCC DROPCLEANBUFFERS;
SELECT * FROM dbo.Test3;
SELECT SYSDATETIME();

Results:

Uniquifier:  3.89 seconds
IDENTITY:    4.21 seconds
GUID:        4.85 seconds

Space used

Looking at simple results from sp_spaceused:

EXEC sp_spaceused N'dbo.Test1';
EXEC sp_spaceused N'dbo.Test2';
EXEC sp_spaceused N'dbo.Test3';

Results:

                      Rows      Reserved    Data      Index size
Test1 - Uniquifier    513300    14024 KB    13880 KB       96 KB
Test2 - IDENTITY      513300    29072 KB    15792 KB    13200 KB
Test3 - GUID          513300    49936 KB    21960 KB    27928 KB

The uniquifier takes up less space than the IDENTITY column (and obviously both take less than the GUID), since it is only needed for collisions (and probably other optimizations I'm not aware of, such as compression).

We can also look at the index pages for the clustered index on the datetime column (index id = 1) and the non-clustered primary key (index id = 2):

DBCC TRACEON(3604,-1);

-- Uniquifier
DBCC IND('tempdb', 'dbo.Test1', 1); -- 1,747 index pages
-- no second index for this table

-- IDENTITY
DBCC IND('tempdb', 'dbo.Test2', 1); -- 1,987 index pages
DBCC IND('tempdb', 'dbo.Test2', 2); -- 1,637 index pages

-- GUID
DBCC IND('tempdb', 'dbo.Test3', 1); -- 2,764 index pages
DBCC IND('tempdb', 'dbo.Test3', 2); -- 3,472 index pages

Page/Row Contents

And finally, we can take a look at a specific page to see what is stored in a row. I simply took the first row from each of the clustered index DBCC IND results from above (your page id values will almost certainly be different):

DBCC PAGE('tempdb',1, 153, 1);
DBCC PAGE('tempdb',1, 199, 1);
DBCC PAGE('tempdb',1, 217, 1);

Uniquifier - pay particular attention to length/record size:

Slot 0, Offset 0x60, Length 15, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Record Size = 15
--------------^^

Memory Dump @0x000000000F7EA060

0000000000000000:   10000c00 0333ba00 fba20000 020000††††.....3º.û¢.....  

Slot 1, Offset 0x6f, Length 23, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 23              
--------------^^       
Memory Dump @0x000000000F7EA06F

0000000000000000:   30000c00 0333ba00 fba20000 02000001 †0....3º.û¢...... 
0000000000000010:   00170001 000000††††††††††††††††††††††.......

IDENTITY seems to have 4 extra bytes in the clustered index:

Slot 0, Offset 0x60, Length 19, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Record Size = 19
--------------^^

Memory Dump @0x0000000011DAA060

0000000000000000:   10001000 a735ba00 fba20000 03020000 †....§5º.û¢...... 
0000000000000010:   030000†††††††††††††††††††††††††††††††...              

Slot 1, Offset 0x73, Length 27, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 27              
--------------^^       
Memory Dump @0x0000000011DAA073

0000000000000000:   30001000 a735ba00 fba20000 04020000 †0...§5º.û¢...... 
0000000000000010:   03000001 001b0001 000000†††††††††††††...........

GUID has an extra 16 bytes in the clustered index:

Slot 0, Offset 0x60, Length 31, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Record Size = 31
--------------^^

Memory Dump @0x0000000011DAA060

0000000000000000:   10001c00 393aba00 fba20000 f3233e73 †....9:º.û¢..ó#>s 
0000000000000010:   a36e114b b1229a80 a5cb090a 030000††††£n.K±".¥Ë  ....  

Slot 1, Offset 0x7f, Length 39, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 39
--------------^^                     
Memory Dump @0x0000000011DAA07F

0000000000000000:   30001c00 393aba00 fba20000 c7bb2544 †0...9:º.û¢..Ç»%D 
0000000000000010:   4ad3574f a2c2029f e4abc9d7 03000001 †JÓWO¢Â.ä«É×.... 
0000000000000020:   00270001 000000††††††††††††††††††††††.'.....

Conclusion

Seems to me, by all accounts, you are better off letting the uniquifier do its own thing (assuming you don't expect to be able to differentiate between two rows with the exact same date/time value). The only time you can get in trouble with this, if any single value is duplicated 2,147,483,648 times, at which point you will overflow the uniquifier range of integer.

Sql-server – Does the Inserted table contain Identity values

The error is caused by attempting to insert AuditTerms when there is no corresponding primary key in TermKeys. After creating the table schema and trigger in tempdb and inserting a few records in TermKeys, I was able to reproduce it by issuing a DELETE twice on the same TermID:

DELETE FROM TermKeys
WHERE TermID = 1

DELETE FROM TermKeys
WHERE TermID = 1

gives me:

Msg 515, Level 16, State 2, Procedure TRG_TermKeys_AuditTerms_IUD, Line 15 Cannot insert the value NULL into column 'TermID', table 'tempdb.dbo.AuditTerms'; column does not allow nulls. INSERT fails. The statement has been terminated.

Something in the application code may be running the query twice; I can't see any other way to get a null value. The best way to avoid the error is to add a precautionary check of @@ROWCOUNT at the beginning of the trigger, since it will return the total number of rows affected by the DML statement that fired it.

ALTER TRIGGER [dbo].[TRG_TermKeys_AuditTerms_IUD] ON [dbo].[TermKeys] 
AFTER INSERT, UPDATE, DELETE NOT FOR REPLICATION AS
BEGIN
  -- If the trigger was fired but no rows were affected, do nothing 
  IF @@ROWCOUNT = 0
    RETURN;
  SET NOCOUNT ON;
  DECLARE @termid int;
  IF EXISTS(SELECT * FROM Deleted) SELECT @termid = TermID FROM Deleted;
  IF EXISTS(SELECT * FROM Inserted) SELECT @termid = TermID FROM Inserted;
  IF EXISTS(SELECT TermID FROM AuditTerms WHERE (TermID = @termid))
  BEGIN
    UPDATE AuditTerms SET UpdatedAt = getdate() WHERE (TermID = @termid);
  END
  ELSE
  BEGIN
    INSERT INTO AuditTerms (TermID, UpdatedAt) VALUES (@termid, getdate());
  END;
END;

Best Answer

Related Solutions

Sql-server – GETDATE() uniqueness when used on INSERT

Sql-server – Does the Inserted table contain Identity values

Related Question