Most SQL developers are familiar with using TOP(n) to return a desired number of results, but what if our ORDER BY column ends up having matching results, do we want to see them? If so, we can use WITH TIES in conjunction with TOP(n). "WITH TIES Used when you want to return two or more rows … Continue reading TOP(n) WITH TIES
I've been using an awesome tool for a while, and I haven't heard a lot of #sqlfamily talk about it so I knew I had to put out a blog about it. The tool I'm talking about is ClearTrace and it's used to normalize and aggregate the data from a SQL Profiler trace. I know, … Continue reading Handy Tool – ClearTrace
I love finding an obscure, rarely talked about feature in SQL Server, and today that is ALTER TABLE...SWITCH PARTITION I was working with a query that was doing several calculations and would take 10- 15 seconds to complete. It was being executed 20-30 times a minute by different users and was causing problems... BLOCKING...eating up CPU...just … Continue reading Table swapping switcheroo (PARTITION SWITCH)
It's T-SQL Tuesday time again, and this month the host is Kendra Little (b | t) whose topic of choice is Interviewing Patterns & Anti-Patterns TL:DR Version - Passion and Aptitude. When hiring someone, make sure they're passionate about the job and have the aptitude to learn and grow. My story starts when I was … Continue reading TSQL Tuesday #93: Interviewing Patterns & Anti-Patterns
Sometimes a sequential chain of numbers gets broken, and sometimes you may want to identify where that break in sequence happens. Well if you’re looking for solution, here is the quick and dirty on how to get it done. In this 2 part series we’ll explore how to identify gaps and islands in your data. … Continue reading identifying broken numeric sequences using SQL – Part 1 (Islands)
When troubleshooting performance issues in SQL Server,one of the more common mistakes I come across are mismatched datatypes in predicates. This can cause a huge hit in performance and sometimes it won't make a difference at all. Check out my sample code below, and watch out for IMPLICIT_CONVERSION !!! /*Step 1 - Run this to … Continue reading IMPLICIT CONVERSION
SQL Server 2005 introduced the OUTPUT clause which has the ability to access the INSERTED and DELETED tables during DML actions. The OUTPUT clause can be added to your T-SQL scripts to write modified row data out to a table or return data back to the client. The OUTPUT clause can be used for auditing and … Continue reading output clause
...to be continued.