Reputation
Next tag badge:
572/400 score
77/80 answers
Badges
8 104 163
Newest
 Enlightened
Impact
~1.6m people reached

3h
comment To find SQL Server is actually used or not?
@sean the phrase I used was "get some idea" not "establish with certainty". Looking in both those places has provided useful information to me when looking at decommissioning processes.
10h
comment To find SQL Server is actually used or not?
You can look at cached plans and index usage stats to get some idea too.
11h
comment SQL Row concatenation with XML PATH and STUFF giving aggregate sql error
@B.Mc something like this pastebin.com/Fz601fDC
1d
comment Conditionally add an index to a temp table
Is PKColumn the PK of the temp table? I.e. It's unique?
1d
comment Database stored procedure with a “preview mode”
This approach seems like a lot of overhead in terms of unnecessary locks and transaction log usage.
1d
comment sp_procoption seems to be failing on SQL 2005 instance
Does the code to set up the trace work correctly when you just run it outside a stored procedure in 2005. I suspect not so concentrating on sp procoption would be a red herring.
2d
comment Amazing improvement of script execution speed when split - not understood
@High no. In fact some people regularly do the exact opposite and assign parameters to variables to defeat parameter sniffing because of the side note. Using 'option recompile' with variables in your original script would have worked just as well and avoided the side note issue but then you pay for extra compilation time which might not be needed. Probably in your case it would be negligible compared to overall execution time without though.
2d
comment Execution time with warm up cache
Which situation is a user going to typically encounter? If you are confident that most of the time the data will be in cache then that will be the more representative value. Though you might still look at cold cache speed to see the worst case.
2d
comment Amazing improvement of script execution speed when split - not understood
Is the original long script using variables that have become parameters in the new arrangement?
2d
comment Script to Delete Tables With A Prefix
No it is never guaranteed. stackoverflow.com/questions/15138593/…
2d
comment Script to Delete Tables With A Prefix
This is not a guaranteed approach to string concatenation connect.microsoft.com/SQLServer/feedback/details/606786/…
Apr
27
comment SPID Change for a process?
No this isn't possible.
Apr
27
comment How to Join two table using pivot with combining two field in one table
@paramtrivedi no problem!
Apr
27
comment How to Join two table using pivot with combining two field in one table
You would need to look at the places in the query that currently have hardcoded values and extract the actual distinct values from the table instead and use these instead of the hardcoded values. If you google "dynamic pivot" you wil find examples.
Apr
27
comment How to Join two table using pivot with combining two field in one table
You can have P2_Src and P2’ just follow straight after ’P1 with a comma separating them. In the same way as P1_Src follows P1 in my answer above. Put the expressions calculating Criteria in P1 and P2 then the final select joins those two table expressions.
Apr
27
comment How to Join two table using pivot with combining two field in one table
@paramtrivedi Concatenate them together as a single column in the P2_Src table so that has three columns DegreeField, Val, Col
Apr
27
comment How to Join two table using pivot with combining two field in one table
What RDBMS are you using? Are the output columns fixed? Why are you doing this in SQL rather than in the presentation layer?
Apr
27
comment Where can I find the SQL Server to download?
blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/…
Apr
27
comment LOB_DATA, slow table scans and some I/O questions
@SeanGallardy yep, seems like it then :-)
Apr
27
comment LOB_DATA, slow table scans and some I/O questions
@sean where do you get 10 from? The question States 1000. Each Lob can be split over multiple pages. So presumably in this case on average 13. Select * means all columns will be returned of course but if some aren't needed just use an explicit column list and don't select them (better practice anyway). I don't see partitioning the table as relevant.