SQL Server Performance

load data and foreign key

Discussion in 'ALL SQL SERVER QUESTIONS' started by Wendy elizabeth, Mar 29, 2012.

  1. Wendy elizabeth New Member

    Since I am new to setting up a database on sql server 2008 r2 and creating tables that are referenced by foreign keys, I would like to ask the following about the 4 tables that I created below:
    1. The way I setup the foreign keys is this ok why or why not? Can you explain and/or point me a reference that will explain the better solution for me?
    2. In 3 tables the data will not change much after the data is initially loaded. However in the table called Rej_History, I will be loading data to that table daily. Thus for all the tables, can you tell me if I need to drop indexes every time I update the data. Basically can you tell me and/or point me to a reference that will tell me how to setup a script to load the data?

    CREATE TABLE [dbo].[Rej_Contacts](
    [MRC_Contact_ID] [int] IDENTITY(1,1) NOT NULL,
    [MRC_Prefix] [varchar](30) NULL,
    [MRC_Last_Name] [varchar](50) NULL,
    [MRC_First_Name] [varchar](50) NULL,
    [MRC_Phone_Number] [varchar](25) NULL,
    [MRC_Email] [varchar](150) NULL,
    [MRC_Address] [varchar](100) NULL,
    [MRC_City] [varchar](50) NULL,
    [MRC_State] [varchar](2) NULL,
    [MRC_Zip] [varchar](10) NULL,
    [MRC_Update_Date] [datetime] NULL,
    [MRC_Updated_By] [varchar](50) NULL
    CONSTRAINT [PK_Rej_Contacts] PRIMARY KEY CLUSTERED
    (
    [MRC_Contact_ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    ------------
    CREATE TABLE [dbo].[Rej_History](
    [MRH_Id] [int] IDENTITY(1,1) NOT NULL,
    [MRH_Plan_Id] [int] NULL,
    [MRH_Create_Date] [datetime] NULL,
    [MRH_Code_ID] [int] NULL,
    [MRH_Tran_Count] [numeric](18, 0) NULL,
    [MRH_Batch_Size] [numeric](18, 0) NULL,
    [MRH_Tran_Code_Description] [varchar](max) NULL
    CONSTRAINT [PK_Rej_History] PRIMARY KEY CLUSTERED
    (
    [MRH_Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[Rej_History] WITH NOCHECK ADD CONSTRAINT [PK_MRH_RNumber] FOREIGN KEY([MRH_Plan_Id])
    REFERENCES [dbo].[Rej_Plans] ([MRP_Plan_Id])
    GO
    ALTER TABLE [dbo].[Rej_History] WITH NOCHECK ADD CONSTRAINT [PK_MRH_Tran_Codes] FOREIGN KEY([MRH_Code_ID])
    REFERENCES [dbo].[Tran_Codes] ([TRC_Code_Id])
    GO
    ----------------
    CREATE TABLE [dbo].[Rej_Plans](
    [MRP_Plan_Id] [int] IDENTITY(1,1) NOT NULL,
    [MRP_PLan_Number] [varchar](10) NULL,
    [MRP_Contact_Id] [int] NOT NULL,
    [MRP_Parent_Organization_Name] [varchar](100) NOT NULL,
    [MRP_Update_Date] [datetime] NULL,
    [MRP_Updated_By] [varchar](50) NULL
    CONSTRAINT [PK_MRP_RNumber] PRIMARY KEY CLUSTERED
    (
    [MRP_Plan_Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    ----------------
    CREATE TABLE [dbo].[Tran_Codes](
    [TRC_Code_Id] [int] IDENTITY(1,1) NOT NULL,
    [TRC_Tran_Code] [numeric](6, 0) NOT NULL,
    [TRC_Type] [char](1) NULL,
    [TRC_Title] [nvarchar](75) NULL,
    [TRC_Long_Definition] [varchar](max) NULL
    CONSTRAINT [PK_Tran_Codes] PRIMARY KEY CLUSTERED
    (
    [TRC_Code_Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    -----------------
  2. Orlando Colamatteo New Member

    In general I recommend using WITH CHECK, instead of using WITH NOCHECK as you have it. There are specific cases when WITH NOCHECK is an OK idea in some circumstances as an interim step, but eventually all FKs should be "checked" otherwise it can lead to suboptimal execution plans.

    Whether you drop and recreate indexes before and after you load data will depend on how important performance is for your process. I find that building indexes (any persistent DDL for that matter) in code to be tedious and a latent exposure for problems down the line. That said, some processes benefit greatly from this practice, but some see no measurable benefits at all. Test it both ways and see with which you are happier.

    Code re-posted with formatting:

    Code:
    CREATE TABLE [dbo].[Rej_Contacts]
    (
    [MRC_Contact_ID] [int] IDENTITY(1, 1)
                            NOT NULL,
    [MRC_Prefix] [varchar](30) NULL,
    [MRC_Last_Name] [varchar](50) NULL,
    [MRC_First_Name] [varchar](50) NULL,
    [MRC_Phone_Number] [varchar](25) NULL,
    [MRC_Email] [varchar](150) NULL,
    [MRC_Address] [varchar](100) NULL,
    [MRC_City] [varchar](50) NULL,
    [MRC_State] [varchar](2) NULL,
    [MRC_Zip] [varchar](10) NULL,
    [MRC_Update_Date] [datetime] NULL,
    [MRC_Updated_By] [varchar](50)
        NULL
        CONSTRAINT [PK_Rej_Contacts]
        PRIMARY KEY CLUSTERED ([MRC_Contact_ID] ASC)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    )
        ON
    [PRIMARY]
    
    ------------
    CREATE TABLE [dbo].[Rej_History]
    (
    [MRH_Id] [int] IDENTITY(1, 1)
                    NOT NULL,
    [MRH_Plan_Id] [int] NULL,
    [MRH_Create_Date] [datetime] NULL,
    [MRH_Code_ID] [int] NULL,
    [MRH_Tran_Count] [numeric](18, 0) NULL,
    [MRH_Batch_Size] [numeric](18, 0) NULL,
    [MRH_Tran_Code_Description] [varchar](MAX)
        NULL
        CONSTRAINT [PK_Rej_History]
        PRIMARY KEY CLUSTERED ([MRH_Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
        ON [PRIMARY]
    )
        ON
    [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[Rej_History] WITH NOCHECK ADD CONSTRAINT [PK_MRH_RNumber] FOREIGN KEY([MRH_Plan_Id])
    REFERENCES [dbo].[Rej_Plans] ([MRP_Plan_Id])
    GO
    
    ALTER TABLE [dbo].[Rej_History] WITH NOCHECK ADD CONSTRAINT [PK_MRH_Tran_Codes] FOREIGN KEY([MRH_Code_ID])
    REFERENCES [dbo].[Tran_Codes] ([TRC_Code_Id])
    GO
    
    ----------------
    
    CREATE TABLE [dbo].[Rej_Plans]
    (
    [MRP_Plan_Id] [int] IDENTITY(1, 1)
                        NOT NULL,
    [MRP_PLan_Number] [varchar](10) NULL,
    [MRP_Contact_Id] [int] NOT NULL,
    [MRP_Parent_Organization_Name] [varchar](100) NOT NULL,
    [MRP_Update_Date] [datetime] NULL,
    [MRP_Updated_By] [varchar](50)
        NULL
        CONSTRAINT [PK_MRP_RNumber]
        PRIMARY KEY CLUSTERED ([MRP_Plan_Id] ASC)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    )
        ON
    [PRIMARY]
    
    GO
    ----------------
    CREATE TABLE [dbo].[Tran_Codes]
    (
    [TRC_Code_Id] [int] IDENTITY(1, 1)
                        NOT NULL,
    [TRC_Tran_Code] [numeric](6, 0) NOT NULL,
    [TRC_Type] [char](1) NULL,
    [TRC_Title] [nvarchar](75) NULL,
    [TRC_Long_Definition] [varchar](MAX)
        NULL
        CONSTRAINT [PK_Tran_Codes]
        PRIMARY KEY CLUSTERED ([TRC_Code_Id] ASC)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    )
        ON
    [PRIMARY]

Share This Page