InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 11:53:29 GMT20http://www.sqlservercentral.com/Forums/Topic1408195-1292-1.aspx@duration is negative number....like -1,-2,-3Fri, 18 Jan 2013 04:08:17 GMTprakash.kumar3669http://www.sqlservercentral.com/Forums/Topic1408195-1292-1.aspx[quote]WHERE SomeNumber >=0 AND SomeNumber < 0[/quote]this is the actual condition: SubmitDate>=DATEADD(day,DATEDIFF(day,0,GETDATE()),@duration) AND SubmitDate < DATEADD(day,DATEDIFF(day,0,GETDATE()),1)Fri, 18 Jan 2013 04:06:05 GMTprakash.kumar3669http://www.sqlservercentral.com/Forums/Topic1408195-1292-1.aspx[quote][b]prakash.kumar3669 (1/17/2013)[/b][hr][quote][b]GilaMonster (1/17/2013)[/b][hr]Index on (IsDeleted, SubmitDate DESC) include (ProdName,Category,TotalStock,Price)[/quote]What would be the exact sql query to create the index.[/quote]Why don't you open up books online and read over Create Index.[quote][quote]btw, that's a rather odd predicate on the date column...[/quote]how can you ellaborate ?[/quote][quote]SubmitDate>=' ' and SubmitDate<' '[/quote]There is no possible way that any date can be both greater than or equal to '' and less than ''. Let me rephrase that into numbers...WHERE SomeNumber >=0 AND SomeNumber < 0That's completely, logically impossible as it is only true for a number that's both positive and negative at the same time, which no number is.Either you mis-typed when you copied the query over, or there's some serious flawed logic at work.Fri, 18 Jan 2013 03:00:12 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1408195-1292-1.aspx[quote][b]GilaMonster (1/17/2013)[/b][hr]Index on (IsDeleted, SubmitDate DESC) include (ProdName,Category,TotalStock,Price)[/quote]What would be the exact sql query to create the index.[quote]btw, that's a rather odd predicate on the date column...[/quote]how can you ellaborate ?Thu, 17 Jan 2013 21:28:59 GMTprakash.kumar3669http://www.sqlservercentral.com/Forums/Topic1408195-1292-1.aspxIndex on (IsDeleted, SubmitDate DESC) include (ProdName,Category,TotalStock,Price)btw, that's a rather odd predicate on the date column...Thu, 17 Jan 2013 06:39:09 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1408195-1292-1.aspx[quote]For better solution.post that Query[/quote]select ProdName,Category,TotalStock,Price from Product where SubmitDate>=' ' and SubmitDate<' ' and IsDeleted=0 order by SubmitDate DescThu, 17 Jan 2013 05:12:12 GMTprakash.kumar3669http://www.sqlservercentral.com/Forums/Topic1408195-1292-1.aspx[quote][b]prakash.kumar3669 (1/17/2013)[/b][hr]What i have read from online articles, its important to index the columns which comes in where condition.[/quote] Not only Where BUT group by, order by , JOIN's ON clause , Partititon by , and also the SELECT's columns. See this [url]http://www.sqlservercentral.com/articles/Indexing/68439/[/url][quote][b]prakash.kumar3669 (1/17/2013)[/b][hr]if i have 5 columns to retrieve in my queries then what's the best way of indexing ?[/quote] then those columns should be part of Covering index INCLUDE's part. For better solution.post that QueryThu, 17 Jan 2013 04:30:21 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1408195-1292-1.aspx[quote]Why not?[/quote]I don't know, i have no experience of implementing indexing till now. What i have read from online articles, its important to index the columns which comes in where condition. if i have 5 columns to retrieve in my queries then what's the best way of indexing ?Thu, 17 Jan 2013 04:23:33 GMTprakash.kumar3669http://www.sqlservercentral.com/Forums/Topic1408195-1292-1.aspx[quote][b]prakash.kumar3669 (1/17/2013)[/b][hr]in other words covering queries in not an option i guess.[/quote]Why not?Thu, 17 Jan 2013 03:19:43 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1408195-1292-1.aspx[quote]Do you always require "SELECT *" in your every query ?[/quote]the number of columns i need to display is 6-7 so i have put on * for the reference, in other words covering queries in not an option i guess.Thu, 17 Jan 2013 01:55:28 GMTprakash.kumar3669http://www.sqlservercentral.com/Forums/Topic1408195-1292-1.aspx[quote][b]prakash.kumar3669 (1/17/2013)[/b][hr]In my table (large one) there is mainly three select queries:1) select * from Product where productid=101Here productid is primary key so it is clustered index, so no problem here.2) select * from Product where IsDeleted=0 order by SubmitDate Desc3) select * from Product where SubmitDate>=' ' and SubmitDate<' ' and IsDeleted=0 order by SubmitDate Descfor 2) and 3) IsDeleted is a bit type contains duplicate values, and SubmitDate is datetime contains duplicate values.How to set index for 2) and 3)[/quote] Do you always require "SELECT *" in your every query ?Thu, 17 Jan 2013 01:26:55 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1408195-1292-1.aspxIndex on IsDeleted, SubmitDate DESCProbably won't be used unless there's a small number of rows that qualify or you can get rid of that SELECT * , retrieve only the columns required and make the index covering.Thu, 17 Jan 2013 01:19:21 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1408195-1292-1.aspxIn my table (large one) there is mainly three select queries:1) select * from Product where productid=101Here productid is primary key so it is clustered index, so no problem here.2) select * from Product where IsDeleted=0 order by SubmitDate Desc3) select * from Product where SubmitDate>=' ' and SubmitDate<' ' and IsDeleted=0 order by SubmitDate Descfor 2) and 3) IsDeleted is a bit type contains duplicate values, and SubmitDate is datetime contains duplicate values.How to set index for 2) and 3)Thu, 17 Jan 2013 01:06:48 GMTprakash.kumar3669