259 reputation
112
bio website
location
age
visits member for 2 years, 1 month
seen Dec 17 at 9:35

Dec
1
comment SQL Server job - powershell Where does “log to table” save logs
Thanks, I dont have help configured correctly I think, pressing F1 leads to help about the help viewer :)
Sep
7
comment Undo SQL UPDATE Azure DB
@kin ah.. that info was added later. I'll still let this be here if anyone using the new editions stumbles on this question...
Aug
15
comment Why is my filtered index being ignored?
Got it.. I presume this would be applicable in a scenario where the index is created on (a,xyz) WHERE a IS NOT NULL, if xyz contains 100's of values for each a, SQL would still be able to filter out the a's where it a IS NULL trivially during the query due to the tree structure of the index?
Aug
15
comment Why is my filtered index being ignored?
@AaronBertrand I went through the 1st link just now, but the situation described there is virtually the same as mine, (except that IS NULL is used in the blog, but IS NOT NULL is used in my query. However, thats the situation for both the index and query) The optimizer did use the filtered index in the blog. In my case it does not (except when the filtered index is the only one)
Aug
8
comment Should I check for existence of temp tables in Stored Procedures?
Exactly what I was looking for, esp the code segments! I'll read through the links and post a separate question if I have specific doubts
Aug
8
comment Should I check for existence of temp tables in Stored Procedures?
@AaronBertrand What sort of code will cause an error where the SP fails, but the tables persist? I tried a trivial procedure and it doesnt exhibit this behaviour (call with 1, then 0 (which fails after creating the temp table due to a divide by 0), then 1 as parameters from the same SSMS query window) pastebin.com/yW7D4SgJ
Aug
8
comment Should I check for existence of temp tables in Stored Procedures?
Got it.. any examples of what sort of issues could creep up?
Mar
10
comment db INDEX on ORDER BY FIELD in very simple query does not help - still slow response?
I dont know anything about db2, but assuming it is similar to SQL Server, did you try a clustered index instead of a non clustered index (since you are using a select * query)?
Jan
15
comment Fetch Records alternatively from database
hmm.. apparently theres no straightforward way to do it in MySQL since it doesn't have the equivalents of a CTE or RowNumber, so any method I suggest would be hacky. Probably try out Neo's answer if it works for you (and take ahofmann's advise for the future)
Jan
15
comment Fetch Records alternatively from database
Don't know if its possible in MySQL, but how about using a ROWNUMBER() over Date_Time , join the CTE with itself, with the join condition as rownumber+1 and a where condition to filter out records where the left rownumber is even or the right rownumber is odd
Jan
15
comment Stateful views in SQL Server
@despicable updated answer with more info
Jan
14
comment How does paging work with ROW_NUMBER in SQL Server?
Ah, you're right, theres a performance difference. I had read this: blogs.technet.com/b/dataplatforminsider/archive/2011/11/01/… where he mentions no difference, but just saw channel9.msdn.com/posts/SQL11UPD03-REC-02 where he shows theres a lot of difference.. (though in the audio underemphasis the performance difference)
Jan
14
comment How does paging work with ROW_NUMBER in SQL Server?
Should be noted though that OFFSET/FETCH NEXT do not offer any performance benefits over the CTE method
Dec
12
comment Forcing an index spool
Updated the statistics, though I have the actual data in the table. Still getting the row count spool, guess its a build difference since I'm on 11.0.2218
Dec
12
comment Forcing an index spool
Tried the 1st method, and it works. Thanks. But I get a row count spool after the index spool which you do not get in the plan you have shown. Any idea why?
Dec
11
comment Why are there execution plan differences between OFFSET … FETCH and the old-style ROW_NUMBER scheme?
Not very sure, so putting it as comment, but I guess its because you have the same order by condition for the row numbering and the final result set. Since in the 2nd condition, the optimizer knows this, it does not need to sort the results again. In the first case however, it need to make sure the results from the outer select are sorted as well as the row numbering in the inner result. Creating a proper index on #objects should solve the issue
Nov
29
comment DBCC TRACEON (652) does not disable read ahead reads
Wow, found out 2 awesome things in a single answer, though wondering, how did you find out that there is an exceptional case with the Batch Hash table build? Did you read about that somewhere, or was it just from trial and error?
Nov
28
comment Will using bigint vs mediumint have a performance impact?
Kind of OT, but you mention Why does the user know the value of the ID column? . Isnt this acceptable behaviour in some cases? Example: students in a school will know their roll numbers, and that will probably be the only value uniquely identifying them. The same can be said for a bank account number as well.
Nov
27
comment DBCC TRACEON (652) does not disable read ahead reads
@MartinSmith No, I do have Hash Match (Inner Join) and parallelism though. Have uploaded the XML execution plan, though I have no clue how to read it
Nov
26
comment Bitwise operations in TSQL
True, but like in C I can say 0x00000010 to represent 16 in hexadecimal, or 0b00000010 to represent 2 in binary, is there a similar set of notations for TSQL?