Dear All,
I have table which contain more than 5 lakhs of records.
When i exectue select * from table it is taking 45 to 60 sec to excetue.

the data Structure is like this
CREATE TABLE [Table1] (
    [$AuditTrailId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__$AgIndCAI__$Audi__19E63423] DEFAULT (newid()),
    [$UserId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [$Operation] [bit] NOT NULL ,
    [$SessionId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [$IPId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [$DateTime] [datetime] NOT NULL CONSTRAINT [DF__$AgIndCAI__$Date__1ADA585C] DEFAULT (getdate()),
    [$DataProfileClassId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [FormId] [bigint] NOT NULL ,
    [FormCode] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [MAWBFormId] [bigint] NULL ,
    [SAWBFormId] [bigint] NULL ,
    [JobNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MasterConsignmentId] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ConsignmentId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [HAWBNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BusinessBranch] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [DestinationBranch] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [AgilityClearance] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CreditClient] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BusinessType] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LetterOfCredit] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ShipperName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ConsigneeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ConsigneeEmail] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NoOfPieces] [int] NULL ,
    [GrossWeight] [decimal](27, 2) NULL ,
    [ChargeableWeight] [decimal](27, 2) NULL ,
    [Remarks] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Department] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ServiceType] [bigint] NULL ,
    [OriginCountry] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OriginPlace] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DeparturePort] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DestinationCountry] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [DestinationPort] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DeliveryPlace] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Transhipment] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [HAWBTerms] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MAWBTerms] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DocReceivedDate] [datetime] NULL ,
    [ShipperInvoiceNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ConsigneePONo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FileName] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DateCreated] [datetime] NOT NULL ,
    [CreatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [DateModified] [datetime] NULL ,
    [ModifiedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [StateId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OwnerOrgId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [OwnerLocId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SenderId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [RecipientId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [RequestFor] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [IsOpened] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SubmissionFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SubmissionComments] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Self] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CompanyCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EmpNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ClearanceAgentId] [bigint] NULL ,
    [ClearanceAgentName] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ClearanceAgentPerson] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ClearanceAgentContactNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BRORequired] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BROUploaded] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PODSignedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PODDateTime] [datetime] NULL ,
    [PODPlace] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PODRemarks] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CCMailId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CHAMailId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ReceivedFrom] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PaymentMode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PaymentDate] [datetime] NULL ,
    [Account] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Amount] [decimal](18, 0) NULL ,
    [BankName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ddchequeno] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [receiptno] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Cost] [numeric](9, 0) NULL ,
    [CostValue] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [hawbValue] [float] NULL ,
    [FormType] [int] NULL ,
    [TotalReceived] [int] NULL ,
    [PiecesRemaining] [int] NULL ,
    [PackingType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LedgerConsigneeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LedgerConsigneeAddress] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [GoodsDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LedgerAccount] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BROSupportDoc] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ClientName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CompleteAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TelephoneFaxNos] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CreditResponsibilityBranch] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Salespersoncode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Contactperson] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EMailId] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DispatchDeliver] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Pickupdate] [datetime] NULL ,
    [ICAReceived] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ReceivedGrossWeight] [decimal](18, 0) NULL ,
    [ReceivedChargeableWeight] [decimal](18, 0) NULL ,
    [CANConsigneeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CANAddress] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CANdespatchDate] [datetime] NULL ,
    [CourrierAWBNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ConsigneeCHAName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ACANFormId] [bigint] NULL ,
    [CLFormId] [bigint] NULL ,
    [Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CommodityItemNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [RateCharge] [decimal](18, 0) NULL ,
    [ParentFormId] [bigint] NULL ,
    [CarrierRefNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DeliveryMode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SLMailId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Priority] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NeedChangesReason] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ccpp] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [HoldReason] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [HoldRemarks] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [IsHold] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [HoldStatus] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK__$AgIndCAIHouseAi__18F20FEA] PRIMARY KEY  CLUSTERED
    (
        [$AuditTrailId]
    )  ON [PRIMARY]
) ON [PRIMARY]
GO


Easy there any possibilty to fecth the records in less time
select * from Table1 where formid=12
 

Based on your query stated last (select * from Table1 where formid = 12), you can build an index on the FormID field and that should help.

Thats a pretty big table (in terms of number of columns you have). Do you really need ALL the columns? Retrieve only those you need and your query will be faster (In addition to the index as suggested by martins)

Oops what a big table.... :-)  Ok,  regarding your table.

1. You are using formid which is not indexed.

2. Get the exact column you want.. specify like this.. Select collist from tablename where condition

Regards,

Venkatesan Prabu .J

As mentioned here, your table is huge! If you have the opportunity to redesign things, try to split some of the columns out into side tables, linked by the ID value - nullable columns are good choices for this. Then you can just select what you need, joining to these side tables only if needed.
 

Do you need all rows?

Try this

select * from Table1 where AuditTrailId>'' and formid=12