2,486 reputation
1726
bio website 127.0.0.1
location Frankfurt Am Main, Germany
age 26
visits member for 2 years, 10 months
seen 8 hours ago

If you want to contact me privately leave a temporary comment under any of my posts. Leave contact details.


1d
comment Will a non-clustered index on a column with covering index on all the columns of table affect the Delete statement Records)?
Look at the execution plan. It answers all of this.
Dec
18
comment In SQL Server, can I guarantee an order without an explicit ORDER BY clause when an index seek is forced on a table with only a clustered index?
@JohnnyM what about solving this in the client? Is this solution not acceptable? With SQL CLR you can stop pulling results early as well.
Dec
17
comment In SQL Server, can I guarantee an order without an explicit ORDER BY clause when an index seek is forced on a table with only a clustered index?
I thought of simply using a temp table containing the distinct customer ids. That code is not shown but easy to write. This is just a sketch of the solution. Maybe it was too brief but I hope the key idea is coming through!
Dec
17
answered In SQL Server, can I guarantee an order without an explicit ORDER BY clause when an index seek is forced on a table with only a clustered index?
Dec
17
comment In SQL Server, can I guarantee an order without an explicit ORDER BY clause when an index seek is forced on a table with only a clustered index?
I failed to find a single-query solution. Are you open to use procedural T-SQL? The perf problem of plan B comes from the fact that all rows for a given store (of which there can be many) are being processed. This can be changed using procedural T-SQL.
Dec
17
comment In SQL Server, can I guarantee an order without an explicit ORDER BY clause when an index seek is forced on a table with only a clustered index?
@CrimsonKing the distinct filters out the 2nd match.
Dec
17
comment In SQL Server, can I guarantee an order without an explicit ORDER BY clause when an index seek is forced on a table with only a clustered index?
@CrimsonKing I was just about to post the same comment but it works because the index is next sorted on Amount DESC. A flow distinct is all that is needed to weed out duplicate customers.
Dec
17
comment SQL Transaction Logs Overwrite
It appears you do not yet understand what tlogs are good for. This puts your data at risk.
Dec
5
awarded  Announcer
Dec
3
accepted What significance does an index_id < 256000 have?
Dec
3
comment What significance does an index_id < 256000 have?
It's probably a design error of SQL Server to give out index_id's with this regularity. They should be randomized so that nobody can mistakenly rely on them.
Dec
3
asked What significance does an index_id < 256000 have?
Dec
3
comment Sql server table insert performance optimization
SELECT INTO is about the fastest insert DML method there is. What throughput are you getting in rows/sec and MB/sec? Maybe it is simply near the expected maximum. What server version is this?
Dec
2
accepted Simulate more RAM with SQL Server
Dec
2
answered Simulate more RAM with SQL Server
Dec
1
comment Why could a SQL Server table grow far quicker than the data to it?
Is this an insert-only table? Did the schema change (dropped columns etc.)?
Nov
30
comment Does it ever make sense to use RAM Disk to force RAM allocation for tempdb with SQL Server 2008
If you do this you store a lot of data twice - on the RAM disk and in buffer pool. Wasteful.
Nov
30
comment Simulate more RAM with SQL Server
@MartinSmith this works! Very easy to do. The WHATIF value influences the <OptimizerHardwareDependentProperties> values in the plan. Using this facility I discovered that I already have enough RAM on my dev box. Lowering the amount of RAM I get the star plan (somewhat surprisingly). This is a very valuable optimization tool.
Nov
30
asked Simulate more RAM with SQL Server
Nov
29
comment Breaking up large transaction - worth it?
If you can endure 800GB spikes what do you care about 50GB? Assuming that you do care: Batching allows log backups to clear the log. A long-running batch prevents that. You can run log backups concurrently with other load. Run one every 5min or so.