Solved

Replication has changed order in my table

Posted on 2010-11-09
4
215 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 2

Accepted Solution

by:
Michael-Thomas earned 350 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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 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