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. 

Using a Recursive CTE to generate a contiguous sequence of values…

I am working on a chapter for Syngress’ 70-433 test prep guide, and talking about character sets.  In my research I wanted to write a query that would return the values 0 through 255 and the the character associated with each of those numbers.

Yeah if I had a table Like AdventureWorks.Person.Contact where there was a set of 256 contiguous values, I could highjack those values for my source numbers, but that seems much less elegant.

Here is what I came up with:

WITH numbers AS
(
  SELECT 0 AS Number
  UNION ALL
  SELECT Number + 1
  FROM Numbers
  WHERE Number + 1 <= 255
)
SELECT Number, CHAR(Number) AS Character FROM numbers
OPTION (MAXRECURSION 255);

Note the OPTION (MAXRECURSION 255) at the end.  Without that it will error after the first 100 rows (really its after the 100th recursion along this branch, but I digress) by default. But that option will cause it to error if I don’t stop the recursion myself at 255, hence the WHERE Number + 1 <= 255 where clause on the recursive query definition in the CTE.

Anyhow, if you run the query above you will get a result similar to the following

Number  Character
—— ———
…     …
63      ?
64      @
65      A
66      B
67      C
…     …
97      a
98      b
99      c
…     …
253     ý
254     þ
255     ÿ

 

Enjoy!

LA Code Camp Slides…

Ok, here they are.  If you attended my sessions you know they are more demo than slides so it is questionable how much value you can get from the decks themselves, but people asked and I promised so here they are.  I would like to do screen captures of these and hope to do that soon.  If and when I get that done I will post links on the blog.

Sessions:

Enjoy!

Using CTEs to Build Running Sums…

I am teaching a class today on optimizing queries and one of the topics in on refactoring cursors in to queries.  The book stated that Cumulative Totals is an example of where a cursor is required.  I beg to differ.  The following example uses two features of SQL Server to build a running sum with set based syntax.

I will explain the following code snippet below, but here it is:

USE AdventureWorks
WITH NumberedProducts (RowNum,ProductID,Name,ListPrice)
AS
(
SELECT
  ROW_NUMBER() OVER (ORDER BY ListPrice, Name) AS RowNum,
  ProductID, Name, ListPrice
FROM Production.Product
WHERE ListPrice > 0
),
CummulativeSums (RowNum, ProductID, Name, ListPrice, RunningSum)
AS
(
  SELECT TOP 1
    RowNum, ProductID, Name, ListPrice, ListPrice AS RunningSum
  FROM NumberedProducts
  ORDER BY ListPrice, Name
  UNION ALL
  SELECT
    This.RowNum, This.ProductID, This.Name, This.ListPrice, This.ListPrice + CS.RunningSum AS RunningSum
  FROM NumberedProducts AS This
  JOIN CummulativeSums AS CS
  ON This.RowNum = CS.RowNum + 1
)
SELECT * FROM CummulativeSums
OPTION (maxrecursion 503)

The two features I used are Common Table Expressions (or CTEs). and the ROW_NUMBER() function.

I used CTEs to facilitate the recursive query needed to iterate through records, collecting values and accumulating totals on a row by row basis.  I am using the Product.Product table in the SQL 2005 AdventureWorks sample database as my data set.  I need a way to first order the products.  A Running total assumes some order to the data.  I used ListPrice, and where ListPrice was the same, then [Name].

For the recursive part to work, I needed a way to correlate a product to it’s previous product.  There is an automatically generated ProductID column, but the ProductIDs are in no way correlated to the ListPrice, [Name] sort order so that didn’t make sense.  Plus there isn’t any quaranatee that they are sequential and that would make joining to the "next" product difficult. 

To solve my problem I used the ROW_NUMBER() function to number the rows in an original set of Product Data that had been ordered by ListPrice, [Name].  I used a CTE to define that set. 

WITH NumberedProducts (RowNum,ProductID,Name,ListPrice)
AS
(
SELECT
  ROW_NUMBER() OVER (ORDER BY ListPrice, Name) AS RowNum,
  ProductID, Name, ListPrice
FROM Production.Product
WHERE ListPrice > 0
),

