Non-clustered indexes on 1 separate fileGroup?

Hi Guys,

I am reviewing the storage structure of my growing db.

I am going to create multiple FileGroups and db files.
Most of the filegroups will be used to allow table partitioning and put the heavily used partitions on different storage than the read-only archives. TempDB will also have its own filegroup/storage.

For Indexes, I have a doubt. I am sure I can have all my non-clustered indexes on a separate fileGroup and storage but what about primary keys and/or clustered indexes?

Should I move them to the same filegroup than the non-clustered indexes or should I leave them with the data? What would be best?

I am running a 100GB reporting datamart on SQL2005 enterprise, There are almost no transactions, just a daily and weekly refresh. I will soon have more storage, SAN or NetApp, not sure yet.

Thanks,

Philippe

Hi

Moving your indexes to a seperate filegroup can improve the parallism of some querries that need to work with indexes and then do a bookmark lookup afterwards. If you have a VLDB like you said you should consider doing it, since the indexes cant be kept in memory all the time. So when you access the indexes it will not block the retrieval of your data from the other partition.

nonclustered indexes

Non-clustered is additional data with pointers to the actual data, Then
where the pointers are stored?
shineshine
Leaf level of NCI contains index rows. Each index row contains a non
clustered key and 'pointer/s' to the actual data.

> where the pointers are stored?
Index Page
"shine" <shine@.discussions.microsoft.com> wrote in message
news:1B4E3260-A560-48BD-A5BB-F78B2A1FFF90@.microsoft.com...
> Non-clustered is additional data with pointers to the actual data, Then
> where the pointers are stored?
> shine

Non-clustered index on a field and a "%" sign

Folks,
I'm not sure, but if you have a non-clustered index on a field and
you have "%" character as the first character of the search parameter
does this bypass the index? In other words, do you need a certain
amount of characters before the % sign in SQL Server 2000?
Any comments/knowledge much appreciated,
Thanks,
Al.
Not necessarily. It does mean that a scan will be done, not a seek. Here's
a case where it will scan the NC index:
1) You have a NC index on ColA.
2) You run: SELECT ColA FROM MyTable WHERE ColA LIKE '%XYZ'
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<almurph@.altavista.com> wrote in message
news:a8035a0d-7d2f-4ea4-9394-fc3b9b3c6247@.w34g2000hsg.googlegroups.com...
Folks,
I'm not sure, but if you have a non-clustered index on a field and
you have "%" character as the first character of the search parameter
does this bypass the index? In other words, do you need a certain
amount of characters before the % sign in SQL Server 2000?
Any comments/knowledge much appreciated,
Thanks,
Al.

Non-clustered index on a field and a "%" sign

Folks,
I'm not sure, but if you have a non-clustered index on a field and
you have "%" character as the first character of the search parameter
does this bypass the index? In other words, do you need a certain
amount of characters before the % sign in SQL Server 2000?
Any comments/knowledge much appreciated,
Thanks,
Al.Not necessarily. It does mean that a scan will be done, not a seek. Here's
a case where it will scan the NC index:
1) You have a NC index on ColA.
2) You run: SELECT ColA FROM MyTable WHERE ColA LIKE '%XYZ'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<almurph@.altavista.com> wrote in message
news:a8035a0d-7d2f-4ea4-9394-fc3b9b3c6247@.w34g2000hsg.googlegroups.com...
Folks,
I'm not sure, but if you have a non-clustered index on a field and
you have "%" character as the first character of the search parameter
does this bypass the index? In other words, do you need a certain
amount of characters before the % sign in SQL Server 2000?
Any comments/knowledge much appreciated,
Thanks,
Al.|||"almurph@.altavista.com" wrote:
> Folks,
> I'm not sure, but if you have a non-clustered index on a field and
> you have "%" character as the first character of the search parameter
> does this bypass the index? In other words, do you need a certain
> amount of characters before the % sign in SQL Server 2000?
> Any comments/knowledge much appreciated,
> Thanks,
> Al.
It disqualifies the seeking or partial scanning of the index. But as
mentioned by Tom, a full nonclustered index scan could still be used.
If you know that you will always have the % sign preceding the text, and
never after the text, then you could consider storing the data in
REVERSE order, or create a computed column on the REVERSE value, index
that, and use that column in your query.
For example: SELECT * FROM my_table WHERE reversed_domain LIKE
REVERSE('%.altavista.com')
Gert-Jan

Non-clustered index on a field and a "%" sign

Folks,
I'm not sure, but if you have a non-clustered index on a field and
you have "%" character as the first character of the search parameter
does this bypass the index? In other words, do you need a certain
amount of characters before the % sign in SQL Server 2000?
Any comments/knowledge much appreciated,
Thanks,
Al.Not necessarily. It does mean that a scan will be done, not a seek. Here's
a case where it will scan the NC index:
1) You have a NC index on ColA.
2) You run: SELECT ColA FROM MyTable WHERE ColA LIKE '%XYZ'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<almurph@.altavista.com> wrote in message
news:a8035a0d-7d2f-4ea4-9394-fc3b9b3c6247@.w34g2000hsg.googlegroups.com...
Folks,
I'm not sure, but if you have a non-clustered index on a field and
you have "%" character as the first character of the search parameter
does this bypass the index? In other words, do you need a certain
amount of characters before the % sign in SQL Server 2000?
Any comments/knowledge much appreciated,
Thanks,
Al.|||"almurph@.altavista.com" wrote:
> Folks,
> I'm not sure, but if you have a non-clustered index on a field and
> you have "%" character as the first character of the search parameter
> does this bypass the index? In other words, do you need a certain
> amount of characters before the % sign in SQL Server 2000?
> Any comments/knowledge much appreciated,
> Thanks,
> Al.
It disqualifies the seeking or partial scanning of the index. But as
mentioned by Tom, a full nonclustered index scan could still be used.
If you know that you will always have the % sign preceding the text, and
never after the text, then you could consider storing the data in
REVERSE order, or create a computed column on the REVERSE value, index
that, and use that column in your query.
For example: SELECT * FROM my_table WHERE reversed_domain LIKE
REVERSE('%.altavista.com')
--
Gert-Jan

non-clusted index and space

Hi I understand clustered and non-clustered indexes
increase the amount of disk space used i have a table that
is 200mb and wondered if I added a non-clsted index how
much extra space that would use and is it added to the
table size or somewhere else.
thanks for any help
MikeyMikey:
As for how much space an index will use, a lot depends on the type and
number of columns in the index. If you
EXEC sp_spaceused 'tablename'
before and after you create the index you can get a pretty good idea
of how much space the index requires.
Non clustered indexes would not change the size of the table, while a
clustered index 'becomes' the table.
HTH,
Scott
http://www.OdeToCode.com
On Thu, 8 Jan 2004 05:24:01 -0800, "Mikey"
<anonymous@.discussions.microsoft.com> wrote:

quote:

---

>Hi I understand clustered and non-clustered indexes
>increase the amount of disk space used i have a table that
>is 200mb and wondered if I added a non-clsted index how
>much extra space that would use and is it added to the
>table size or somewhere else.
>thanks for any help
>Mikey

---