SQL Join Resources

A Join combines records from two (or more) tables to create a new data set.  There are several ways to join SQL tables.  Below are the most common Joins.

• INNER Join – Result will contain matching records from both tables
• LEFT Join – Result will contain all records from the “left” table even if the join-condition does not find any matching record in the “right” table
• RIGHT Join - Result will contain all records from the “right” table even if the join-condition does not find any matching record in the “left” table
• FULL Join – Result will contain all records from both tables, and fill in NULLs for missing matches on either side
• CROSS Join – Result will contain returns the cartesian product of the sets of records from the two joined tables (Dangerous with large tables)

Instead of including all the normal code snippets illustrating each type of join, I am listing links to others who have done that job very well.

• Jeff Atwood gives a wonderful Visual Explanation of SQL Joins.  He describes each join in detail.
• Kathi Kellenberger posted a great article on Writing Outer Joins in T-SQL.  She gives a brief review of the INNER Join, then goes on to a much more detailed description of OUTER Joins.
• Jeff Smith gives us a warning to Be Careful When Mixing INNER and OUTER Joins.  Warnings are always a great way to learn from other people’s experiences.
• Kenneth Downs created a very informative post about Joins titled The JOIN is the Cornerstone of Powerful Queries.  This article too, provides an abundance of information about Joins.
• Jason Massie suggested a great set of articles on joins from Craig Freedman.  There looks to be some in-depth information on joins here.

The above articles are ones the I will likely reference over and over again.

Column Headings from SQL Results - Easier Way

I posted yesterday on pulling the column headings from a SQL Server result set and transferring them to Excel.  I received a comment from Scott Stonehouse outlining a much easier way to do what I need to do.

Simply access the Tools Menu and select Options.  On the left, expand Query Results/SQL Server and select Results to Grid.  On the right check Include column headers when copying or saving the results.

Run a query in SQL Server Management Studio and select the contents of the result set.

 

Paste the results into Excel - like magic, there are the column headings.

 

To me, Scott proved what the Internet and blogs are for.  Teaching and Learning.

Column Headings from SQL Results

I have had some one-off requests to basically dump data from SQL Server to an Excel spreadsheet.  I am the kind that wants to make the spreadsheet look decent, so I would like to at least have column headings.  The below process provides a less painful way to get column headings from a SQL results grid into Excel. 

In SSMS, go to Query>Results To>Results to Text

Run the query
Copy the column heading line and paste into NotePad

Next, put the headings on separate lines by doing CTRL-Right Arrow-Enter until you hit the end of the list

 

Copy the list and paste into Excel starting on line 2
Once in Excel, do a Ctrl C, select A1 and Paste/Paste Special and Check transpose (ok)

As you can see the column headings are now on Row A

Go back to SSMS and go to Query>Results To>Results to Grid
Run the query

Copy the contents and paste it in the Excel file starting in Row B

   

–

This process helps tremendously when the result set contains more than 20 fields; however, if you find yourself pulling the same data on a regular basis, I recommend using SQL Server Integration Services or Excel automation.

–

HDNUG Meeting - 04/10/08 - Recap

I attended the Houston .NET User Group meeting Thursday night where there was a great turnout of well over 100 people.  The sponsor was Clearpoint Technology.

        

The presentation was given by Mohammad Azam and the topic was LINQ to SQL and Gotchas.  Mohammad is a very interesting presenter and really knows his stuff.  He is what I like to call a coding presenter.  A presenter that codes their samples on the spot and does not have a ton of slides in the slide deck.  Although, he did present his first slide that read “Slide 1 of 347″  (or something like that) as a joke to break the ice.  Well it worked, he had the whole room laughing.

Below are some of the items/points presented.

• CRUD operations using LINQ to SQL
• The DelayLoaded property in the data designer
• Data load options
• LINQ to SQL as an alternative to calling Stored Procedures from C# code
• Anonymous Data Types

Mohammad finished the presentation by going over some of the problems he encountered while working with LINQ to SQL.

There was a plethora of information in the presentation - so much it was difficult to note everything.

    

–

Related Content

• My Presentation at Houston .NET User Group on LINQ to SQL - Mohammad Azam
• Houston .NET User Group Meeting LINQ to SQL Demo Downloads! - Mohammad Azam

HASSUG Meeting Recap - 04/08/08

I attended the Houston SQL Server User Group meeting today during my lunch hour.  The speaker was Randy Dyess with Solid Quality Mentors and the topic was SQL Server memory management.

There were about 30 people in attendance.  Up from the past.  When asked, several said they heard about the group via the Microsoft Launch last month.

It was an information-packed presentation.  So packed that there was not enough time to finish the entire demo.

[Download Presentation Materials] 

–

Some of the items covered

• Memory Limits
• Memory Extensions

1. AWE - Address Windowing Extension
2. PAE - Physical Address Extension

• Cache Concepts (cache - Basically, memory broken into 8k pages)
• Memory Allocation

1. Dynamic Memory Allocation
2. Fixed Memory Allocation (SQL Server’s memory)

• SQL Memory