Next, I used a second CTE to do the recursion.  The anchor query retrieves the top 1 product ordered by ListPrice, [Name].  As this is the first row in the result set the "RunningSum" is the same as the current records ListPrice:

SELECT TOP 1
    RowNum, ProductID, Name, ListPrice, ListPrice AS RunningSum
FROM NumberedProducts
ORDER BY ListPrice, Name

The recursive part joins back to the CTE on the RowNum being one greater that it’s "parent" from the anchor query.  The running sum is built by taking the current row’s ListPrice and adding it to the "RunningSum" value of it’s parent:

SELECT
    This.RowNum, This.ProductID, This.Name, This.ListPrice,
    This.ListPrice + CS.RunningSum AS RunningSum
FROM NumberedProducts AS This
JOIN CummulativeSums AS CS
ON This.RowNum = CS.RowNum + 1

There is one last thing that can cause a problem with this; maxrecursion.  SQL Server defaults to only allowing 100 recursion iterations in a CTE by default.  We can override that however with an option on the final query.

In the AdventureWorks.Product.Product table there are 504 products by default.  That means after the anchor row, we would have to recurse 503 times to get the remaining products.  To demonstrate this I set the maxrecursion option to 503.   You would likely want to set a higher value to allow additional rows to be added in the future, and have it not break your query.

Unfortunately, it doesn’t appear that there is an easy way to set the value for maxrecursion dynamically (like from a variable or subquery).  You would have to resort to dynamic sql execution, but you can just set it to a large value you know will exceed your desired recursion count. 

Anyhow, I am sure other’s have arrived at this solution already, but for me it was a new way of thinking of a common problem.

Query SQL Server 2005 Permissions

The following query joins some of the system views together to show a report of the permissions assigned in the current database. Set the @Principal ID value to the name of a specific user or role, or leave it as NULL to view all.

DECLARE @Principal sysname
SET @Principal = NULL — set this to a specific user or role name if desired.

SELECT
prin.name AS PrincipalName,
prin.type_desc AS PrincipalType,
CASE
WHEN perm.class=1 and perm.minor_id = 0 THEN ‘OBJECT’
WHEN perm.class=1 and perm.minor_id = 0 THEN ‘COLUMN’
ELSE perm.class_desc
END AS SecurableType,
sch.name AS SchemaName,
obj.name AS ObjectName,
IsNull(col.name,”) AS ColumnName,
state_desc AS PermissionState,
permission_name AS Permission
–,*
FROM sys.database_principals AS prin
JOIN sys.database_permissions AS perm
ON prin.principal_ID = perm.grantee_principal_ID
JOIN sys.objects AS obj
ON perm.major_id = obj.object_id
AND perm.minor_id = 0
LEFT JOIN sys.columns AS col
ON perm.major_id = col.object_id
AND col.column_id = perm.minor_id
JOIN sys.schemas AS sch
ON obj.schema_id = sch.schema_id
WHERE @Principal IS NULL OR prin.name=@Principal

Cool script to document table column usage in SQL 2005

Hey,

While running the Microsoft course “2784 – Tuning and Optimizing Queries using SQL Server 2005″ I ran into a need to list all table columns that were referenced by the various code objects (Procs, View, etc.) in the database.

After some poking around I decided to use a few features of SQL Server 2005 to get my results; The sys.sql_dependencies system catalog view and the PIVOT statement.

The result I desired would show every column in every table that is referenced by a programming object, and document the usage (Select or Update) of the column by the programming object. For Example:

Table    Column     pInsertEmployee  pUpdateEmployee vOrders<br />Employee EmployeeID U                U               S<br />Employee DateAdded  U    <br />Orders   EmployeeID                                  S<br />

Using the sys.sql_depencies system catalog view, the PIVOT operator and some dynamic sql code generation I was able to get what I needed. Following is the script I used.

