create other index

Hello:
I create a key in one table, the field is uniqueidentifier, but I search
very frequently for one [date] field, and I like to index this field with
datetime datatype to optimize the search, how can I do that?
Best regards,
Owen."Owen" <anibal@.prensa-latina.cu> wrote in message
news:uxQMR9vEGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello:
> I create a key in one table, the field is uniqueidentifier, but I search
> very frequently for one [date] field, and I like to index this field with
> datetime datatype to optimize the search, how can I do that?
> Best regards,
> Owen.
>
CREATE INDEX <indexname> ON <TableName> (<column1>, <column2>, ...)
Example:
CREATE INDEX IX_Frogs_BirthDate ON Frogs (BirthDate)
As a side note.. If you are frequently searching on a range of dates using
statements like BETWEEN, then you may find a CLUSTERED index on this date
column to be far more effective. Clustered indexes on GUIDs can be clumsy
at best.
Rick Sawtell
MCT, MCSD, MCDBA|||hi, thanks for answer:
the problem is that I need keep this two index on the same table, the guid
and the dates, but only one can be CLUSTERED, how can optimize this to all
index work faster?
Best regards,
Owen.
"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:eWCHVDwEGHA.644@.TK2MSFTNGP09.phx.gbl...
> "Owen" <anibal@.prensa-latina.cu> wrote in message
> news:uxQMR9vEGHA.1032@.TK2MSFTNGP11.phx.gbl...
with
> CREATE INDEX <indexname> ON <TableName> (<column1>, <column2>, ...)
> Example:
> CREATE INDEX IX_Frogs_BirthDate ON Frogs (BirthDate)
>
> As a side note.. If you are frequently searching on a range of dates
using
> statements like BETWEEN, then you may find a CLUSTERED index on this date
> column to be far more effective. Clustered indexes on GUIDs can be
clumsy
> at best.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Owen as you say only one index can be clustered, but one of the significant
benefits of a clustered index is for querioes based on a range of values
since all those rows will be found near each other (all else being equal).
Now a GUID is *extremely unlikely* (probably nearly safe to say never)
likely to be used in a query like
WHERE guidcol between 'yuckyguidvalue1' and 'yuckyguidvalue2'
So makeing the CI on the date will have the benefit (if you retrieve rows
based on a date range) of being faster than retrieving the same range when
the supporting index is non-clustered. Hence Rick's suggestion that you
consider making the CI on the date column.
To go much further needs an understanding of the type of queries on the
table, and the approx size of it, and is frequently best checked by running
testst on your configuration.
You may have guessed I fall in to the camp of disliking guids for any sort
of identifiers unless there is an absolute cast-iron reason for needing them
(like a distributed db that has to have surrogate keys generated uniquely,
or complex replication) .
Mike John
"Owen" <anibal@.prensa-latina.cu> wrote in message
news:eNcVeIwEGHA.3064@.TK2MSFTNGP10.phx.gbl...
> hi, thanks for answer:
> the problem is that I need keep this two index on the same table, the guid
> and the dates, but only one can be CLUSTERED, how can optimize this to all
> index work faster?
> Best regards,
> Owen.
>
> "Rick Sawtell" <Quickening@.msn.com> wrote in message
> news:eWCHVDwEGHA.644@.TK2MSFTNGP09.phx.gbl...
> with
> using
> clumsy
>|||Just don't forget that the GUID may be an FK where the "=" searches would
return many rows. In that case depending on how many queries against each
column and what columns you fetch we still may consider using the GUID as
the clustered index.
/ Tobias|||True - apologies - i was falling into an assumption that the guid was going
to be unique!
Mike
"Tobias Thernstrm" <ttnospam@.rbam.se> wrote in message
news:O%23qfmNCFGHA.3632@.TK2MSFTNGP10.phx.gbl...
> Just don't forget that the GUID may be an FK where the "=" searches would
> return many rows. In that case depending on how many queries against each
> column and what columns you fetch we still may consider using the GUID as
> the clustered index.
> / Tobias
>