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 ?
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.
39. Sample of Table variable usage ?
Table Variable have the scope with in the object execution,Afte that it can't able to access.
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.
output:
id NAME age salary department
----------- ------------------------------ ----------- ----------- ----------------------------------------
1 KA NULL NULL NULL
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
id NAME age salary department
----------- ------------------------------ ----------- ----------- ----------------------------------------
1 KA NULL NULL NULL
No comments:
Post a Comment