-- =============================================================================<br />-- Title: SQL Server 2005 Column Usage<br />-- Author: Bret Stateham<br />-- bret@pingit.biz<br />-- Created: 08/01/07<br />-- Description: Sample script to show table column usage by server objects<br />-- =============================================================================<br />SET NOCOUNT ON<br /><br />IF OBJECT_ID('TempDB..#Dependencies') IS NOT NULL<br />DROP TABLE #Dependencies<br /><br />CREATE TABLE #Dependencies<br />(<br />  ReferencingObject nvarchar(256),<br />  ReferencingColumn nvarchar(256),<br />  ReferencedObject nvarchar(256),<br />  ReferencedColumn nvarchar(256),<br />  Usage nchar(256)<br />)<br /><br />INSERT INTO #Dependencies (ReferencingObject,ReferencingColumn,ReferencedObject,ReferencedColumn,Usage)<br />SELECT<br />  object_name(object_id) AS ReferencingObject<br />  ,IsNull<br />   (<br />    (<br />     SELECT name <br />     FROM sys.columns AS c <br />     WHERE c.object_id = d.object_id <br />       AND c.column_id = d.column_id<br />    )<br />    ,''<br />   ) AS ReferencingColumn<br />  ,OBJECT_NAME(referenced_major_id) AS ReferencedObject<br />  ,ISNULL<br />   (<br />    (<br />     SELECT name <br />     FROM sys.columns AS c <br />     WHERE c.object_id = d.referenced_major_id <br />       AND c.column_id = d.referenced_minor_id<br />    )<br />    ,''<br />   ) AS ReferencedColumn<br />  ,CASE<br />    WHEN is_selected = 1 and is_updated = 1 THEN 'SU'<br />    WHEN is_selected = 1 and is_updated = 0 THEN 'S'<br />    WHEN is_selected = 0 and is_updated = 1 THEN 'U'<br />    WHEN is_selected = 0 and is_updated = 0 THEN ''<br />   END AS Usage<br />FROM sys.sql_dependencies AS d<br /><br />DECLARE @PivotStatement nvarchar(max)<br />DECLARE @PivotColumns nvarchar(max)<br />DECLARE @SelectColumns nvarchar(max)<br /><br />SET @PivotStatement = ''<br />SET @PivotColumns = NULL<br />SET @SelectColumns = ''<br /><br />SELECT @PivotColumns = <br />  COALESCE(@PivotColumns + ',[' + Referencing + ']','[' + Referencing + ']')<br />FROM<br />(<br />  SELECT DISTINCT<br />    CASE<br />      WHEN ReferencingColumn <> '' THEN<br />        ReferencingObject + '.' + ReferencingColumn<br />      ELSE<br />        ReferencingObject<br />    END AS Referencing<br />  FROM #Dependencies<br />) AS DistinctReferencing<br /><br />SELECT <br />  @SelectColumns = ISNULL(@SelectColumns,'') + <br />                   ' ,ISNULL([' + Referencing + '],'''') AS [' + Referencing + ']' + CHAR(13) + CHAR(10)<br />FROM<br />(<br />  SELECT DISTINCT<br />    CASE<br />      WHEN ReferencingColumn <> '' THEN<br />        ReferencingObject + '.' + ReferencingColumn<br />      ELSE<br />        ReferencingObject<br />    END AS Referencing<br />  FROM #Dependencies<br />) AS DistinctReferencing<br /><br />SET @PivotStatement = '<br />  SELECT<br />    ReferencedObject<br />    ,ReferencedColumn<br />' + @SelectColumns + '<br />    FROM<br />    (<br />      SELECT<br />        ReferencedObject<br />        ,ReferencedColumn<br />        ,CASE<br />           WHEN ReferencingColumn <> '''' THEN<br />             ReferencingObject + ''.'' + ReferencingColumn<br />           ELSE<br />             ReferencingObject<br />         END AS Referencing<br />        ,Usage<br />      FROM #Dependencies<br />    ) AS d<br />    PIVOT<br />    (<br />      MAX(Usage)<br />      FOR Referencing IN (' + @PivotColumns + ')<br />    ) AS PivotedDependencies<br />    ORDER BY ReferencedObject, ReferencedColumn<br />'<br /><br />EXEC (@PivotStatement)<br /><br />DROP TABLE #Dependencies<br />SET NOCOUNT OFF<br />