Category Archives: SQL Server

I have run across a few great resources for SQL Server recently. Some are new and some have been around for awhile and I’m just discovering them.

• SQLServerPedia is a community wiki-like site. Anyone can edit articles, add new code, etc. Changes are moderated by a group of SQL Server professionals with ten or more years of experience.
• SQL Server Help provides in-depth articles on topics such as SQL Injection, DBA Roles and Transact-SQL code samples. Great resource for developers.
• SQL Server Programming Fundamentals is a series of articles from Blackwasp Software Development. The tutorial is aimed at software developers using SQL Server for data storage.
• A while back Pinal Dave posted Interview Questions and Answers Complete List Download.

Until later…

In the world of Application and DB Development there is always a need to display dates in a format that is understandable to your audience.  For example, in the US we normally expect to see a date as mm/dd/yyyy; however, in Great Britain, the normal format is dd/mm/yyyy.  The Convert function in SQL Server provides a means to translate SQL Server’s default date format to just about any format.

The syntax for the Convert function is as follows:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

–

Available Date Formats –

 Source Code

It shows some of the different ways dates can be formatted and produces the result below.

Below are a few resources that I found on using the Convert function.

• Working with SQL Server Date/Time Variables – DataBase Journal
• Working with SQL Server Date/Time Variables: Part Two – Displaying Dates and Times in Different Formats – DataBase Journal
• What the format codes mean in SQL ‘CONVERT’ – DBForums
• HOWTO – Compare a date string to datetime in SQL Server? – StackOverflow
• How to convert DateTime to VarChar – StackOverflow
• Sql Server string to date conversion – StackOverflow

I also posted a while back about Handling Dates in SQL Server.

–

Happy Learning!

We had a “brown bag” lunch at work last Wednesday and the topic was SQL Server Reporting Services (SSRS). We use SSRS extensively at our company, but I have just recently been exposed to it, so I thought I would post some resources and information on the topic.

SQL Server Reporting Services is a report generation tool, similar to Crystal Reports, that can be used to make interactive and printed reports. The design environment takes a little getting used to, but it is a pretty powerful tool. Reports can be exported to a number of different formats including XLS, PDF and XML.

Information Resources

• Microsoft SQL Server Reporting Services online resources – Joe Webb
• Introducing SQL Server Reporting Services – Wei-Meng Lee
• Getting started with SQL Server Reporting Services – Soshan Fernandes
• SQL Server Reporting Services
• SQL Server Reporting Services Team Blog
• Interactive sorting in SQL Server Reporting Services 2005 – Joe Webb

Video Tutorials

• Reporting Services Part A – DNR TV
• Reporting Services Part B – DNR TV
• Reporting Services Part C – DNR TV
• Reporting Services – ReportViewer Control – DNR TV
• SQL Server Reporting Services Training Videos – LearnReportingServices.com

Support Resources

• SQL Server Reporting Services Forum – MSDN
• SQL Server Reporting Services Forum – ASP.NET

–

Updated 08/28/2008 – I was informed via email of 2 more great resources for Reporting Services.

• Rules to Better SQL Reporting Services – SSW/Adam Cogan
• SQL Reporting Services Suggestions – SSW/Adam Cogan

–

Happy Learning!!!

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.

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.

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.

–