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 on computed column ignored

Hello,
I have a non-unique, non-clustered index on a computed column:
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
[cweb].[tbVehicleMainTest]
(
[ComputedYear] ASC
)
INCLUDE ( [Make],
[Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
The column computedYear is a "Presisted computed column" as:
(case when [DateFirstRegistered]<=[DateOfManufacture] then
datepart(year,[DateFirstRegistered]) when
[DateFirstRegistered]>[DateOfManufacture] then datepart(year,
[DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
It basically year for some datetime columns and choose the year.
When I do query:
set QUOTED_IDENTIFIER on;
set ANSI_NULLS on;
set ANSI_PADDING on;
set ANSI_WARNINGS on;
set ARITHABORT on;
set CONCAT_NULL_YIELDS_NULL on;
set NUMERIC_ROUNDABORT off;
select [Make],[Model] from cweb.tbVehicleMainTest where
[ComputedYear]=1
Sql Server uses index scan instead of index seek.
Would someone mind to point out what is wrong with my method? (I am
using sql 2005)
If you use an index hint to force it to use that index does it perform
better? If I am understanding your code, I think you should also INCLUDE for
date columns.
Jason Massie
http://statisticsio.com
<DAXU@.hotmail.com> wrote in message
news:9c39ea60-7c62-42b5-8385-763b0e94f90b@.a28g2000hsc.googlegroups.com...
> Hello,
> I have a non-unique, non-clustered index on a computed column:
> SET ARITHABORT ON
> SET CONCAT_NULL_YIELDS_NULL ON
> SET QUOTED_IDENTIFIER ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> SET NUMERIC_ROUNDABORT OFF
> CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
> [cweb].[tbVehicleMainTest]
> (
> [ComputedYear] ASC
> )
> INCLUDE ( [Make],
> [Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
> ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
> [PRIMARY]
> The column computedYear is a "Presisted computed column" as:
> (case when [DateFirstRegistered]<=[DateOfManufacture] then
> datepart(year,[DateFirstRegistered]) when
> [DateFirstRegistered]>[DateOfManufacture] then datepart(year,
> [DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
> It basically year for some datetime columns and choose the year.
> When I do query:
> set QUOTED_IDENTIFIER on;
> set ANSI_NULLS on;
> set ANSI_PADDING on;
> set ANSI_WARNINGS on;
> set ARITHABORT on;
> set CONCAT_NULL_YIELDS_NULL on;
> set NUMERIC_ROUNDABORT off;
> select [Make],[Model] from cweb.tbVehicleMainTest where
> [ComputedYear]=1
> Sql Server uses index scan instead of index seek.
> Would someone mind to point out what is wrong with my method? (I am
> using sql 2005)

index on computed column ignored

Hello,
I have a non-unique, non-clustered index on a computed column:
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
[cweb].[tbVehicleMainTest]
(
[ComputedYear] ASC
)
INCLUDE ( [Make],
[Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
The column computedYear is a "Presisted computed column" as:
(case when [DateFirstRegistered]<=[DateOfManufacture] then
datepart(year,[DateFirstRegistered]) when
[DateFirstRegistered]>[DateOfManufacture] then datepart(year,
[DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
It basically year for some datetime columns and choose the year.
When I do query:
set QUOTED_IDENTIFIER on;
set ANSI_NULLS on;
set ANSI_PADDING on;
set ANSI_WARNINGS on;
set ARITHABORT on;
set CONCAT_NULL_YIELDS_NULL on;
set NUMERIC_ROUNDABORT off;
select [Make],[Model] from cweb.tbVehicleMainTest where
[ComputedYear]=1
Sql Server uses index scan instead of index seek.
Would someone mind to point out what is wrong with my method? (I am
using sql 2005)If you use an index hint to force it to use that index does it perform
better? If I am understanding your code, I think you should also INCLUDE for
date columns.
Jason Massie
http://statisticsio.com
<DAXU@.hotmail.com> wrote in message
news:9c39ea60-7c62-42b5-8385-763b0e94f90b@.a28g2000hsc.googlegroups.com...
> Hello,
> I have a non-unique, non-clustered index on a computed column:
> SET ARITHABORT ON
> SET CONCAT_NULL_YIELDS_NULL ON
> SET QUOTED_IDENTIFIER ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> SET NUMERIC_ROUNDABORT OFF
> CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
> [cweb].[tbVehicleMainTest]
> (
> [ComputedYear] ASC
> )
> INCLUDE ( [Make],
> [Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
> ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
> [PRIMARY]
> The column computedYear is a "Presisted computed column" as:
> (case when [DateFirstRegistered]<=[DateOfManufacture] then
> datepart(year,[DateFirstRegistered]) when
> [DateFirstRegistered]>[DateOfManufacture] then datepart(year,
> [DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end
)
> It basically year for some datetime columns and choose the year.
> When I do query:
> set QUOTED_IDENTIFIER on;
> set ANSI_NULLS on;
> set ANSI_PADDING on;
> set ANSI_WARNINGS on;
> set ARITHABORT on;
> set CONCAT_NULL_YIELDS_NULL on;
> set NUMERIC_ROUNDABORT off;
> select [Make],[Model] from cweb.tbVehicleMainTest where
> [ComputedYear]=1
> Sql Server uses index scan instead of index seek.
> Would someone mind to point out what is wrong with my method? (I am
> using sql 2005)

index on computed column ignored

Hello,
I have a non-unique, non-clustered index on a computed column:
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
[cweb].[tbVehicleMainTest]
(
[ComputedYear] ASC
)
INCLUDE ( [Make],
[Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
The column computedYear is a "Presisted computed column" as:
(case when [DateFirstRegistered]<=[DateOfManufacture] then
datepart(year,[DateFirstRegistered]) when
[DateFirstRegistered]>[DateOfManufacture] then datepart(year,
[DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
It basically year for some datetime columns and choose the year.
When I do query:
set QUOTED_IDENTIFIER on;
set ANSI_NULLS on;
set ANSI_PADDING on;
set ANSI_WARNINGS on;
set ARITHABORT on;
set CONCAT_NULL_YIELDS_NULL on;
set NUMERIC_ROUNDABORT off;
select [Make],[Model] from cweb.tbVehicleMainTest where
[ComputedYear]=1
Sql Server uses index scan instead of index seek.
Would someone mind to point out what is wrong with my method? (I am
using sql 2005)If you use an index hint to force it to use that index does it perform
better? If I am understanding your code, I think you should also INCLUDE for
date columns.
Jason Massie
http://statisticsio.com
<DAXU@.hotmail.com> wrote in message
news:9c39ea60-7c62-42b5-8385-763b0e94f90b@.a28g2000hsc.googlegroups.com...
> Hello,
> I have a non-unique, non-clustered index on a computed column:
> SET ARITHABORT ON
> SET CONCAT_NULL_YIELDS_NULL ON
> SET QUOTED_IDENTIFIER ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> SET NUMERIC_ROUNDABORT OFF
> CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
> [cweb].[tbVehicleMainTest]
> (
> [ComputedYear] ASC
> )
> INCLUDE ( [Make],
> [Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
> ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
> [PRIMARY]
> The column computedYear is a "Presisted computed column" as:
> (case when [DateFirstRegistered]<=[DateOfManufacture] then
> datepart(year,[DateFirstRegistered]) when
> [DateFirstRegistered]>[DateOfManufacture] then datepart(year,
> [DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
> It basically year for some datetime columns and choose the year.
> When I do query:
> set QUOTED_IDENTIFIER on;
> set ANSI_NULLS on;
> set ANSI_PADDING on;
> set ANSI_WARNINGS on;
> set ARITHABORT on;
> set CONCAT_NULL_YIELDS_NULL on;
> set NUMERIC_ROUNDABORT off;
> select [Make],[Model] from cweb.tbVehicleMainTest where
> [ComputedYear]=1
> Sql Server uses index scan instead of index seek.
> Would someone mind to point out what is wrong with my method? (I am
> using sql 2005)

Index Computed Column?

Can I create an index on a variation of a column that isn't actually in
the table?

I have a ParcelNumber column, with values like

123 AB-670
12345ABC 000-00-040
12-345-67
AP34567890

The blanks and non-alphanumeric characters cause problems with users,
because sometimes they're there, and sometimes they aren't. So I would
like to create an index based on this column, with the non-alphanumeric
characters squeezed out. Of course I can add such a column to the
table and index it, but I'm wondering if it can be done without
actually adding the column.

Thanks,
JimSure, google up "indexes on computed columns"|||(jim_geissman@.countrywide.com) writes:

Quote:

Originally Posted by

Can I create an index on a variation of a column that isn't actually in
the table?
>
I have a ParcelNumber column, with values like
>
123 AB-670
12345ABC 000-00-040
12-345-67
AP34567890
>
The blanks and non-alphanumeric characters cause problems with users,
because sometimes they're there, and sometimes they aren't. So I would
like to create an index based on this column, with the non-alphanumeric
characters squeezed out. Of course I can add such a column to the
table and index it, but I'm wondering if it can be done without
actually adding the column.

Yes, you can, provided that your computation is deterministic.

First define the computed column:

ALTER TABLE tbl ADD computedcol AS <expression>

Then just create an index on the column. You will be told if the column
is not good for this. Reading the topic on CREATE INDEX is a good idea.

In SQL 2005 you can add PERSISTED after the column definition. This
permits you to persist a computed column without indexing it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks, Alexander and Erland.

Jim|||Oops, I forgot the follow-up...

If I were to create such a computed column index based on the column
with the non-alphanumerics squeezed out, and were to query against
@.variable that also had the non-alphanumerics squeezed out, I suppose I
would need to use an index hint to ensure that index was utilised?

jim_geissman@.countrywide.com wrote:

Quote:

Originally Posted by

Thanks, Alexander and Erland.
>
Jim

|||(jim_geissman@.countrywide.com) writes:

Quote:

Originally Posted by

Oops, I forgot the follow-up...
>
If I were to create such a computed column index based on the column
with the non-alphanumerics squeezed out, and were to query against
@.variable that also had the non-alphanumerics squeezed out, I suppose I
would need to use an index hint to ensure that index was utilised?

No, you should not have to. But you have to ensure that a number
of SET commands are in the right position. Most important, beware of
SET ARITHABORT which must be ON for SQL 2000, but which is never ON
by default, except in Query Analyzer.

-
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Jim,

In addition to Erland's remark, note that the settings during a stored
procedure's execution are the settings in effect during its creation:

SET ANSI_NULLS OFF
GO

CREATE PROCEDURE T1
@.C1 CHAR(1), @.C2 CHAR(1)
AS
SELECT CASE WHEN @.C1 = @.C2 THEN 'Inside SP: Equal' ELSE 'Inside SP: Not
Equal' END
GO

SET ANSI_NULLS ON
GO

GO

DECLARE @.C1 CHAR(1), @.C2 CHAR(1)
SELECT CASE WHEN @.C1 = @.C2 THEN 'Outside SP: Equal' ELSE 'Outside SP:
Not Equal' END

EXEC T1 @.c1, @.c2
GO

DROP PROCEDURE T1
GO

-------
Outside SP: Not Equal

(1 row(s) affected)

-------
Inside SP: Equal

(1 row(s) affected)