Martin Smith

18,132 reputation

43782

bio	website
	location	United Kingdom
	age
visits	member for	1 year, 8 months
	seen	6 hours ago
stats	profile views	1,440

Email: stackoverflowmartinsmith@gmail.com

	18,132 reputation	bio	website		visits	member for	1 year, 8 months
43782 badges		location	United Kingdom		seen	6 hours ago

summary answers questions tags badges favorites bounties reputation activity

2,014 Actions

suggestions reviews revisions comments badges posts accepts all

7h	comment	What is the difference between these two index declarations and do I need both? @MatthewSteeples It is only stored on the leaf pages then. Not the upper level pages.
12h	awarded	Enlightened
12h	reviewed	Close How much time is considered 'Ideal' for a SELECT query?
12h	reviewed	Reviewed Is it ok to let users add new columns and tables to your database?
13h	awarded	Nice Answer
14h	comment	Nonclustered index is faster than clustered index? I actually agree with the vast majority of your answer and your sentiments about not taking costs too literally. The only part that jarred was the extrapolation in the last paragraph because the costing model already does something pretty similar to your manual estimation.
15h	comment	Why does selecting top 1 from composite index DESC also used to partition by month not select the top value? @PaulWhite - Ah thanks very much for that.
18h	comment	Nonclustered index is faster than clustered index? According to http://www.qdpma.com/ppt/CostFormulas2.ppt‎ The formula used to cost an Index Scan or Index Seek without lookup is (version dependant) IO (0.003125 + 0.00074074 per page) and CPU (0.0001581 + 0.0000011 per row). The fixed costs and rows are equal for CI and NCI so the only variable is pages.
18h	comment	Nonclustered index is faster than clustered index? Generally speaking it is yes but in this case the reason why the query optimiser costs the clustered index as more than the non clustered index (the subject of this question) is precisely because of the different page counts.
19h	comment	What is the difference between these two index declarations and do I need both? Regarding "do I need both?" impossible for us to tell you as it requires an evaluation of costs and benefits to your overall workload.
19h	answered	What is the difference between these two index declarations and do I need both?
22h	comment	How to write a query to find all tables in a db that have a specific column name When asking questions it is better to check back and respond to any requests for clarification.
23h	comment	Need to divide single column into multiple columns @SureshE - If the number of values is not evenly divisible by 5 then the last row will have `NULL`s to pad out the missing value. If the source data has `NULL`s these will appear in the results. If neither of those explain it please provide more details.
1d	comment	Nonclustered index is faster than clustered index? SQL Server does aleady refer to the `data_pages` in `sys.allocation_units`. You can see this from `CREATE TABLE T1(C INT);CREATE TABLE T2(C INT);UPDATE STATISTICS T1 WITH PAGECOUNT = 1;UPDATE STATISTICS T2 WITH PAGECOUNT = 100` then comparing the estimated costs `SELECT * FROM T1;SELECT * FROM T2;`
1d	comment	Nonclustered index is faster than clustered index? It is a bit of a leap to infer that your `10%/90%` figure is more accurate than the `38%/62%`. Strings with a length between 100 and 200 will certainly be a gross overestimate of space requirements for a firstname/lastname pair so you will have lower page density than the OP. When I try against your example data the estimated costs show up as 87%/13%.
1d	comment	Trying to find the last time that a value has changed On SQL Server this gives the error. `Cannot perform an aggregate function on an expression containing an aggregate or a subquery`
1d	comment	Trying to find the last time that a value has changed Did you pick your username on a similar basis?
1d	revised	Trying to find the last time that a value has changed edited tags
1d	answered	Nonclustered index is faster than clustered index?
2d	comment	Index usage stats DMV indicates no index activity Ah re your edit. SQL Express auto closes databases by default so you probably lose it then.