How to delete table data based on the foreign key relationship.

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

Query to get the parent level and child level hierarchy data of a particular account.

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)

Query to get the child level hierarchy data of a particular account.

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?