SQL Tip: LEFT JOINs and WHERE clauses…are they really LEFT JOINs?

There are times when I review SQL queries and find people using LEFT OUTER JOINs but then use WHERE clauses that turn those joins into INNER JOINs. Recently as I mentioned this to someone they reminded me that I should blog about it...so here I am blogging about it. 🙂 First off, if you aren't … Continue reading SQL Tip: LEFT JOINs and WHERE clauses…are they really LEFT JOINs?

Advertisements

SQL Tip: Return only latest record by date

[edit: April 2019] This was originally posted to my internal (to Microsoft) blog on Sep 29, 2011 as part of a series I called 'SQL Tips' for coworkers based on an email distribution list I sent out before the internal blog. I have left this as originally written but have updated formatting for my WordPress … Continue reading SQL Tip: Return only latest record by date

SQL Tip: Create comma separated string from values in a column (& COALESCE)

[edit: April 2019] This was originally posted to my internal (to Microsoft) blog on March 19, 2012 as part of a series I called 'SQL Tips' for coworkers and an email distribution list people could sign up to. I have left this as originally written but have updated formatting for my WordPress theme. Note: there … Continue reading SQL Tip: Create comma separated string from values in a column (& COALESCE)

SQL Tip: Returning a portion of a string field – dynamically (CHARINDEX)

[edit: April 2019] This was originally posted to my internal (to Microsoft) blog on May 1, 2012 as part of a series I called 'SQL Tips' for coworkers and an email distribution list people could sign up to. I have left this as originally written but have updated formatting for my WordPress theme. I can’t … Continue reading SQL Tip: Returning a portion of a string field – dynamically (CHARINDEX)

Hardware Inventory – View to Class/Property Info

Over the past several years I've gotten a lot of questions from people about what WMI class is the source of data for a particular hardware inventory view ("v_GS_..."). This past week I created a couple queries to provide a team who has generally been the source of 95% of these questions and I figured … Continue reading Hardware Inventory – View to Class/Property Info

Change Tracking (aka Syscommittab) Issues and Cleanup – Part 1

Replication in Configuration Manager is talked about pretty frequently. It's a pretty awesome feature, but also a pain at times. One of the reasons I find it to be so cool is that it takes advantage of a SQL feature called Change Tracking. This feature is the fundamental basis for replication - and is a … Continue reading Change Tracking (aka Syscommittab) Issues and Cleanup – Part 1

SQL Tip: Creating a Grand Total (and additional subtotals)

[edit: April 2019] This was originally posted to my internal (to Microsoft) blog on Jan 23, 2012 as part of a series I called 'SQL Tips' for coworkers and an email distribution list people could sign up to. I have left this as originally written but have updated formatting for my WordPress theme. Sometimes when … Continue reading SQL Tip: Creating a Grand Total (and additional subtotals)