AlexKuznetsov

3,627 reputation

1820

bio	website	sqlblog.com/blogs/…
	location	Chicago, IL
	age
visits	member for	1 year, 5 months
	seen	1 hour ago
stats	profile views	234

Developer

	3,627 reputation	bio	website	sqlblog.com/blogs/…	visits	member for	1 year, 5 months
1820 badges		location	Chicago, IL		seen	1 hour ago

summary answers questions tags badges favorites bounties reputation activity

289 Comments

suggestions reviews revisions comments badges posts accepts all

May 13	comment	Gaps and islands: client solution vs T-SQL query @MarkStorey-Smith I apologize for the delay: I needed to complete a project.
Apr 18	comment	Only allow one checked row in a Column in SQL Server Remus, this is a common requirement. For example, I have a large to-do list, but there is at most one thing I am working on right now.
Apr 18	comment	Only allow one checked row in a Column in SQL Server +1 I would do the same thing. In fact, this is a common requirement. For example, I have a large to-do list, but there is at most one thing I am working on right now.
Apr 17	comment	Unique Constraint with multiple null columns I cannot figure out what your problem is. Can you post your CREATE TABLE statemnts?
Apr 12	comment	Would using timestamps solve consistency issues with snapshot isolation? I would not ever suggest turning on READ_COMMITTED_SNAPSHOT at the database level, at least not without very serious code review, because it can introduce lots of subtle bugs.
Apr 9	comment	Hash in SQL query What is your RDBMS?
Apr 5	comment	Gaps and islands: client solution vs T-SQL query @jcolebrand if there is no limit on length of intervals, then I need to scan a huge range even if I only need gaps during one hour. Still, I think although I simplified the problem as much as possible, yet it still resembles real life ones.
Apr 5	comment	Gaps and islands: client solution vs T-SQL query @MarkStorey-Smith sure. Can you provide more details - what column types/widths to add?
Apr 5	comment	Gaps and islands: client solution vs T-SQL query @jcolebrand Can you provide an example of "abstracted the data from the tables into properly optimized temp tables". Also can you explain what do you mean by "Islands aren't sets. They're the absence of sets".
Apr 4	comment	Gaps and islands: client solution vs T-SQL query @jcolebrand yes I think so. Relational theory assumes unordered data sets, but temporal data inherently ordered, so RDBMS is not quite cut out to work with temporal data. What do you think?
Apr 4	comment	Gaps and islands: client solution vs T-SQL query @PeterLarsson can you suggest a better way to benchmark? Writing to a file mimics quite slow consumption of data by the client.
Apr 4	comment	Gaps and islands: client solution vs T-SQL query This is about 30% faster than your other solution. 1 gap: (00:00:12.1355011 00:00:11.6406581), 2M-1 gaps (00:00:12.4526817 00:00:11.7442217). Still this is about 25% slower than the client side solution in its worst case, exactly as predicted by Adam Machanic on twitter.
Apr 4	comment	Gaps and islands: client solution vs T-SQL query Peter, on a data set with one gap this is more than 10 times slower: (00:00:18.1016745 -- 00:00:17.8190959) On the data with 2M-1 gaps, it is 2 times slower: (00:00:17.2409640 00:00:17.6068879)
Apr 4	comment	Gaps and islands: client solution vs T-SQL query Most applications want to use IEnumerable<SomeClassOrStruct> - in this case we just yield return instead adding a line to a list. To keep this example short, I have removed lots of things not essential to measuring raw performance.
Mar 25	comment	How do you document your databases? I do not think there is one best practice fit for all situations.
Mar 25	comment	How do you document your databases? You can change something and forget to change your extended properties accordingly, rendering them incorrect. Can you automatically detect such discrepancies?
Mar 19	comment	SQL Server in a state of confusion: does the database exist or doesn't it? I think this is a bug, right?
Mar 16	comment	Handling concurrent access to a key table without deadlocks in SQL Server @MarkStorey-Smith It is my first choice because I have researched and stress tested it only once, and I can reuse it in any situation - the serializing has already happened, so everything that happens after sp_getapplock does not effect the outcome. With standard locking mechanisms, I can never be so sure - adding an index or just getting another execution plan can cause deadlocks where before there were none. Ask me how I know.
Mar 13	comment	Handling concurrent access to a key table without deadlocks in SQL Server Sure, MERGE is not free of bugs. I would suggest trying out sp_getapplock - it works for me.
Mar 13	comment	Handling concurrent access to a key table without deadlocks in SQL Server it is very easy to have sp_getapplock become a deadlock victim, but not if you begin transaction, call sp_getapplock once to acquire an exclusive lock, and go ahead with your modification.