Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Replication has changed order in my table

Posted on 2010-11-09
4
Medium Priority
?
234 Views
Last Modified: 2012-05-10
For transactional replication to work you have to have a primary key.  
Before transactional replication was set up I had a table called dbo.Area which had columns AreaID(int,not null) and AreaDescription(char(15),null) this was result when I did a select

Select * from dbo.Area;

     AreaID    AreaDescription
1      100        Coke Side
2       200        Pusher Side

To make transactional replication work I added a UniqueGuid(PK, uniqueidentifier, not null).  Now when I do the same select I am getting

AreaID    AreaDescription
1      200        Coke Side
2      100        Pusher Side

This is causing issues in our production environment.  How do I get the descending order back?

Thank you for your help
0
Comment
Question by:algotube
  • 2
  • 2
4 Comments
 
LVL 2

Accepted Solution

by:
Michael-Thomas earned 1400 total points
ID: 34097975
Your clustered index will effect the default sort order.  If you have the cluster on your UniqueGuid it may not come back with AreaID desc by default as you might expect.  You can add a simple order clause at the end of your query or change your cluster.

You can make AreaID the cluster and keep your UniqueGuid as the primary key with alter table statements after the table is created.
0
 

Author Comment

by:algotube
ID: 34191813
Michael-Thomas

  Thank you so much for your reply. Sorry for not getting back to you sooner.  I had to attend a family emergency that was out of town and just got back today.  I will try the alter table statement and get back to you.

Regards
0
 

Author Comment

by:algotube
ID: 34192003
Michael-Thomas

  Can you please provide an example of the ALTER TABLE statement that would make the AreaID the cluster?

Thank you
0
 
LVL 2

Expert Comment

by:Michael-Thomas
ID: 34199992
If your primary key is your clustered index you can use this technique, but it will cause blocking so schedule it in a maintenance window
-- remove the current PK
ALTER TABLE  dbo.MyTable 
DROP CONSTRAINT PK_MyTable

-- add the new PK constraint
ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable PRIMARY KEY (ID)

-- Add the clustered index
CREATE CLUSTERED INDEX IX_MyTable ON dbo.MyTable(ID) WITH (ONLINE = ON)

Open in new window

If you have 2005+ enterprise and your clustered index is not your primary key you can use this technique.  You can do it online which will avoid blocking issues.  
If you can still use this technique if you don't have enterprise and your clustered index is not your primary key, but it will cause blocking so do it in a maintenance window
DROP INDEX IX_MyTable ON dbo.MyTable WITH (ONLINE = ON)

CREATE CLUSTERED INDEX IX_MyTable ON dbo.MyTable(ID) WITH (ONLINE = ON)

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question