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
41. Difference between the UNION and UNION ALL ?
Union Returns the unique row of result set. Union ALL gives the all records from the two tables. i.e when executing below code we are getting two records.
output:
42. Sample of ISNULL, NULLIF, EXISTS , IN, NOT, WHILE, IF ELSE, SWITCH CASE
43. How to create a Transaction in SQL SERVER ?
Transaction is used to record the sequence of steps and maintain a consistent finish of operation based on execution statement if operation reaches the final statement then we can commit the changes to the table , otherwise we can rollback the changes .
example
44. How to use a Try and Catch Block ?
45. what is usage of Merge ?
Merge is like a Join used to join two tables and based on the condition , if the condition match then for particular we can create a login for target table, When condition not matched we can create a another logic.
46. How to retrieve a Value from an XML and insert into table ?
Click Here to Read
47. What is Collation in SQL SERVER ?
Collation is a set of rules applied for the proper use of character for either a language in the server, for comparing and sorting, It have case sensitivity, Accent sensitivity and khana sensitivity
48. What is User defined Data Type ?
User defined Data type is a type which can be created from the base type, and make the end user not knowing about type.
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
41. Difference between the UNION and UNION ALL ?
Union Returns the unique row of result set. Union ALL gives the all records from the two tables. i.e when executing below code we are getting two records.
SELECT * FROM employee
UNION ALL
SELECT * FROM employee
id NAME age salary department
----------- ------------------------------ ----------- ----------- ----------------------------------------
1 KA NULL NULL NULL
1 KA NULL NULL NULL
-- used to check
if the first parameter of the function is null then second paramter of the
function is returned as value
SELECT ISNULL(age,28) FROM employee
--used to check
whether the both parameters passes are same consider to be null otherwise first
parameter
-- is a column
second parameter is -1 that indicates if -1 occurs treated it as null value
SELECT NULLIF(age,-1) FROM employee
-- Exist keyword
is used to check whether the exist of a record based on particular condition
IF EXISTS(SELECT name FROM employee WHERE
id = 1)
BEGIN
PRINT 'Employee is working in this organisation'
END
-- In condition
is used to fetch records in that condition present in filter
SELECT * FROM Employee WHERE id IN (1,2,4)
-- Not is used
to fetch the records other than specified condition.
SELECT * FROM Employee WHERE id NOT IN (1,2,4)
-- While is loop
execute a statement based on condition
DECLARE @i INT =1
WHILE @i < 8
BEGIN
PRINT @i
SELECT @i = @i + 1
END
-- If Else Condition
DECLARE @i INT = 1
IF @i = 2
BEGIN
SELECT 'value
is 2'
END
ELSE
BEGIN
SELECT 'value
is not 2'
END
--Switch case
statement
DECLARE @i INT = 1
DECLARE @result VARCHAR(30)
BEGIN
SELECT @result = CASE
WHEN
@i > 5 THEN 'Greater than 5'
WHEN @i <5 THEN 'Less than 5'
ELSE
'None'
END
SELECT @result
END
43. How to create a Transaction in SQL SERVER ?
Transaction is used to record the sequence of steps and maintain a consistent finish of operation based on execution statement if operation reaches the final statement then we can commit the changes to the table , otherwise we can rollback the changes .
example
BEGIN TRY
BEGIN TRAN
DECLARE @i INT
SELECT @i = id FROM employee WHERE name = 'KA'
IF @i IS NOT NULL
BEGIN
SELECT 'transaction
commited'
COMMIT TRAN
END
ELSE
BEGIN
SELECT 'transaction
rollbacked'
ROLLBACK TRAN
END
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
END catch
44. How to use a Try and Catch Block ?
BEGIN TRY
SELECT CAST('d' AS INT) -- Predefined cast error
RAISERROR('error on cast ',16,1) -- User defined raise
error
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS 'Mesage' ,
ERROR_LINE() AS 'Line no',
ERROR_NUMBER() AS 'Error number',
ERROR_SEVERITY() AS 'Severity',
ERROR_STATE() AS 'State'
END catch
Merge is like a Join used to join two tables and based on the condition , if the condition match then for particular we can create a login for target table, When condition not matched we can create a another logic.
MERGE INTO EMPLOYEE AS E
USING EMPTABLE AS T
ON E.id = T.id
WHEN MATCHED THEN
UPDATE SET
E.NAME = T.NAME
WHEN NOT MATCHED THEN
INSERT(NAME,age)
VALUES(T.NAME,T.AGE);
46. How to retrieve a Value from an XML and insert into table ?
Click Here to Read
47. What is Collation in SQL SERVER ?
Collation is a set of rules applied for the proper use of character for either a language in the server, for comparing and sorting, It have case sensitivity, Accent sensitivity and khana sensitivity
-- Employee
table have name Raj, rAj, raj, RAJ with differnet case sensitivity of same name
SELECT * FROM employee
WHERE name = 'Raj'
-- Above query
results four records, To fetch the correct records as match exact the same case
sensitivity
-- Collate
should be added in where condition
SELECT * FROM employee
WHERE name COLLATE Latin1_General_CS_AS = 'Raj'
-- permanently
added the collate in table column
ALTER TABLE employee
ALTER COLUMN name COLLATE
Latin1_General_CS_AS
EXEC sp_help PersonDB
-- To fetch collation of any column
in any table from the database
48. What is User defined Data Type ?
User defined Data type is a type which can be created from the base type, and make the end user not knowing about type.
CREATE TYPE KA_Type FROM INT
DECLARE @d KA_Type
SELECT @d = 2
SELECT @d
No comments:
Post a Comment