Partial Index

[Maggy]

Can someone tell if it's possible to partially index a
varchar. For instance, I have a varchar(40) field, but I
don't want to index all 40 characteres; I want to index
the first 15 characters. Is this possible? And if it is,
how might one write the SQL statement. I've tried using
the Substring function, but it has failed to work.

Thanks,

Maggy

[Carlos Sacristan]

I thinks it is possible, but you have to change some things. Look this
example:

CREATE TABLE [dbo].[example] (
[a] [varchar] (40) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
[b] AS (left([a],15))
) ON [PRIMARY]
GO

set
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIF
IER,ANSI_NULLS on
GO

set NUMERIC_ROUNDABORT off
GO

CREATE INDEX [IX_example] ON [dbo].[example]([b]) ON [PRIMARY]
GO

set arithabort OFF
GO

set numeric_roundabort OFF
GO

set quoted_identifier OFF
GO



--
--

Un saludo

--
--
----------------------------------------------
"Sólo sé que no sé nada. " (Sócrates)

(Guía de netiquette del foro)
[url]http://www.helpdna.net/bosqlfaq00.htm[/url]
[url]http://perso.wanadoo.es/rubenvigon/foro[/url]

(FAQ's de SQL Server)
[url]http://support.microsoft.com/default.aspx?scid=/support/sql/70faq.asp[/url]
[url]http://www.helpdna.net/bosqlfaq.htm[/url]

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> escribió en el mensaje
news:enYUuaJQDHA.1584@TK2MSFTNGP11.phx.gbl...

> It is not possible to do that at this time.
>
> --
>
> Andrew J. Kelly
> SQL Server MVP
>
>
> "Maggy" <rmagnus@solutran.com> wrote in message
> news:407601c34096$23da5890$a401280a@phx.gbl...

> > Can someone tell if it's possible to partially index a
> > varchar. For instance, I have a varchar(40) field, but I
> > don't want to index all 40 characteres; I want to index
> > the first 15 characters. Is this possible? And if it is,
> > how might one write the SQL statement. I've tried using
> > the Substring function, but it has failed to work.
> >
> > Thanks,
> >
> > Maggy

>
>

[Andrew J. Kelly]

Well yes, if you want to create a new column then you can index it. But you
can't use expressions in the index definition itself.

--

Andrew J. Kelly
SQL Server MVP


"Carlos Sacristan" <csacristanARROBAocasoPUNTOes> wrote in message
news:ONOhrjJQDHA.2256@TK2MSFTNGP11.phx.gbl...

>
> I thinks it is possible, but you have to change some things. Look this
> example:
>
> CREATE TABLE [dbo].[example] (
> [a] [varchar] (40) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
> [b] AS (left([a],15))
> ) ON [PRIMARY]
> GO
>
> set
>

ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIF

> IER,ANSI_NULLS on
> GO
>
> set NUMERIC_ROUNDABORT off
> GO
>
> CREATE INDEX [IX_example] ON [dbo].[example]([b]) ON [PRIMARY]
> GO
>
> set arithabort OFF
> GO
>
> set numeric_roundabort OFF
> GO
>
> set quoted_identifier OFF
> GO
>
>
>
> --
> --
>
> Un saludo
>
> --
> --
> ----------------------------------------------
> "Sólo sé que no sé nada. " (Sócrates)
>
> (Guía de netiquette del foro)
> [url]http://www.helpdna.net/bosqlfaq00.htm[/url]
> [url]http://perso.wanadoo.es/rubenvigon/foro[/url]
>
> (FAQ's de SQL Server)
> [url]http://support.microsoft.com/default.aspx?scid=/support/sql/70faq.asp[/url]
> [url]http://www.helpdna.net/bosqlfaq.htm[/url]
>
> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> escribió en el mensaje
> news:enYUuaJQDHA.1584@TK2MSFTNGP11.phx.gbl...

> > It is not possible to do that at this time.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Maggy" <rmagnus@solutran.com> wrote in message
> > news:407601c34096$23da5890$a401280a@phx.gbl...

> > > Can someone tell if it's possible to partially index a
> > > varchar. For instance, I have a varchar(40) field, but I
> > > don't want to index all 40 characteres; I want to index
> > > the first 15 characters. Is this possible? And if it is,
> > > how might one write the SQL statement. I've tried using
> > > the Substring function, but it has failed to work.
> > >
> > > Thanks,
> > >
> > > Maggy

> >
> >

>
>

[Carlos Sacristan]

That's because I said to Maggy 'you have to change some things' ;-)


--
--

Un saludo

--
--
----------------------------------------------
"Sólo sé que no sé nada. " (Sócrates)

(Guía de netiquette del foro)
[url]http://www.helpdna.net/bosqlfaq00.htm[/url]
[url]http://perso.wanadoo.es/rubenvigon/foro[/url]

(FAQ's de SQL Server)
[url]http://support.microsoft.com/default.aspx?scid=/support/sql/70faq.asp[/url]
[url]http://www.helpdna.net/bosqlfaq.htm[/url]

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> escribió en el mensaje
news:ug$v$wJQDHA.3192@TK2MSFTNGP10.phx.gbl...

> Well yes, if you want to create a new column then you can index it. But

you

> can't use expressions in the index definition itself.
>
> --
>
> Andrew J. Kelly
> SQL Server MVP
>
>
> "Carlos Sacristan" <csacristanARROBAocasoPUNTOes> wrote in message
> news:ONOhrjJQDHA.2256@TK2MSFTNGP11.phx.gbl...

> >
> > I thinks it is possible, but you have to change some things. Look

this

> > example:
> >
> > CREATE TABLE [dbo].[example] (
> > [a] [varchar] (40) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
> > [b] AS (left([a],15))
> > ) ON [PRIMARY]
> > GO
> >
> > set
> >

>

ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIF

> > IER,ANSI_NULLS on
> > GO
> >
> > set NUMERIC_ROUNDABORT off
> > GO
> >
> > CREATE INDEX [IX_example] ON [dbo].[example]([b]) ON [PRIMARY]
> > GO
> >
> > set arithabort OFF
> > GO
> >
> > set numeric_roundabort OFF
> > GO
> >
> > set quoted_identifier OFF
> > GO
> >
> >
> >
> > --
> > --
> >
> > Un saludo
> >
> > --
> > --
> > ----------------------------------------------
> > "Sólo sé que no sé nada. " (Sócrates)
> >
> > (Guía de netiquette del foro)
> > [url]http://www.helpdna.net/bosqlfaq00.htm[/url]
> > [url]http://perso.wanadoo.es/rubenvigon/foro[/url]
> >
> > (FAQ's de SQL Server)
> > [url]http://support.microsoft.com/default.aspx?scid=/support/sql/70faq.asp[/url]
> > [url]http://www.helpdna.net/bosqlfaq.htm[/url]
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> escribió en el mensaje
> > news:enYUuaJQDHA.1584@TK2MSFTNGP11.phx.gbl...

> > > It is not possible to do that at this time.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Maggy" <rmagnus@solutran.com> wrote in message
> > > news:407601c34096$23da5890$a401280a@phx.gbl...
> > > > Can someone tell if it's possible to partially index a
> > > > varchar. For instance, I have a varchar(40) field, but I
> > > > don't want to index all 40 characteres; I want to index
> > > > the first 15 characters. Is this possible? And if it is,
> > > > how might one write the SQL statement. I've tried using
> > > > the Substring function, but it has failed to work.
> > > >
> > > > Thanks,
> > > >
> > > > Maggy
> > >
> > >

> >
> >

>
>