SQL Server Performance

100% Fill Factor. When to use it?

Discussion in 'Performance Tuning for DBAs' started by Will192, Sep 20, 2005.

  1. Will192 New Member

    I have a table that's heavily queried and updated. Deletes and inserts are probably less than a half dozen times a year and the system is locked to users at those times. I have three unclustered indexes set at 100% fill factor. Would there be any advantage to reducing the fill factor? It is probably 50/50 split on the #of rows updated and the #of rows queried.

    Live to Throw
    Throw to Live
  2. dineshasanka Moderator

  3. FrankKalis Moderator

  4. satya Moderator

    You can reduce the number of page splits by setting the appropriate fill factor option to reserve free space on each index page. The fill factor is a value from 1 through 100 that specifies the percentage of the index page to be left empty.

    The fill factor percentage is used only at the time the index is created. If the table contains read-only data (or data that very rarely changed), you can set the 'fill factor' option to 100.
    Keep in mind about page splits problem, if there is more free space means that SQL Server has to traverse through more pages to get the same amount of data.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. gagilman New Member

    If your data was read-only fill factor 100 would be the way to go.

    You said your data get's updated. When you update data, the new values go into the indexes. So you probably don't want fill factor 100.

  6. znichter New Member

    gagilman, why wouldn't you use a 100% fill factor in this situation?

    Even if these are inserts into the tables, one, the inserts are 6 times a year. Two, the users are locked out of the database during these loading and maintenance periods. Three, during this lockout period you can rebuild indexes to defrag them if need be.

    It would only make since that this fill factor would be 100% for optimal read performance given the fact that that is the primary use of these tables and that tables are offline during load periods which as stated above allows for maintenance on these tables.

    Zach
    http://www.sqljunkies.com/WebLog/odds_and_ends/
  7. FrankKalis Moderator

    Sometimes it can be really helpful to read closely through the postings. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />Although INSERTs and DELETEs are only 6x a year, he write the table is heavily UPDATEd. <br />So, I think, before suggesting anything on the fillfactor, the more important question is, if there is a clustered index on that table. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  8. satya Moderator

    Monitor the fragmentation to determine the ideal configuration on a per-tableclustered index basis; the amount of storage required can be dramatically different for configurations with a 5% to 10% difference. The lower the fill factor (i.e., 40% to 50%), the more storage is needed and the more pages an index will have to scan or seek to fulfill the query request. With a high fill factor (90% to 100%), less storage is needed and fewer pages are scanned, but costly page splits can occur causing performance degradation and index fragmentation.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. znichter New Member

    My bad, didnt see that. Thanks Frank.

    Zach
    http://www.sqljunkies.com/WebLog/odds_and_ends/
  10. FrankKalis Moderator

    Frankly, I can't count, how many times I misunderstood something, or overread some important points. ...but I have the advantage, that I can blame it on the language barrier. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Ich nix verstehen, ich deutsch... [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  11. ranjitjain New Member

    Hi Will,
    To avoid the frequent page splits you can go for having a clustered index on a column which has value in increment order (eg. an identity column)
  12. mmarovic Active Member

    quote:I have three unclustered indexes set at 100% fill factor.
    If columns that are part of non-clustered index are not updated you can keep 100% fill factor. If they are, it is better to have lower fill-factor. To find optimal you will have to test, it depends too much on usage pattern, so nobody can tell you which to use without knowing much more about your db and process.
  13. FrankKalis Moderator

    That's why I was asking whether there is a clustered index present or not. On a heap you don't have to deal with data fragmentation because of page splits anyway. As for index defragmentation this is a very useful source:
    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

    And since nobody here owns a crystal ball, we should let Will192 respond, how things are going on so far and if this has turned out helpful for him or not.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  14. Will192 New Member

    Actually what I found out was that if you have either a VARCHAR column or any column allows NULLs, then SQL Server doesn't do a true update, but a delete and insert!

    The table allows NULLs on a couple of columns, so I have reduced the fill factor to 80%. We will see if it improves performance.

    Live to Throw
    Throw to Live
  15. ranjitjain New Member

    Try having a default value as zero length string instead of NULL.
  16. mmarovic Active Member

    Good point about delete/insert. Please let us know the impact. I would also try 90% if columns updated are not part of index.
  17. FrankKalis Moderator

    AFAIK, every UPDATE is internally a DELETE and INSERT operation. That's why you can use the inserted and deleted pseudotables within a trigger. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  18. mmarovic Active Member

    There was a difference if update is done "in place" or not. It really depended on presence of varchar/nullable column. I don't remember how it actually works in case of mssql server 2000, can someone find a chapter about the topic? I don't have "Inside mssql server" or any other sql server book with me anymore. <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br /><br />Besides the question how update affects data rows, I am not sure if delete/insert of data row when clustered index is present affects index in case no index column is updated.
  19. FrankKalis Moderator

    From Inside SQL Server 2000

    quote:
    Updating in Place
    In SQL Server 2000, updating a row in place is the rule rather than the exception. This means that the row stays in exactly the same location on the same page and only the bytes affected are changed. In addition, the log will contain a single record for each such updated row unless the table has an update trigger on it or is marked for replication. In these cases, the update still happens in place, but the log will contain a delete record followed by an insert record.

    In cases where a row can't be updated in place, the cost of a not-in-place update is minimal because of the way the nonclustered indexes are stored and because of the use of forwarding pointers. Updates will happen in place if a heap is being updated or if a table with a clustered index is updated without any change to the clustering keys. You can also get an update in place if the clustering key changes, but the row does not need to move. For example, if you have a clustered index on a last name column containing consecutive key values of 'Able', 'Becker', and 'Charlie', you might want to update 'Becker' to 'Baker'. Since the row would stay in the same location even after changing the clustered index key, SQL Server will perform this as an update in place.

    Updating Not in Place
    If your update can't happen in place because you're updating clustering keys, it will occur as a delete followed by an insert. In some cases, you'll get a hybrid update: Some of the rows will be updated in place and some won't. If you're updating index keys, SQL Server builds a list of all the rows that need to change as both a delete and an insert operation. This list is stored in memory, if it's small enough, and is written to tempdb if necessary. This list is then sorted by key value and operator (delete or insert). If the index whose keys are changing isn't unique, the delete and insert steps are then applied to the table. If the index is unique, an additional step is carried out to collapse delete and insert operations on the same key into a single update operation.
    As far as I understand this, "done in place" is almost meant literally and doesn't have something to do with VARCHAR or NULLs. But I must correct myself. I was thinking inside replication (the newest baby I'm playing around with).

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  20. mmarovic Active Member

    quote:As far as I understand this, "done in place" is almost meant literally and doesn't have something to do with VARCHAR or NULLs.
    I'm pretty sure it was like that in sql server 6.5, maybe even 7.0. Anyway, for 2000 it looks like it is not the case, so updating columns that are not stored in non-clustered index leafs should not cause page splits at all.
  21. Will192 New Member

    The columns updated aren't going to be in the clustered index cause there is not a clustered index on the table.

    Live to Throw
    Throw to Live
  22. mmarovic Active Member

    Ok, but are they part of non-clustered index definition where you had 100% fill-factor specified?
  23. Will192 New Member

    Yes, some of the columns in the unclustered indexes are updated.

    Live to Throw
    Throw to Live
  24. mmarovic Active Member

    Ok, then fill-factor 80 is really one that was the best for me in most cases, but it may be anything between 50 and 90.
  25. Will192 New Member

    Will I see a difference between fill factor 50 and 90 in query times on a table with about 2.6 million rows and the following structure:

    OFF_COD char (4)
    VACYEAR smallint
    DTE char (10)
    YR smallint
    MO tinyint
    DOM tinyint
    HR tinyint
    MI tinyint
    SAVAIL smallint
    SFULL smallint
    VACDAY smalldatetime

    As you can see, the table isn't very wide, but it is hit a lot during the day. If I speed up the queries to this table, then my whole application speeds up. The DTE, MO, DOM, HR, and MI fields are calculated before insertion to help speed up future selects. I can't really normalize the table any further because only the OFF_COD, VACYEAR, SAVAIL, SFULL and VACDAY fields are the core of the table.





    Live to Throw
    Throw to Live
  26. mmarovic Active Member

    Are you still trying to speed-up updates? 2.6 million rows is small enough table to be 'testable'. That's much better then to relay on speculation we can offer.

    However, I would like to test my skills, so if you don't mind please let me know what is table pk, index structure and which columns are updated.
  27. Will192 New Member

    OFF_COD char (4)
    VACYEAR smallint
    DTE char (10) - UPDATED
    YR smallint - UPDATED
    MO tinyint - UPDATED
    DOM tinyint - UPDATED
    HR tinyint - UPDATED
    MI tinyint - UPDATED
    SAVAIL smallint - UPDATED
    SFULL smallint - UPDATED
    VACDAY smalldatetime - UPDATED

    Pretty much all the columns are heavily queried, but the ones that are in EVERY query are VACYEAR, OFF_COD, (VACDAY or DTE), SAVAIL, SFULL. I have done a ton of testing on this table, I just want to know if there is anything obvious that I have missed.

    Indexes - unclustered, 80% fill factor
    UpdateSlots - (DTE, OFF_COD, VACDAY, VACYEAR)
    DefRes1 - (DTE, OFF_COD, VACYEAR, VACDAY, SAVAIL, SFULL)
    AvailCal - (OFF_COD, MO, VACDAY, VACYEAR, YR, DTE, DOM, HR, MI, SAVAIL, SFULL)

    The AvailCal index fully covers the most used query. I would say that the query it covers is probably ran about 5000-6000 times a day on average. The data that the query returns must be current at that point in time, so putting the results of the query in another table is not an option. Unfortunately the AvailCal query is also the most complex.

    I've got the AvailCal query to run in under a second most of the time, but it it pulling about 3000 rows out of this table and there is a subquery that hits another table in the query. The subquery is also fully covered, but that table is going to be another thread once I have this table optimized.

    The query already runs fast, but I am trying to get every little ounce of speed out of it. Like I said, I speed up access to this table, my whole application speeds up. Here's the AvailCal query just in case anyone's curious:

    SELECT DISTINCT O.DTE, O.SAVAIL-O.SFULL AS SLOTS, DATEPART(dw,O.DTE) AS WD,
    O.MO, O.DOM, O.YR, RIGHT('0'+LTRIM(RTRIM(CONVERT(CHAR(2),O.HR))),2) AS H, O.MI,
    (SELECT COUNT(*) FROM VPVAC AS V
    WHERE V.OFF_COD='XXXX' AND O.VACDAY>=V.VACSTART AND O.VACDAY<=V.VACSTOP AND
    V.WLIST=1 AND V.VACYEAR=2005) AS WL,
    O.HR FROM VPOFF AS O WHERE O.OFF_COD='XXXX' AND O.YR=2005 AND
    O.MO=5+1 AND O.VACYEAR=2005
    ORDER BY O.MO, O.DTE, O.HR, O.MI


    The subquery is only 19% of the query processing and 63% is sorting the data. My first instinct was to put a clustered index on the table to eliminate the sort, but I didn't want to kill the updates and inserts. If I went to a fill factor of 50%, would I be able to put a clustered index on the table?


    Live to Throw
    Throw to Live
  28. mmarovic Active Member

    A few questions without analysing too much info from the last post (I'll need some time for that):

    1. Why DTS is char(10) instead of smallDateTime? You may gain performance by changing data type.
    2. What is the meaning of dates recorded?
    3. Why is date changed? Are new values much different then old ones?
    4. Do date parts recorded in columns yr, mo, dom, hr, mi come from DTE or vacDay?
  29. mmarovic Active Member

    5. Why do you have both vacYear and the VacDay?<br />6. Why do you order by o.mo when it is fixed by where condition o.mo = 6? Why 5+1 instead of 6? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />7. What is the purpose of returning 3000 rows? If they are supposed to be displayed so user can review them, better display first N (e.g 50) and offer to show more on request. The query runs fast but client application needs some time to fetch and display them.
  30. Will192 New Member

    1. Why DTS is char(10) instead of smallDateTime? You may gain performance by changing data type.

    DTE is a date in the form YYYY-MM-DD.

    2. What is the meaning of dates recorded?
    3. Why is date changed? Are new values much different then old ones?

    DTE and VACDAY is calendar day, IT IS NOT CHANGED. My mistake!!! I got it confused with the vacation table. So the columns, OFF_COD, VACDAY, DTE, VACYEAR, YR, MO, DOM, HR and MI aren't updated. So here is the new list:

    OFF_COD char (4)
    VACYEAR smallint
    DTE char (10)
    YR smallint
    MO tinyint
    DOM tinyint
    HR tinyint
    MI tinyint
    SAVAIL smallint - UPDATED
    SFULL smallint - UPDATED
    VACDAY smalldatetime

    4. Do date parts recorded in columns yr, mo, dom, hr, mi come from DTE or vacDay?

    The parts come from both. They are inserted once and then the columns SAVAIL and SFULL are updated as people modify vacation.

    VACYEAR and YR can be different cause you can have a Vacation day carryover from the previous year.

    Live to Throw
    Throw to Live
  31. mmarovic Active Member

    What is table pk?
  32. mmarovic Active Member

    quote:DTE is a date in the form YYYY-MM-DD.
    Define DTE as smallDateTime, its size is 4 bytes, that will help performance, because more items will fit the buffer.
    quote:4. Do date parts recorded in columns yr, mo, dom, hr, mi come from DTE or vacDay?

    The parts come from both. They are inserted once and then the columns SAVAIL and SFULL are updated as people modify vacation.

    VACYEAR and YR can be different cause you can have a Vacation day carryover from the previous year.
    I didn't quite understand, what is the meaning of DTE?

    Anyway, I think you can drop 'derived' columns and use just dates. That will make rows and index smaller, so more data fits the buffer and you may have also smaller b-tree depth.

    I would create clustered pk with fill factor 0 and leave SAVAIL and SFULL out of any index.
  33. mmarovic Active Member

    Do you mean DTE = convert(vacDay, char(10), &lt;some date format number&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' />?
  34. Will192 New Member

    DTE stands for DATE, I don't like to use reserved words for column names. The format for the DTE column is CONVERT(VACDAY, CHAR(10), 21). DTE is not a calcuated column, but it is calculated off of VACDAY when the row is initially inserted.

    I know that I could eliminate the derived columns, but they are never changed once inserted and it speeds up the select queries. If they never change then it serves to preprocess the DATEPART(MO, VACDAY) and similar processes.

    Not sure what you guys are meaning by asking what the table pk is. I don't see anywhere that I talk about table pk.

    If I go fill factor 0, doesn't that just mean database default?

    Live to Throw
    Throw to Live
  35. mmarovic Active Member

    quote:DTE stands for DATE, I don't like to use reserved words for column names. The format for the DTE column is CONVERT(VACDAY, CHAR(10), 21). DTE is not a calcuated column, but it is calculated off of VACDAY when the row is initially inserted.
    You don't need that column at all.

    quote:I know that I could eliminate the derived columns, but they are never changed once inserted and it speeds up the select queries. If they never change then it serves to preprocess the DATEPART(MO, VACDAY) and similar processes.
    It makes sense if you ever select rows by monts without specifying year or by day without specifying year and months. In that case you can make indexes starting with such column. But you can make this column computed and define index on it. If you allways select rows by date range then you can get rid of derived columns and you don't need indexes on date part.
    quote:Not sure what you guys are meaning by asking what the table pk is. I don't see anywhere that I talk about table pk.
    I guess you haven't created pk constraint on that table, but it still may exist subset of columns identifying single row. Maybe off_code + vacDay? if so create clustered pk on these columns.
    quote:If I go fill factor 0, doesn't that just mean database default?
    It is default unless you changed it. It is similar to fill factor 100% but there is space for a couple of items in each non-leaf node.
  36. Will192 New Member

    DTE column - if I am pulling all records for one day and I pull based on the DTE column, it is faster than doing the CONVERT on the VACDAY column. That is the whole reason for keeping the DTE column.

    Isn't having an index on a computed column a thing for SQL 2005?

    pk - primary key constraint. Gotcha. I thought you guys were refferring to something else. If I am not doing a key join in the structure of the database, then why do I need a primary constraint? You can't have a pk wihtout a clustered index, right? I will look into setting up a primary constraint if I decide to make the AvailCal index clustered.

    I know that this thread isn't resolved yet, but I just wanted to take the chance to say thanks for all of the input that everyone has had on this thread.

    Live to Throw
    Throw to Live
  37. mmarovic Active Member

    quote:DTE column - if I am pulling all records for one day and I pull based on the DTE column, it is faster than doing the CONVERT on the VACDAY column. That is the whole reason for keeping the DTE column.
    You don't have to convert anything. Try:

    select *
    from VPOFF
    where vacDay between '20050929 0:00:00' and '20050930 0:00:00'
    and off_cod = 'xxx'
    Create clustered pk on off_cod and vacDay before.
    quote:Isn't having an index on a computed column a thing for SQL 2005?
    No.
    quote:If I am not doing a key join in the structure of the database, then why do I need a primary constraint?
    To controll uniqness and to use index created automatically for faster access.
    quote:You can't have a pk wihtout a clustered index, right?
    Wrong.
    quote:I will look into setting up a primary constraint if I decide to make the AvailCal index clustered.
    You mean index containing all table columns? That index should be dropped. Optimal table and index design would be:
    pk columns:
    -----------
    OFF_COD char (4)
    VACDAY smalldatetime
    ---------------------
    other columns:
    ---------------------
    VACYEAR smallint
    SAVAIL smallint
    SFULL smallint
    ----------------
    Make pk clustered, based on queries you can create index on vacYear column. Learn to make date range queries and your app will fly.

    [Edited] Made sure it is clear that pk columns should be off_cod and vacDay.
  38. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by mmarovic</i><br /><pre id="code"><font face="courier" size="2" id="code">select *<br />from VPOFF<br />where vacDay between '20050929 0:00:00' and '20050930 0:00:00'<br /> and off_cod = 'xxx'<br /></font id="code"></pre id="code"><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Being a lazycoder, you can avoid some keystrokes by doing<br /><pre id="code"><font face="courier" size="2" id="code">select *<br />from VPOFF<br />where vacDay between '20050929' and '20050930'<br /> and off_cod = 'xxx'<br /></font id="code"></pre id="code"><br />Since omitted time portion always default to midnight.<br />So far for the pseudo thread-related stuff.<br /><br />Mirko, now that you've done most of the analysis yourself, what about doing the writing yourself, too? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  39. mmarovic Active Member

    Frank, I guess you are talking about another thread related to index design. I guess you are right, I spent so much time on it, I should spend a litle bit more and make an article.

    About query, you are right, but I wanted to make it explicit and I made a small mistake, correct query is:


    select VacDay
    from VPOFF
    where vacDay >= '20050929' and vacDay < '20050930'
    and off_cod = 'xxx'
  40. mmarovic Active Member

    Another correction, you may need non clustered index on vacDay only on top of what I already proposed. Another possibility is to create pk on VacDay and off_cod in that order and non-clustered index on off_cod and another non-clustered index on VacYear.
  41. FrankKalis Moderator

    Another correction [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Both queries are equivalent<br /><pre id="code"><font face="courier" size="2" id="code"><br />CREATE TABLE #t<br />(<br /> k1 INT IDENTITY<br /> , d1 SMALLDATETIME<br />)<br />INSERT INTO #t VALUES('20050929')<br />INSERT INTO #t VALUES('20050929 23:59:00')<br />INSERT INTO #t VALUES('20050930 00:01:00')<br />SELECT *<br /> FROM #t <br /> WHERE d1 &gt;= '20050929' AND d1 &lt; '20050930' <br /><br />SELECT *<br /> FROM #t <br /> WHERE d1 BETWEEN '20050929' AND '20050930' <br /><br />DROP TABLE #t<br /></font id="code"></pre id="code"><br />Return the same resultset.<br /><br />...and yes, I think you are right. Seems like I've mixed up these threads.<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  42. mmarovic Active Member

    Frank, you are right regarding the reason I mentioned before figuring out it was wrong, so I edited my post. So the reason is not having hours and minutes other then 0, it is just the oposite: if I have a row '20050930 0:00:00' then query will return that row too. If we add:<pre id="code"><font face="courier" size="2" id="code">insert into #t('20050930')</font id="code"></pre id="code">two queries will return different resultset.<br /><br />I made too many sloppy mistakes recently [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]
  43. Madhivanan Moderator

    I dont know anything about the discussions except the date query<br />If the requirement is to return 29th sep 2005 records then<br /><br />SELECT * FROM #t WHERE d1 &gt;= '20050929' AND d1 &lt; '20050930' <br /><br />is the correct one<br /><br />Frank, if you include this statement, then I am correct [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />INSERT INTO #t VALUES('20050930 00:00:00')<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  44. Madhivanan Moderator

    [sniped]

    Madhivanan

    Failing to plan is Planning to fail
  45. FrankKalis Moderator

    Another correction [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br />BETWEEN is inclusive. So, it's correct that <br /><pre id="code"><font face="courier" size="2" id="code"><br />INSERT INTO #t VALUES('20050930 00:00:00')<br /></font id="code"></pre id="code"><br />is returned. But even more annoying is the fact that I've chosen SMALLDATETIME as the data type. Check this:<br /><pre id="code"><font face="courier" size="2" id="code"><br />INSERT INTO #t VALUES('20050930 00:00:29')<br /></font id="code"></pre id="code"><br />is also included in the resultset. That's because of the accuracy of this data type. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  46. FrankKalis Moderator

  47. Madhivanan Moderator

    Frank, I think you need holiday today itself [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  48. mmarovic Active Member

    Will, take a look at link Frank posted, it's great guide about datetime queries.<br /><br />Frank, fortunately weekend is just a few hours away, I think I'll stop posting and rest a litle bit and it looks like you need a rest too [<img src='/community/emoticons/emotion-1.gif' alt=':)' />].
  49. FrankKalis Moderator

    Yes, I guess you're right. <br />...and I'll take the whole next week off, too. [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br />May you all have a great weekend!<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  50. mmarovic Active Member

    Have a nice vacation! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  51. Will192 New Member

    Thanks for all the responses. I copied the production table over to another database and I am running tests on different index changes based on this thread. I found some duplicate records, so after I get the application developers to clean them up, I will work on putting a pk on the table.

    I'm not sure what I was thinking about when I posted that you couldn't put an index on a computed column. I have done that before.

    I will look over all the posts today and do some testing and post my results later.

    Live to Throw
    Throw to Live

Share This Page