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

QUOTENAME :

Quotename() is a SQL Server String function. It Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.

Syntax :

QUOTENAME ( 'character_string' [ , 'quote_character' ] ) 

If the character_string or column value is greater than the 128 characters then quotename function will returns NULL.

Example`s :

SELECT Quotename('satya') 

Result : [satya]

Note : If quote_character is not specified, by default it take brackets.

SELECT Quotename('satya', '(') 

Result : (satya)

SELECT Quotename('satya', '{') 

Result : {satya}

SELECT Quotename('satya', '[') 

Result : [satya]

SELECT Quotename('satya', '''') 

Result : 'satya'

SELECT Quotename('satya', '"') 

Result : "satya"

SELECT Quotename('satya', '<') 

Result : <satya>

Applying QUOTENAME Function to Column :

CREATE TABLE table_quotename 
  ( 
     info VARCHAR(250) 
  ) 

INSERT INTO table_quotename 
SELECT Replicate('satya mssql', 2) 
UNION ALL 
SELECT Replicate('satya mssql', 4) 
UNION ALL 
SELECT Replicate('satya mssql', 13) 

SELECT Quotename(info), 
       Quotename(info, '['), 
       Quotename(info, '{'), 
       Quotename(info, ''''), 
       Quotename(info, '(') 
FROM   table_quotename 

In the above query result last column values are showing as NULL, because values in the third row is greater than 128 characters that`s why result is NULL.

How to fetch the top four salaries without duplicate records ?

For example we have a Employee table like below, in this we need (nagarjuna,2000), (vijay,3000),(Ram,4000) and (bharani,5000) records.

 Employee Table Data :

NAME	SALARY
venkat	1000
satya	1000
nagarjuna	2000
jyothi	2000
krishna	2000
vijay	3000
Anil	3000
Ram	4000
bharani	5000

 Expected Result :

NAME	SALARY
bharani	5000
Ram	4000
Anil	3000
jyothi	2000

Query to fetch the expected result,

SELECT TOP 4 salary, 
             name 
FROM   (SELECT salary, 
               name, 
               Row_number() 
                 OVER( 
                   partition BY salary 
                   ORDER BY salary DESC) AS Record_count 
        FROM   employee 
        GROUP  BY salary, 
                  name) e 
WHERE  record_count = 1 
ORDER  BY salary DESC 

Can we add multiple foreign_key constraint`s with single ALTER Table statement ?

Yes we can.

Check the below code :

CREATE TABLE sal 
  ( 
     name CHAR(5) PRIMARY KEY, 
     sal  INT 
  ) 

CREATE TABLE sal2 
  ( 
     name CHAR(5), 
     sal  INT PRIMARY KEY 
  ) 

CREATE TABLE sal3 
  ( 
     name CHAR(5), 
     sal  INT 
  ) 

ALTER TABLE sal3 
  ADD CONSTRAINT fk_sal_name FOREIGN KEY(name) REFERENCES sal(name), CONSTRAINT 
  fk_sal2_sal FOREIGN KEY(sal) REFERENCES sal2(sal) 

what type of index create when we create a foreign key on a table ?

No index will be create when we create a foreign key on a table.

Check the below code :

CREATE TABLE sal 
  ( 
     name CHAR(5) NOT NULL, 
     sal  INT 
  ) 

INSERT INTO sal 
VALUES     ('a', 
            100), 
            ('b', 
             200), 
            ('c', 
             300), 
            ('d', 
             400), 
            ('e', 
             100), 
            ('f', 
             200), 
            ('g', 
             400), 
            ('h', 
             300), 
            ('i', 
             500) 

SELECT * 
FROM   sal 

-- only HEAP index will be present 
SELECT Object_name(object_id), 
       * 
FROM   sys.indexes 
WHERE  object_id IN ( Object_id('sal') ) 

CREATE TABLE sal2 
  ( 
     name CHAR(5), 
     sal  INT 
  ) 

-- only HEAP index will be present 
SELECT Object_name(object_id), 
       * 
FROM   sys.indexes 
WHERE  object_id IN ( Object_id('sal2') ) 

ALTER TABLE sal 
  ADD CONSTRAINT pk_sal PRIMARY KEY(name) 

-- Now CLUSTERED index will be created default with primary key. 
SELECT Object_name(object_id), 
       * 
FROM   sys.indexes 
WHERE  object_id IN ( Object_id('sal') ) 

ALTER TABLE sal2 
  ADD CONSTRAINT ref_sal_name FOREIGN KEY(name) REFERENCES sal(name) 

-- After creating the foreign key constraint also, we have only HEAP index on sal2 table. 
SELECT Object_name(object_id), 
       * 
FROM   sys.indexes 
WHERE  object_id IN ( Object_id('sal2') ) 

SELECT Object_name(object_id), 
       * 
FROM   sys.indexes 
WHERE  object_id IN ( Object_id('sal'), Object_id('sal2') )