Foreign key base script generation for delete

with Fkeys as (

    select distinct

         OnTable       = OnTable.name
        ,AgainstTable  = AgainstTable.name

    from

        sysforeignkeys fk

        inner join sysobjects onTable
            on fk.fkeyid = onTable.id

        inner join sysobjects againstTable
            on fk.rkeyid = againstTable.id

    where 1=1
        AND AgainstTable.TYPE = 'U'
        AND OnTable.TYPE = 'U'
        -- ignore self joins; they cause an infinite recursion
        and OnTable.Name <> AgainstTable.Name
    )

,MyData as (

    select
         OnTable = o.name
        ,AgainstTable = FKeys.againstTable

    from

        sys.objects o

        left join FKeys
            on  o.name = FKeys.onTable

    where 1=1
        and o.type = 'U'
        and o.name not like 'sys%'
    )

,MyRecursion as (

    -- base case
    select
         TableName    = OnTable
        ,Lvl        = 1
    from
        MyData
    where 1=1
        and AgainstTable is null

    -- recursive case
    union all select
         TableName    = OnTable
        ,Lvl        = r.Lvl + 1
    from
        MyData d
        inner join MyRecursion r
            on d.AgainstTable = r.TableName
)
select
     max(Lvl) As Lvl
    ,TableName
    ,strSql = 'delete from [' + tablename + ']'
from
    MyRecursion
group by
    TableName
order by
     1 desc
    ,2 desc

UP (Parent) and Down Level (Child Node) Hierarchy.

Declare @TestTable Table
(
ID int,
Name varchar(50),
ParentID int
)

Insert into @TestTable values
(1,'A',null),
(2,'Aa',1),
(3,'Aaa',1),
(4,'Ab',2),
(5,'Abb',4),
(6,'B',null),
(7,'Ba',6),
(8,'Bbb',7),
(9,'Abc',4),
(10,'Acc',9)


Declare @ID int = 4

;with cte
(
ID,Name,ParentID
)
As
(
Select ID,Name,ParentID
From @TestTable  where ID = @ID

Union All

Select a.ID , a.Name,a.ParentID
from cte c inner join @TestTable a on c.ParentID = a.ID
)
,
cte1
(
ID,Name,ParentID
)
As
(
Select ID,Name,ParentID
From cte where ParentID is null

Union All

Select a.ID , a.Name,a.ParentID
from cte1 c inner join @TestTable a on a.ParentID = c.ID
)

Select * from Cte1 Option (MAXRECURSION 32767)

Down Level (Child Node) Hierarchy.

Declare @TestTable Table
(
ID int,
Name varchar(50),
ParentID int
)

Insert into @TestTable values
(1,'A',null),
(2,'Aa',1),
(3,'Aaa',1),
(4,'Ab',2),
(5,'Abb',4),
(6,'B',null),
(7,'Ba',6),
(8,'Bbb',7),
(9,'Abc',4),
(10,'Acc',9)


Declare @ID int = 4

;with cte

(

ID , Name,ParentID,ParentName

)

As

(

 Select ID , Name,ParentID,
(select Name from @TestTable where ID = a.ParentID) parentName
 From @TestTable a where ID = @ID

Union All

Select m.ID , m.Name,c.ID,c.Name
from cte c inner join @TestTable m on m.ParentID = c.ID

)

Select * from cte Option (MAXRECURSION 32767)

SQL Server Interview Questions

1. What is indexing?
   Types of index. details about Clustered and non clustered index.
 
2. What is CTE (Common Table Expression)? advantage?
   Use of it, Recursive CTE?
 
3. Difference between Temp table and table variable?

4. What is Transaction?
   Describe Isolation levels deeply.
 
5. What is locking ? Types of locking., locking levels

6. What is service broker?

7. Backup and restoration. Describe backup types?
   one scenario to Restore using 3 backup types.
 
8. Query for delete duplicate records keep original.

9. Query for paging

10. Query for hierarchy structure.

11. varChar vs nvarchar vs Char

12. How to clear identity field.

13. Nth largest salary of an employee.

14. Rows to column without using loop.

15. What is Cursor ? Types of cursor?

16. Performance Tuning? Profiler,Query tuning advisor.

17. What precautions taking for Query optimization?

18. Triggers ? Instead of Trigger? Have u used DDL trigger?

19. Normalization? Denormalization? Types of normal forms?

20. Apply Clause?

21. Pivot and UnPivot?

22. Difference of SQL Server 2005 and 2008 / 2008 and 2008R2

23. DB Mail vs SQL Mail?

24. Database design for Online shopping.

