Introduction to SQL Server for Windows Administrators…

I presented at the WiNSUG user group here in San Diego last week (Feb 5th, 2009).  My topic was “Introduction to SQL Server for Windows Administrators”.  It was pouring rain, so I really appreciate the folks that made it to the meeting.

I have posted the slide deck from my session up on my website so feel free to download it if you would like: icon_pptxIntroduction to SQL Server for Windows Administrators

Slide Title Screen Shot

Reporting Services Architecture Diagram…

I have a standard diagram that I draw on the board when I teach reporting services classes.  I have lately however been teaching some classes online and drawing this diagram by hand on the screen gets a little tedious.  To simplify my life, I drew it in Visio so I could re-use it.  I am posting it up here so that my students, or anybody else for that matter can grab the diagram and re-use it. 

It represents a typical SQL Server 2005 native reporting services deployment.  This also describes the basic structure of a 2008 install, just replay IIS with HTTP.SYS) I may update it in the future, or create another version to include SharePoint installations. 

Use the links below to download the image.  If you update it, or have suggestions for improving it let me know! 

Reporting Services Overview Diagram for Blog

 icon_zip  Download the original Visio Diagram as well as a JPG and PDF (0.5MB)

Applying a Collation to Columns in a View….

Steve Evans asked me a question today that was kind of interesting.  His question was something like:

“I have a view that returns data from multiple tables.  I want two of the columns returned through the view to be case sensitive.  Can I just have the view treat those two columns as case sensitive, or do I have to define those two columns on the tables as case sensitive?”

Or more generically, “Can I return a column in a view with a different collation than the source column?”.

The short answer, is that yes, we can enforce a collation on a column in a view that is different than the collation on the source columns.

The following code creates a view that returns the ProductID column, and two versions of the Name column from the AdventureWorks.Production.Product table.  The first Name column is left to the same collation as the source column, but the second Name column (aliased as NameSensitive) specifies a case sensitive collation for the column in the view. 

USE AdventureWorks;

GO

CREATE VIEW

  Production.ProductSensitive

AS

SELECT

  ProductID,

  Name,

  Name COLLATE SQL_Latin1_General_CP1_CS_AS AS NameSensitive

FROM Production.Product;

 
The key piece is the line:
 

   Name COLLATE SQL_Latin1_General_CP1_CS_AS AS NameSensitive

Most database developers know collations can be applied to databases and columns, but many don’t realize that we can apply collations to expressions as well.  That is exactly what we are doing in that line.  We are applying the case sensitive collation SQL_Latin1_General_CP1_CS_AS to the expression in the select list. 

After running the code above to make the view, we can test that it works correctly by running the following statements:

–Should match at least one row. The [Name] column

–is not case sensitive

SELECT * FROM Production.ProductSensitive

WHERE Name=‘awc logo cap’;

 

–Won’t match any rows because [NameSensitive] is

–case sensitive

SELECT * FROM Production.ProductSensitive

WHERE NameSensitive=‘awc logo cap’;

 

–Will match rows because [NameSensitive] is

–case sensitive, and the ‘AWC Logo Cap’ literal

–uses the proper case.

SELECT * FROM Production.ProductSensitive

WHERE NameSensitive=‘AWC Logo Cap’;

Anyhow, that was a fun question to answer, and thought it might be of either real use, or at last theoretical use to others.  Let me know if you end up using somewhere and why.