SQL Server Identity

In SQL Server, an IDENTITY property can be added to a column of a table to auto-generate numeric values. This property is often used to create a primary key column for a table, ensuring that each row has a unique identifier. It's similar in functionality to the auto-increment feature in some other database systems.

Key Characteristics:

Seed: It's the value used for the very first row loaded into the table.
Increment: It's the incremental value added to the identity value of the previous row loaded.

Syntax:

When creating a new table:

CREATE TABLE table_name (
    column_name data_type IDENTITY(seed, increment),
    ...
);

For an existing table, you would generally have to add a new column or recreate the table, as you cannot add an IDENTITY property to an existing column.

Examples:

Creating a Table with an Identity Column:
```
CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1),
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);
```
In this example, the EmployeeID column will start at 1 (seed) and will increment by 1 (increment) for each new record.
Inserting Data:
When inserting data into a table with an identity column, you do not specify a value for the identity column unless you override the IDENTITY property.
```
INSERT INTO Employees (FirstName, LastName) VALUES ('John', 'Doe');
```
SQL Server will automatically generate a value for EmployeeID.

Important Points:

Scope_Identity(), @@Identity, and Ident_Current('tablename') are functions to retrieve the last identity value inserted into an identity column. Of these, Scope_Identity() is often preferred as it returns the last identity value generated in the same session and the same scope.
If you need to insert explicit values into an identity column, you can use SET IDENTITY_INSERT table_name ON; before the insert operation. Remember to set it back to OFF once you're done to prevent accidental inserts of explicit values.
There can only be one identity column per table.
The IDENTITY property is typically used with primary key columns, but this isn't a strict requirement. It can be used with any numeric column in a table.

Using the IDENTITY property in SQL Server tables is a convenient way to auto-generate unique values for a column, particularly useful for primary key columns to ensure row uniqueness in a straightforward manner.

How to use Identity columns in SQL Server:
- Identity columns automatically generate unique values for a column.
```
CREATE TABLE example_table (
   id INT IDENTITY(1,1),
   name VARCHAR(255)
);
```
Creating tables with Identity columns in SQL Server:
- Defining an identity column during table creation.
```
CREATE TABLE example_table (
   id INT IDENTITY(1,1),
   name VARCHAR(255)
);
```
Modifying existing tables with Identity columns:
- Adding an identity column to an existing table.
```
ALTER TABLE existing_table
ADD id INT IDENTITY(1,1);
```
Identity columns and primary key constraints in SQL Server:
- Using an identity column as the primary key.
```
CREATE TABLE example_table (
   id INT IDENTITY(1,1) PRIMARY KEY,
   name VARCHAR(255)
);
```
Setting seed and increment values for Identity columns:
- Customizing the starting value and increment for an identity column.
```
CREATE TABLE example_table (
   id INT IDENTITY(100,5),
   name VARCHAR(255)
);
```

Turning off Identity for inserts in SQL Server:

Temporarily disabling the automatic generation of identity values.

SET IDENTITY_INSERT example_table ON;

INSERT INTO example_table (id, name) VALUES (101, 'John');

SET IDENTITY_INSERT example_table OFF;

Identity columns and bulk insert operations in SQL Server:
- Handling identity columns when performing bulk insert operations.
```
BULK INSERT example_table
FROM 'data_file.csv'
WITH (TABLOCK, ORDER(1, 2), BATCHSIZE = 1000);
```

Identity_insert option in SQL Server:

Using the IDENTITY_INSERT option to allow explicit values.

SET IDENTITY_INSERT example_table ON;

INSERT INTO example_table (id, name) VALUES (101, 'John');

SET IDENTITY_INSERT example_table OFF;

Identity columns and data types in SQL Server:

Identity columns can be associated with various data types.

CREATE TABLE example_table (
   id INT IDENTITY(1,1),
   decimal_column DECIMAL(10,2),
   varchar_column VARCHAR(255)
);

Retrieving the last generated Identity value in SQL Server:
- Using SCOPE_IDENTITY() to get the last identity value.
```
INSERT INTO example_table (name) VALUES ('Alice');

SELECT SCOPE_IDENTITY() AS last_identity_value;
```
Reseeding Identity columns in SQL Server:
- Resetting the identity seed value.
```
DBCC CHECKIDENT ('example_table', RESEED, 100);
```
Identity columns vs. Sequence objects in SQL Server:
- Comparing identity columns and sequences for generating unique values.
```
CREATE SEQUENCE example_sequence
   START WITH 1
   INCREMENT BY 1;

SELECT NEXT VALUE FOR example_sequence;
```

Identity columns and replication in SQL Server:

Handling identity columns in replication scenarios.

-- Define the identity range for replication
EXEC sp_addarticle @article = 'example_table',
                   @source_object = 'example_table',
                   @identityrangemanagementoption = 'manual';