Partial Indexes

Anyone knows when Partial Indexes (indexes created on the first n characters
of a char or varchar field) will be available in SQL Server?
Does SQL Server 2005 have this feature?
TIA
Eduardo SicouretYou can simulate it by creating an index on a computed column:
ALTER TABLE YourTable
ADD First10Characters AS (LEFT(YourColumn, 10))
CREATE INDEX IX_First10Characters
ON YourTable (First10Characters)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Eduardo Sicouret" <esicouret> wrote in message
news:OGROGUdfGHA.3652@.TK2MSFTNGP02.phx.gbl...
> Anyone knows when Partial Indexes (indexes created on the first n
> characters of a char or varchar field) will be available in SQL Server?
> Does SQL Server 2005 have this feature?
> TIA
> Eduardo Sicouret
>|||Thanks for answering.
I know that workaround is available, but my specific question is if "partial
indexes" are available in SQL Server 2005?
I don't have disk space to insert another column to my tables.
Regards,
Eduardo Sicouret
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escribi en el
mensaje news:O2QRGcdfGHA.3488@.TK2MSFTNGP02.phx.gbl...
> You can simulate it by creating an index on a computed column:
> ALTER TABLE YourTable
> ADD First10Characters AS (LEFT(YourColumn, 10))
> CREATE INDEX IX_First10Characters
> ON YourTable (First10Characters)
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:OGROGUdfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>|||Hi Eduardo
No, there is no such thing as partial indexes.
However, the disk space requirement for the partial index would be the same
as the disk space required for the index on the computed column. So how do
you figure you have disk space for an index but don't have disk space for
the computed column?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Eduardo Sicouret" <esicouret> wrote in message
news:urRqyfdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
> Thanks for answering.
> I know that workaround is available, but my specific question is if
> "partial indexes" are available in SQL Server 2005?
> I don't have disk space to insert another column to my tables.
> Regards,
> Eduardo Sicouret
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escribi en el
> mensaje news:O2QRGcdfGHA.3488@.TK2MSFTNGP02.phx.gbl...
>|||I will have the space of the computed column + the space of the index on the
computed column...
That will be 2 times the computed column...
Regards,
Eduardo Sicouret
"Kalen Delaney" <replies@.public_newsgroups.com> escribi en el mensaje
news:upIMcqdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
> Hi Eduardo
> No, there is no such thing as partial indexes.
> However, the disk space requirement for the partial index would be the
> same as the disk space required for the index on the computed column. So
> how do you figure you have disk space for an index but don't have disk
> space for the computed column?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:urRqyfdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
>|||Untrue. Computed columns are not persisted, unless you use SQL Server
2005's "persisted computed column" feature, explicitly (by adding the
keyword PERSISTED after the column definition).
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Eduardo Sicouret" <esicouret> wrote in message
news:umHRwFefGHA.4828@.TK2MSFTNGP05.phx.gbl...
>I will have the space of the computed column + the space of the index on
>the computed column...
> That will be 2 times the computed column...
> Regards,
> Eduardo Sicouret
> "Kalen Delaney" <replies@.public_newsgroups.com> escribi en el mensaje
> news:upIMcqdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
>|||The computed column takes no space until you create the index on it.
How big is the partial column you think you need to index and how are
computing the size of the index?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Eduardo Sicouret" <esicouret> wrote in message
news:umHRwFefGHA.4828@.TK2MSFTNGP05.phx.gbl...
>I will have the space of the computed column + the space of the index on
>the computed column...
> That will be 2 times the computed column...
> Regards,
> Eduardo Sicouret
> "Kalen Delaney" <replies@.public_newsgroups.com> escribi en el mensaje
> news:upIMcqdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
>|||didn't know that computed columns takes no space...
I will test this workaround to see if it works. The problem is that my
tables are 2 million records big so increasing a column has a considerably
big impact.
Regards,
Eduardo Sicouret
"Kalen Delaney" <replies@.public_newsgroups.com> escribi en el mensaje
news:u7juBLefGHA.3456@.TK2MSFTNGP05.phx.gbl...
> The computed column takes no space until you create the index on it.
> How big is the partial column you think you need to index and how are
> computing the size of the index?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:umHRwFefGHA.4828@.TK2MSFTNGP05.phx.gbl...
>|||2 million is not all that may rows at all. I occasionally create tables with
2 million rows just for testing purposes.
How big is the partial column you want to index? How much free space do you
have?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Eduardo Sicouret" <esicouret> wrote in message
news:uQy0PlefGHA.4276@.TK2MSFTNGP03.phx.gbl...
> didn't know that computed columns takes no space...
> I will test this workaround to see if it works. The problem is that my
> tables are 2 million records big so increasing a column has a considerably
> big impact.
> Regards,
> Eduardo Sicouret
> "Kalen Delaney" <replies@.public_newsgroups.com> escribi en el mensaje
> news:u7juBLefGHA.3456@.TK2MSFTNGP05.phx.gbl...
>|||Unfortunately I have only one 16GB disk and 700MB free after truncating and
shrinking the log. And this disk is also for the O.S.
The main table is accesed several times a second, so increasing a column is
a difficult task. table is 1.7GB big.
To reduce the indexes size I thought of a solution like partial indexing.
regards...
Eduardo Sicouret
"Kalen Delaney" <replies@.public_newsgroups.com> escribi en el mensaje
news:u0cPCrefGHA.2188@.TK2MSFTNGP04.phx.gbl...
>2 million is not all that may rows at all. I occasionally create tables
>with 2 million rows just for testing purposes.
> How big is the partial column you want to index? How much free space do
> you have?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:uQy0PlefGHA.4276@.TK2MSFTNGP03.phx.gbl...
>
>