| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2008-01-17 : 05:40:48
|
| I've decided to put the clustered index on the edit date column on an audit table. As the edit date for a new record is always going to be higher (more recent) than the previous record, the value would go onto the end of the index. So is there still a value in (1) providing a fill specification of less than 100% and (2) padding the index? |
|
|
pootle_flump
1064 Posts |
Posted - 2008-01-17 : 06:50:38
|
| No and No :o)However, I would add a uniqueifier (an identity field) and make that the second column in your clustered index. It makes zero logical sense but means that you can specify your clustered index as UNIQUE which will help the optimiser and a few other things. That is, of course, assuming you don't have a natural key in there. |
 |
|
|
pootle_flump
1064 Posts |
Posted - 2008-01-17 : 06:55:43
|
| The edit date never changes right? You only ever insert into this table? |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2008-01-17 : 08:56:51
|
| correct, only ever insert. my pk is a uniqueidentifier. still sure i should bolt that on to the index? its a little big... guids arent the best thing for indexes i've heard... |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-01-17 : 09:39:24
|
Ooh gosh no don't  Since this is really an admin table and the two things you want with this are speed and integrity I would add an identity and make it the second column of a unique clustered index, the first column being your date field. This will make your clustered index pointer narrower (assuming there are lots of ties in the date column - depends on the throughput of edits and which date data type you have used of course) in the leaf level of the nonclustered index.Does that make sense? I know what I am saying but have reread that and it sounds a bit clumsy.... |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-01-17 : 09:41:00
|
| BTW - you know that a PK is enforced internally as an index? So your GUID forms an index, like it or not. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-01-17 : 09:50:56
|
quote:
Originally posted by pootle_flump
Ooh gosh no don't Since this is really an admin table and the two things you want with this are speed and integrity I would add an identity and make it the second column of a unique clustered index, the first column being your date field. This will make your clustered index pointer narrower (assuming there are lots of ties in the date column - depends on the throughput of edits and which date data type you have used of course) in the leaf level of the nonclustered index.Does that make sense? I know what I am saying but have reread that and it sounds a bit clumsy....
I would add the identity, and just make that the clustered index. It will be only increasing, and the identity alone is enough to make it unique. If there is a need, add indexes on other columns as needed.Stay away from the uniqueidentifier for the clustered index, unless you are populating it with NEWSEQUENTIALID:CREATE TABLE myTable (ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID()) CODO ERGO SUM |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2008-01-17 : 09:51:08
|
| ya i know. throughput isnt that big, so i think i'll leave it as is, ta |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-01-17 : 09:55:51
|
quote:
Originally posted by Michael Valentine JonesI would add the identity, and just make that the clustered index. It will be only increasing, and the identity alone is enough to make it unique. If there is a need, add indexes on other columns as needed.[/code] CODO ERGO SUM
Depends on the sort of queries issued (which of course I forgot to ask). If a common one will be "what changes were made between x datetime and y datetime?" then I like coolerbob's clustered index on date.I'm actually not sure what purpose the GUID serves at all TBH. Since this will be an "end of the line table" (i.e. it should not have any children) I don't know if it really needs one. Guessing again of course |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2008-01-18 : 07:37:14
|
ur probably right. its just that we made a system-wide design decision not to use integers (back in the day part of the reason for this was sql replication issues) and all our code objects kind of work on that premise that an object has a guid. so we kinda just stick withquote:
DI'm actually not sure what purpose the GUID serves at all TBH. Since this will be an "end of the line table" (i.e. it should not have any children) I don't know if it really needs one. Guessing again of course
|
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2008-01-18 : 07:39:11
|
I should prob use NEWSEQUENTIALID anyway? seems more useful with no downside.quote:
Originally posted by Michael Valentine Jones
quote:
Originally posted by pootle_flump
Ooh gosh no don't Since this is really an admin table and the two things you want with this are speed and integrity I would add an identity and make it the second column of a unique clustered index, the first column being your date field. This will make your clustered index pointer narrower (assuming there are lots of ties in the date column - depends on the throughput of edits and which date data type you have used of course) in the leaf level of the nonclustered index.Does that make sense? I know what I am saying but have reread that and it sounds a bit clumsy....
I would add the identity, and just make that the clustered index. It will be only increasing, and the identity alone is enough to make it unique. If there is a need, add indexes on other columns as needed.Stay away from the uniqueidentifier for the clustered index, unless you are populating it with NEWSEQUENTIALID:CREATE TABLE myTable (ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID()) CODO ERGO SUM
|
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-01-18 : 08:16:23
|
| NEWSEQUENTIALID has all the downsides and upsides of GUIDS except that they are always increasing, so no splits on insert. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2008-01-18 : 10:17:12
|
| fyi, I had trouble getting the management studio to accept NEWSEQUENTIALID as the default value for a uniqueidentifier column. had to script it.Further to this, the management studio doesnt allow u to create a foreign key reference to a pk column with NEWSEQUENTIALID. It says : Error validating the default for column (whatever your primary column is)Basically, once you start using NEWSEQUENTIALID, forget about using management studio to make any further changes. it will fail on the validation every time because of it. maybe there's a hotfix out? |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-01-31 : 10:37:36
|
| You can create it in script though right? I don't use the gui to create stuff so I'm afraid I wouldn't know about limitations there... |
|
|
|
|
Index Decision