I am not a DBA so I could use some advice on something a I fumbling through. I desire to have a clustered index on a column other than the Primary Key. I have a few junction tables that I may want to alter, create table, or ...

I have practiced with an example table that is not really a junction table. It is just a table I decided to use for practice. When I execute the script, it seems to do everything I expect. For instance, there are not any constraints but there are indexes. The PK is the correct column. The script I created seems good to me.

So here is my question, "Does this script create questions for you or does it seem normal?" Maybe there is a better way or a more conventional approach.

CREATE TABLE [dbo].[tblNotificationMgr](
	[NotificationMgrKey] [int] IDENTITY(1,1) NOT NULL,
	[ContactKey] [int] NOT NULL,
	[EventTypeEnum] [tinyint] NOT NULL,

 CONSTRAINT [tblNotificationMgr$PrimaryKey] PRIMARY KEY NONCLUSTERED 
(
	[NotificationMgrKey]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

 CREATE CLUSTERED INDEX [tblNotificationMgr$ContactKey] ON [tblNotificationMgr] 
 ([ContactKey] ASC)

GO

You can create a clustered index with column other than the primary key column. But you can have one clustered index most for each table.

Most of time, I have seen the primary key column defined as a clustered index as well.

CREATE TABLE dbo.tbl_Notification_Mgr
(notification_mgr_key INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY,
 contact_key INTEGER NOT NULL,
 event_type_enum TINYINT NOT NULL);

This is full of classic noob mistakes.

1. The use of the affix of "tbl-" in a table name is so bad it has a name; tibbling. What you have said in ISO-11179 terms is that this table handles notification about tibbles! The purpose of a database (not just SQL) is to reduce redundancies, not increase them. 
2. The use of "key" in a column is wrong we name data elements for what they are by their nature, never for how they are used in one place in the schema. Data and meta data are never mixed. 
3. Unlike the C family, we do not have "enum" structures in SQL. And even if we did, that would be more meta-data as how something is modeled and not what it's nature is. 
4. The proprietary, totally non-relational IDENTITY table property is a count of the physical insertion attempts to one disk on one machine in the world. It is not ever part of a logical schema by definition. When noobs use it as a key and name it id, we call them ID-iots. 
5. What math do you do with event_type_enum? None; it is an encoding. That means it should be a string and have a constraint on it. You need to invent an encoding scheme, and I would probably use an abbreviation code. Yes, it takes a lot of work to do a database correctly. You have to start with each data element. 
6. The term junction table has never been part of RDBMS. It comes from Network databases and refers to a particular kind of pointer structure. We have referenced and referencing table, and table can model a set of entities, a relationship or be an auxiliary. 

Since we have no specs or sample data I will guess that the Notifications are in some kind of queue. You can use a standard sequence to manage it. Here is my skeleton, using guesses and what you gave us. I probably would also have a timestamp, etc. 

CREATE SEQUENCE Notification_Queue
 AS INTEGER
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 999999
 NO CYCLE;

CREATE TABLE Notification_Mgr
(notification_seq INTEGER 
 DEFAULT NEXT VALUE FOR Notification_Queue
 NOT NULL PRIMARY KEY, 
 contact_id CHAR(10) NOT NULL
 REFERENCES Contacts(contract_id),
 event_type CHAR(5) NOT NULL
 CHECK (event_type IN (..))
);

See why it takes a few years to become a DB designer? :)

OK, thanks for the comment on the value being unique. As I mentioned, I have intentions of 'clustering' FK columns. So I do not intend the clustered column being unique. I did some googling and it seems there can be some downsides to using clustering on non-unique values. Although, it is not a requirement to do so.

I am glad I posted. I do not want to be penny wise and pound foolish. I will do further research regarding clustered index. I am certainly open to additional comments and criticism. 

Good day,

Without getting the full characteristic and the business model, I can not tell you what is the best, but In most cases it is best to have a clustered index on a column that narrow (small size), unique, static (fixed-length), and ever-increasing. For more information regarding this, check this article: https://www.simple-talk.com/sql/learn-sql-server/effective-clustered-indexes/

In your case you have an IDENTITY column but you created the CLUSTERED index on another column [ContactKey] which dowes not fit the the rules above. You should think, why did you need an IDENTITY and why dont you use it as your CLUSTERED INDEX.

In first glance your database structure do not look optimistic to me.

* by the way, I do not agree with most of what CELKO is saying.
>> For example, I think that his approach regarding names is wrong and will leads to non-readable code. There is a reason that most expert in my experience use three parts name, including Microsoft! A three part name mean that we use for example <1. element type>_<2. meaningful name>_<3. Unique part like company name>. I think that any developing tutorial will teach you this. For example Microsoft use names like SP_XXX_MS, which is MS for Microsoft, XXX us the meaningful part, and SP for procedure (or fn for function and so on).
>> Another example is that I would probably fired someone that will start to think about standards as goal and will forget that standards are just a tool! SQL Server is NOT fully ISO standards database, and this should be in low priority comparing to performance or readable code for example... Yes we do need to be as close as we can to a standards but this is NOT THE GOAL but a tool to help us. 

@Ronen Ariely

Thanks for the link. There is some good information there that will help me. I apologize for not providing a full backstory. In an effort to be concise, I may have created unnecessary questions for those wanting to help. 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. I believe I have enough information regarding the script. I have also gained some insight into how the clustered index works (plus some good reference material).

As for the table in the example, the enum field will act as a composite key with the ContactKey field. I have some chatty calls that need to be made. So I am looking into using this particular table for that purpose. Hence, looking into indexing. As for the PK, I do not have a use for it at the moment but I included it just in case. I may need the PK for delete or update operations. In the end, the PK may be extraneous. I suspect it would be easier to remove than to add it in.

>> 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,