Solved

Replication has changed order in my table

Posted on 2010-11-09
4
220 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

617 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