Joins in SQL SERVER

view plaincopy to clipboardprint?

1. DECLARE @Employee TABLE  
2. (  
3.  EmpID  INT,  
4.  EmpName  VARCHAR(50),  
5.  DeptID  INT  
6. )  
7.   
8. INSERT INTO @Employee  
9. SELECT 1,'kannu',1 UNION ALL  
10. SELECT 2,'Ram',1 UNION ALL  
11. SELECT 3,'Kumar',2 UNION ALL  
12. SELECT 4,'Murali',3 UNION ALL  
13. SELECT 5,'Ranjith',4 UNION ALL  
14. SELECT 6,'Suresh',4 UNION ALL  
15. SELECT 7,'Selva',5 UNION ALL  
16. SELECT 8,'Muthu',6   
17.   
18. DECLARE @Department TABLE  
19. (  
20.  DeptID  INT,  
21.  DeptName VARCHAR(50)  
22. )  
23.   
24. INSERT INTO @Department  
25. SELECT 1,'Account' UNION ALL  
26. SELECT 2,'Testing' UNION ALL  
27. SELECT 3,'Web Develop' UNION ALL  
28. SELECT 5,'PDA Develop' UNION ALL  
29. SELECT 6,'Java' UNION ALL  
30. SELECT 7,'DBA'  

DECLARE @Employee TABLE
(
 EmpID  INT,
 EmpName  VARCHAR(50),
 DeptID  INT
)

INSERT INTO @Employee
SELECT 1,'kannu',1 UNION ALL
SELECT 2,'Ram',1 UNION ALL
SELECT 3,'Kumar',2 UNION ALL
SELECT 4,'Murali',3 UNION ALL
SELECT 5,'Ranjith',4 UNION ALL
SELECT 6,'Suresh',4 UNION ALL
SELECT 7,'Selva',5 UNION ALL
SELECT 8,'Muthu',6 

DECLARE @Department TABLE
(
 DeptID  INT,
 DeptName VARCHAR(50)
)

INSERT INTO @Department
SELECT 1,'Account' UNION ALL
SELECT 2,'Testing' UNION ALL
SELECT 3,'Web Develop' UNION ALL
SELECT 5,'PDA Develop' UNION ALL
SELECT 6,'Java' UNION ALL
SELECT 7,'DBA'

What is Join in SQL SERVER?

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.
Join Types

1. Inner Join
   • Inner Join
   • Equi-Join
   • Natural Join
2. Outer join
   • Left Join
     • Left Join Excluding Inner Join
   • Right Join
     • Right Join Excluding Inner Join
   • Full Outer Join
     • Outer Join Excluding Inner Join Or Left & Right Joins Excluding Inner Join
3. Cross Join / Cartesian product
4. Self Join / Auto Join

Join
The query compares each row of table(@Employee) with each row of table(@Department) to find all pairs of rows which satisfy the join predicate.

Inner Join
The query produces a new result set by combining the column values of both the table @Employee and @Department.
It will produce the resultset if there is atleast one match.

An inner join is a join with a join condition that may contain both equality and non-equality sign whereas an equijoin is a join with a join condition that only contain only equality sign. So we can say an equijoin is a type of inner join containing (Equal)= operator in the join condition.

It is good to know the difference between join and INNER JOIN keyword. Actually there is no difference. If we write JOIN then by default INNER JOIN is performed.

Equi-join or Inner Join
Join based on equality test. Using other operator disqualifies equijoin.

view plaincopy to clipboardprint?

1. SELECT A.EmpID  ,  
2.        A.EmpName,  
3.        A.DeptID  ,  
4.        B.DeptID ,  
5.        B.DeptName  
6. FROM   @Employee A  
7.        INNER JOIN @Department B  
8.  ON     A.DeptID=B.DeptID  

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID  ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       INNER JOIN @Department B
 ON     A.DeptID=B.DeptID

Complex Join Or Inner Join
If join nested then it is called complex join
Jon based on more then one column from each table.

Natural Join
Select the Particular column in Both TableA and TableB. Both table should have same no of columns and names (i.e. Identical columns in both tables). This is accomplished by using union operator in both the select statement.

view plaincopy to clipboardprint?

1. SELECT EmpID  ,  
2.        EmpName,  
3.        DeptID  
4. FROM   @Employee  
5. UNION --Eliminates duplicates  
6. --UNION ALL --Get all the records from both the table  
7. SELECT EmpID  ,  
8.        EmpName,  
9.        DeptID  
10. FROM   @Employee  

SELECT EmpID  ,
       EmpName,
       DeptID
FROM   @Employee
UNION --Eliminates duplicates
--UNION ALL --Get all the records from both the table
SELECT EmpID  ,
       EmpName,
       DeptID
FROM   @Employee

Outer Join
Left Outer Join
Display all columns in left side and only Match values in right side. Remaining values will be NULL.

view plaincopy to clipboardprint?

1. SELECT A.EmpID  ,  
2.        A.EmpName,  
3.        A.DeptID  ,  
4.        B.DeptID ,  
5.        B.DeptName  
6. FROM   @Employee A  
7.        LEFT OUTER JOIN @Department B  
8.        ON     A.DeptID=B.DeptID  

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID  ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       LEFT OUTER JOIN @Department B
       ON     A.DeptID=B.DeptID

Left Join Excluding Inner Join

view plaincopy to clipboardprint?

1. SELECT A.EmpID  ,  
2.        A.EmpName,  
3.        A.DeptID  ,  
4.        B.DeptID ,  
5.        B.DeptName  
6. FROM   @Employee A  
7.        LEFT JOIN @Department B  
8.        ON     A.DeptID=B.DeptID  
9. WHERE B.DeptID IS NULL  

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID  ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       LEFT JOIN @Department B
       ON     A.DeptID=B.DeptID
WHERE B.DeptID IS NULL

Difference between On clause and Where clause when used with left join

view plaincopy to clipboardprint?

1. SELECT E.EmpID  ,  
2.        E.EmpName,  
3.        D.DeptName  
4. FROM   @Employee             AS E  
5.        LEFT JOIN @Department AS D  
6.        ON     D.DeptID = E.DeptID  
7.        AND  
8.               (  
9.                      D.DeptName = 'Account'  
10.               OR     D.DeptName = 'Java'  
11.               )  
12.                 
13. SELECT E.EmpID  ,  
14.        E.EmpName,  
15.        D.DeptName  
16. FROM   @Employee             AS E  
17.        LEFT JOIN @Department AS D  
18.        ON     D.DeptID = E.DeptID  
19. WHERE (  
20.               D.DeptName = 'Account'  
21.        OR     D.DeptName = 'Java'  
22.        )  

SELECT E.EmpID  ,
       E.EmpName,
       D.DeptName
FROM   @Employee             AS E
       LEFT JOIN @Department AS D
       ON     D.DeptID = E.DeptID
       AND
              (
                     D.DeptName = 'Account'
              OR     D.DeptName = 'Java'
              )
              
SELECT E.EmpID  ,
       E.EmpName,
       D.DeptName
FROM   @Employee             AS E
       LEFT JOIN @Department AS D
       ON     D.DeptID = E.DeptID
WHERE (
              D.DeptName = 'Account'
       OR     D.DeptName = 'Java'
       )

Now let us understand ON clause it is apply before JOIN that is why it retrieves all the result of Table2 where there are Flag = 1 but it does not affect Table1 so it retrieves all the rows of table1. When WHERE clause is applied it applies to complete result so it removes all the rows from Table1 and Table2 where Flag is not equal to 1, essentially keeping flag = 1 rows from Table1 and Table2.
Right Outer Join
Just Opposite Of Left Outer Join

view plaincopy to clipboardprint?

1. SELECT A.EmpID  ,  
2.        A.EmpName,  
3.        A.DeptID  ,  
4.        B.DeptID ,  
5.        B.DeptName  
6. FROM   @Employee A  
7.        RIGHT JOIN @Department B  
8.        ON     A.DeptID=B.DeptID  
9.          

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID  ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       RIGHT JOIN @Department B
       ON     A.DeptID=B.DeptID
       

Right Join Excluding Inner Join

view plaincopy to clipboardprint?

1. SELECT A.EmpID  ,  
2.        A.EmpName,  
3.        A.DeptID  ,  
4.        B.DeptID ,  
5.        B.DeptName  
6. FROM   @Employee A  
7.        RIGHT JOIN @Department B  
8.        ON     A.DeptID=B.DeptID  
9. WHERE A.DeptID IS NULL  

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID  ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       RIGHT JOIN @Department B
       ON     A.DeptID=B.DeptID
WHERE A.DeptID IS NULL

Full Outer Join
A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

view plaincopy to clipboardprint?

1. SELECT A.EmpID  ,  
2.        A.EmpName,  
3.        A.DeptID  ,  
4.        B.DeptID ,  
5.        B.DeptName  
6. FROM   @Employee A  
7.        FULL OUTER JOIN @Department B  
8.        ON     A.DeptID=B.DeptID  

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID  ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       FULL OUTER JOIN @Department B
       ON     A.DeptID=B.DeptID

Outer Join Excluding Inner Join Or Left & Right Joins Excluding Inner Join

view plaincopy to clipboardprint?

1. SELECT A.EmpID  ,  
2.        A.EmpName,  
3.        A.DeptID ,  
4.        B.DeptID ,  
5.        B.DeptName  
6. FROM   @Employee A  
7.        FULL OUTER JOIN @Department B  
8.        ON     A.DeptID=B.DeptID  
9. WHERE  A.DeptID IS NULL  
10. OR     B.DeptID IS NULL  

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       FULL OUTER JOIN @Department B
       ON     A.DeptID=B.DeptID
WHERE  A.DeptID IS NULL
OR     B.DeptID IS NULL

Cartesian Product Or Cross Join
Known as Join without condition
ResultSet = No of Rows in TableA * No of Rows in TableB
Display all the Possibilities of combinations.

view plaincopy to clipboardprint?

1. SELECT *  
2. FROM   @Employee  
3. INNER JOIN @Department  
4. ON 1 = 1  
5.   
6. --Explicit  
7. SELECT *  
8. FROM   @Employee  
9. CROSS JOIN @Department  
10.   
11. --Implicit  
12. SELECT *  
13. FROM   @Employee, @Department  

SELECT *
FROM   @Employee
INNER JOIN @Department
ON 1 = 1

--Explicit
SELECT *
FROM   @Employee
CROSS JOIN @Department

--Implicit
SELECT *
FROM   @Employee, @Department

Self-Join
A table joins with itself with one or two aliases to stave off confusion are called self-join.

view plaincopy to clipboardprint?

1. SELECT A.EmpName ,  
2.        B.EmpName ,  
3.        A.DeptID  ,  
4.        B.DeptID  
5. FROM   @Employee A  
6.        INNER JOIN @Employee B  
7.        ON     A.DeptID = B.DeptID  

SELECT A.EmpName ,
       B.EmpName ,
       A.DeptID  ,
       B.DeptID
FROM   @Employee A
       INNER JOIN @Employee B
       ON     A.DeptID = B.DeptID

References:
codeproject
wikipedia
c-sharpcorner.com
techbubbles
marcoullis
Logical Query Processing

asp menu

Table structure for creating the Menu

view plaincopy to clipboardprint?

1. SET ANSI_NULLS ON  
2. GO  
3. SET QUOTED_IDENTIFIER ON  
4. GO  
5. SET ANSI_PADDING ON  
6. GO  
7. CREATE TABLE [dbo].[MainMenu](  
8.  [MenuID] [int] NOT NULL,  
9.  [ParentMenuID] [int] NULL,  
10.  [LabelMenu] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,  
11.  [LinkMenu] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,  
12.  [TargetMenu] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,  
13.  [HighlightMenu] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,  
14.  CONSTRAINT [PK_MainMenu] PRIMARY KEY CLUSTERED   
15. (  
16.  [MenuID] ASC  
17. ) ON [PRIMARY]  
18. ) ON [PRIMARY]  
19.   
20. GO  
21. SET ANSI_PADDING OFF  
22. GO  
23. EXEC dbo.sp_addextendedproperty @name=N'MS_Description', @value=N'Identity Column' ,@level0type=N'USER', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MainMenu', @level2type=N'COLUMN', @level2name=N'MenuID'  
24.   
25. GO  
26. EXEC dbo.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of the menu.' ,@level0type=N'USER', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MainMenu', @level2type=N'COLUMN', @level2name=N'LabelMenu'  
27.   
28. GO  
29. EXEC dbo.sp_addextendedproperty @name=N'MS_Description', @value=N'Url for the label' ,@level0type=N'USER', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MainMenu', @level2type=N'COLUMN', @level2name=N'LinkMenu'  
30.   
31. GO  
32. EXEC dbo.sp_addextendedproperty @name=N'MS_Description', @value=N'Main menu' ,@level0type=N'USER', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MainMenu', @level2type=N'COLUMN', @level2name=N'HighlightMenu'  

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MainMenu](
 [MenuID] [int] NOT NULL,
 [ParentMenuID] [int] NULL,
 [LabelMenu] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [LinkMenu] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [TargetMenu] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [HighlightMenu] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_MainMenu] PRIMARY KEY CLUSTERED 
