Professional Web Applications Themes

Bulk Copy and Clustered Index - Microsoft SQL / MS SQL Server

Hi all, When doing a Bulk Copy to load large amount of data into a table, I was told that it would be wise to (I don't have any existing data in that table) 1) Drop all th existing indexes 2) Bulk copy the data into the table 3) Rebuild the indexes However if one of the indexes is a clustered index, would the index-rebuilding process take a very long time (since SQL Server rearrange the phyical data around the clustered index) ? My question is : Should I just leave the indexes as they are when doing bulk-copy? or ...

  1. #1

    Default Bulk Copy and Clustered Index

    Hi all,

    When doing a Bulk Copy to load large amount of
    data into a table, I was told that it would be wise to

    (I don't have any existing data in that table)

    1) Drop all th existing indexes
    2) Bulk copy the data into the table
    3) Rebuild the indexes

    However if one of the indexes is a clustered index,
    would the index-rebuilding process take a very long
    time (since SQL Server rearrange the phyical data
    around the clustered index) ?

    My question is :

    Should I just leave the indexes as they are when doing bulk-copy?
    or should I drop the indexes, bulk copy the data, and rebuild the indexes?

    Thanks in advance,

    Frank Cheng






    Frank Guest

  2. #2

    Default Bulk Copy and Clustered Index

    I would drop the indexes, import the data in order of the
    column your clustered index will be on and then add your
    clustered index, that should cut down on page splitting
    and data rearrangement and have a nice clean table with
    minimal fragmentation.

    HTH

    Ray Higdon MCSE, MCDBA, CCNA

     
    bulk-copy? 
    rebuild the indexes? 
    Ray Guest

  3. #3

    Default Re: Bulk Copy and Clustered Index

    If you can load in the cluster key order you should leave the clustered
    index in place and use the ORDER BY hint in BCP.EXE

    Net

    Please reply only to the newsgroups.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    You assume all risk for your use.
    Copyright © SQLDev.Net 1991-2003 All rights reserved.

    "Ray Higdon" <com> wrote in message
    news:920e01c35b8a$bec06480$gbl... 
    > bulk-copy? 
    > rebuild the indexes? [/ref]


    Gert Guest

Similar Threads

  1. bulk copy using format file
    By Stuart in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 5th, 02:53 PM
  2. Clustered index on GUID
    By Miroo_news in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 23rd, 06:41 AM
  3. Convert index to clustered
    By Anith Sen in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 11th, 04:57 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139