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 Get the mailserver that powers this list at http://www.coolfusion.com
