christopher.secord@gmail.com (christopher.secord@gmail.com) writes:
> So as a minimum, the table is going to look like this:
>
> CREATE TABLE Articles (
> ArticleID uniqueidentifier,
> PublishDate datetime,
> Title nvarchar (50)
> ArticleContent ntext
> )
> GO
>
> ALTER TABLE Articles ADD
> CONSTRAINT PK_Articles
> PRIMARY KEY NONCLUSTERED (ArticleID)
> WITH FILLFACTOR = 100
> GO
>
> As you can see, I'm not going to use a clustered index on a column of
> type UniqueIdentifier. I got that much from this newsgroup and from
> websites on sql server performance tuning.

Certainly clustered index on Uniqueidentifier and a fillfactor of 100
is a recipe for disaster. However, SQL Server MVP Greg Linwood pointed
out to me that with a low fill factor, uniqueidentifier very can be a
good choice for a clustered index. New rows will be inserted in existing
gaps. But this requires that you reindex when the gaps are starting
to run out.

> Two questions. 1: I will obviously need to list recent articles. I'll
> need to do: select top 10 ArticleID, PublishDate, Title from Articles
> order by PublishDate desc
>
> Will there be any problem with an index on a datetime field to make
> that query faster?
>
> CREATE UNIQUE CLUSTERED INDEX IX_Articles_PublishDate
> ON Articles (PublishDate DESC)
> WITH FILLFACTOR = 100
> GO

PublishDate could indeed be a good choice for the clustered index,
but I don't think you should make it unique. Surely, you must be able
to store two articles published the same day! (I'm assuming that you
will keep the time portion to midnight.)




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp