Index on Computed column or Indexed View

I have a large nvarchar(2000) that need to be queried on often based on a
subset of the data (first 50 characters). The application creating and using
the data cannot be modified to capture a short and long column... I was
wondering if creating a computed column with the formula being
Left(longcolumn, 50) and creating an index based on this column could be a
good option? Or would it be preferable to create an indexed view?
Any other suggestion are welcomed
Thank you for your helpI would go with a computed column to start with.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Martin Rajotte" <MartinRajotte@.discussions.microsoft.com> wrote in message
news:0EB763CD-5C4E-455C-83E2-1454742D5507@.microsoft.com...
> I have a large nvarchar(2000) that need to be queried on often based on a
> subset of the data (first 50 characters). The application creating and
using
> the data cannot be modified to capture a short and long column... I was
> wondering if creating a computed column with the formula being
> Left(longcolumn, 50) and creating an index based on this column could be a
> good option? Or would it be preferable to create an indexed view?
> Any other suggestion are welcomed
> Thank you for your help
>|||Thank you for help. It confirms my tests that I performed last night.
"Narayana Vyas Kondreddi" wrote:

> I would go with a computed column to start with.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Martin Rajotte" <MartinRajotte@.discussions.microsoft.com> wrote in messag
e
> news:0EB763CD-5C4E-455C-83E2-1454742D5507@.microsoft.com...
> using
>
>

Index Lookups

I have queried sys.dm_db_index_usage_stats and found an index that has a high
number of IndexLookups vs. IndexSeeks. This index is on a table that is
refernced in over 1200 stored procedures. The index is comprised of a single
column.
I could examine each of these 1200 stored procedures individually to see what
additional columns might be added or included in the index to reduce the
lookups, or run Profiler and include Event Performance > ShowPlan Text and
search for the table or index in ShowPlan Text, but neither of these options
thrill me.
Any other ideas on how one might go about determining the additional columns
needed to cover these lookups?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
cbrichards via droptable.com,
That index could be the clustered index for that table. I do not think you
want to add more columns to the key of the clustered index. The look_up is
from a nonclustered to a clustered, so you will have to identify first the
nonclustered indexes participating in the look_up and see if you can add more
columns, either to the key or to the leaf nodes using the new feature
"INCLUDE (COLUMN_NAME,...)" to make the nonclustered index a covering one.
AMB
"cbrichards via droptable.com" wrote:

