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]

What is De-normalization ? Why should we go for De-normalization ?

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is a technique to move from higher to lower normal forms of database modeling in order to improve the data retrieval performance.

In a normalized database, more joins are required to gather all the information from multiple tables, as data is stored in multiple tables rather than in one large table. Queries that have a lot of complex joins will require more CPU usage and will adversely affect performance. So in order to improve the query performance we go for  De-normalization process.