SQL Optimisation :: Convert Primary Keys to Clustered Indexes

We recently spotted that one of the systems we use did not make use of clustered indexes on any tables out of the box. As a result performance was not as good as it could have been. The below script allows for an easy win optimisation by finding all tables which do not include a clustered index, and converting the table’s primary key to be a clustered index.

In a future post I’ll put up more details on what clustered indexes are, why you should always (pretty much) use them and other useful info for anyone playing with databases.

--This script is designed for MS SQL Server
use DbNameToOptimise

--disable all constraints on all tables (to avoid these causing errors when altering the indexes)
sp_msforeachtable 'alter table ? nocheck constraint all'
declare @sqls table(object_id bigint, sort int, sql nvarchar(max))
insert @sqls
select t.object_id, ic.key_ordinal, case when ic.key_ordinal=1 then  'CREATE UNIQUE CLUSTERED INDEX [' + + '] ON [' + + ']([' else ',[' end + + case when ic.key_ordinal=icagg.maxko then ']) WITH DROP_EXISTING' else ']' end sql
from sys.tables t
inner join sys.indexes i on t.object_id = i.object_id
inner join sys.index_columns ic on i.object_id=ic.object_id and  i.index_id = ic.index_id
inner join sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
inner join (
      select object_id, index_id, MAX(key_ordinal) maxko from sys.index_columns group by object_id,index_id
) icagg on i.object_id = icagg.object_id and i.index_id = icagg.index_id
where t.is_ms_shipped=0
and i.is_primary_key=1
and not exists (
      --ignore tables which already have a clustered index
      select 1
      from sys.indexes i2
      where t.object_id = i2.object_id
      and i2.type = 1
order by,, ic.key_ordinal
declare @objid bigint
, @sql nvarchar(max)
while exists (select top 1 1 from @sqls)
      set @sql=''
      select top 1 @objid=object_id from @sqls
      select @sql = @sql + sql from @sqls where object_id=@objid order by sort
      delete from @sqls where object_id = @objid
      exec (@sql)

--reenable constraints to leave the db as we found it (aside from the fix)
sp_msforeachtable 'alter table ? check constraint all'


A Request / Random Thoughts

As with many of my posts, this is basically an unedited brain dump – apologies. Hopefully this will encourage some interesting comments / discussion though. . .

A Standard for Developer APIs
Facebook, Twitter, Worpress (and I’m sure Google+ will) offer APIs to developers allowing them to pull data out of their applications and manipulate it as they like. Most of these services offer similar functions; authentication, get the last x posts, pull back a grid of contacts, etc. All do this in their own way.
What would be great is some unification – either a library over the top of the existing APIs to pull them all in line, or for some set of social standards to be formed in the same way Netscape, IE, Mozilla and more came up with ECMAScript as a way to allow javascript to become portable. What I’m hoping for is something like this:

//this is entirely made up code - not (yet) some awesome new Google thing 
var application = GetApplication('Google+'); //creates a new object with an "application interface" for Google+
if (application.authenticate('Developer42','DemoCodePassword') //authenticate a user against the web app
    var identity =; //pull back an object which represents me
    var allFriends = me.ListContacts(); //by default pull back all contacts
    var colleagues = me.ListContacts('colleagues'); //or filter by group
    var posts = identity.GetPosts(20); //get my last 20 posts
    var friendsPosts = allFriends.GetPosts(100); //Get the last 100 posts by my friends/contacts

A Service for Services
This is probably what the guys who came up with UDDI were thinking:

If two companies offer a service to give out exchange rate information, and both use the same standard, when I want to get back exchange rate info why can’t I just post a request to the web saying “give me the exchange rate from USD to GBP” and have it chuck back .67 without all the hassle of searching for a suitable service.

There’s a whole bunch of data which we often need, but have to trawl the web for. Search engines began to make this better, WolframAlpha got a bit closer, but no one’s yet cracked it. What I’d like is a single web site containing a catalogue of services and their schemas. I pick a service, write code to its schema, then use the service url to pull back this data. From my point of view I’m just pulling data from, but in the background that could be talking to any (approved) provider. I guess the reason this doesn’t yet exist is the issue around monetisation; but surely there’s a way. . . ?
Below’s my wish list of services:

- Exchange Rates
- Share Prices
- National Holidays
- Daylight Savings Dates
- Post Code / Geo (long & lat) Conversion
- Credit Checks
- Product Prices
- Companies House Info


My First Wave Robot ::

Filed under: Google, Technology, Wave — Tags: , , , , , , , — Developer42 @ 00:34 first demo


<?xml version="1.0" encoding="utf-8"?>
<appengine-web-app xmlns="">


<?xml version="1.0" encoding="utf-8"?>
 "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
<web-app xmlns="" version="2.5">


<?xml version="1.0" encoding="utf-8"?>
<w:robot xmlns:w="">
    <w:capability name="blip_submitted" content="true" />
    <w:capability name="wavelet_self_added" content="true" />

import java.util.*;
//FIX FOR ISSUE 354 is used where the robot suffers from the bug mentioned in the below links
public class SmileyServlet extends AbstractRobotServlet {
	 * Robot's version id & usage info
	private static final long serialVersionUID = 1L;
	private static final long serialSubVersionUID = 2L; //this is to help me prove that my latest changes 
                                                            //have been uploaded correctly
	private static final String serialVersionID = "Version #" + new Long(serialVersionUID).toString() + "." + 
                                                      new Long(serialSubVersionUID).toString();
	private static final String helpInfo = "\nThis robot is my learning tool for wave development, " +
                                               "so may occasionally break!  Apologies if you're one of " +
                                               "the people for whom it doesn't work!";
	private static HashMap dictionary = null;
	 * Creates the images & registers them against their invoking smileys 
	 * */	
	static {
		final int width = 24;
		final int height = 24;
		dictionary = new HashMap();
		dictionary.put(":)",new Image("",width,height,":)"));
		dictionary.put(":(",new Image("",width,height,":("));
		dictionary.put(";)",new Image("",width,height,";)"));
		dictionary.put(":D",new Image("",width,height,":D"));
     * Handles changes to the wave 
	public void processEvents(RobotMessageBundle bundle) {		
		if (bundle.wasSelfAdded()) {
			final Blip blip = bundle.getWavelet().appendBlip();
			blip.getDocument().delete(); //FIX FOR ISSUE 354
			final TextView textView = blip.getDocument();
			textView.append( serialVersionID );
			textView.append( helpInfo );
		for (Event e: bundle.getEvents()) {
			if ((e.getType() == EventType.BLIP_SUBMITTED) || (e.getType() == EventType.WAVELET_SELF_ADDED)) {
				final Blip blip = e.getBlip();
	private void smile(TextView textView){
		final String text = textView.getText();
		Iterator<Map.Entry> i = dictionary.entrySet().iterator();
			Map.Entry pic =;
			String key = pic.getKey();
			int pos = -1;
			while((pos = text.indexOf(key,++pos))>-1){
				textView.delete(new Range(pos, pos + key.length()));


Create a free website or blog at

%d bloggers like this: