How to Create Index

If I run query mostly on column expressions, like I have two columns in table
one for date and other is for time, when i want to build index, then i build
index on both columns, but will these separate index work when i write query
with this expression,
where convert(varchar(10),saleDate,121) + ' ' +
right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
both columns have data type of datetime.
My question is, will the separate indexes of both columns will be utilized'
Or what is the best practice, when you are searching on expressions in
queries? not on just simply column.The expression is not sargable because you are applying functions to the
column value. If your data contains the default values for the time and
date components (i.e. '1900-01-01' and '00:00:00.000'), try:
WHERE
saleDate = '20050101' AND
saleTime = '11:30:00'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> If I run query mostly on column expressions, like I have two columns in
> table
> one for date and other is for time, when i want to build index, then i
> build
> index on both columns, but will these separate index work when i write
> query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be
> utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||This (convert(varchar(23),saletime,121) will cause a table scan
Doesn't saletime inlude the date? why is this in 2 fields?
you could use where date > and time > (2 conditions)
http://sqlservercode.blogspot.com/
"Imran" wrote:
> If I run query mostly on column expressions, like I have two columns in table
> one for date and other is for time, when i want to build index, then i build
> index on both columns, but will these separate index work when i write query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"Dan Guzman" wrote:
> The expression is not sargable because you are applying functions to the
> column value. If your data contains the default values for the time and
> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> WHERE
> saleDate = '20050101' AND
> saleTime = '11:30:00'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Imran" <Imran@.discussions.microsoft.com> wrote in message
> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> > If I run query mostly on column expressions, like I have two columns in
> > table
> > one for date and other is for time, when i want to build index, then i
> > build
> > index on both columns, but will these separate index work when i write
> > query
> > with this expression,
> > where convert(varchar(10),saleDate,121) + ' ' +
> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > both columns have data type of datetime.
> > My question is, will the separate indexes of both columns will be
> > utilized'
> > Or what is the best practice, when you are searching on expressions in
> > queries? not on just simply column.
> >
> >
> >
>
>|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"SQL" wrote:
> This (convert(varchar(23),saletime,121) will cause a table scan
> Doesn't saletime inlude the date? why is this in 2 fields?
> you could use where date > and time > (2 conditions)
> http://sqlservercode.blogspot.com/
> "Imran" wrote:
> > If I run query mostly on column expressions, like I have two columns in table
> > one for date and other is for time, when i want to build index, then i build
> > index on both columns, but will these separate index work when i write query
> > with this expression,
> > where convert(varchar(10),saleDate,121) + ' ' +
> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > both columns have data type of datetime.
> > My question is, will the separate indexes of both columns will be utilized'
> > Or what is the best practice, when you are searching on expressions in
> > queries? not on just simply column.
> >
> >
> >|||You would have to create a computed column containing that expression and then index that computed
column. Or, create view with the expression and index the view (note that optimizer will only use
indexes on view by itself if you have enterprise edition).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:7D3B3CD5-BD21-4C32-8B1F-AE6AA9832C49@.microsoft.com...
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
>
> "Dan Guzman" wrote:
>> The expression is not sargable because you are applying functions to the
>> column value. If your data contains the default values for the time and
>> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
>> WHERE
>> saleDate = '20050101' AND
>> saleTime = '11:30:00'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Imran" <Imran@.discussions.microsoft.com> wrote in message
>> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
>> > If I run query mostly on column expressions, like I have two columns in
>> > table
>> > one for date and other is for time, when i want to build index, then i
>> > build
>> > index on both columns, but will these separate index work when i write
>> > query
>> > with this expression,
>> > where convert(varchar(10),saleDate,121) + ' ' +
>> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
>> > both columns have data type of datetime.
>> > My question is, will the separate indexes of both columns will be
>> > utilized'
>> > Or what is the best practice, when you are searching on expressions in
>> > queries? not on just simply column.
>> >
>> >
>> >
>>|||Imran,
SQL-Server does not have the feature to index expressions. You can only
index one or more columns. Although you could work around this by
creating a computed column and index this column, it will (probably) not
be used automatically if your query specifies "left(citycode,5) ='12345'".
But it would be so much simpler to rewrite your predicate to "citycode
LIKE '12345%'". This basically does the same, and it will almost
certainly use an index on column citycode.
HTH,
Gert-Jan
Imran wrote:
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
> "Dan Guzman" wrote:
> > The expression is not sargable because you are applying functions to the
> > column value. If your data contains the default values for the time and
> > date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> >
> > WHERE
> > saleDate = '20050101' AND
> > saleTime = '11:30:00'
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Imran" <Imran@.discussions.microsoft.com> wrote in message
> > news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> > > If I run query mostly on column expressions, like I have two columns in
> > > table
> > > one for date and other is for time, when i want to build index, then i
> > > build
> > > index on both columns, but will these separate index work when i write
> > > query
> > > with this expression,
> > > where convert(varchar(10),saleDate,121) + ' ' +
> > > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > > both columns have data type of datetime.
> > > My question is, will the separate indexes of both columns will be
> > > utilized'
> > > Or what is the best practice, when you are searching on expressions in
> > > queries? not on just simply column.
> > >
> > >
> > >
> >
> >
> >sql