December 8, 2008 at 5:37 pm
I have a traffic logger table with a clustered index on the ClientIP,Stamp,SiteID columns.
I also have a columm VisitorID uniqueidentifier (newsequentialid()) which I want to use as the Primary key
as it also is going to engage in FK relationship with another table.
What I wanted to know was whether its possible to create a Primary Key based on this column but also within the non-clustered index that is created at the same time use some include columns (fields that are mostly bits and strings).
I currently have it set up as a UNIQUE constraint instead of a PK with the nonclustered index set up with VisitorID as the main column and some included columns so that works but I cannot seem to work out the SQL syntax or use the management console to turn the UNIQUE constraint into a PK so I am not sure whether its possible or even whether I should bother trying to do this however I would like to know for curiosity sake how.
When I try an ALTER on the table it won't let me add INCLUDE columns and I get syntax error and using the index/key pop up in MC won't let me manually change the index type from UNQIUE to PK and the Index manager hasn't got an option to set the nclidx as a PK.
I could have another index created purely for the PK on visitor ID but don't see the point when the current index is fine.
Thanks for any help or advice in advance.
December 9, 2008 at 12:06 am
Hello,
I am not 100 percent sure what you want to do. Could you possibly list the indexes and constraints that you currently have (fields, index/constraint-type, clustered or not) and then do the same for what you want to end up with?
If you just want to change the VisitorID from a Unique Constraint to a (non-clustered) PK, then I would assume that you could drop the Unique Constraint and add a PK one instead. (Providing the table is not so large that it would take an unacceptable amount of time).
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
December 9, 2008 at 12:50 am
Unfortunately you can't edit the automatically created index for a PK.
But it might still be usefull to keep your current unique index with the included columns. The index for the PK can be used when joning other tables, while the unique index can be used for queries which include the included columns.
[font="Verdana"]Markus Bohse[/font]
December 9, 2008 at 4:11 am
I am thinking that its just not possible to add INCLUDE columns into the non-clustered index created for the PK and would have to either just use a UNIQUE constraint instead of PK or duplicate the index without the INCLUDE columns for the PK.
For reference the current non-clustered UNIQUE index which I would like (if possible) to convert into a PK is this:
CREATE UNIQUE NONCLUSTERED INDEX [PK_TRAFFIC_VISITORS_VisitorID] ON [dbo].[TRAFFIC_VISITORS]
(
[VisitorID] ASC
)
INCLUDE ( [UserAgent],
[AgentType],
[VisitType],
[JavascriptEnabled],
[FlashEnabled],
[Browser],
[OS]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 70) ON [PRIMARY]
I cannot seem to do it through the MC and trying to do a change script or an ALTER statement like
the following gives me an error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'INCLUDE'.
Msg 319, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
ALTER TABLE dbo.TRAFFIC_VISITORS ADD CONSTRAINT
PK_TRAFFIC_VISITORS PRIMARY KEY NONCLUSTERED
(
VisitorID
) INCLUDE ( [UserAgent],
[AgentType],
[VisitType],
[JavascriptEnabled],
[FlashEnabled],
[Browser],
[OS])
WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
But maybe I have my syntax totally wrong for doing this.
Thanks for your comments.
December 9, 2008 at 5:27 am
Hello again,
From BOL it seems you can only use Included columns in a Create Index statement i.e. not in an Add Constraint statement.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
December 9, 2008 at 5:57 am
Ok thanks I will just keep the UNIQUE non clustered index then as that can also be used in FK relationships.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply