Solved

Replication has changed order in my table

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to enforce inte 8 59
SQL Query Syntax:  Why is the CTE Pulling in More Data Than Asked For? 5 69
Problem with SqlConnection 4 178
Query to Add Late Tolerance 10 76
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
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.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 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