> I have queried sys.dm_db_index_usage_stats and found an index that has a high
> number of IndexLookups vs. IndexSeeks. This index is on a table that is
> refernced in over 1200 stored procedures. The index is comprised of a single
> column.
> I could examine each of these 1200 stored procedures individually to see what
> additional columns might be added or included in the index to reduce the
> lookups, or run Profiler and include Event Performance > ShowPlan Text and
> search for the table or index in ShowPlan Text, but neither of these options
> thrill me.
> Any other ideas on how one might go about determining the additional columns
> needed to cover these lookups?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
>
|||Thanks Alejandro. You are correct...this is a clustered index.
So I believe you have clarified a misunderstanding on my part. I thought the
User_Lookups value returned from sys.dm_db_index_usage_stats was the number
of times the index itself caused a lookup, but from your statement, it
appears the User_Lookups value is the number of times the index is used by
other indexes to lookup information. Is that a correct understanding?
Alejandro Mesa wrote:[vbcol=seagreen]
>cbrichards via droptable.com,
>That index could be the clustered index for that table. I do not think you
>want to add more columns to the key of the clustered index. The look_up is
>from a nonclustered to a clustered, so you will have to identify first the
>nonclustered indexes participating in the look_up and see if you can add more
>columns, either to the key or to the leaf nodes using the new feature
>"INCLUDE (COLUMN_NAME,...)" to make the nonclustered index a covering one.
>AMB
>[quoted text clipped - 9 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
|||It may just be a misunderstanding in the way indexes are organized. Remember
that the clustered index IS the data. So anytime you are going to the actual
data pages, you are using the clustered index.
It's not clear what you mean by 'the index caused a lookup'.
The clustered index keys are used as the bookmark values in nonclustered
indexes. So anytime a nonclustered index is used to find the data you need,
SQL Server will find the corresponding clustered key in the nonclustered
index,and then use it to find the row in the clustered index (the data).
So user_lookups is the total number of times the index was used for a
lookup. And for a clustered index, the number will probably be bigger than
for any other index, because you'll have all the queries that use the
clustered index key in the user query, plus all the queries that use a
nonclustered index to get to the data.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6ed74f8c473ce@.uwe...
> Thanks Alejandro. You are correct...this is a clustered index.
> So I believe you have clarified a misunderstanding on my part. I thought
> the
> User_Lookups value returned from sys.dm_db_index_usage_stats was the
> number
> of times the index itself caused a lookup, but from your statement, it
> appears the User_Lookups value is the number of times the index is used by
> other indexes to lookup information. Is that a correct understanding?
> Alejandro Mesa wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
>
|||cbrichards via droptable.com,
That is my understanding also, but I have not found anything in BOL to
support that. You can do some test in your local computer (personal - NOT
PRODUCTION). Restart the db engine, and execute this:
use northwind
go
-- index seek on PK_Orders (clustered)
select *
from dbo.orders
where orderid = 10250
select *
from sys.dm_db_index_usage_stats
select *
from dbo.orders
where orderdate = '19980101'
select *
from sys.dm_db_index_usage_stats
select *
from dbo.orders
where employeeid = 5 and customerid= cast(N'WARTH' as nchar(5))
select *
from sys.dm_db_index_usage_stats
go
AMB
"cbrichards via droptable.com" wrote:

> Thanks Alejandro. You are correct...this is a clustered index.
> So I believe you have clarified a misunderstanding on my part. I thought the
> User_Lookups value returned from sys.dm_db_index_usage_stats was the number
> of times the index itself caused a lookup, but from your statement, it
> appears the User_Lookups value is the number of times the index is used by
> other indexes to lookup information. Is that a correct understanding?
> Alejandro Mesa wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
>

Index Lookups

I have queried sys.dm_db_index_usage_stats and found an index that has a hig
h
number of IndexLookups vs. IndexSeeks. This index is on a table that is
refernced in over 1200 stored procedures. The index is comprised of a single
column.
I could examine each of these 1200 stored procedures individually to see wha
t
additional columns might be added or included in the index to reduce the
lookups, or run Profiler and include Event Performance > ShowPlan Text and
search for the table or index in ShowPlan Text, but neither of these options
thrill me.
Any other ideas on how one might go about determining the additional columns
needed to cover these lookups?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1cbrichards via droptable.com,
That index could be the clustered index for that table. I do not think you
want to add more columns to the key of the clustered index. The look_up is
from a nonclustered to a clustered, so you will have to identify first the
nonclustered indexes participating in the look_up and see if you can add mor
e
columns, either to the key or to the leaf nodes using the new feature
"INCLUDE (COLUMN_NAME,...)" to make the nonclustered index a covering one.
AMB
"cbrichards via droptable.com" wrote:

> I have queried sys.dm_db_index_usage_stats and found an index that has a h
igh
> number of IndexLookups vs. IndexSeeks. This index is on a table that is
> refernced in over 1200 stored procedures. The index is comprised of a sing
le
> column.
> I could examine each of these 1200 stored procedures individually to see w
hat
> additional columns might be added or included in the index to reduce the
> lookups, or run Profiler and include Event Performance > ShowPlan Text and
> search for the table or index in ShowPlan Text, but neither of these optio
ns
> thrill me.
> Any other ideas on how one might go about determining the additional colum
ns
> needed to cover these lookups?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>|||Perhaps there is anything in the missing_indexes DMV's?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:6ed5f925db6ae@.uwe...[vbcol
=seagreen]
>I have queried sys.dm_db_index_usage_stats and found an index that has a hi
gh
> number of IndexLookups vs. IndexSeeks. This index is on a table that is
> refernced in over 1200 stored procedures. The index is comprised of a sing
le
> column.
> I could examine each of these 1200 stored procedures individually to see w
hat
> additional columns might be added or included in the index to reduce the
> lookups, or run Profiler and include Event Performance > ShowPlan Text and
> search for the table or index in ShowPlan Text, but neither of these optio
ns
> thrill me.
> Any other ideas on how one might go about determining the additional colum
ns
> needed to cover these lookups?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>[/vbcol]|||Thanks Alejandro. You are correct...this is a clustered index.
So I believe you have clarified a misunderstanding on my part. I thought the
User_Lookups value returned from sys.dm_db_index_usage_stats was the number
of times the index itself caused a lookup, but from your statement, it
appears the User_Lookups value is the number of times the index is used by
other indexes to lookup information. Is that a correct understanding?
Alejandro Mesa wrote:[vbcol=seagreen]
>cbrichards via droptable.com,
>That index could be the clustered index for that table. I do not think you
>want to add more columns to the key of the clustered index. The look_up is
>from a nonclustered to a clustered, so you will have to identify first the
>nonclustered indexes participating in the look_up and see if you can add mo
re
>columns, either to the key or to the leaf nodes using the new feature
>"INCLUDE (COLUMN_NAME,...)" to make the nonclustered index a covering one.
>AMB
>
>[quoted text clipped - 9 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1|||It may just be a misunderstanding in the way indexes are organized. Remember
that the clustered index IS the data. So anytime you are going to the actual
data pages, you are using the clustered index.
It's not clear what you mean by 'the index caused a lookup'.
The clustered index keys are used as the bookmark values in nonclustered
indexes. So anytime a nonclustered index is used to find the data you need,
SQL Server will find the corresponding clustered key in the nonclustered
index,and then use it to find the row in the clustered index (the data).
So user_lookups is the total number of times the index was used for a
lookup. And for a clustered index, the number will probably be bigger than
for any other index, because you'll have all the queries that use the
clustered index key in the user query, plus all the queries that use a
nonclustered index to get to the data.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6ed74f8c473ce@.uwe...
> Thanks Alejandro. You are correct...this is a clustered index.
> So I believe you have clarified a misunderstanding on my part. I thought
> the
> User_Lookups value returned from sys.dm_db_index_usage_stats was the
> number
> of times the index itself caused a lookup, but from your statement, it
> appears the User_Lookups value is the number of times the index is used by
> other indexes to lookup information. Is that a correct understanding?
> Alejandro Mesa wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>|||cbrichards via droptable.com,
That is my understanding also, but I have not found anything in BOL to
support that. You can do some test in your local computer (personal - NOT
PRODUCTION). Restart the db engine, and execute this:
use northwind
go
-- index seek on PK_Orders (clustered)
select *
from dbo.orders
where orderid = 10250
select *
from sys.dm_db_index_usage_stats
select *
from dbo.orders
where orderdate = '19980101'
select *
from sys.dm_db_index_usage_stats
select *
from dbo.orders
where employeeid = 5 and customerid= cast(N'WARTH' as nchar(5))
select *
from sys.dm_db_index_usage_stats
go
AMB
"cbrichards via droptable.com" wrote:

> Thanks Alejandro. You are correct...this is a clustered index.
> So I believe you have clarified a misunderstanding on my part. I thought t
he
> User_Lookups value returned from sys.dm_db_index_usage_stats was the numbe
r
> of times the index itself caused a lookup, but from your statement, it
> appears the User_Lookups value is the number of times the index is used by
> other indexes to lookup information. Is that a correct understanding?
> Alejandro Mesa wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>

Index Lookups

I have queried sys.dm_db_index_usage_stats and found an index that has a high
number of IndexLookups vs. IndexSeeks. This index is on a table that is
refernced in over 1200 stored procedures. The index is comprised of a single
column.
I could examine each of these 1200 stored procedures individually to see what
additional columns might be added or included in the index to reduce the
lookups, or run Profiler and include Event Performance > ShowPlan Text and
search for the table or index in ShowPlan Text, but neither of these options
thrill me.
Any other ideas on how one might go about determining the additional columns
needed to cover these lookups?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1cbrichards via SQLMonster.com,
That index could be the clustered index for that table. I do not think you
want to add more columns to the key of the clustered index. The look_up is
from a nonclustered to a clustered, so you will have to identify first the
nonclustered indexes participating in the look_up and see if you can add more
columns, either to the key or to the leaf nodes using the new feature
"INCLUDE (COLUMN_NAME,...)" to make the nonclustered index a covering one.
AMB
"cbrichards via SQLMonster.com" wrote:
> I have queried sys.dm_db_index_usage_stats and found an index that has a high
> number of IndexLookups vs. IndexSeeks. This index is on a table that is
> refernced in over 1200 stored procedures. The index is comprised of a single
> column.
> I could examine each of these 1200 stored procedures individually to see what
> additional columns might be added or included in the index to reduce the
> lookups, or run Profiler and include Event Performance > ShowPlan Text and
> search for the table or index in ShowPlan Text, but neither of these options
> thrill me.
> Any other ideas on how one might go about determining the additional columns
> needed to cover these lookups?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>|||Perhaps there is anything in the missing_indexes DMV's?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:6ed5f925db6ae@.uwe...
>I have queried sys.dm_db_index_usage_stats and found an index that has a high
> number of IndexLookups vs. IndexSeeks. This index is on a table that is
> refernced in over 1200 stored procedures. The index is comprised of a single
> column.
> I could examine each of these 1200 stored procedures individually to see what
> additional columns might be added or included in the index to reduce the
> lookups, or run Profiler and include Event Performance > ShowPlan Text and
> search for the table or index in ShowPlan Text, but neither of these options
> thrill me.
> Any other ideas on how one might go about determining the additional columns
> needed to cover these lookups?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>|||Thanks Alejandro. You are correct...this is a clustered index.
So I believe you have clarified a misunderstanding on my part. I thought the
User_Lookups value returned from sys.dm_db_index_usage_stats was the number
of times the index itself caused a lookup, but from your statement, it
appears the User_Lookups value is the number of times the index is used by
other indexes to lookup information. Is that a correct understanding?
Alejandro Mesa wrote:
>cbrichards via SQLMonster.com,
>That index could be the clustered index for that table. I do not think you
>want to add more columns to the key of the clustered index. The look_up is
>from a nonclustered to a clustered, so you will have to identify first the
>nonclustered indexes participating in the look_up and see if you can add more
>columns, either to the key or to the leaf nodes using the new feature
>"INCLUDE (COLUMN_NAME,...)" to make the nonclustered index a covering one.
>AMB
>> I have queried sys.dm_db_index_usage_stats and found an index that has a high
>> number of IndexLookups vs. IndexSeeks. This index is on a table that is
>[quoted text clipped - 9 lines]
>> Any other ideas on how one might go about determining the additional columns
>> needed to cover these lookups?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1|||It may just be a misunderstanding in the way indexes are organized. Remember
that the clustered index IS the data. So anytime you are going to the actual
data pages, you are using the clustered index.
It's not clear what you mean by 'the index caused a lookup'.
The clustered index keys are used as the bookmark values in nonclustered
indexes. So anytime a nonclustered index is used to find the data you need,
SQL Server will find the corresponding clustered key in the nonclustered
index,and then use it to find the row in the clustered index (the data).
So user_lookups is the total number of times the index was used for a
lookup. And for a clustered index, the number will probably be bigger than
for any other index, because you'll have all the queries that use the
clustered index key in the user query, plus all the queries that use a
nonclustered index to get to the data.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6ed74f8c473ce@.uwe...
> Thanks Alejandro. You are correct...this is a clustered index.
> So I believe you have clarified a misunderstanding on my part. I thought
> the
> User_Lookups value returned from sys.dm_db_index_usage_stats was the
> number
> of times the index itself caused a lookup, but from your statement, it
> appears the User_Lookups value is the number of times the index is used by
> other indexes to lookup information. Is that a correct understanding?
> Alejandro Mesa wrote:
>>cbrichards via SQLMonster.com,
>>That index could be the clustered index for that table. I do not think you
>>want to add more columns to the key of the clustered index. The look_up is
>>from a nonclustered to a clustered, so you will have to identify first the
>>nonclustered indexes participating in the look_up and see if you can add
>>more
>>columns, either to the key or to the leaf nodes using the new feature
>>"INCLUDE (COLUMN_NAME,...)" to make the nonclustered index a covering one.
>>AMB
>> I have queried sys.dm_db_index_usage_stats and found an index that has a
>> high
>> number of IndexLookups vs. IndexSeeks. This index is on a table that is
>>[quoted text clipped - 9 lines]
>> Any other ideas on how one might go about determining the additional
>> columns
>> needed to cover these lookups?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>|||cbrichards via SQLMonster.com,
That is my understanding also, but I have not found anything in BOL to
support that. You can do some test in your local computer (personal - NOT
PRODUCTION). Restart the db engine, and execute this:
use northwind
go
-- index seek on PK_Orders (clustered)
select *
from dbo.orders
where orderid = 10250
select *
from sys.dm_db_index_usage_stats
select *
from dbo.orders
where orderdate = '19980101'
select *
from sys.dm_db_index_usage_stats
select *
from dbo.orders
where employeeid = 5 and customerid= cast(N'WARTH' as nchar(5))
select *
from sys.dm_db_index_usage_stats
go
AMB
"cbrichards via SQLMonster.com" wrote:
> Thanks Alejandro. You are correct...this is a clustered index.
> So I believe you have clarified a misunderstanding on my part. I thought the
> User_Lookups value returned from sys.dm_db_index_usage_stats was the number
> of times the index itself caused a lookup, but from your statement, it
> appears the User_Lookups value is the number of times the index is used by
> other indexes to lookup information. Is that a correct understanding?
> Alejandro Mesa wrote:
> >cbrichards via SQLMonster.com,
> >
> >That index could be the clustered index for that table. I do not think you
> >want to add more columns to the key of the clustered index. The look_up is
> >from a nonclustered to a clustered, so you will have to identify first the
> >nonclustered indexes participating in the look_up and see if you can add more
> >columns, either to the key or to the leaf nodes using the new feature
> >"INCLUDE (COLUMN_NAME,...)" to make the nonclustered index a covering one.
> >
> >AMB
> >
> >> I have queried sys.dm_db_index_usage_stats and found an index that has a high
> >> number of IndexLookups vs. IndexSeeks. This index is on a table that is
> >[quoted text clipped - 9 lines]
> >> Any other ideas on how one might go about determining the additional columns
> >> needed to cover these lookups?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>