(
 [MenuID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC dbo.sp_addextendedproperty @name=N'MS_Description', @value=N'Identity Column' ,@level0type=N'USER', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MainMenu', @level2type=N'COLUMN', @level2name=N'MenuID'

GO
EXEC dbo.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of the menu.' ,@level0type=N'USER', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MainMenu', @level2type=N'COLUMN', @level2name=N'LabelMenu'

GO
EXEC dbo.sp_addextendedproperty @name=N'MS_Description', @value=N'Url for the label' ,@level0type=N'USER', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MainMenu', @level2type=N'COLUMN', @level2name=N'LinkMenu'

GO
EXEC dbo.sp_addextendedproperty @name=N'MS_Description', @value=N'Main menu' ,@level0type=N'USER', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MainMenu', @level2type=N'COLUMN', @level2name=N'HighlightMenu'

Query to insert records

view plaincopy to clipboardprint?

1. INSERT INTO dbo.MainMenu  
2. SELECT 1,NULL,'Home,'#,'_self','1' UNION ALL  
3. SELECT 2,NULL,'Accessories,'#,'_self','2' UNION ALL  
4. SELECT 3,NULL,'Products,'#,'_self','3' UNION ALL  
5. SELECT 4,NULL,'Contact,'#,'_self','4' UNION ALL  
6. SELECT 5,2,'Accessory1,'#,'_self',NULL UNION ALL  
7. SELECT 6,3,'Product1,'#,'_self',NULL UNION ALL  
8. SELECT 7,3,'Product2,'#,'_self',NULL UNION ALL  
9. SELECT 8,5,'Accessory2,'#,'_self',NULL UNION ALL  
10. SELECT 9,5,'Accessory3,'#,'_self',NULL UNION ALL  
11. SELECT 10,8,'Acc,'#,'_self',NULL  

INSERT INTO dbo.MainMenu
SELECT 1,NULL,'Home,'#,'_self','1' UNION ALL
SELECT 2,NULL,'Accessories,'#,'_self','2' UNION ALL
SELECT 3,NULL,'Products,'#,'_self','3' UNION ALL
SELECT 4,NULL,'Contact,'#,'_self','4' UNION ALL
SELECT 5,2,'Accessory1,'#,'_self',NULL UNION ALL
SELECT 6,3,'Product1,'#,'_self',NULL UNION ALL
SELECT 7,3,'Product2,'#,'_self',NULL UNION ALL
SELECT 8,5,'Accessory2,'#,'_self',NULL UNION ALL
SELECT 9,5,'Accessory3,'#,'_self',NULL UNION ALL
SELECT 10,8,'Acc,'#,'_self',NULL

Query to generate Insert statements

view plaincopy to clipboardprint?

1. SELECT 'SELECT ' + CAST(MenuID AS VARCHAR) +',' + COALESCE(CAST(ParentMenuID AS VARCHAR), 'NULL')+ ',''' + LabelMenu + ','''+ LinkMenu + ','''+ TargetMenu + ''','+ (CASE WHEN HighlightMenu IS NULL OR HighlightMenu = '' THEN 'NULL' ELSE +''''+ HighlightMenu +'''' END) + ' UNION ALL' FROM dbo.MainMenu  

SELECT 'SELECT ' + CAST(MenuID AS VARCHAR) +',' + COALESCE(CAST(ParentMenuID AS VARCHAR), 'NULL')+ ',''' + LabelMenu + ','''+ LinkMenu + ','''+ TargetMenu + ''','+ (CASE WHEN HighlightMenu IS NULL OR HighlightMenu = '' THEN 'NULL' ELSE +''''+ HighlightMenu +'''' END) + ' UNION ALL' FROM dbo.MainMenu

C# Code

view plaincopy to clipboardprint?

1. protected void Page_Load(object sender, EventArgs e)  
2.     {  
3.         if (!IsPostBack)  
4.         {  
5.             PopulateMenu();  
6.         }  
7.     }  
8.   
9.     private void PopulateMenu()  
10.     {  
11.         //Get the menu structure from the table  
12.         DataSet dsMenu = GetDataSetForMenu();  
13.   
14.         foreach (DataRow drDataRow in dsMenu.Tables["Menu"].Rows)  
15.         {  
16.             //If the ParentMenuID is NullOrEmpty then it is the top most menu  
17.             if (string.IsNullOrEmpty(Convert.ToString(drDataRow["ParentMenuID"])))  
18.             {  
19.                 MenuItem mainMenuItem = new MenuItem((string)drDataRow["LabelMenu"], Convert.ToString(drDataRow["MenuID"]));  
20.                 Menu1.Items.Add(mainMenuItem);  
21.                 //CreateSubMenu(dsMenu, drDataRow, mainMenuItem);  
22.             }  
23.             else //Otherwise it is the submenu  
24.             {  
25.                 CreateSubMenu(dsMenu, drDataRow, new MenuItem());  
26.             }  
27.         }  
28.     }  
29.   
30.     private void CreateSubMenu(DataSet dsSubMenu, DataRow drDataRow, MenuItem mainMenuItem)  
31.     {  
32.         //MenuID is the subMenu of the ParentMenuID  
33.         //string MenuID = Convert.ToString(drDataRow["MenuID"]);  
34.         string ParentMenuID = Convert.ToString(drDataRow["ParentMenuID"]);  
35.   
36.         //Which is always true  
37.         if (mainMenuItem.Text == string.Empty)  
38.         {  
39.             //If it is the firstChild of the parent find it here itself  
40.             mainMenuItem = Menu1.FindItem(Convert.ToString(drDataRow["ParentMenuID"]));  
41.             //If we can't able to find it in the main menu we have to look for sub-menu  
42.             if (mainMenuItem == null)  
43.                 foreach (MenuItem mi in Menu1.Items)  
44.                 {  
45.                     //Find the ParentMenu by ParentMenuID  
46.                     mainMenuItem = GetParentMenuItem(mi, Convert.ToString(drDataRow["ParentMenuID"]));  
47.                     if (mainMenuItem == null)  
48.                         continue;  
49.                     if (!mainMenuItem.Text.Trim().Equals(string.Empty))  
50.                         break;  
51.                 }  
52.         }  
53.   
54.         if (mainMenuItem == null)  
55.             return;  
56.         //This the child element  
57.         if (!string.IsNullOrEmpty(ParentMenuID))  
58.         {  
59.             MenuItem childrenItem = new MenuItem((string)drDataRow["LabelMenu"], Convert.ToString(drDataRow["MenuID"]));  
60.             childrenItem.Target = (string)drDataRow["LinkMenu"];  
61.             mainMenuItem.ChildItems.Add(childrenItem);  
62.         }  
63.     }  
64.   
65.     // Recursive function     
66.     private MenuItem GetParentMenuItem(MenuItem root, string value)  
67.     {  
68.         MenuItem menuItem;  
69.         //Is MenuItem has ChildItems  
70.         if (root.ChildItems.Count > 0)  
71.         {  
72.             //Check which ChildItems is the Parent  
73.             for (int i = 0; i < root.ChildItems.Count; i++)  
74.             {  
75.                 menuItem = GetParentMenuItem(root.ChildItems[i], value);  
76.                 if (menuItem != null)  
77.                     return menuItem;  
78.             }  
79.         }  
80.         if (root.Value == value)  
81.         {  
82.             return root;  
83.         }  
84.         else  
85.             return null;  
86.     }  
87.   
88.     private DataSet GetDataSetForMenu()  
89.     {  
90.         String constr = "Server=10.16.105.171;Database=test;Uid=test;Pwd=test123;Connect Timeout=0; pooling='true'; Max Pool Size=200";  
91.         String query = @"SELECT MenuID, ParentMenuID, LabelMenu, LinkMenu FROM dbo.MainMenu ORDER BY MenuID ASC";  
92.         DataSet dsMenu = new DataSet();  
93.   
94.         using (SqlConnection conn = new SqlConnection(constr))  
95.         {  
96.             SqlDataAdapter da = new SqlDataAdapter(query, conn);  
97.             da.Fill(dsMenu);  
98.             da.Dispose();  
99.         }  
100.   
101.         dsMenu.DataSetName = "Menus";  
102.         dsMenu.Tables[0].TableName = "Menu";  
103.         DataRelation relation = new DataRelation("ParentChild",  
104.                                 dsMenu.Tables["Menu"].Columns["MenuID"],  
105.                                 dsMenu.Tables["Menu"].Columns["ParentMenuID"], true);  
106.   
107.         relation.Nested = true;  
108.         dsMenu.Relations.Add(relation);  
109.   
110.         //This constraint cannot be enabled as not all values have corresponding parent values.  
111.   
112.         return dsMenu;  
113.     }  

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            PopulateMenu();
        }
    }

    private void PopulateMenu()
    {
        //Get the menu structure from the table
        DataSet dsMenu = GetDataSetForMenu();

        foreach (DataRow drDataRow in dsMenu.Tables["Menu"].Rows)
        {
            //If the ParentMenuID is NullOrEmpty then it is the top most menu
            if (string.IsNullOrEmpty(Convert.ToString(drDataRow["ParentMenuID"])))
            {
                MenuItem mainMenuItem = new MenuItem((string)drDataRow["LabelMenu"], Convert.ToString(drDataRow["MenuID"]));
                Menu1.Items.Add(mainMenuItem);
                //CreateSubMenu(dsMenu, drDataRow, mainMenuItem);
            }
            else //Otherwise it is the submenu
            {
                CreateSubMenu(dsMenu, drDataRow, new MenuItem());
            }
        }
    }

    private void CreateSubMenu(DataSet dsSubMenu, DataRow drDataRow, MenuItem mainMenuItem)
    {
        //MenuID is the subMenu of the ParentMenuID
        //string MenuID = Convert.ToString(drDataRow["MenuID"]);
        string ParentMenuID = Convert.ToString(drDataRow["ParentMenuID"]);

        //Which is always true
        if (mainMenuItem.Text == string.Empty)
        {
            //If it is the firstChild of the parent find it here itself
            mainMenuItem = Menu1.FindItem(Convert.ToString(drDataRow["ParentMenuID"]));
            //If we can't able to find it in the main menu we have to look for sub-menu
            if (mainMenuItem == null)
                foreach (MenuItem mi in Menu1.Items)
                {
                    //Find the ParentMenu by ParentMenuID
                    mainMenuItem = GetParentMenuItem(mi, Convert.ToString(drDataRow["ParentMenuID"]));
                    if (mainMenuItem == null)
                        continue;
                    if (!mainMenuItem.Text.Trim().Equals(string.Empty))
                        break;
                }
        }

        if (mainMenuItem == null)
            return;
        //This the child element
        if (!string.IsNullOrEmpty(ParentMenuID))
        {
            MenuItem childrenItem = new MenuItem((string)drDataRow["LabelMenu"], Convert.ToString(drDataRow["MenuID"]));
            childrenItem.Target = (string)drDataRow["LinkMenu"];
            mainMenuItem.ChildItems.Add(childrenItem);
        }
    }

    // Recursive function   
    private MenuItem GetParentMenuItem(MenuItem root, string value)
    {
        MenuItem menuItem;
        //Is MenuItem has ChildItems
        if (root.ChildItems.Count > 0)
        {
            //Check which ChildItems is the Parent
            for (int i = 0; i < root.ChildItems.Count; i++)
            {
                menuItem = GetParentMenuItem(root.ChildItems[i], value);
                if (menuItem != null)
                    return menuItem;
            }
        }
        if (root.Value == value)
        {
            return root;
        }
        else
            return null;
    }

    private DataSet GetDataSetForMenu()
    {
        String constr = "Server=10.16.105.171;Database=test;Uid=test;Pwd=test123;Connect Timeout=0; pooling='true'; Max Pool Size=200";
        String query = @"SELECT MenuID, ParentMenuID, LabelMenu, LinkMenu FROM dbo.MainMenu ORDER BY MenuID ASC";
        DataSet dsMenu = new DataSet();

        using (SqlConnection conn = new SqlConnection(constr))
        {
            SqlDataAdapter da = new SqlDataAdapter(query, conn);
            da.Fill(dsMenu);
            da.Dispose();
        }

        dsMenu.DataSetName = "Menus";
        dsMenu.Tables[0].TableName = "Menu";
        DataRelation relation = new DataRelation("ParentChild",
                                dsMenu.Tables["Menu"].Columns["MenuID"],
                                dsMenu.Tables["Menu"].Columns["ParentMenuID"], true);

        relation.Nested = true;
        dsMenu.Relations.Add(relation);

        //This constraint cannot be enabled as not all values have corresponding parent values.

        return dsMenu;
    }

Reference Exception: This constraint cannot be enabled as not all values have corresponding parent values. We will get this exception if we set 0 for ParentMenuID. Where the LabelMenu is parent. Since there is no MenuID with value 0

ArrayList to List Conversion

ArrayList To List

view plaincopy to clipboardprint?

1. List<int> newList = new List<int>(arrayList.ToArray(typeof(int)));  
2. List<string> newList = new List<string>(arrayList.ToArray(typeof(string)));  
3. </string></string></int></int>  

List newList = new List(arrayList.ToArray(typeof(int)));
List newList = new List(arrayList.ToArray(typeof(string)));

Undocumented SQL Server Commands

Some Useful Undocumented SQL Server 7.0 and 2000 DBCC Commands
SQL Server 2000 useful undocumented stored procedures
SQL Server Extended Stored Procedures

Constraints In SQL Server

Constraints: Rules defined on the table, which cannot be violated by the users. It provides a powerful yet easy way to enforce the data integrity in your database.

A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database.
Data integrity comes in the following forms:

1. Entity Integrity
2. Domain Integrity
3. Referential integrity
4. User-Defined Integrity

Entity Integrity ensures that there are no duplicate rows in a table.(i.e. Ensures that a table has a primary key).
Entity Integrity can be defined by defining

1. Primary Key Constraint
2. Unique Key Constraint
3. or by building an Unique Indexes

Domain Integrity ensures that data values meet certain criteria; i.e. by choosing an appropriate data type.

1. Other approaches include defining CHECK constraints or FOREIGN KEY constraints, or writing a trigger.
2. Default Constraint, Null Constraint, NOT null Constraint also an aspect of domain integrity.

Referential integrity Enforces relationships between two tables, a referenced table, and a referencing table. It ensures that rows cannot be deleted or updated in the referenced, when used in referencing table.

1. Foreign Key

User-Defined Integrity enforces some specific business rules that do not fall into entity, domain, or referential integrity categories. Each of these categories of the data integrity can be enforced by the appropriate constraints.

Microsoft SQL Server supports the following constraints:

1. PRIMARY KEY
   
   • Composite Key or Compound key or Concatenated Key or Aggregate Key
   • Single-Column Key
   • Natural Key
   • Surrogate Key
   • Super Key
   • Candidate Key
   • Alternate Key or Secondary Key
2. UNIQUE
3. FOREIGN KEY
4. CHECK
5. NOT NULL

A PRIMARY KEY is a column or a combination of columns that uniquely identify a record. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity. NULL values are not allowed. A table can have only one primary key. A primary key adds a clustered index to the table The key can created for single column or more columns of a table. When two or more columns are used as a primary key, they are called a composite key. Not Null Constraint is automatically enforced. SQLPrimaryKeys

A Single-Column Key defined with an ever-increasing identity column.

Surrogate Key
It’s also Primary Key. If there no suitable natural key exists then we have to create surrogate key. It is a single column primary key that created automatically (example: Identity key column in SQL Server) or system generated values (like generated via a table in the schema).

Candidate Key
A column or a set of columns can be called as candidate key if they identify each row of a table uniquely. A table can have multiple candidate keys. One of them is specified as Primary key and rest of them can be called as alternate key.

Composite Key
A key formed by combining at least two or more columns is called Composite Key. Each single column's data can be duplicated but the combination values of these columns cannot be duplicated.
Rules

1. At all times it holds in the relation assigned to that variable that there are no
   two distinct tuples (rows) with the same values for these attributes and
2. There is not a proper subset of this set of attributes for which (1) holds.

Natural Key
It’s also Primary Key. If it is reference by foreign key or by defining an unique index on it.

Super Key
A Super Key is a combination of attributes that can be uniquely used to identify a database record. A table might have many Super Keys. A primary key is therefore a minimum Super Key.

Alternate Key
A candidate key that is not the primary key is called an alternate key.

A UNIQUE constraint enforces the uniqueness of the values in a single column or a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints. Not Null Constraint is not automatically enforced. So it allows only one null value.

A FOREIGN KEY constraint prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints. We can create constraints when the table is created, as part of the table definition by using the CREATE TABLE statement.

Default constraint is used to fill column with default value defined during creation of table if nothing is supplied while inserting data.

Super Key - Any combination of keys that make it unique.
Candidate Key - Minimum SK is called candidate key.
Primary Key - Any CK, which does not take null value.
Foreign Key - Reference to PK of another table with no nulls
Unique Key - Same feature is PK but can take null values. Also, in a table there can be many UK.

Any attribute that is uniquely identifying a row in a table is candidate key for the table. We select one of the candidate keys as Primary key.
All candidate keys, which are not chosen as primary key, are Alternate keys.

The key which uniquely identify the rows of the table and which is made up of more than one attribute is called Composite key. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key one of them will become the primary key and the rest are called alternate keys.

Reference
Natural vs. Surrogate Keys in SQL Server
Different Types of SQL Keys
Surrogate Keys, Natural Keys, Candidate Keys, Composite Keys and Super Keys?