Problem
I work on SQL server 2012 I have table have 300 hundred million rows
when select from table any records it is too much heavy
I need any way can enhance performance of table at least if i select small amount of data as 1000 rows
can be access quickly and not take too much time
so if there are any idea i can accept to access this table quickly
because when select data it is hanging and take too much time to show data .
so how to do that please ?
What I have tried:
CREATE TABLE [Parts].[Nop_Part](
[PartID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[PartNumber] [nvarchar](70) NOT NULL,
[PartNumberNon] [nvarchar](60) NOT NULL,
[CompanyID] [int] NOT NULL,
[Active] [bit] NOT NULL,
[Deleted] [bit] NOT NULL,
[PartsFamilyID] [int] NOT NULL,
[PackageID] [int] NULL,
[PinOutId] [int] NOT NULL,
[GroupID] [bigint] NULL,
[Equation] [varchar](500) NULL,
[Masked_ID] [int] NULL,
[CreatedDate] [datetime] NULL,
[CreatedBy] [int] NULL,
[ModifiedDate] [datetime] NULL,
[Modifiedby] [int] NULL,
[DeletedDate] [datetime] NULL,
[DeletedBy] [int] NULL,
[LatestCompanyID] [bigint] NOT NULL,
[DateOfLatestCompanyID] [datetime] NULL,
CONSTRAINT [PK_Nop_Part] PRIMARY KEY CLUSTERED
(
[PartID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UC_Partnon_LatestCompanyID] UNIQUE NONCLUSTERED
(
[PartNumberNon] ASC,
[LatestCompanyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [uc_partnumbernonCompany] UNIQUE NONCLUSTERED
(
[PartNumberNon] ASC,
[CompanyID] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [Parts].[Nop_Part] ADD CONSTRAINT [DF_Nop_Part_PinOutId] DEFAULT ((0)) FOR [PinOutId]
GO
ALTER TABLE [Parts].[Nop_Part] ADD DEFAULT (getdate()) FOR [DateOfLatestCompanyID]
GO
ALTER TABLE [Parts].[Nop_Part] WITH NOCHECK ADD FOREIGN KEY([Masked_ID])
REFERENCES [dbo].[Core_Masked] ([ID])
GO