|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
create other indexHello:
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" <ani***@prensa-latina.cu> wrote in message CREATE INDEX <indexname> ON <TableName> (<column1>, <column2>, ...)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. > > 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. Show quote "Rick Sawtell" <Quicken***@msn.com> wrote in message news:eWCHVDwEGHA.644@TK2MSFTNGP09.phx.gbl... > > "Owen" <ani***@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 > > > 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 Show quote "Owen" <ani***@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" <Quicken***@msn.com> wrote in message > news:eWCHVDwEGHA.644@TK2MSFTNGP09.phx.gbl... >> >> "Owen" <ani***@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 >> >> >> > > 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 Show quote "Tobias Thernström" <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 >  |
|||||||||||||||||||||||
Other interesting topics