Solved

Replication has changed order in my table

Posted on 2010-11-09
4
191 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now