Thursday 23 June 2016

Question and Answers in Sql Server - Part 11

In This post Let we see some important question and answers in Sql Server, which is a continuation of previous post.


36. What is the Difference between the Truncate and Delete ?
       Delete is a row by row execution process, It makes the row as lock to mark for delete. It have filter based remove of record based on where clause.It activates the trigger , to record the operation in Log. It can be rollback.DELETE is DML Command

    Truncate is used to delete all records , doesn't have the filter condition, Can't able to rollback, doesn't maintain the log file to maintain the records removal. Faster than Delete operation. Truncate is DDL Command.



37. Various ways to insert the records in to the table ?


INSERT INTO employee (name,age,salary)
VALUES('hh',28,230333)

SELECT * INTO newtable FROM employee

INSERT INTO newtable
(
name,
age,
salary
)

SELECT 'KA',26,393778




38. What is CTE ?
      CTE is known as Common table expression. Which have temporary result set have validity up to the next line of execution. there are various things is exists to store the temporary result set , Table variable, Table valued function and CTE. Now we see sample of CTE.

;WITH cte AS (SELECT * FROM employee)

SELECT * FROM cte




39. Sample of Table variable usage ?
      Table Variable have the scope with in the object execution,Afte that it can't able to access.

DECLARE @emptable TABLE
(
id INT ,
NAME VARCHAR(30)
)

INSERT INTO @emptable
        ( id, NAME )
SELECT id ,NAME
FROM Employee


SELECT * FROM @emptable





40 what is the usage of UNION ?
      Union is the keyword which is used to concat the values of two or more tables which have the same number of columns with same alias name. Result set have distinct rows it avoids the repeatation of records.In this example we are selecting a two table with union , but result set have only one record.

SELECT * FROM employee
UNION
SELECT * FROM employee


output:

id          NAME                           age         salary      department
----------- ------------------------------ ----------- ----------- ----------------------------------------
1           KA                             NULL        NULL        NULL




No comments:

Post a Comment