Why this new nullable TimeStamp field contains values after that I added to a table with pre existing records?

Question

I have the following doubt. I am working with Microsoft SQL Server and I added a TimeStamp field named Data into a table specifying that this field is nullable. I added this new field to a table containing some pre existing records.

The problem is that querying my table:

SELECT TOP 1000 [ID]
      ,[Numero protocollo]
      ,[Anno]
      ,[IdUor]
      ,[Protocollista]
      ,[Protocollato]
      ,[Lavorato]
      ,[Errore]
      ,[CopiaConoscenza]
      ,[Inoltro]
      ,[Data]
  FROM [IWG_PG].[dbo].[PROT_INOLTRO]

I am obtaining that these Data coulm contains values as 0x00000000000007F8, 0x00000000000007F9, etcetc

Why? I expected to find null. Why these records have a value for the Data field?

EDIT-!: This is the script:

USE [IWG_PG]
GO

/****** Object:  Table [dbo].[PROT_INOLTRO]    Script Date: 1/2/2019 4:54:03 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PROT_INOLTRO](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Numero protocollo] [int] NOT NULL,
    [Anno] [int] NOT NULL,
    [IdUor] [int] NOT NULL,
    [Protocollista] [nvarchar](50) NOT NULL,
    [Protocollato] [bit] NOT NULL,
    [Lavorato] [bit] NOT NULL,
    [Errore] [bit] NOT NULL,
    [CopiaConoscenza] [bit] NOT NULL,
    [Inoltro] [bit] NOT NULL,
    [Data] [timestamp] NULL,
 CONSTRAINT [PK_PROT_INOLTRO] PRIMARY KEY NONCLUSTERED 
(
    [Numero protocollo] ASC,
    [Anno] ASC,
    [IdUor] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

As you can see the Data field is setted to NULL

Maybe your table has a Default value? Please, include the script that generates the table (created from SQL Server Management Studio) — Angel M., Jan 02 '19 at 15:50
This is effectively a duplicate of [this question](https://stackoverflow.com/q/42215930/1048425). Timestamp is a synonum, for RowVersion, which is not nullable (even if it is stated as such). — GarethD, Jan 02 '19 at 16:01
@GarethD More likely a duplicate of https://stackoverflow.com/q/42215930, which shows NULL rowversion is possible. — Paul White, Apr 15 '23 at 12:17

score 3 · Answer 1 · answered Jan 02 '19 at 15:56

The timestamp data type is deprecated and also a synonym for the rowversion data type (see https://learn.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-2017 for details). And rowversioncontains the automatically generated binary numbers you're getting. Quote from the same page:

Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type.

Maybe you just want to use a datetime column?

Paul White · Answer 2 · 2023-04-15T12:19:08.510

This is legacy, and very arguably incorrect, behaviour.

SQL Server 2022 contains a feature to add a NULL rowversion for existing rows when a nullable rowversion column is added. This new metadata-only operation is not documented or publicly released yet. It is only accessible by enabling an undocumented trace flag.

This suggests the change is still being developed and/or fully tested. If it makes it into the final product, I expect it will be linked to database compatibility level or some other configuration option.

score -1 · Answer 3 · answered Jan 02 '19 at 15:55

-1

You also shouldn´t use the name Data. This name could be reserved --> https://learn.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-2017

Maybe this could cause some side effects

answered Jan 02 '19 at 15:55

Christian

152
1
13

Same issues also changing name – AndreaNobili Jan 02 '19 at 15:57

Why this new nullable TimeStamp field contains values after that I added to a table with pre existing records?

3 Answers3