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

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