1. MemToLeave (aka Memory Outside Buffer Pool)
2. Worker Threads
3. Buffer Pool

• Linked Servers
• SQL CLR
• Memory Counters
• Extended stored procs run in unmanaged memory

–

Great SQL Server Blogs

I am always looking for good resources to help me do my job. In this post I will list some SQL Server based blogs that have helped me a log lately.

• Joe Webb - Musings and observations about SQL Server
• Pinal Dave - SQL Server Authority
• Denis Gobo - SQL Blog
• Kalen Delaney - SQL Blog
• Simon Sabin - Blog on SQL Server stuff
• The Database Programmer - All things related to database applications
• Jeff Smith - Random Thoughts and Cartesian Products w/ MS SQL Server

The above blogs are great resources for SQL Server and I highly recommend them.

–

Point in Time Architecture

One of the projects I am working on is using a Point in Time Architecture (PTA) for the database model.  This kind of architecture is very useful when history and audit trails are important.  Basically, nothing is ever “really” deleted. This is accomplished by using begin and end dates on records and capping the end date when an update or deletion is performed.

Example:

 
CLICK FOR LARGER IMAGE

As you can see from the above example, each time an update is made, the SystemEndDate is populated and a new record is inserted with a SystemStart date of the same value.

I really like the idea of the Point in Time Architecture.  It makes my life easier when customers call me wanting to know who deleted an account or who posted against an account.

Authur Fuller posted a great article on Simple-Talk defining a Point in Time Architecture.  It goes into great detail on the requirements, implementation details and dealing with the different operations (insert, update, delete, select).

If history and/or and audit trail is important, the Point in Time Architecture is a viable option.

New Vertical Tab Group in SSMS

There is an option in SQL Server Management Studio that I did not know about until today. It is the New Vertical Tab Group option (there is a Horizontal too, I just find the Vertical more useful). This option is helpful if you have more than one tab open (ie, a query on one and a table view on another).

On a screen with two or more tabs, right-click on the tab that you would like to see in a split screen/tab and select New Vertical Tab Group.

That is all there is to it. Now it is easy to reconcile things like fields for a query, etc.

I have found this most helpful if I am building a query from a table and would like to see the table and the query that I am building at the same time. It is easier than going back and fourth between the two tabs.

–

Handling Dates in SQL Server

Manipulating dates on any platform can be difficult. In SQL Server, there are several functions that make this task a little easier.

GetDate - Returns the current system date. [GetDate()]

GetUTCDate - Returns the current UTC date/time. [GetUTCDate()]

DatePart - Returns an integer that represents the specified part of the specified date. [DatePart(datepart,date)]

DateAdd - Returns a new datetime value based on adding an interval to the specified date. [DateAdd(datepart ,number,date)]

DateDiff - Returns the number of date and time boundaries crossed between two specified dates. [DateDiff(datepart,startdate,enddate)]

DateName - Returns a character string representing the specified datepart of the specified date. [DateName(datepart,date)]

–

Below are some sample uses of the above functions:

   1: SELECT

   2:     GETUTCDATE() AS UtcDate, 

   3:     GETDATE() AS CurrentDate,

   4:  

   5:     – Equivelent to using MONTH(GETDATE()), DAY(GETDATE()) and YEAR(GETDATE())

   6:     DATEPART(month, GETDATE()) AS CurrentMonth,

   7:     DATEPART(day, GETDATE()) AS CurrentDay,

   8:     DATEPART(year, GETDATE()) AS CurrentYear,

   9:  

  10:     – Add 3 months to the date

  11:     DATEADD(month ,3,GETDATE()) AS ThreeMonthsFromNow,

  12:  

  13:     – Number of days between today and Christmas

  14:     DATEDIFF(day,GETDATE(),‘12/25/2007 00:00:00′) AS DaysTillChristmas,

  15:  

  16:     – Return the character month

  17:     DATENAME(month,GETDATE()) AS MonthText

–

There are many more ways to manipulate dates. I have provided links to some related articles below. I especially like the FormatDate function I found in my SQL Server Magazine this month created by Roy Byrd.

Display Dates in the Format You Need
SQL Server DateTime Formatting
How to get the name of the day of date
Retrieving the Date From a SQL Server DateTime
Working with Date/Time values in SQL: Don’t Format/Convert — just use DATETIME

–

Good SQL Server Tips

I was reading my September/October Code Magazine and ran across a great article by Kevin Goff called The Baker’s Dozen: 13 Productivity Tips for Transact-SQL 2005. He does the Baker’s Dozen Series for Code Magazine and I usually find his stuff really helpful. I am trying to learn more about SQL Server and this time he helped me even more, so I thought I would do a quick post about the article and the man.

I advise checking out Kevin’s blog because I read in most of his articles that if he has ideas after the article is published, that is where they will be.

Related Links
The Baker’s Dozen: A 13-Step Crash Course for Learning WCF
The Baker’s Dozen: 13 Productivity Tips for the Windows Forms DataGrid

There are several Baker’s Dozen articles in the series.  Happy Reading…