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

 

• Moved by Kalman TothModerator 37 minutes ago Design

You are most welcome :-)

the above example that I wrote is regarding UNIQUE constraint. REFERENCES used for creating FOREIGN KEY for example, and It can be multi columns as well. A FOREIGN KEY need to "mapped" columns in the current table to REFERENCES columns in the related table. You can check this tutorial for more information:
https://msdn.microsoft.com/en-us/library/ms189049.aspx?f=255&MSPPError=-2147217396
http://www.w3schools.com/sql/sql_foreignkey.asp

* By the way, You can see in the first link, which is the official Microsoft Book On-line, that the names of the constraint are like I mentioned (names include the type of the element always like PK for primary key of FK...).

FOREIGN KEY to multi columns can be something like this for example: 

ALTER TABLE TableName
ADD CONSTRAINT FK_CurrentTableName_RelatedTableName 
    FOREIGN KEY (ACol1,Acol2) -- This is the type of the constraint + columns in the current table
    REFERENCES RelatedTableName (BCol01,Bcol02) -- Using FOREIGN KEY we need to mapped to the related table and columns.these are columns in the related table

Thank you all for your contributions.

@Naomi N

The $ character within the name is tool generated; not that I am trying to make excuses. It has always caught my eye but I have not stumbled across the special character causing an issue. I am not used to creating DDL types of statements, only DML or maybe a user/login here and there. Maybe this is why I have not noticed it being an issue, other than the fact I don't like how it looks either.

Can you provide an example of where the special character would create an issue?

The only problem with special characters that such names should be used within identifiers such as []. But in general it's better to avoid using them.

MAP Data I totally agree with Naomi

In my opinion you should not use any unique characters in elements names. I can tell as developer that this might leads to strange bugs! Each programming language can behave differently regarding these chars. The bugs might revealed in the worst time, as Murphy's law teach us, at the application side. You are allowed to use Unicode chars as well in names, but again I highly recommend you for elements names use only English, numbers,_

* I am from Israel and I have seen people that use Hebrew names :-( This is so bad in my opinion but allowed by SQL Server.

-- This is working to use Hebrew Names but DON'T USE THIS!!!
-- can you read the code here?!? if not then you can copy to SSMS which deal Hebrew OK
-- the order of the characters messed-up since Hebrew is written from RTL and English from LTR (the forum use LTR)

create table [] ( int)
GO

select * from []
GO

drop table []
GO		

OK, thanks for the reply. I was never sure why the tool generated the name the way it did and now I know to correct the issue, thanks.