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') ) 

Fetch the top 3 maximum salaries wit out duplicates ?

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

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

-- To fetch the top 3 maximum salaries 
SELECT TOP 3 sal 
FROM   sal 
ORDER  BY sal DESC 

-- To fetch the top 3 maximum salaries with out duplicates 
SELECT TOP 3 sal 
FROM   sal 
GROUP  BY sal 
ORDER  BY 1 DESC 

OUTPUT Clause

Returns information from, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. Output clause works like a trigger, but if we create a trigger it will write all the data into audit table related to that event(Insert or Update or Delete). By using OUTPUT clause we can write required information into audit table.

Check the below code to know how it works :


CREATE TABLE TEST(ID INT IDENTITY(1,1),NAME VARCHAR(10),GENDER CHAR(1))
CREATE TABLE TEST_AUDIT(ID INT,NAME VARCHAR(10),GENDER CHAR(1),LST_UPDT_DTM DATETIME DEFAULT GETDATE())

GO

SELECT * FROM TEST
SELECT * FROM TEST_AUDIT

GO

INSERT INTO TEST
OUTPUT INSERTED.ID, INSERTED.NAME, INSERTED.GENDER
INTO TEST_AUDIT(ID,NAME,GENDER)
SELECT 'SATYA', 'M'

GO

SELECT * FROM TEST
SELECT * FROM TEST_AUDIT

GO

UPDATE TEST SET NAME = 'KRISHNA'
OUTPUT INSERTED.ID, INSERTED.NAME, INSERTED.GENDER
INTO TEST_AUDIT(ID,NAME,GENDER)
WHERE NAME = 'SATYA'

GO

SELECT * FROM TEST
SELECT * FROM TEST_AUDIT

GO

DELETE FROM TEST
OUTPUT DELETED.ID, DELETED.NAME, DELETED.GENDER
INTO TEST_AUDIT(ID,NAME,GENDER)
WHERE NAME = 'KRISHNA'

GO

SELECT * FROM TEST
SELECT * FROM TEST_AUDIT

Transaction Control Language (TCL) :

A Transaction Control Language (TCL)  is a used to control transactional processing in a database.

COMMIT :
To apply the transaction by saving the database changes. We cannot roll back a transaction after a commit transaction statement is issued because the data modifications have been made a permanent part of the database. COMMIT statement releases all row and table locks, and makes the changes to visible for other users.
Syntax :
COMMIT [TRAN | TRANSACTION] [TRANSACTION_NAME | TRANSACTION_VARIABLE_NAME]

ROLLBACK :
To undo all changes of a transaction. Rollback command is used for restore database to original since last commit. Rollback transaction erases all data modifications made from the start of the transaction or to a savepoint. The ROLLBACK statement in SQL cancels the proposed changes in a pending database transaction. The transaction can be rolled back completely by specifying the transaction name in the ROLLBACK statement.
Syntax :
ROLLBACK [TRAN | TRANSACTION ] [SAVEPOINT_NAME | SAVEPOINT_VARRIABLE ]


SAVEPOINT : 
To divide the transaction into smaller sections. It defines breakpoints for a transaction to allow partial rollbacks. Savepoints are useful in situations where errors are unlikely to occur. The use of a savepoint to roll back part of a transaction in the case of an infrequent error can be more efficient than having each transaction test to see if an update is valid before making the update. Updates and rollbacks are expensive operations, so savepoints are effective only if the probability of encountering the error is low and the cost of checking the validity of an update beforehand is relatively high.
Syntax :
SAVE [TRAN | TRANSACTION ] [SQVEPOINT_NAME | SAVEPOINT_VARIABLE_NAME]