Always give a proper name to your Foreign Key in SQL Server else it will assign dynamic name

In some cases people don't give name to Foreign keys while creating tables through scripts. By default SQL Server give them a dynamic name like 'FK__TempTeamM__TeamI__1367E606'.

Suppose a case where dynamic FK is created on Team.TeamID and TeamMember.TeamID AND you have multiple customer databases. What if you got a requirement where you have to drop this FK through script.( Remember, in each database FK name will be different )

Here I am giving you an example which shows how dynamic FK is generated AND how to avoid it.

--Add a master table
CREATE TABLE dbo.TempTeam
(
    TeamID   INT           NOT NULL PRIMARY KEY,
    TeamName VARCHAR (200) NOT NULL
) ON [PRIMARY];

GO
--Add child table and making 'TeamID' as FK
--In this case, SQL server will give a dynamic name to this FK
CREATE TABLE [dbo].[TempTeamMember]
(
    [TeamID] INT NOT NULL FOREIGN KEY REFERENCES TempTeam (TeamID) ON DELETE NO ACTION ON UPDATE NO ACTION,
    [UserID] INT NOT NULL
) ON [PRIMARY];

--Let us check the dynamic name of FK. In my case it is 'FK__TempTeamM__TeamI__1367E606'
SELECT name
FROM   sys.foreign_keys
WHERE  parent_object_id = object_id('TempTeamMember');


GO
--Now lets drop the TeamMember table
DROP TABLE [TempTeamMember];


GO
--Add child table and making 'TeamID' as FK with proper name
CREATE TABLE [dbo].[TempTeamMember]
(
    [TeamID] INT NOT NULL,
    [UserID] INT NOT NULL,
    CONSTRAINT FK_TempTeamMember_TempTeam FOREIGN KEY (TeamID) REFERENCES TempTeam (TeamID) ON DELETE NO ACTION ON UPDATE NO ACTION
) ON [PRIMARY];

--FK name is 'FK_TeamMember_Team' which we have defined above
SELECT name
FROM   sys.foreign_keys
WHERE  parent_object_id = object_id('TempTeamMember');

--At the end drop tables
DROP TABLE TempTeamMember;

DROP TABLE TempTeam;  

Always give a proper name to your Foreign Key.

Changing the order of Joins in SQL Server can increase performance

Let use create a scenario where we can test the effects. We are going to create three tables:

CREATE TABLE TableA

(

    TableA_ID UNIQUEIDENTIFIER,

    NOTE      VARCHAR (500)   

);

CREATE TABLE TableB

(

    TableA_ID UNIQUEIDENTIFIER,

    NOTE      VARCHAR (500)   

);

CREATE TABLE TableC

(

    TableA_ID UNIQUEIDENTIFIER,

    NOTE      VARCHAR (500)   

);  

There is a logical relationship between these tables, means without any primary key and also there is no index on any column.

After creating tables let us check the execution plan between following queries:

--Query 1

SELECT *

FROM   TableA AS tblA

       INNER JOIN

       TableB AS tblB

       ON tblA.TableA_ID = tblB.TableA_ID

       INNER JOIN

       TableC AS tblC

       ON tblA.TableA_ID = tblC.TableA_ID;

GO

--Query 2 

SELECT *

FROM   TableA AS tblA

       INNER JOIN

       TableC AS tblC

       ON tblA.TableA_ID = tblC.TableA_ID

       INNER JOIN

       TableB AS tblB

       ON tblA.TableA_ID = tblB.TableA_ID;  

(To check execution plan, select above complete queries and click on 'Query>Include Actual Execution Plan'. Now execute Query.)

Both execution plans are same. Now, inserting 500 rows in TableA, 500 rows in TableB and 1000 rows in TableC

INSERT  INTO TableA (TableA_ID, NOTE)

VALUES             (NEWID(), 'Some Text');

GO 500

INSERT INTO TableB (TableA_ID, NOTE)

SELECT TableA_ID,

       NOTE + '.1'

FROM   TableA;

INSERT INTO TableC (TableA_ID, NOTE)

SELECT TableA_ID,

       NOTE + '.1'

FROM   TableA

UNION ALL

SELECT TableA_ID,

       NOTE + '.2'

FROM   TableA;  

Now let us again check the execution plan of:

--Query 1

SELECT *

FROM   TableA AS tblA

       INNER JOIN

       TableB AS tblB

       ON tblA.TableA_ID = tblB.TableA_ID

       INNER JOIN

       TableC AS tblC

       ON tblA.TableA_ID = tblC.TableA_ID;

GO

--Query 2 

SELECT *

FROM   TableA AS tblA

       INNER JOIN

       TableC AS tblC

       ON tblA.TableA_ID = tblC.TableA_ID

       INNER JOIN

       TableB AS tblB

       ON tblA.TableA_ID = tblB.TableA_ID;  

According to execution plan 'Query 1' is faster than 'Query 2'.

Now question is why two different plans for these queries? The Query Optimizer generate possible execution plans and try to select best one. Join ordering is directly related to the size of the search space as the number of possible plans for a query grows very rapidly depending on the number of tables joined. The optimizer needs to make two important decisions regarding joins:

1. The selection of a join order
2. The choice of a join algorithm

The order in which the tables are joined determines the cost and performance of a query. Join ordering is one of the most complex problems in query optimization. In our case SQL Server pick two different execution plans where query cost are different.

We can extend this research by including relationship between these tables and read the execution plan to identify the behavior.

At the end, do not forget to drop the tables.

DROP TABLE TableA;

DROP TABLE TableB;

DROP TABLE TableC;