Need to create case sensitive Uniqiue Indexes

Hi,

How Can I create a case sensitive unique index so that A1 and a1 are treated
as different ?

I dont mind if I have to make a global DB change.

SteveSteve Thorpe (stephenthorpe@.nospam.hotmail.com) writes:
> How Can I create a case sensitive unique index so that A1 and a1 are
> treated as different ?
> I dont mind if I have to make a global DB change.

You should change the database to use a case-sensitive collation - and
maybe the entire server, since collation mix on the same server can
cause problems with tempdb.

You can achieve this in several ways. You can bulk out all data and
build a new database from scripts where you have changed the collation
and then bulk data back. You can also issue ALTER TABLE ALTER COLUMN
for all columns with character data. You also need to use ALTER COLLATION
SET COLLATION. (This latter command sets the default for future columns,
but does not affect existing ones.)

To change the collation for master, you use the rebuildm facility.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Need some knowledge about indexes

Hi
I need to get my knowledge about indexes expanded a little bit...
What's the best/easiest way to view Indexes and their definition in SQL
server. I can look in sysindexes, but as I see it it only shows me the
different indexes by name, but not so much about the definition etc. (or
maybe I just don't know what to look for). I can also use sp_helpindex, but
that only shows me very little as well. I know I can look up the difinition
in EM, but that's a bit cumbersome to click through all tables to see it.
Reason for asking is that I have 2 almost identical databases, where on one
of them there's a number of indexes defined. These Indexes I'd like to
create in the second database as well.
Best Regards
Steen PerssonSteen
Run this script in QA on source database .
SELECT s1.name, s2.name,
INDEX_COL( s1.name, s2.indid, 1 ),
CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
WHEN 1 THEN 'Clustered'
ELSE 'Non-clustered'
END
FROM sysobjects s1
INNER JOIN sysindexes s2
ON s1.id = s2.id
WHERE s1.xtype = 'U'
AND s2.indid > 0 AND s2.indid < 255
AND s2.name not like '_WA_Sys%'
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>|||Hi,
You could join the below system tables to get all the details of Indexes:-
sysindexes (name and indid columns)
sysindexkeys (object_id,colid)
syscolumns (colid,name)
Note:-
Querying the system tables might not be a good option... But for your query
I could see only this solution
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>|||Great...that helps a lot...
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Steen
> Run this script in QA on source database .
> SELECT s1.name, s2.name,
> INDEX_COL( s1.name, s2.indid, 1 ),
> CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
> WHEN 1 THEN 'Clustered'
> ELSE 'Non-clustered'
> END
> FROM sysobjects s1
> INNER JOIN sysindexes s2
> ON s1.id = s2.id
> WHERE s1.xtype = 'U'
> AND s2.indid > 0 AND s2.indid < 255
> AND s2.name not like '_WA_Sys%'
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...

Need some knowledge about indexes

Hi
I need to get my knowledge about indexes expanded a little bit...
What's the best/easiest way to view Indexes and their definition in SQL
server. I can look in sysindexes, but as I see it it only shows me the
different indexes by name, but not so much about the definition etc. (or
maybe I just don't know what to look for). I can also use sp_helpindex, but
that only shows me very little as well. I know I can look up the difinition
in EM, but that's a bit cumbersome to click through all tables to see it.
Reason for asking is that I have 2 almost identical databases, where on one
of them there's a number of indexes defined. These Indexes I'd like to
create in the second database as well.
Best Regards
Steen PerssonSteen
Run this script in QA on source database .
SELECT s1.name, s2.name,
INDEX_COL( s1.name, s2.indid, 1 ),
CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
WHEN 1 THEN 'Clustered'
ELSE 'Non-clustered'
END
FROM sysobjects s1
INNER JOIN sysindexes s2
ON s1.id = s2.id
WHERE s1.xtype = 'U'
AND s2.indid > 0 AND s2.indid < 255
AND s2.name not like '_WA_Sys%'
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>|||Hi,
You could join the below system tables to get all the details of Indexes:-
sysindexes (name and indid columns)
sysindexkeys (object_id,colid)
syscolumns (colid,name)
Note:-
Querying the system tables might not be a good option... But for your query
I could see only this solution
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>|||Great...that helps a lot...
Regards
Steen
Uri Dimant wrote:
> Steen
> Run this script in QA on source database .
> SELECT s1.name, s2.name,
> INDEX_COL( s1.name, s2.indid, 1 ),
> CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
> WHEN 1 THEN 'Clustered'
> ELSE 'Non-clustered'
> END
> FROM sysobjects s1
> INNER JOIN sysindexes s2
> ON s1.id = s2.id
> WHERE s1.xtype = 'U'
> AND s2.indid > 0 AND s2.indid < 255
> AND s2.name not like '_WA_Sys%'
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
>> Hi
>> I need to get my knowledge about indexes expanded a little bit...
>> What's the best/easiest way to view Indexes and their definition in
>> SQL server. I can look in sysindexes, but as I see it it only shows
>> me the different indexes by name, but not so much about the
>> definition etc. (or maybe I just don't know what to look for). I can
>> also use sp_helpindex, but that only shows me very little as well. I
>> know I can look up the difinition in EM, but that's a bit cumbersome
>> to click through all tables to see it. Reason for asking is that I
>> have 2 almost identical databases, where on one of them there's a
>> number of indexes defined. These Indexes I'd like to create in the
>> second database as well.
>> Best Regards
>> Steen Persson

Need some knowledge about indexes

Hi
I need to get my knowledge about indexes expanded a little bit...
What's the best/easiest way to view Indexes and their definition in SQL
server. I can look in sysindexes, but as I see it it only shows me the
different indexes by name, but not so much about the definition etc. (or
maybe I just don't know what to look for). I can also use sp_helpindex, but
that only shows me very little as well. I know I can look up the difinition
in EM, but that's a bit cumbersome to click through all tables to see it.
Reason for asking is that I have 2 almost identical databases, where on one
of them there's a number of indexes defined. These Indexes I'd like to
create in the second database as well.
Best Regards
Steen Persson
Steen
Run this script in QA on source database .
SELECT s1.name, s2.name,
INDEX_COL( s1.name, s2.indid, 1 ),
CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
WHEN 1 THEN 'Clustered'
ELSE 'Non-clustered'
END
FROM sysobjects s1
INNER JOIN sysindexes s2
ON s1.id = s2.id
WHERE s1.xtype = 'U'
AND s2.indid > 0 AND s2.indid < 255
AND s2.name not like '_WA_Sys%'
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>
|||Hi,
You could join the below system tables to get all the details of Indexes:-
sysindexes (name and indid columns)
sysindexkeys (object_id,colid)
syscolumns (colid,name)
Note:-
Querying the system tables might not be a good option... But for your query
I could see only this solution
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>
|||Great...that helps a lot...
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Steen
> Run this script in QA on source database .
> SELECT s1.name, s2.name,
> INDEX_COL( s1.name, s2.indid, 1 ),
> CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
> WHEN 1 THEN 'Clustered'
> ELSE 'Non-clustered'
> END
> FROM sysobjects s1
> INNER JOIN sysindexes s2
> ON s1.id = s2.id
> WHERE s1.xtype = 'U'
> AND s2.indid > 0 AND s2.indid < 255
> AND s2.name not like '_WA_Sys%'
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...

Need magic trick to set index as PK

Let's say that you have a 10gb table, 23m rows, and somehow it was created
and populated with ten indexes, including one that is clustered and unique,
but none of them seems to be marked as the Primary Key. Is there any magic
trick we can do to get it instantly marked as Primary Key?
This is SQLServer 2005.
What we ... er, that is, my hypothetical friend ... does not want to do, is
to drop the clustered index, rewriting the 10gb table, then define the PK,
rewriting the 10gb table again. I believe I read that SQLServer 2005 has a
new trick that might let it move the clustered index in *one* copy instead of
two, but how about *no* copies instead of one?
It occurs to me as I write this, that dropping all the secondary indexes
might be a good first step.
Thanks for good advice,
Josh
ps - the clustered unique index that is not the PK, is already conveniently
*named* XPKblahblahblah.
pps - will it make any difference to anything that uses that database, that
we do have the unique clustered key on the right fields, but it is not the
Primary Key?
Beleive it or not, if you fully qualify the foreign key constraint, you will
be able to create it against the unique index.
Wierd, but true.
"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:9D5E95E4-685A-4D16-8F85-21B4DA4355B6@.microsoft.com...
> Let's say that you have a 10gb table, 23m rows, and somehow it was created
> and populated with ten indexes, including one that is clustered and
> unique,
> but none of them seems to be marked as the Primary Key. Is there any
> magic
> trick we can do to get it instantly marked as Primary Key?
> This is SQLServer 2005.
> What we ... er, that is, my hypothetical friend ... does not want to do,
> is
> to drop the clustered index, rewriting the 10gb table, then define the PK,
> rewriting the 10gb table again. I believe I read that SQLServer 2005 has
> a
> new trick that might let it move the clustered index in *one* copy instead
> of
> two, but how about *no* copies instead of one?
> It occurs to me as I write this, that dropping all the secondary indexes
> might be a good first step.
> Thanks for good advice,
> Josh
> ps - the clustered unique index that is not the PK, is already
> conveniently
> *named* XPKblahblahblah.
> pps - will it make any difference to anything that uses that database,
> that
> we do have the unique clustered key on the right fields, but it is not the
> Primary Key?
|||On Fri, 9 Nov 2007 16:45:05 -0800, "Jay" <nospam@.nospam.org> wrote:

>Beleive it or not, if you fully qualify the foreign key constraint, you will
>be able to create it against the unique index.
>Wierd, but true.
Right, was doing that on SQL7 when replication demanded PK be a GUID.
I'd still like to fiddle the bits so the little gold keys show up on
the index.
(kind of academic now, actually, the guy involved spent the two hours
rewriting the 10gb table, bless the new fast hardware!)
(and actually, with the new SQL2005 features, I guess the trick is you
DON'T first delete the secondary indexes)
J.

>"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
>news:9D5E95E4-685A-4D16-8F85-21B4DA4355B6@.microsoft.com...
>

Need magic trick to set index as PK

Let's say that you have a 10gb table, 23m rows, and somehow it was created
and populated with ten indexes, including one that is clustered and unique,
but none of them seems to be marked as the Primary Key. Is there any magic
trick we can do to get it instantly marked as Primary Key?
This is SQLServer 2005.
What we ... er, that is, my hypothetical friend ... does not want to do, is
to drop the clustered index, rewriting the 10gb table, then define the PK,
rewriting the 10gb table again. I believe I read that SQLServer 2005 has a
new trick that might let it move the clustered index in *one* copy instead of
two, but how about *no* copies instead of one'
It occurs to me as I write this, that dropping all the secondary indexes
might be a good first step.
Thanks for good advice,
Josh
ps - the clustered unique index that is not the PK, is already conveniently
*named* XPKblahblahblah.
pps - will it make any difference to anything that uses that database, that
we do have the unique clustered key on the right fields, but it is not the
Primary Key?Beleive it or not, if you fully qualify the foreign key constraint, you will
be able to create it against the unique index.
Wierd, but true.
"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:9D5E95E4-685A-4D16-8F85-21B4DA4355B6@.microsoft.com...
> Let's say that you have a 10gb table, 23m rows, and somehow it was created
> and populated with ten indexes, including one that is clustered and
> unique,
> but none of them seems to be marked as the Primary Key. Is there any
> magic
> trick we can do to get it instantly marked as Primary Key?
> This is SQLServer 2005.
> What we ... er, that is, my hypothetical friend ... does not want to do,
> is
> to drop the clustered index, rewriting the 10gb table, then define the PK,
> rewriting the 10gb table again. I believe I read that SQLServer 2005 has
> a
> new trick that might let it move the clustered index in *one* copy instead
> of
> two, but how about *no* copies instead of one'
> It occurs to me as I write this, that dropping all the secondary indexes
> might be a good first step.
> Thanks for good advice,
> Josh
> ps - the clustered unique index that is not the PK, is already
> conveniently
> *named* XPKblahblahblah.
> pps - will it make any difference to anything that uses that database,
> that
> we do have the unique clustered key on the right fields, but it is not the
> Primary Key?|||On Fri, 9 Nov 2007 16:45:05 -0800, "Jay" <nospam@.nospam.org> wrote:
>Beleive it or not, if you fully qualify the foreign key constraint, you will
>be able to create it against the unique index.
>Wierd, but true.
Right, was doing that on SQL7 when replication demanded PK be a GUID.
I'd still like to fiddle the bits so the little gold keys show up on
the index.
(kind of academic now, actually, the guy involved spent the two hours
rewriting the 10gb table, bless the new fast hardware!)
(and actually, with the new SQL2005 features, I guess the trick is you
DON'T first delete the secondary indexes)
J.
>"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
>news:9D5E95E4-685A-4D16-8F85-21B4DA4355B6@.microsoft.com...
>> Let's say that you have a 10gb table, 23m rows, and somehow it was created
>> and populated with ten indexes, including one that is clustered and
>> unique,
>> but none of them seems to be marked as the Primary Key. Is there any
>> magic
>> trick we can do to get it instantly marked as Primary Key?
>> This is SQLServer 2005.
>> What we ... er, that is, my hypothetical friend ... does not want to do,
>> is
>> to drop the clustered index, rewriting the 10gb table, then define the PK,
>> rewriting the 10gb table again. I believe I read that SQLServer 2005 has
>> a
>> new trick that might let it move the clustered index in *one* copy instead
>> of
>> two, but how about *no* copies instead of one'
>> It occurs to me as I write this, that dropping all the secondary indexes
>> might be a good first step.
>> Thanks for good advice,
>> Josh
>> ps - the clustered unique index that is not the PK, is already
>> conveniently
>> *named* XPKblahblahblah.
>> pps - will it make any difference to anything that uses that database,
>> that
>> we do have the unique clustered key on the right fields, but it is not the
>> Primary Key?
>

Need magic trick to set index as PK

Let's say that you have a 10gb table, 23m rows, and somehow it was created
and populated with ten indexes, including one that is clustered and unique,
but none of them seems to be marked as the Primary Key. Is there any magic
trick we can do to get it instantly marked as Primary Key?
This is SQLServer 2005.
What we ... er, that is, my hypothetical friend ... does not want to do, is
to drop the clustered index, rewriting the 10gb table, then define the PK,
rewriting the 10gb table again. I believe I read that SQLServer 2005 has a
new trick that might let it move the clustered index in *one* copy instead o
f
two, but how about *no* copies instead of one'
It occurs to me as I write this, that dropping all the secondary indexes
might be a good first step.
Thanks for good advice,
Josh
ps - the clustered unique index that is not the PK, is already conveniently
*named* XPKblahblahblah.
pps - will it make any difference to anything that uses that database, that
we do have the unique clustered key on the right fields, but it is not the
Primary Key?Beleive it or not, if you fully qualify the foreign key constraint, you will
be able to create it against the unique index.
Wierd, but true.
"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:9D5E95E4-685A-4D16-8F85-21B4DA4355B6@.microsoft.com...
> Let's say that you have a 10gb table, 23m rows, and somehow it was created
> and populated with ten indexes, including one that is clustered and
> unique,
> but none of them seems to be marked as the Primary Key. Is there any
> magic
> trick we can do to get it instantly marked as Primary Key?
> This is SQLServer 2005.
> What we ... er, that is, my hypothetical friend ... does not want to do,
> is
> to drop the clustered index, rewriting the 10gb table, then define the PK,
> rewriting the 10gb table again. I believe I read that SQLServer 2005 has
> a
> new trick that might let it move the clustered index in *one* copy instead
> of
> two, but how about *no* copies instead of one'
> It occurs to me as I write this, that dropping all the secondary indexes
> might be a good first step.
> Thanks for good advice,
> Josh
> ps - the clustered unique index that is not the PK, is already
> conveniently
> *named* XPKblahblahblah.
> pps - will it make any difference to anything that uses that database,
> that
> we do have the unique clustered key on the right fields, but it is not the
> Primary Key?|||On Fri, 9 Nov 2007 16:45:05 -0800, "Jay" <nospam@.nospam.org> wrote:

>Beleive it or not, if you fully qualify the foreign key constraint, you wil
l
>be able to create it against the unique index.
>Wierd, but true.
Right, was doing that on SQL7 when replication demanded PK be a GUID.
I'd still like to fiddle the bits so the little gold keys show up on
the index.
(kind of academic now, actually, the guy involved spent the two hours
rewriting the 10gb table, bless the new fast hardware!)
(and actually, with the new SQL2005 features, I guess the trick is you
DON'T first delete the secondary indexes)
J.

>"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
>news:9D5E95E4-685A-4D16-8F85-21B4DA4355B6@.microsoft.com...
>

Need input...Creating Indexes on 37 million row table...

Hello, all. Looking for suggestions, input,
recommendations, etc. I'm running SQL 2K.
I have a table with 37 million rows that I need to create
about 5 new indexes on. The columns already exist. The
db is used for datawarehousing, and contains static
data...the only update is a monthly insert of about 300K
records. Other than that, users only query it all day.
The db is also running in Simple Recovery mode (no need to
log any transactions).
Realizing this will take a LONG time to run, I'd like to
find out the best/most efficient way/with minimum downtime
to get this done. Luckily, I have the luxury of
restricted access to the db while I perform this, if need
be.
So, I'd like to hear from the gurus on how to do this.
Thanks
RozThere really is no faster way to create an index other than to make sure
your log file (even in simple mode) is on a separate raid array than the
data or tempdb. If you have tempdb on a separate array than the data you
can specify the sort in tempdb option to speed it up some.
Andrew J. Kelly SQL MVP
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2746101c4636e$82f9ae10$a501280a@.phx
.gbl...
> Hello, all. Looking for suggestions, input,
> recommendations, etc. I'm running SQL 2K.
> I have a table with 37 million rows that I need to create
> about 5 new indexes on. The columns already exist. The
> db is used for datawarehousing, and contains static
> data...the only update is a monthly insert of about 300K
> records. Other than that, users only query it all day.
> The db is also running in Simple Recovery mode (no need to
> log any transactions).
> Realizing this will take a LONG time to run, I'd like to
> find out the best/most efficient way/with minimum downtime
> to get this done. Luckily, I have the luxury of
> restricted access to the db while I perform this, if need
> be.
> So, I'd like to hear from the gurus on how to do this.
> Thanks
> Roz

Need input...Creating Indexes on 37 million row table...

Hello, all. Looking for suggestions, input,
recommendations, etc. I'm running SQL 2K.
I have a table with 37 million rows that I need to create
about 5 new indexes on. The columns already exist. The
db is used for datawarehousing, and contains static
data...the only update is a monthly insert of about 300K
records. Other than that, users only query it all day.
The db is also running in Simple Recovery mode (no need to
log any transactions).
Realizing this will take a LONG time to run, I'd like to
find out the best/most efficient way/with minimum downtime
to get this done. Luckily, I have the luxury of
restricted access to the db while I perform this, if need
be.
So, I'd like to hear from the gurus on how to do this.
Thanks
Roz
There really is no faster way to create an index other than to make sure
your log file (even in simple mode) is on a separate raid array than the
data or tempdb. If you have tempdb on a separate array than the data you
can specify the sort in tempdb option to speed it up some.
Andrew J. Kelly SQL MVP
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2746101c4636e$82f9ae10$a501280a@.phx.gbl...
> Hello, all. Looking for suggestions, input,
> recommendations, etc. I'm running SQL 2K.
> I have a table with 37 million rows that I need to create
> about 5 new indexes on. The columns already exist. The
> db is used for datawarehousing, and contains static
> data...the only update is a monthly insert of about 300K
> records. Other than that, users only query it all day.
> The db is also running in Simple Recovery mode (no need to
> log any transactions).
> Realizing this will take a LONG time to run, I'd like to
> find out the best/most efficient way/with minimum downtime
> to get this done. Luckily, I have the luxury of
> restricted access to the db while I perform this, if need
> be.
> So, I'd like to hear from the gurus on how to do this.
> Thanks
> Roz

Need input...Creating Indexes on 37 million row table...

Hello, all. Looking for suggestions, input,
recommendations, etc. I'm running SQL 2K.
I have a table with 37 million rows that I need to create
about 5 new indexes on. The columns already exist. The
db is used for datawarehousing, and contains static
data...the only update is a monthly insert of about 300K
records. Other than that, users only query it all day.
The db is also running in Simple Recovery mode (no need to
log any transactions).
Realizing this will take a LONG time to run, I'd like to
find out the best/most efficient way/with minimum downtime
to get this done. Luckily, I have the luxury of
restricted access to the db while I perform this, if need
be.
So, I'd like to hear from the gurus on how to do this.
Thanks
RozThere really is no faster way to create an index other than to make sure
your log file (even in simple mode) is on a separate raid array than the
data or tempdb. If you have tempdb on a separate array than the data you
can specify the sort in tempdb option to speed it up some.
--
Andrew J. Kelly SQL MVP
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2746101c4636e$82f9ae10$a501280a@.phx.gbl...
> Hello, all. Looking for suggestions, input,
> recommendations, etc. I'm running SQL 2K.
> I have a table with 37 million rows that I need to create
> about 5 new indexes on. The columns already exist. The
> db is used for datawarehousing, and contains static
> data...the only update is a monthly insert of about 300K
> records. Other than that, users only query it all day.
> The db is also running in Simple Recovery mode (no need to
> log any transactions).
> Realizing this will take a LONG time to run, I'd like to
> find out the best/most efficient way/with minimum downtime
> to get this done. Luckily, I have the luxury of
> restricted access to the db while I perform this, if need
> be.
> So, I'd like to hear from the gurus on how to do this.
> Thanks
> Roz