>> There is some good information there that will help me... I have also gained some insight
into how the clustered index works (plus some good reference material).
I am glad to hear :-)
I got to link from Google by the way (first page of the result... just needed to check 3-4 and chose one good result)
>> The main purpose for starting this thread is so I can depend on some script that will create tables. The syntax is of a great importance to me.
the syntax is OK
>> the enum field will act as a composite key with the ContactKey field.
small off topic comment :-)
please use the word COLUMN and not field (in form you have fields but table have rows and columns).
>> So I am looking into using this particular table for that purpose. Hence, looking into indexing.
Indexes need to fit your queries and without the business model. Once you have the business model and you will know what queries you want to execute we will be able to in this issue as well.
>> or the PK, I do not have a use for it at the moment but I included it just in case.
This is very bad approach. this column can influence every insert that you do for example. Don't create extra keys or indexes (or for that meter anything.. extra = extra job usually).
>> I may need the PK for delete or update operations.
This is mean that you use that column for searching!
If you always point to data through this column then this mean that it probably should be the clustered index. You must lean and understand the
meaning of index, and
when it is uses, and how it is uses, before you design indexes. same go with Primary key (or any other element), especially when dealing with clustered index.
* In most cases I recommend to have surrogate key especially in each Entity table (IDENTITIY fit this rule). You should search Google for Natural Key verses Surrogate Key.
You can start here but you can not learn from one short blog! you should look for much more and read/learn the issue!
>> I suspect it would be easier to remove than to add it in.
Not necessarily correct!
During the time that you have an unnecessary index/key you pay in performance. You can always add columns just like removing them. Moreover, there are cases that we need to load lot of data to a table and best option is to remove indexes before the action
and recreate them after, but again I ca not recommend you anything since you do not mastered the subject, and we have no information regarding your full business model :-)
>> I believe I have enough information regarding the script.
I glad to hear that :-)
I think that you got all the information that you asked for, and we can give in the forum (if not please Please try to formulate what is not clear in the form of a direct question if possible)
Please remember to close the thread, by marking the (correct) answer/s. and you are welcome to vote for any useful response as well. If you have another question/issue please feel free to ask, usually better in new thread :-)
have a nice day,