INSERT.SELECT
to populate the table.C1
, and the second row has the surrogate value of 61. Both of these values were generated by the IDENTITY property. However, the allocation of the values is not contiguous. This behavior is by design.INT
and BIGINT
data types only.CREATE TABLE
part of the statement. You also can't use the IDENTITY function in the SELECT
part of the CTAS. To populate a table, you need to use CREATE TABLE
to define the table followed by INSERT.SELECT
to populate it.SET IDENTITY_INSERT <your table> ON|OFF
syntax. You can use this syntax to explicitly insert values into the IDENTITY column.MAX()
function to determine the highest value allocated for a distributed table: