I have a series of tables I am developing for a new application. I have been asked to create indexes. I'm trying to code these by hand and not use automated tools. I want to post the structure of the table and the structure of a view that is created that uses a join. Then some questions about unique indexes and speed indexes. Here is the table:CREATE TABLE mqs ( mqsId INT NOT NULL IDENTITY (1, 1), mIdINT NOT NULL, queueId INT NOT NULL, deliveryId INT NOT NULL, created DATETIME DEFAULT GETUTCDATE(), updated DATETIME DEFAULT GETUTCDATE(), CONSTRAINT pk_mqsId PRIMARY KEY (mqsId) )
And the view:SELECT mqs1.mqsId, mqs1.messageId, mqs1.queueId, mqs1.deliveryStateId, mqs1.created FROM mqs mqs1INNER JOIN (SELECT messageId, queueId, created = max(created) FROM mqs GROUP BY messageId, queueId) mqs2 ON mqs1.messageId = mqs2.messageId AND mqs1.queueId = mqs2.queueId AND mqs1.created = mqs2.createdGROUP BY mqsId, mqs1.messageID, mqs1.queueId, mqs1.deliveryStateId, mqs1.created
I have planned to create a unique index on mqs using the columns messageId, queueId, and deliveryStateId. I want to create a speed index but not sure what to do here. I am guessing on the columns used in the join: messageId,queueId and created. But I'm not sure. Nor do I quite understand the concept of using includes in an index. Can someone help break it down?Craig Greenwood
Creating indexes
Posted - 2011-12-19 : 12:19:58
