ACID Properties :

When a transaction processing system creates a transaction, it will ensure that the transaction will have certain characteristics.These characteristics are known as the ACID properties. ACID is an acronym for atomicity, consistency, isolation, and durability.

Atomicity : The atomicity property identifies that the transaction is atomic. An atomic transaction is either fully completed, or is not begun at all.

Consistency : A transaction enforces consistency in the system state by ensuring that at the end of any transaction the system is in a valid state.

Isolation : When a transaction runs in isolation, it appears to be the only action that the system is carrying out at one time.

Durability : A transaction is durable in that once it has been successfully completed, all of the changes it made to the system are permanent.

Collation :

A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish. Collation determines how your data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width. It's very often important with regards to internazionalization.

It defines how to deal with,
1. Accents (äàa etc)
2. Case (Aa)
3. The language context:
In a French collation, cote < côte < coté < côté.
In the SQL Server Latin1 default , cote < coté < côte < côté
4. ASCII sorts (a binary collation)

SPARSE Columns :

Introduced in MSSQL 2008, Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve non null values.

The SQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. Therefore, when the column value is NULL for any row in the table, the values require no storage.

Sparse columns are a property of the storage layer, rather than the logical table. Therefore a SELECT…INTO statement does not copy over the sparse column property into a new table.


Limitations :
1. Geography, geometry, image, ntext, text, timestamp and user-defined data types cannot be specified as SPARSE.
2. Sparse column cannot have a default value.
3. Computed column can contain a sparse column, but computed column cannot be marked as SPARSE.
4. Sparse column cannot be part of a clustered index or a unique primary key index. However, both persisted and non persisted computed columns that are defined on sparse columns can be part of a clustered key.

Example :
--  Using create table statement

CREATE TABLE user_sparse 
  ( 
     userid   INT sparse, 
     username VARCHAR(10) sparse, 
     gender   CHAR(1) 
  ) 

-- Using alter table statement

ALTER TABLE user_sparse 
ALTER COLUMN gender  CHAR(1) sparse

-- Check the sparse column status in sys.columns table

SELECT is_sparse, 
       * 
FROM   sys.columns 
WHERE  object_id = Object_id('user_sparse') 

MERGE :

Merge statement introduced in MSSQL server 2008. Using merge statement we can include multiple DML operations logic in one statement. It Performs insert, update, or delete operations on a target table based on the results of a join with a source table.

MERGE syntax consists of five primary clauses :

 1. MERGE clause specifies the table or view that is the target of the insert, update, or delete operations.
 2. USING clause specifies the data source being joined with the target.
 3. ON clause specifies the join conditions that determine where the target and source match.
 4. WHEN clauses (WHEN MATCHED, WHEN NOT MATCHED BY TARGET, and WHEN NOT MATCHED BY SOURCE) specify the actions to take based on the results of the ON clause and any additional search criteria specified in the WHEN clauses.
 5. OUTPUT clause returns a row for each row in the target that is inserted, updated, or deleted.

Example : If records are exists in users table then we have to update the records otherwise we have to insert into users table.

-- creating temp table as source
CREATE TABLE #testmerge 
  ( 
     username VARCHAR(10), 
     limitamt NUMERIC(20, 6) 
  ) 

-- populating the source table
INSERT INTO #testmerge 
VALUES     ('satya', 
            20000.000000), 
            ('jyothi', 
             22000.000000), 
            ('suresh', 
             30000.000000), 
            ('gopal', 
             44000.000000), 
            ('ram', 
             20000.000000) 

-- updating / inserting the users table records using MERGE statement
MERGE users AS target 
using #testmerge AS source 
ON target.username = source.username 
WHEN matched THEN 
  UPDATE SET limitamt = source.limitamt 
WHEN NOT matched THEN 
  INSERT (username, 
          limitamt) 
  VALUES (source.username, 
          source.limitamt); 

Table-Valued Parameter :

Table-Valued Parameters are a new parameter type in MSSQL Server 2008. Table-valued parameters are declared by using user-defined table types. You can use table-valued

parameters to send multiple rows of data to a stored procedure or function, without creating a temporary table or many parameters.

Steps to write a procedure using Table-Valued Parameter :

1. Create a table data type and define the table structure.
2. Create a procedure with Table-Valued Parameter.
3. Declare a varaible of table data type and insert the data into table daa type.
4. Pass the table data type to procedure.

Benefits :

1. Table-Valued Parameter give better performance than pass list of parameters to stored procedure or passing values using while loop / Cursors.
2. Do not acquire locks for the initial population of data from a client.
3. Enable you to include complex business logic in a single stored procedure.

Limitations :

1. Table-Valued Parameters can only be indexed to support Unique or Primary Key Constraints.
2. Table-Valued Parameters are read only in stored procedure code. Cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in stored procedures.
3. Table-Valued Parameters design can not be modify using Alter Table statement.
4. Table-Valued Parameters can not pass to CLR user-defined functions.
5. MSSQL Server does not maintain statistics on columns of table-valued parameters.
6. Cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO.

Example :

GO

CREATE TYPE UserTableType AS TABLE
( Username VARCHAR(100)
, is_active bit)

GO

-- Creating Stored Procedure
CREATE PROCEDURE Update_Isactive_Users
    @TVP UserTableType READONLY
    AS
Begin
    SET NOCOUNT ON
Update u set is_active = t.is_active from users u join @TVP t on u.username = t.username
END

GO

-- Defing Table Valued Parameters
DECLARE @UserTVP
AS UserTableType

GO

-- Populating data into @UserTVP
INSERT INTO @UserTVP (Username, is_active)
    SELECT 'satya', 1 union all
SELECT 'jyothi', 1 union all
SELECT 'ravi', 1 union all
SELECT 'naveen', 0 union all
SELECT 'suresh', 0 union all
SELECT 'ganesh', 1 union all
SELECT 'sasi', 0
 
GO

-- Executing Procedure by passing the Table Valued Parameters
EXEC Update_Isactive_Users @UserTVP

GO