Thursday, 23 June 2016

Question and Answers in Sql Server - Part 12


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


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


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





42. Sample of ISNULL, NULLIF, EXISTS , IN, NOT, WHILE, IF ELSE, SWITCH CASE

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





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.

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