One table will be quicker to search on - but will slow down inserts, updates and deletes if you have numerous indexes on it
-----Original Message----- From: Clark Baker [mailto:[EMAIL PROTECTED]] Sent: 21 January 2003 13:43 To: SQL Subject: RE: Data storage best practice Hey everybody, I've got an add-on question then... We have a large table (large # of columns) that has several date/time fields. These fields, along with several other tinyint and smallint fields are used as search criteria for the main search of our site, so they get searched on a lot. Is it better to have all these fields in one table and just pick the best field to be a clustered index and make the rest regular indices -OR- should I break the table up into smaller tables so that I can have multiple clustered indices (one clustered index for each new mini-table) and then join the tables when we do the query for the site's main search? Now that I type it and read it the latter seems silly. Clark -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Robertson-Ravo, Neil (RX) Sent: Tuesday, January 21, 2003 5:15 AM To: SQL Subject: RE: Data storage best practice Yeah sure, [sic/] : Clustered Indexes -------------------------------- A clustered index determines the physical order of data in a table. Think of it as a phonebook, which arranges data by last name. Because the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index. However, the index can comprise multiple columns (a composite index), like the way a telephone directory is organized by last name and first name. Note : a table can contain only one clustered index, but can contain multiple individual indexes and It is important to define the clustered index key with as few columns as possible. You should use a clustered in index for columns that contain a limited number of unique values, queries that return a range of values or queries which are using operators such as BETWEEN, >, >=, <, and <=, and qQueries that return large result sets. A clustered index is particularly efficient on columns that are often searched for ranges of values. After the row with the first value is found using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent. For example, if an application frequently executes a query to retrieve records between a range of dates, a clustered index can quickly locate the row containing the beginning date, and then retrieve all adjacent rows in the table until the last date is reached. This can help increase the performance of this type of query. [sic/] : Unique Indexes ----------------------------- Creating a unique index ensures that any attempt to duplicate key values fails. If a single query is created that causes duplicate and nonduplicate key values to be added, SQL Server rejects all rows, including the nonduplicate key values. For example, if a single insert statement retrieves 20 rows from table A and inserts them into table B, and 10 of those rows contain duplicate key values, by default all 20 rows are rejected. (However, the IGNORE_DUP_KEY clause can be specified when creating the index that causes only the duplicate key values to be rejected#; the nonduplicate key values are added - In the example above only the 10 duplicate key values would be rejected; the other 10 nonduplicate key values would be inserted into table B). NOTE : A unique index cannot be created if there are any duplicate key values and you cannot create a unique index on a single column if that column contains NULL in more than one row. Similarly, you cannot create a unique index on multiple columns if the combination of columns contains NULL in more than one row. These are treated as duplicate values for indexing purposes. This make sense? Neil -----Original Message----- From: Alex Husic [mailto:[EMAIL PROTECTED]] Sent: 21 January 2003 09:32 To: SQL Subject: Re: Data storage best practice Does clustered index mean one index with all the necessary fields in it? Should we create individual indexes, too? Could anyone explain best practices between clutered and individual indexes? Thanks Alex ----- Original Message ----- From: "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Tuesday, January 21, 2003 9:19 AM Subject: RE: Data storage best practice > > Yep, a decent clustered index will fo you fine, 300K records is piddly > compared to some of the DB's we use here (10-15 million records within > some). > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: 21 January 2003 06:26 > To: SQL > Subject: RE: Data storage best practice > > > As long as you index your table, 300,000 should not be any problem > what so ever. > > Christian Watt > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Monday, January 20, 2003 10:03 PM > To: SQL > Subject: Data storage best practice > > I'm running SQL 2k for the lists and have some new enhancements (like > full text > searching) going in soon. I've got a question about the best way to > store and retrieve the data. With a DB of 300,000 records with about > 100,000 records per > year, is it better to have all of the records in a single table or would it > be > better to have multiple tables with each years records? > On one hand I'd have a single huge table and on the other I'd have 1 > table for each year with about 100,000 records. Which would be the > better? Is there any > downside to having a single large table? > Thanks > > Michael Dinowitz > Master of the House of Fusion > http://www.houseoffusion.com > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