25. ER diagram ? Explain what for ER Diagram? Table Relation ship.

26. Indexed view? What conditions consider when creating indexed view?

27. SSRS Architecture? Tablix? Types of Group? Chart? New Controls in SQL server 2008R2? Have u used map control?

28. SCD ?

29. What is Collation? explain sensitivities.

30. How can i pass multiple value as a parameter to SP. explain table valued type and xml.

31. What is Dirty Read? phantom read,un repeatable read , lost updates

32. Deadlocking?

33. Explain system databases?

34. Replication , logshipping , mirroring

35. what datatype for using image?

36. What is SQL Injection? how to protect sql injection?

37. Error handling?

38. Table scan Index Seek and Index Scan?

39.  DBCC Commands?

40.  What are Ranking Functions?

41. Date Functions / string functions?

42. What is the FILLFACTOR?

43. Correlated subquery ? Is it good method?

How to stop or disable Recursive Trigger/Nested Trigger in SQL Server.

A trigger execution may occured another trigger execution or same trigger execution. This trigger called Nested Trigger Or Recursive Trigger. SQL Server allows maximum 32 Level recursion.

Disable Nesting/Recursing Triggers

Method 1:

sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

Method 2:

ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF

Method 3:




Restrict Trigger Nesting to certain level.

Put following script in trigger code. This will stop the trigger recursion after certain levels. In following case it will stop after 5 recursion.

IF ((
SELECT TRIGGER_NESTLEVEL()) > 5 )
RETURN

Comma seperated date splitting without using loop.

--Comma seperated date splitting without using loop.

--// Table variable declarion
Declare @TableVar Table
(
    ID            int,
    CustomDates varchar(max)
)

--// Data insertion
Insert into @TableVar values(1,'May 01 2012  4:00AM,May 05 2012  4:00AM,Apr 13 2012 10:00AM')

select * from @TableVar

;with cte
(
    ID,CustomDates,strDt,pos
)
As
(
    select ID,
        CustomDates,
        substring(
                    CustomDates,1,case when (charindex(',',CustomDates) -1) = -1 then Len(CustomDates) else charindex(',',CustomDates) -1 end
                 ) strDt,charindex(',',CustomDates) as pos
       
    from @TableVar
    union all
    select s.ID,
        s.CustomDates,
       
             substring(s.CustomDates,cte.pos+1,
                    case when (charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates)))-1) = -1 then len(s.CustomDates) else charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates)))-1 end)       

           ,pos + case when charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) = 0 then len(substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) else charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) end as pos


    from @TableVar s inner join cte on cte.ID = s.ID
    where cte.pos < len(s.CustomDates)
)

select strDt from cte OPTION (MAXRECURSION 32767);

---------------------------------------------------------------

Another Method:

Declare @TableVar Table

(

ID int,

Data varchar(max)

)

Insert into @TableVar values(1,'May 01 2012 4:00AM,May 05 2012 4:00AM,Apr 13 2012 10:00AM')


DECLARE @XML XML

SELECT @XML = '<Root><Child><Data>'+REPLACE(Data,',','</Data></Child><Child><Data>')+'</Data></Child></Root>' FROM @TableVar

SELECT CAST(DOC.COL.value('Data[1]','NVARCHAR(400)') AS DATE) AS Data

FROM @XML.nodes('/Root/Child') DOC(COL)

Normal query instead of MERGE operation

