Advice

Hi,
Nightly I am importing 8000 rows of data into a table with 3 billion rows.
The table has one clustered index. Should I disable the clustered index
before importing? If so where do I find info on disabling and enabling
clustered index on a column?
ThanksYou can't "disable" a clustered index, you would have to drop it and
recreate it. And doing that would be very costly with 3 billion rows. So,
you do not want to do that.
Tom
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:85B9A949-CDC1-410C-90C3-CC3502C055D3@.microsoft.com...
> Hi,
> Nightly I am importing 8000 rows of data into a table with 3 billion rows.
> The table has one clustered index. Should I disable the clustered index
> before importing? If so where do I find info on disabling and enabling
> clustered index on a column?
> Thanks

Advantages of using nonclustered index after using clustered index on one table

Hi everyone,
When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ?
In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ?

Thanks

Yes, you are correct. A nonclustered index like an index in a book "points" to a particular record which makes them ideal for exact matches queries. A NCI will either point to a rowid in a heap situation or the key in a CI-based table.

BOL 2005 excerpt:

If the underlying table is sorted using a clustered index, the location is the clustering key value; otherwise, the location is the row ID (RID) comprised of the file number, page number, and slot number of the row

HTH,

Derek

|||

see more info on the other post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=522859&SiteID=1

creation of Clustered index must happen first because

if NCIs are created first. The creation of a clustered index later will

force the recreation of all the NCIs to force it to use CI's rather than RID as locator

thereby causing a lot of trouble in the database.

|||

It is not entirely accurate that rows are sorted and stored in a clustered index. The b-tree structure ensures logical ordering of the rows and physically the rows may not be sorted in the same manner or present next to one another in a page for example. Please take a look at the link below for series of BOL topics on indexes:

http://msdn2.microsoft.com/en-us/library/ms189271.aspx

ADO Connection port with clustered servers

I am using ADO to connect to clustered SQL Servers, how do I know which port to use?
My current connection string format is:
Provider=sqloledb;Network Library=DBMSSOCN;Data Source=<server ip>,<server port>;Initial Catalog=<my catalog>;/<user>/<pass>
But with clustered servers this will not work as ports are assigned dynamically.
Thanks
If the client machine is using MDAC 2.6 or greater it will figure out the
port number.
MDAC sends a UDP packet to the server to gather this information before
sending the login packet.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||So should I just not include the port number in the connection string?
"Kevin McDonnell [MSFT]" wrote:

> If the client machine is using MDAC 2.6 or greater it will figure out the
> port number.
> MDAC sends a UDP packet to the server to gather this information before
> sending the login packet.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

ADO Connection port with clustered servers

I am using ADO to connect to clustered SQL Servers, how do I know which port
to use?
My current connection string format is:
Provider=sqloledb;Network Library=DBMSSOCN;Data Source=<server ip>,<server p
ort>;Initial Catalog=<my catalog>;/<user>/<pass>
But with clustered servers this will not work as ports are assigned dynamica
lly.
ThanksIf the client machine is using MDAC 2.6 or greater it will figure out the
port number.
MDAC sends a UDP packet to the server to gather this information before
sending the login packet.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||So should I just not include the port number in the connection string?
"Kevin McDonnell [MSFT]" wrote:

> If the client machine is using MDAC 2.6 or greater it will figure out the
> port number.
> MDAC sends a UDP packet to the server to gather this information before
> sending the login packet.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>