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...
>> 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
>|||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...
>> 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...
>> 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
>>
>|||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...
>> 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...
>> 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
>>
>>
>|||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...
>> 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...
>> 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...
>> 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
>>
>>
>>
>|||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...
>> 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...
>> 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...
>> 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
>>
>>
>>
>|||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...
>>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...
>> 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...
>> 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
>>
>>
>>
>>
>|||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...
>> 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...
>> 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...
>> 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...
>>> 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
>>>
>>
>>
>>
>>
>>
>|||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...
>> 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...
>>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...
>> 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...
>>> 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
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
>|||This is a multi-part message in MIME format.
--050308010900010509020903
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
I'd be looking to add more disk to your server. I was going to suggest
considering increasing the fillfactor on your clustered index(es) in
order to compact the tables a bit (and hence save some space) but this
would most likely result in more index fragmentation and in any case to
recreate an index you need about double the disk space used by the index
(which you don't have by the sounds of it).
This disk space issue is a bit of a problem. How would you rebuild your
indexes if you don't have enough disk space to do so? If I were you I'd
look at clearing some stuff off that disk to give more disk space,
checking fragmentation of your indexes (DBCC SHOWCONTIG (SQL 2000) or
sys.dm_db_index_physical_stats (SQL 2005)), possibly defragmenting your
indexes (DBCC INDEXDEFRAG (SQL 2000) or ALTER INDEX ... REORGANIZE (SQL
2005)) or rebuilding your indexes (DBCC DBREINDEX (SQL 2000) or ALTER
INDEX ... REBUILD (SQL 2005)). Is there any old data that you can purge
from your DB (after doing a full DB backup of course)?
Basically, not even having enough disk space to rebuild your largest
index is an issue. Disk space is dirt cheap these days - you ought to
be able to pick up a couple hundred gig (SATA) or at least 70G (SCSI)
for peanuts. Have a brief 1 hour outage to add your new disk, bring the
SQL instance back up, and add another file (on the new disk) to your
filegroup(s) to allow more room for this stuff. You might even consider
shuffling your data around or moving the entire DB to the new disk (with
sp_detach_db/sp_attach_db) thereby leaving your original disk to be OS &
SQL binaries only. I'd make acquiring more hardware a priority...that's
my 2c.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Eduardo Sicouret wrote:
>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...
>>
>>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...
>>
>>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...
>>
>>>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...
>>>
>>>
>>>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
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
>
--050308010900010509020903
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I'd be looking to add more disk to your server. I was going to
suggest considering increasing the fillfactor on your clustered
index(es) in order to compact the tables a bit (and hence save some
space) but this would most likely result in more index fragmentation
and in any case to recreate an index you need about double the disk
space used by the index (which you don't have by the sounds of it).<br>
<br>
This disk space issue is a bit of a problem. How would you rebuild
your indexes if you don't have enough disk space to do so? If I were
you I'd look at clearing some stuff off that disk to give more disk
space, checking fragmentation of your indexes (DBCC SHOWCONTIG (SQL
2000) or sys.dm_db_index_physical_stats (SQL 2005)), possibly
defragmenting your indexes (DBCC INDEXDEFRAG (SQL 2000) or ALTER INDEX
... REORGANIZE (SQL 2005)) or rebuilding your indexes (DBCC DBREINDEX
(SQL 2000) or ALTER INDEX ... REBUILD (SQL 2005)). Is there any old
data that you can purge from your DB (after doing a full DB backup of
course)?<br>
<br>
Basically, not even having enough disk space to rebuild your largest
index is an issue. Disk space is dirt cheap these days - you ought to
be able to pick up a couple hundred gig (SATA) or at least 70G (SCSI)
for peanuts. Have a brief 1 hour outage to add your new disk, bring
the SQL instance back up, and add another file (on the new disk) to
your filegroup(s) to allow more room for this stuff. You might even
consider shuffling your data around or moving the entire DB to the new
disk (with sp_detach_db/sp_attach_db) thereby leaving your original
disk to be OS & SQL binaries only. I'd make acquiring more
hardware a priority...that's my 2c.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Eduardo Sicouret wrote:
<blockquote cite="miduwkNFBffGHA.4568@.TK2MSFTNGP03.phx.gbl" type="cite">
<pre wrap="">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" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:replies@.public_newsgroups.com"><replies@.public_newsgroups.com></a> escribió en el mensaje
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:u0cPCrefGHA.2188@.TK2MSFTNGP04.phx.gbl">news:u0cPCrefGHA.2188@.TK2MSFTNGP04.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">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
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.solidqualitylearning.com</a>">http://www.solidqualitylearning.com">www.solidqualitylearning.com</a>
"Eduardo Sicouret" <esicouret> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:uQy0PlefGHA.4276@.TK2MSFTNGP03.phx.gbl">news:uQy0PlefGHA.4276@.TK2MSFTNGP03.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">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" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:replies@.public_newsgroups.com"><replies@.public_newsgroups.com></a> escribió en el mensaje
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:u7juBLefGHA.3456@.TK2MSFTNGP05.phx.gbl">news:u7juBLefGHA.3456@.TK2MSFTNGP05.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">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
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.solidqualitylearning.com</a>">http://www.solidqualitylearning.com">www.solidqualitylearning.com</a>
"Eduardo Sicouret" <esicouret> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:umHRwFefGHA.4828@.TK2MSFTNGP05.phx.gbl">news:umHRwFefGHA.4828@.TK2MSFTNGP05.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">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" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:replies@.public_newsgroups.com"><replies@.public_newsgroups.com></a> escribió en el mensaje
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:upIMcqdfGHA.4464@.TK2MSFTNGP04.phx.gbl">news:upIMcqdfGHA.4464@.TK2MSFTNGP04.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">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
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.solidqualitylearning.com</a>">http://www.solidqualitylearning.com">www.solidqualitylearning.com</a>
"Eduardo Sicouret" <esicouret> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:urRqyfdfGHA.4464@.TK2MSFTNGP04.phx.gbl">news:urRqyfdfGHA.4464@.TK2MSFTNGP04.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">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" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:amachanic@.hotmail._removetoemail_.com"><amachanic@.hotmail._removetoemail_.com></a> escribió en
el mensaje <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:O2QRGcdfGHA.3488@.TK2MSFTNGP02.phx.gbl">news:O2QRGcdfGHA.3488@.TK2MSFTNGP02.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">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
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.apress.com/book/bookDisplay.html?bID=457</a>">http://www.apress.com/book/bookDisplay.html?bID=457">http://www.apress.com/book/bookDisplay.html?bID=457</a>
--
"Eduardo Sicouret" <esicouret> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:OGROGUdfGHA.3652@.TK2MSFTNGP02.phx.gbl">news:OGROGUdfGHA.3652@.TK2MSFTNGP02.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">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
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--050308010900010509020903--
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment