none
Index Scan Or Cluster Index Scan in Query Execution Plan

    Question

  • Hello,

    When I observe my Query execution plan, I get Cluster Index Scan or Index Scan on tables used in the query.

    Till what I know and have regarding them, they are not good for your query performance when we have large data in tables.

    I can some how remove Cluster Index Scan some how using a simple trick

    DECLARE @I int
    
    SET @I = 0
    
    SELECT * FROM Table_Name WHERE Table_Name_PK > @I
    
    OPTION (OPTIMIZE FOR (@I=0))

    But some times this trick does not work, majorly when only Index Scan is present.

    Can you guys please help me understand this thing and give me some solution which can be applied to remove the Scans.

    Your Help will be appreciated.

    Wednesday, March 14, 2012 6:44 AM

Answers

All replies

  • DECLARE @I int SET @I = 0 SELECT * FROM Table_Name WHERE Table_Name_PK > @I AND Table_Name_PK < 10000000

    Same index scan?



    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, March 14, 2012 6:55 AM
  • Hello Uri,

    Thanks for the reply, I tried using it but I am not getting the required output.

    Wednesday, March 14, 2012 7:11 AM
  • You need to provide DDL and sample data for your table.

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    Wednesday, March 14, 2012 7:23 AM
  • By using this query

    DECLARE @I int
    SET @I = 0
    SELECT * FROM Table_Name WHERE Table_Name_PK > @I

    You are requesting (if available) multiple records from a table (a range).
    When requesting a range, the query optimizer could decide to perform an index scan. This is not bad behaviour. Of course an index seek would be even better, but since you are requesting all columns (select *) in a range (> @I) an index scan is not a bad score.

    Whether the optimizer picks out the clustered index scan or the (nonclustered) index scan, depends on the selection: Which columns  do you select and what range do you request?
    If you are trying to optimize your query, consider selecting only the columns you really need (SELECT Name, Adress, etc instead of SELECT *).


    M. Streutker, Info Support | Blog

    Wednesday, March 14, 2012 7:30 AM
  • Well , post sample data as Jeff suggested and we will able provide you with more accurate solution

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, March 14, 2012 7:30 AM
  • Hello M. Streutker

    I just posted a sample query. But in real case I am selected specific columns which will are needful.

    Wednesday, March 14, 2012 7:37 AM
  • Hello Uri,

    The query have joins of multiple tables so it is difficult for me to post sample data.

    When I tried your solution in a query selecting data from a single table it is not giving any of the scans in it.

    Can you suggest something about this.

    Wednesday, March 14, 2012 7:51 AM
  • Hello Uri,

    The query have joins of multiple tables so it is difficult for me to post sample data.

    When I tried your solution in a query selecting data from a single table it is not giving any of the scans in it.

    Can you suggest something about this.

    So the query you posted above is not the actual query causing problems!

    If you want us to assist you then post the REAL query causing the problem and post DDL and sample data for your tables.  You're wasting everyones time by not posting this information as an answer given for your FAKE query will not work on your REAL query.


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    Wednesday, March 14, 2012 7:54 AM
  • http://msdn.microsoft.com/en-us/library/ms176005.aspx

    Thanks and regards, Rishabh , Microsoft Community Contributor

    Wednesday, March 14, 2012 8:30 AM
  • Hello Mr. Wharty

    Below are the DDL and Query which is causing the issue.

    WITH ParentShareHolderCertificatesSeller
    		  AS
    		  (
    			   SELECT CertificateID,ClientID,FolioID,CertificateNo,NoofShares,ParentCertificateID 
    				 FROM TM_ShareHolderCertificates WITH(NOLOCK)
    				WHERE IsDeleted = 0 AND CertificateID > @I 
    		  )


    SELECT	CAST(REPLICATE(0,10-LEN(f.FolioNo)) AS VARCHAR) + CAST(f.FolioNo AS VARCHAR) AS FolioNo ,
    						f.ShareHolderName,			c.ClientName,			'TRANSFER' AS 'Transaction',
    						shc.CreatedDate AS 'TransactionDate',				CAST(shc.CertificateNo AS VARCHAR)AS 'OldCertificateNo',
    						CAST(shc.NoofShares AS VARCHAR) AS 'OldNoofShares',	CAST(Pshc.CertificateNo AS VARCHAR) AS 'NewCertificateNo',
    						CAST(Pshc.NoofShares AS VARCHAR) AS 'NewNoofShares'
    				  FROM	TM_Folios f WITH(NOLOCK) -- Transfered By Folio Details
    			INNER JOIN	TR_CertificateToSell cts WITH(NOLOCK) ON cts.FolioID = f.FolioID -- Transfer to & by details
    			INNER JOIN	Clients c WITH(NOLOCK) ON c.Clientid = cts.Clientid -- Client Details
    			INNER JOIN	TM_ShareHolderCertificates shc WITH(NOLOCK) ON shc.CertificateID = cts.CertificateID -- Details of Certificate which was transfered
    	   LEFT OUTER JOIN	ParentShareHolderCertificatesSeller pshc WITH(NOLOCK) ON shc.CertificateID = pshc.parentcertificateid AND pshc.FolioID = shc.FolioID -- Details of Certificate which was created for Transfering Folio if there was shares remaining
    				 WHERE	cts.InwardType = N'TR' AND f.FolioID = ISNULL(@FolioNo ,f.FolioID)
    				   AND	shc.ClientID = ISNULL(@ClientID ,shc.ClientID) AND	shc.CertificateID > @I 
    				   AND	cts.CertificateTransactionID >@I


    CREATE TABLE [dbo].[TM_Folios](
    	[FolioID] [bigint] IDENTITY(1,1) NOT NULL,
    	[FolioNo] [bigint] NOT NULL,
    	[ShareholderName] [varchar](150) NOT NULL,
    	[Name] [varchar](150) NULL,
    	[Surname] [varchar](150) NULL,
    	[JointHolder1] [varchar](150) NULL,
    	[JointHolder2] [varchar](150) NULL,
    	[JointHolder3] [varchar](150) NULL,
    	[JointHolder4] [varchar](150) NULL,
    	[JointHolder5] [varchar](150) NULL,
    	[Address1] [varchar](150) NOT NULL,
    	[Address2] [varchar](150) NOT NULL,
    	[Address3] [varchar](150) NULL,
    	[Address4] [varchar](150) NULL,
    	[Address5] [varchar](150) NULL,
    	[StateID] [int] NOT NULL,
    	[CityID] [int] NOT NULL,
    	[PincodeID] [int] NOT NULL,
    	[CreatedBy] [int] NOT NULL,
    	[CreatedDate] [datetime] NOT NULL,
    	[UpdatedBy] [int] NULL,
    	[UpdatedDate] [datetime] NULL,
    	[Title] [varchar](15) NULL,
    	[Language] [varchar](15) NULL,
    	[MemberTypeID] [int] NULL,
    	[IsAddressUnknown] [bit] NULL,
    	[PaymentMethodID] [int] NULL,
    	[Annual_Report] [bit] NULL,
    	[IdentityNo] [varchar](100) NULL,
    	[MergedTo] [varchar](100) NULL,
    	[ForwardingAddress1] [varchar](150) NULL,
    	[ForwardingAddress2] [varchar](150) NULL,
    	[ForwardingAddress3] [varchar](150) NULL,
    	[ForwardingAddress4] [varchar](150) NULL,
    	[ForwardingAddress5] [varchar](150) NULL,
    	[AccountType] [varchar](30) NULL,
    	[Nationality] [varchar](25) NULL,
    	[IsActive] [bit] NULL,
    	[Comments] [varchar](150) NULL,
    	[PassportNo] [varchar](25) NULL,
    	[CompanyRegistration] [varchar](25) NULL,
    	[PrincipleMemberID] [bigint] NULL,
    	[ReportIndicator] [varchar](50) NULL,
    	[Designation] [varchar](50) NULL,
    	[Telephone1] [varchar](15) NULL,
    	[Telephone2] [varchar](15) NULL,
    	[Fax] [varchar](15) NULL,
    	[MobileNo] [varchar](15) NULL,
    	[Email] [varchar](50) NULL,
    	[EthnicGroup] [varchar](50) NULL,
    	[ExternalNumber] [varchar](20) NULL,
    	[ResidenceStatus] [int] NULL,
    	[TaxNo] [varchar](20) NULL,
    	[AddressStatues] [varchar](50) NULL,
    	[TestCustumColumn] [nvarchar](200) NULL,
    	[BankAccHolderName] [varchar](150) NULL,
    	[FolioType] [bigint] NULL,
    	[Gender] [char](1) NULL,
     CONSTRAINT [PK_TR_Folios] PRIMARY KEY CLUSTERED 
    (
    	[FolioID] 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 [dbo].[TM_Folios] ADD  CONSTRAINT [DF_TM_Folios_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]
    GO
    
    ALTER TABLE [dbo].[TM_Folios] ADD  CONSTRAINT [DF_TM_Folios_IsActive]  DEFAULT ((1)) FOR [IsActive]
    GO
    
    CREATE TABLE [dbo].[TR_CertificateToSell](
    	[CertificateTransactionID] [bigint] IDENTITY(1,1) NOT NULL,
    	[InwardID] [bigint] NOT NULL,
    	[InwardType] [varchar](2) NOT NULL,
    	[ClientID] [bigint] NOT NULL,
    	[FolioID] [bigint] NOT NULL,
    	[CertificateID] [bigint] NOT NULL,
    	[NoofShares] [int] NOT NULL,
    	[BuyerTransactionID] [int] NULL,
    	[CreatedBy] [int] NULL,
    	[CreatedDate] [datetime] NULL,
    	[EntryTransactionNo] [numeric](18, 0) NULL,
    	[ProcessTransactionNo] [numeric](18, 0) NULL,
    	[AgentID] [int] NULL,
    	[DoNotPrint] [bit] NULL,
    	[SafiresReferenceNo] [varchar](50) NULL,
     CONSTRAINT [PK_TR_CertificateToSell] PRIMARY KEY CLUSTERED 
    (
    	[CertificateTransactionID] 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 [dbo].[TR_CertificateToSell]  WITH NOCHECK ADD  CONSTRAINT [FK_TR_CertificateToSell_TM_ShareHolderCertificates] FOREIGN KEY([CertificateID])
    REFERENCES [dbo].[TM_ShareHolderCertificates] ([CertificateID])
    GO
    
    ALTER TABLE [dbo].[TR_CertificateToSell] NOCHECK CONSTRAINT [FK_TR_CertificateToSell_TM_ShareHolderCertificates]
    GO
    
    ALTER TABLE [dbo].[TR_CertificateToSell] ADD  CONSTRAINT [DF_TR_CertificateToSell_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]
    GO
    


    CREATE TABLE [dbo].[Clients](
    	[ClientID] [bigint] IDENTITY(1,1) NOT NULL,
    	[ClientName] [varchar](100) NOT NULL,
    	[ShortCode] [varchar](8) NULL,
    	[RTABranchID] [int] NOT NULL,
    	[RegAddress1] [varchar](40) NOT NULL,
    	[RegAddress2] [varchar](40) NOT NULL,
    	[RegAddress3] [varchar](40) NULL,
    	[RegAddress4] [varchar](40) NULL,
    	[RegPincodeID] [int] NULL,
    	[ContactPerson] [varchar](25) NULL,
    	[ContactAddress1] [varchar](40) NULL,
    	[ContactAddress2] [varchar](40) NULL,
    	[ContactAddress3] [varchar](40) NULL,
    	[ContactAddress4] [varchar](40) NULL,
    	[ContactPincodeID] [int] NULL,
    	[AuthorisedCapital] [decimal](18, 2) NULL,
    	[IssuedCapital] [decimal](18, 2) NULL,
    	[PaidUpCapital] [decimal](18, 2) NULL,
    	[CertificateNo] [bigint] NOT NULL,
    	[IMInwardNoTransfer] [bigint] NOT NULL,
    	[InwardNoTransfer] [bigint] NOT NULL,
    	[InwardNoDemat] [bigint] NOT NULL,
    	[InwardNoRemat] [bigint] NOT NULL,
    	[OutwardNoTransfer] [bigint] NOT NULL,
    	[OutwardNoDemat] [bigint] NOT NULL,
    	[OutwardNoRemat] [bigint] NOT NULL,
    	[TransactionNo] [bigint] NOT NULL,
    	[ClientStatusID] [int] NULL,
    	[IsUsedForNameChange] [bit] NULL,
    	[IsActive] [bit] NOT NULL,
    	[CreatedBy] [int] NOT NULL,
    	[CreatedDate] [datetime] NOT NULL,
    	[UpdatedBy] [int] NULL,
    	[UpdatedDate] [datetime] NULL,
    	[RegistrationNo] [varchar](20) NULL,
    	[IsListed] [bit] NULL,
    	[InwardNoDematCancel] [bigint] NULL,
    	[InwardNoRematCancel] [bigint] NULL,
    	[InwardReMobilize] [bigint] NULL,
    	[OutwardReMobilize] [bigint] NULL,
    	[InwardImmobilize] [bigint] NULL,
    	[SendStrateMsg] [bit] NULL,
    	[BNDMember] [bit] NULL,
    	[GenerateCertificateNo] [bit] NULL,
    	[ClientType] [bigint] NULL,
    	[LinkTo] [bigint] NULL,
     CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED 
    (
    	[ClientID] 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 [dbo].[Clients] ADD  CONSTRAINT [DF_Clients_CertificateNo]  DEFAULT ((0)) FOR [CertificateNo]
    GO
    
    ALTER TABLE [dbo].[Clients] ADD  CONSTRAINT [DF_Clients_IMInwardNoTransfer]  DEFAULT ((0)) FOR [IMInwardNoTransfer]
    GO
    
    ALTER TABLE [dbo].[Clients] ADD  CONSTRAINT [DF_Clients_InwardNoTransfer]  DEFAULT ((0)) FOR [InwardNoTransfer]
    GO
    
    ALTER TABLE [dbo].[Clients] ADD  CONSTRAINT [DF_Clients_InwardNoDemat]  DEFAULT ((0)) FOR [InwardNoDemat]
    GO
    
    ALTER TABLE [dbo].[Clients] ADD  CONSTRAINT [DF_Clients_InwardNoRemat]  DEFAULT ((0)) FOR [InwardNoRemat]
    GO
    
    ALTER TABLE [dbo].[Clients] ADD  CONSTRAINT [DF_Clients_OutwardNoTransfer]  DEFAULT ((0)) FOR [OutwardNoTransfer]
    GO
    
    ALTER TABLE [dbo].[Clients] ADD  CONSTRAINT [DF_Clients_OutwardNoDemat]  DEFAULT ((0)) FOR [OutwardNoDemat]
    GO
    
    ALTER TABLE [dbo].[Clients] ADD  CONSTRAINT [DF_Clients_OutwardNoRemat]  DEFAULT ((0)) FOR [OutwardNoRemat]
    GO
    
    ALTER TABLE [dbo].[Clients] ADD  CONSTRAINT [DF_ER_Client_IsDeleted]  DEFAULT ((0)) FOR [IsActive]
    GO
    
    ALTER TABLE [dbo].[Clients] ADD  CONSTRAINT [DF_Clients_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]
    GO
    
    ALTER TABLE [dbo].[Clients] ADD  CONSTRAINT [DF_Clients_InwardNoDematCancel]  DEFAULT ((0)) FOR [InwardNoDematCancel]
    GO
    
    ALTER TABLE [dbo].[Clients] ADD  CONSTRAINT [DF_Clients_InwardNoRematCancel]  DEFAULT ((0)) FOR [InwardNoRematCancel]
    GO
    
    ALTER TABLE [dbo].[Clients] ADD  CONSTRAINT [DF__Clients__SendStr__6DB809C1]  DEFAULT ((1)) FOR [SendStrateMsg]
    GO
    
    ALTER TABLE [dbo].[Clients] ADD  CONSTRAINT [DF__Clients__BNDMemb__6EAC2DFA]  DEFAULT ((1)) FOR [BNDMember]
    GO
    
    ALTER TABLE [dbo].[Clients] ADD  CONSTRAINT [DF__Clients__Generat__6FA05233]  DEFAULT ((1)) FOR [GenerateCertificateNo]
    GO
    
    ALTER TABLE [dbo].[Clients] ADD  DEFAULT ((0)) FOR [LinkTo]
    GO
    
    CREATE TABLE [dbo].[TM_ShareHolderCertificates](
    	[CertificateID] [bigint] IDENTITY(1,1) NOT NULL,
    	[ClientID] [bigint] NOT NULL,
    	[FolioID] [bigint] NOT NULL,
    	[CertificateNo] [int] NOT NULL,
    	[CertificateDate] [datetime] NULL,
    	[NoOfShares] [bigint] NOT NULL,
    	[ParentCertificateID] [bigint] NULL,
    	[IsTransactionInitiated] [bit] NOT NULL,
    	[CertificateGeneratingActivityType] [int] NULL,
    	[IsDeleted] [bit] NOT NULL,
    	[Status] [int] NULL,
    	[CreatedBy] [int] NOT NULL,
    	[CreatedDate] [datetime] NOT NULL,
    	[UpdatedBy] [int] NULL,
    	[UpdatedDate] [datetime] NULL,
     CONSTRAINT [PK_TM_ShareHolderCertificates] PRIMARY KEY CLUSTERED 
    (
    	[CertificateID] 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].[TM_ShareHolderCertificates]  WITH NOCHECK ADD  CONSTRAINT [FK_TM_ShareHolderCertificates_Clients] FOREIGN KEY([ClientID])
    REFERENCES [dbo].[Clients] ([ClientID])
    GO
    
    ALTER TABLE [dbo].[TM_ShareHolderCertificates] CHECK CONSTRAINT [FK_TM_ShareHolderCertificates_Clients]
    GO
    
    ALTER TABLE [dbo].[TM_ShareHolderCertificates]  WITH NOCHECK ADD  CONSTRAINT [FK_TM_ShareHolderCertificates_TM_Folios] FOREIGN KEY([FolioID])
    REFERENCES [dbo].[TM_Folios] ([FolioID])
    GO
    
    ALTER TABLE [dbo].[TM_ShareHolderCertificates] CHECK CONSTRAINT [FK_TM_ShareHolderCertificates_TM_Folios]
    GO
    
    ALTER TABLE [dbo].[TM_ShareHolderCertificates] ADD  CONSTRAINT [DF_TM_ShareHolderCertificates_IsTransactionInitiated]  DEFAULT ((0)) FOR [IsTransactionInitiated]
    GO
    
    ALTER TABLE [dbo].[TM_ShareHolderCertificates] ADD  CONSTRAINT [DF_TM_ShareHolderCertificates_CreatedDate_1]  DEFAULT (getdate()) FOR [CreatedDate]
    GO
    





    Wednesday, March 14, 2012 9:03 AM
  • Thank you.  Your actual query is far more complex than your originally posted query hence the importance of posting actual examples.

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript


    Wednesday, March 14, 2012 9:11 AM
  • What are the data types of the following variables

    @FolioNo, @ClientID and @I


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    Wednesday, March 14, 2012 9:29 AM
  • Hello Mr. Wharty

    @FolioNo, @ClientID  are BIGINT

    @I is INT

    Thanks for the help. I have found this type of problems in many of my queries.

    Wednesday, March 14, 2012 9:38 AM
  • You have only a clustered index on TM_ShareHolderCertificates table on CertificateID column. So your below query always prefer a scan:

    WITH ParentShareHolderCertificatesSeller
    		  AS
    		  (
    			   SELECT CertificateID,ClientID,FolioID,CertificateNo,NoofShares,ParentCertificateID 
    				 FROM TM_ShareHolderCertificates WITH(NOLOCK)
    				WHERE IsDeleted = 0 AND CertificateID > @I 
    		  )

    And also creating a covering index on the query columns may not improve performance since the IsDeleted column is a bit field and the selectivity can be very low. The query optimizer may avoid the index and go for a scan. My suggestion is to create an indexed view or filtered index on highly selective columns that may improve the query performance. Have a look at the below links:

    Techniques for Indexing Low-Selectivity Columns in SQL Server

    Filtered Index Design Guidelines

    - Krishnakumar S

    Wednesday, March 14, 2012 9:58 AM
  • The following article deals with query optimization:

    http://www.sqlusa.com/articles/query-optimization/

    Make sure you have indexes on FK and WHERE clause predicate columns.

    Remove NOLOCK hint, else you are asking for trouble.


    Kalman Toth SQL SERVER & BI TRAINING

    Wednesday, March 14, 2012 12:03 PM
  • Hello SQLUSA

    If I don't use NOLOCK then aren't there chances of deadlock.

    As I have come across same situation many a times. Query goes into long processing when NOLOCK is not used. I understand there are risk of Dirty reads if I use it but then is there any other way round.

    Wednesday, March 14, 2012 12:27 PM
  • You cannot completely avoid a deadlock situation, but can minimize deadlocks. Using a NOLOCK hint in a constantly updating table can cause other issues like dirty reads. You have to analyse and identify the scenario where the dealock occurs and rewrite the code. The following links may be helpful in case of deadlocks:

    Minimizing Deadlocks

    Analyzing Deadlocks with SQL Server Profiler

    How to Track Down Deadlocks

    - Krishnakumar S

    Wednesday, March 14, 2012 1:41 PM
  • What will happen if you don't use ISNULL(..) for the parameters. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, March 14, 2012 1:46 PM
  • Hello Naomi N

    Sometimes i will not send any value to the parameters.so i  compare the value of the column to itself.

    Thursday, March 15, 2012 3:51 AM
  • I know, but when you write the query this way, you will always result in scan. Check this blog post

    Do you use Column=@Param OR @Param IS NULL in your WHERE clause? Don't, it doesn't perform


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, March 15, 2012 1:03 PM
  • Hello SQLUSA

    If I don't use NOLOCK then aren't there chances of deadlock.

    Are you running this query on the transactional (OLTP) database?

    Alternates: reporting database, data warehouse database, OLAP cube, running it off-hours, etc..

    The simplest reporting database is last-night db backup restored as a database for reporting use only. Data is as of last night, sufficient for all but real-time reports.

    Restore database script:

    http://www.sqlusa.com/bestpractices2005/restoredbwithsqlcmd/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Tuesday, March 20, 2012 8:30 PM