/****** Object:  StoredProcedure [dbo].[spBlockHr_SAVE]    Script Date: 04/11/2012 15:32:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Author    : Nikhildas
    Date    : July 01 2011 10:45AM
    Purpose : To Save Block HR value.
    Execute : Exec [spBlockHr_SAVE] <xml Value>,@P_RET_VAL    OUTPUT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Modified By                On                Remarks
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    <Next Entry>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
 ALTER PROCEDURE [dbo].[spBlockHr_SAVE]
 (
    @P_XML        XML,
    @P_RET_VAL    INT OUTPUT
 )
 AS
    SET NOCOUNT ON

    DECLARE @DocHandle        INT
   
    DECLARE @BlockHr TABLE
    (
        ID            int ,
        SECTOR        nvarchar(255) ,
        Route        nvarchar(255) ,
        CODE        nvarchar(255) ,
        Month01     float ,
        Month02     float ,
        Month03     float ,
        Month04     float ,
        Month05     float ,
        Month06     float ,
        Month07     float ,
        Month08     float ,
        Month09     float ,
        Month10     float ,
        Month11     float ,
        Month12     float ,
        Total        float
    )   
           
    BEGIN TRY
        BEGIN TRANSACTION
        EXEC sp_xml_preparedocument @DocHandle OUTPUT,@P_XML
       
        INSERT INTO @BlockHr
        SELECT
            ID,
            SECTOR    ,
            Route,
            CODE,
            Month01    ,
            Month02    ,
            Month03 ,
            Month04    ,
            Month05    ,
            Month06    ,
            Month07    ,
            Month08    ,
            Month09    ,
            Month10    ,
            Month11    ,
            Month12    ,
            Total   
        FROM    OPENXML(@DocHandle,'/Root',2)
        WITH
        (
            ID            INT,
            SECTOR        NVARCHAR(1000),
            Route        NVARCHAR(1000),
            CODE        nvarchar(255),
            Month01        FLOAT(53),
            Month02        FLOAT(53),
            Month03        FLOAT(53),
            Month04        FLOAT(53),
            Month05        FLOAT(53),
            Month06        FLOAT(53),
            Month07        FLOAT(53),
            Month08        FLOAT(53),
            Month09        FLOAT(53),
            Month10        FLOAT(53),
            Month11        FLOAT(53),
            Month12        FLOAT(53),
            Total        FLOAT(53)

        )
                   
        EXEC sp_xml_removedocument @DocHandle
/*    Commented on 11-Jul-2011 (For Avoid Merge statement)       
        MERGE BlockHr AS T
        USING @BlockHr    AS S
        ON S.ID = T.ID
        WHEN MATCHED
        THEN
            UPDATE
                SET SECTOR    = S.SECTOR,
                    Route    = S.Route,
                    CODE    = S.CODE,
                    Month01    = S.Month01,
                    Month02    = S.Month02,
                    Month03 = S.Month03,
                    Month04    = S.Month04,
                    Month05    = S.Month05,
                    Month06    = S.Month06,
                    Month07    = S.Month07,
                    Month08    = S.Month08,
                    Month09    = S.Month09,
                    Month10    = S.Month10,
                    Month11    = S.Month11,
                    Month12    = S.Month12,
                    Total    = S.Total
        WHEN NOT MATCHED BY TARGET
        THEN
            INSERT
            (
                SECTOR    ,
                Route,
                CODE,
                Month01    ,
                Month02    ,
                Month03 ,
                Month04    ,
                Month05    ,
                Month06    ,
                Month07    ,
                Month08    ,
                Month09    ,
                Month10    ,
                Month11    ,
                Month12    ,
                Total           
            )
            VALUES
            (
                S.SECTOR    ,
                S.Route        ,
                S.CODE        ,
                S.Month01    ,
                S.Month02    ,
                S.Month03 ,
                S.Month04    ,
                S.Month05    ,
                S.Month06    ,
                S.Month07    ,
                S.Month08    ,
                S.Month09    ,
                S.Month10    ,
                S.Month11    ,
                S.Month12    ,
                S.Total   
            );
*/
            --// Updation
            UPDATE T
                SET SECTOR    = S.SECTOR,
                    Route    = S.Route,
                    CODE    = S.CODE,
                    Month01    = S.Month01,
                    Month02    = S.Month02,
                    Month03 = S.Month03,
                    Month04    = S.Month04,
                    Month05    = S.Month05,
                    Month06    = S.Month06,
                    Month07    = S.Month07,
                    Month08    = S.Month08,
                    Month09    = S.Month09,
                    Month10    = S.Month10,
                    Month11    = S.Month11,
                    Month12    = S.Month12,
                    Total    = S.Total
            FROM [BlockHr] T INNER JOIN @BlockHr S
            ON S.ID = T.ID

            --// Insertion
            INSERT INTO [BlockHr]
            SELECT                
                S.SECTOR    ,
                S.Route        ,
                S.CODE        ,
                S.Month01    ,
                S.Month02    ,
                S.Month03    ,
                S.Month04    ,
                S.Month05    ,
                S.Month06    ,
                S.Month07    ,
                S.Month08    ,
                S.Month09    ,
                S.Month10    ,
                S.Month11    ,
                S.Month12    ,
                S.Total        ,
                1
            FROM  [BlockHr] T RIGHT OUTER JOIN @BlockHr S
            ON S.ID = T.ID   
            WHERE (T.ID IS NULL)
                                           
                   
            EXEC [SPAD_TRX_LOG_SAVE] 0,'USER-COST CENTER','','MODIFY',1,'',NULL
            SET @P_RET_VAL =   1
      COMMIT TRANSACTION
      SET @P_RET_VAL = 1
    END TRY
    BEGIN CATCH
        SET @P_RET_VAL = -1
        ROLLBACK TRANSACTION
    END CATCH