quote:

---

Originally posted by CrazyT

if i were making a create table statement how would i include the indexes in it.

---

quote:

---

Originally posted by Kristen
You need a Unique Constraint (which in SQL will create an index) on all columns.

---

quote:

---

Originally posted by Kristen
Just in case open to interpretation then I meant a separate index on each column, not one index with keys for every column!

---

quote:

---

Originally posted by Kristen

As I assume it (by way of an example)

The table has, say, 10 columns, all UNIQUE, and JOINs to other tables which use each of those 10 (unique) column values

I'm assuming that
1a) SELECTs are far more common than INSERTS
1b) All columns participate in a WHERE or a JOIN reasonably often
2) DELETEs are very rare
3a) UPDATES may occur which change NULL columns to a VALUE (so will cause an ADD to that index, but no changes to other indexes)
3b) UPDATES to other columns (to change an existing value) will be very rare

---

quote:

---

But would you still not index each column given my assumptions?

---

quote:

---

(Unique Constraint is going to be required on each column, but that could be enforced by Trigger to avoid using an index - e.g. if a column is never used in a JOIN or a WHERE)

---

/*** Index Usage Statistics ***************************************************
*
* Reports the reads and writes to the indices of the current database.
*
* Reads indicate that the index has been used in queries
*
* writes indicate the effort of maintaining the index. Therefore the indicies
* with the highest read to write ratio are the indicies used the most be the
* system
*
* Charlie (2009-Dec-18)
*
******************************************************************************/

-- Set this to limit the results to tables / indices above a certain no of rows
DECLARE @rowThreshold INT              SET @rowThreshold = 1000

/*****************************************************************************/
SET NOCOUNT ON

SELECT 
	o.[name] AS [Table Name]
	, i.[name] AS [Index Name]
	, i.[index_id] AS [Index Id]
	, s.[user_seeks] + s.[user_scans] + s.[user_lookups] AS [Total Reads]
	, s.[user_updates] AS [Writes]
	, rws.[rows] AS [Rows]

	, CASE
			WHEN s.[user_updates] < 1 THEN 100
			ELSE 1.00 * (s.[user_seeks] + s.[user_scans] + s.[user_lookups]) / s.[user_updates]
		END AS [Reads Per Write]

	, 'DROP INDEX '
		+ QUOTENAME(i.[name])
		+ ' ON '
		+ QUOTENAME(sc.[name])
		+ '.'
		+ QUOTENAME(OBJECT_NAME(s.[object_id]))
		AS [Drop Statement]

FROM
	sys.dm_db_index_usage_stats s  
	JOIN sys.indexes i ON i.[index_id] = s.[index_id] AND s.[object_id] = i.[object_id]
	JOIN sys.objects o ON s.[object_id] = o.[object_id]
	JOIN sys.schemas sc ON o.[schema_id] = sc.[schema_id]

	-- Rows for the object / index
	JOIN (
		SELECT
			p.[index_Id]
			, p.[object_ID]
			, SUM(p.[rows]) AS [rows]
		FROM
			sys.partitions p
		GROUP BY
			p.[index_Id]
			, p.[object_Id]
		)
		rws ON rws.[index_Id] = s.[index_Id] AND rws.[object_Id] = s.[object_Id]
		
WHERE
	OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
	AND s.[database_id] = DB_ID()   
	AND i.[type_desc] = 'nonclustered'
	AND i.[is_primary_key] = 0
	AND i.[is_unique_constraint] = 0
	AND rws.[rows] > @rowThreshold
ORDER BY
	[Reads Per Write] ASC
	, [Rows] DESC

quote:

---

Originally posted by DBA in the making
The other thing to consider is that "unique" isn't always "unique".

---

quote:

---

Originally posted by CrazyT

all the other columns are not unique. typo on my part.

---

quote:

---

as for what is being done. I will retrieve a 100 rows at a time (primary key, unique key for the data, and a another column -- passing a start/end row with row_number(). Then all I need to do is update two columns in the database on each rows.

So its a simple select but with alot of records. Only 3 columns will have any data to index, with the primary key being one.

---

quote:

---

Originally posted by DBA in the making

quote:

---

Originally posted by DBA in the making
The other thing to consider is that "unique" isn't always "unique".

---

quote:

---

Originally posted by CrazyT

all the other columns are not unique. typo on my part.

---

LOL. ;)

quote:

---

as for what is being done. I will retrieve a 100 rows at a time (primary key, unique key for the data, and a another column -- passing a start/end row with row_number(). Then all I need to do is update two columns in the database on each rows.

So its a simple select but with alot of records. Only 3 columns will have any data to index, with the primary key being one.

---

I don't think using row_number is going to be an efficient way of querying such a table. I just tried it on a table with 1.2m rows, and when selecting high row numbers, the number of reads was huge. The execution plan said it used a clustered index scan, which means it's reading each record sequentially from the clustered index.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

---