Blogging for my Company

Posted on June 20, 2012 by Zach Stagers

The company I currently work for, One2Create, recently started a company blog which employees can contribute too. It just so happens that I’ve decided to dedicate a little of my time to the company blog, and thought I’d share it with my own readers.

The blog will be covering a wide range of topics, from SQL and Reporting (My bit..), to Web Design, Development, Marketing, and Social Media. So if you have an interest in any of the above, check it out.

You can meet myself, and the rest of our bloggers, in the blog team section, and gain a basic understanding of what each individual will be blogging about.

My first contributions was “SQL – Finding and Removing Duplicates“, which is essentially one of my existing articles on steroids. The original can be found under “Finding Duplicates With SQL“, if you’re interested.

Returning Tabular Data as XML with FOR XML PATH and Nested XML Queries

Posted on June 17, 2012 by Zach Stagers

FOR XML PATH is the favorite amongst most Database Developers due to its ease of use, and the fact that you can build almost any XML structure with it.

I’ll also be discusing nested XML Queries in this post, which kind of goes hand in hand with FOR XML PATH. More on this later.

I’ll start off with an example, then explain what’s going on afterwards:

SELECT P.Name AS "@Name",
       P.Age AS "@Age",
       'PersonId = ' + CAST(P.PersonId AS VARCHAR(5)) AS "comment()",
       (
        SELECT O.OrderId AS "@OrderId",
               O.Item AS "@Item"
	FROM Orders O
	WHERE O.PersonId = P.PersonId
	FOR XML PATH('Order'), TYPE
       ) 
 
FROM Person P
 
WHERE P.PersonId IN (1, 2)
 
FOR XML PATH('Person'), ROOT('People'), ELEMENTS XSINIL

As with each of the other XML Modes (Raw, Auto, and Explicit), you’re able to change the name of your elements from the default. To achieve this use FOR XML PATH(‘Person’), this will give your rows the name “Person”.

ROOT(‘People’) specifies the root element to be wrapped around you’re XML. The keyword ELEMENTS in this instance doesn’t do a whole lot as the structure is placed into elements and attributes depending on the names given to the columns, but XSINIL cannot be specified without it. XSINIL is used to give you’re XML a namespace, as well as to handle NULL values.

The actual output of the query used aboved is as below:

<People xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Person Name="Zach Stagers" Age="22">
    <!--PersonId = 1-->
    <Order OrderId="1" Item="Bike" />
    <Order OrderId="2" Item="Laptop" />
    <Order OrderId="3" Item="Phone" />
    <Order OrderId="4" Item="Keyboard" />
    <Order OrderId="5" Item="Mouse" />
    <Order OrderId="10" Item="Pizza" />
  </Person>
  <Person Name="Libbie Coulson" Age="24">
    <!--PersonId = 2-->
  </Person>
</People>

The first two columns in the select are specified as “@Name” and “@Age”, this assigns them as attributes to the Person Element.

Specifying the column name as “comment()” makes the value a comment within the XML, note that this is case sensitive – “comment()” is not the same as “Comment()”.

One column name that I haven’t used in the example is “text()”, this sets the value as the text of the element, instead of an attribute.. for example, using this against P.Name turns the value into:

<Person Age="22">Zach Stagers</Person>

Nested XML Queries

Nested XML Queries are what gives the FOR XML PATH mode its structure. To do this, as in the example above, you would use a correlated subquery. Here, you link the subquery to the outer query on the PersonId – linking the person to their orders, and therefore listing the orders beneath the correct person.

The subqueries FOR XML statement must have TYPE specified. This translates the queries results into true XML, Microsoft did this for backwards compatibility reasons.

Learning SQL

Posted on June 13, 2012 by Zach Stagers

I’ve decided to write a brief summary about how I first got involved with SQL, how I learned, and how I continue to learn about its features.

First, a bit about the language. SQL stands for Structured Query Language, and it’s used to query databases to get the information you want and need from them.

Microsoft SQL, also known as “Sequel”, is my langauge of choice, and will be the main feature of my blog.

I first started to learn about SQL Server and TSQL development when I joined One2Create in November 2008. Previous to this I had very little experience in any development languages. I had learned bits and pieces of HTML, CSS and C#, but had never before used SQL.

The first thing I did was buy a book, this one in fact: Sams Teach Yourself SQL in 10 Minutes

I read through it, and completed the examples as I went. It’s a complete novice’s book, and the author (Ben Forta) has done a very good job of intoducing the language. It gives you more than enough to get an understanding of the basics.

After about a year and a half of using Ben’s book as a reference, and gaining experiance in the industry, I decided I wanted to study for the Microsoft 70-433 SQL Developer 2008 exam.

My work purchased the 70-433 Self-Paced Training Kit Book to allow me to study the more advanced features of SQL in the hope that I could pass the exam. I’m pleased to say that I did go on to pass the exam on my first attempt, with a score of 92%. Passing the exam qualified me as an MCTS: SQL Server 2008, Database Development. I’ve since gone on to take and pass the second exam, which means I’ve been bumped up to an MCITP: Database Developer 2008.

Development languages, including SQL, are ever growing. There will always be more to learn, new features introduced with new versions, as well as keeping up with all of the old stuff! And this is why I love what I do!

Returning Tabular Data as XML with FOR XML EXPLICIT

Posted on June 9, 2012 by Zach Stagers

FOR XML EXPLICIT can be a little awkward to use to say the least, but it has it uses. The good thing about EXPLICIT, is that you can mold it into almost any XML structure you like.

I actually found that reviewing the results as tabular data is what really helped me understand what the FOR XML EXPLICIT query is doing.

Below is the example code that I’m using:

SELECT
	1 AS Tag,
	NULL AS Parent,
	Name AS "Person!1!Name",
	PersonId AS "Person!1!PersonId",
	NULL AS "Orders!2!Item"
FROM Person
 
UNION ALL
 
SELECT
	2 AS Tag,
	1 AS Parent,
	NULL AS "Person!1!Name",
	PersonId AS "Person!1!PersonId",
	Item AS "Orders!2!Item"
 
FROM Orders
 
ORDER BY "Person!1!PersonId"
 
FOR XML EXPLICIT, ROOT('People')

The first thing to note with this query, is the two columns “tag” and “parent”. You must specify these columns in order to use FOR XML EXPLICIT. These columns specify the hierarchy of your results. The first query will have a “tag” of 1, and a NULL “parent”, as it is the first object declared. Each subsequent “tag” and “parent” will then go up by increments of 1.

Another thing to note, is the column names. The value listed before the first exclamation in the column name specifies the Element Name. The value listed before the second exclamation is the “tag” that it relates to. Then the attribute name is specified. ElementName!TagNumber!AttributeName.

Lastly, it’s the ORDER BY “Person!1!PersonId” that really gives the final result its correct structure.

The XML outputted by the query specified above is as follows:

<People>
  <Person Name="Zach Stagers" PersonId="1">
    <Orders Item="Bike" />
    <Orders Item="Laptop" />
    <Orders Item="Phone" />
    <Orders Item="Keyboard" />
    <Orders Item="Mouse" />
    <Orders Item="Pizza" />
  </Person>
  <Person Name="Libbie Coulson" PersonId="2" />
</People>

As with RAW and AUTO, the ROOT() simply specifies the name of the root element. This is optional.

Without the aforementioned ORDER BY clause, all of the orders for person “Zach” would appear under “Libbie” as she is last in the list, and the UNION ALL will just tack the results from the second query to the end of the result. Ordering by the key between the two tables ensures that the orders against PersonId 1 appear under the person with PersonId 1.

As I mentioned earlier in this post, what really helped me understand what was happening with FOR XML EXPLICIT was seeing the results in tabular form. To do this, simply remove the FOR XML line and re-run the code. In the below tabular example, I’ve removed FOR XML EXPLICIT, ROOT(‘People’) from the query:

FOR XML EXPLICIT Tabular

Rotating Graph Data Labels In SSRS

Posted on June 6, 2012 by Zach Stagers

The default settings for Data Labels attached to graphs can cause issues with overlapping, which in turn reduces readability.

In order to rotate the labels, select your graph, then select a single data label (each of them will be highlighted and affected by any changes). Open the properties window whilst the data labels are selected, and expand the “Labels” title, then expand the “Label” tag in the Labels category. You’ll be presented with the below properties:

Graph Data Label Properties

As you may have guessed, the “Rotation” attribute controls the angle of the label. By default, this is set to zero, which will give you horizontal text.

A value of -90 to 90 can be selected. Any number below zero will swing the end of your text upwards, while any number above zero will tilt the end of your text downwards. The below is the result of using the above properties (-90 degrees):

Improved Graph Data Labels

SQL If Else Clause

Posted on June 3, 2012 by Zach Stagers

Frequently used in Stored Procedures, the SQL If can be used to help control the flow and execution of your SQL.

The If checks to see if a boolean condition is true, and if it is, the code directly beneath is executed. This is handy, as we often need to execute completely different code depending on the value of a variable.

The syntax is easy to use, but can make a big difference to your code:

SELECT @Value = COUNT(*) FROM SpecialOffers WHERE Valid = 1
 
IF (@Value > 0)
  BEGIN
 
    SELECT * FROM SpecialOffersProducts
 
  END
ELSE
  BEGIN
 
    SELECT * FROM Products
 
  END

The above code is counting the number of valid special offers and assigning the number to the variable @Value.

The If then checks that @Value is greater than zero, meaning there are valid special offers available. If the check is true, and there are special offers available, our SpecialOffersProducts will be returned. If the count returns as zero, the else will be executed, and the Products will be returned.

The above example would typically be used within a stored procedure.

Multiple conditions can be specified within the If with the use of the AND and OR keywords.

Finding a list of user defined tables with schemas within a SQL Database

Posted on June 1, 2012 by Zach Stagers

A couple of weeks ago I did a blog post on finding a list of user defined tables within a SQL Database. Well, today I’ve improved upon that example to include the schema that the table belongs too as well, plus a little trick to generate you some useful scripts.

USE Database1
 
SELECT S.name + '.' + O.name [SchemaTable]
 
FROM sys.all_objects O JOIN
     sys.schemas S ON S.schema_id = O.schema_id
 
WHERE type = 'U' AND
      is_ms_shipped = 0 AND
      S.schema_id IN (5, 6, 7, 8, 9)
 
ORDER BY O.name

The reason I needed a list of tables and the schemas they belong to in this format was because I needed to add two columns to each of them.

I’m far to busy to open up each of these tables (there were 50 in total) and add the two columns manually, so I used the above SQL, as well as some concatenation to write the query for me:

USE Database1
 
SELECT 'ALTER TABLE ' + S.name + '.' + O.name + ' ADD Child VARCHAR(100) NULL,
       GrandChild VARCHAR(100) NULL' [SQL]
 
FROM sys.all_objects O JOIN
     sys.schemas S ON S.schema_id = O.schema_id
 
WHERE type = 'U' AND
      is_ms_shipped = 0 AND
      S.schema_id IN (5, 6, 7, 8, 9)
 
ORDER BY O.name

I was then able to copy the results into a new query window and execute, saving myself a lot of time.

Keyboard Shortcut to comment code in SSMS

Posted on May 29, 2012 by Zach Stagers

I’m a bit of a junky when it comes to keyboard shortcuts, and I’ve been wondering for awhile if there’s a shortcut available to comment out code in SQL Server Management Studio.

It turns out that there is!

To comment the code:

Highlight the rows that you would like to comment and press CTRL + K, then CTRL + C

To un-comment the code:

Highlight the rows that you would like to un-comment and press CTRL + K, then CTRL + U

Changing Data Types – Cast and Convert

Posted on May 26, 2012 by Zach Stagers

The CAST and CONVERT System Functions essentially achieve the same task, with a few small difference.

CAST is the ANSI compliant version of the two. CAST can change data from one type to another, but cannot change the format of a date. It’s simple to use, and it’s syntax is as follows:

CAST(Expression AS [Target Data Type])

Where I’ve written “Expression”, you would replace with either a column name, or a hardcoded value. “Target Data Type” would be replaced with the name of the Data Type that you’d like to convert the value to.

CONVERT works in a similar manner, but has one more optional value available and is not ANSI standard.

CONVERT([Target Data Type], Expression, Style)

As above, Target Data Type is the data type that you’d like to convert to, and Expression is the column or hardcoded value. You’ll notice that this example also includes Style. This option is available when working with dates. For example, most of the time dates are stored as a DateTime which would look like “2001-01-01 00:00:00:000″. If you wanted to exclude the time and display the date as “01 Jan 2001″ you’d use:

CONVERT (VARCHAR(11), '2001-01-01 00:00:00:000', 106)

Below is a table of some of the more common Date Styles used, and an example of how the output will look. All of the examples use Jan 31 2012.

Style Number	Date Output Example
101	01-31-2012 (MM/DD/YYYY)
103	31-01-2012 (DD/MM/YYYY)
106	31 Jan 2012 (DD MON YYYY)
101	Jan 31, 2012 (MON DD, YYYY)

Not all data types can be converted or cast to all data types, for example a Bit cannot be converted to an Image.

SQL Joins – Left, Right, Inner, Full, and Cross!

Posted on May 24, 2012 by Zach Stagers

SQL Joins are incredibly useful, and knowing the difference between the five different types makes harnessing their great power a lot easier… obviously!

Joins are used to join tables together in a query, so that you can use data from both tables. You are able to join to a maximum of 256 tables, which frankly, should be more than enough!

The syntax goes like this:

SELECT *
FROM Person AS P INNER JOIN
     Orders AS O ON P.PersonId = O.PersonId

The above example uses the most common of all joins, the INNER JOIN. The word “Inner” can actually be omitted from the query, as it isn’t necessary.

The Inner Join will join the two tables together on the linking columns, and only the results where a match is found in both tables will be returned.

Next, we have the OUTER JOIN’s (Left, Right, and Full). These are just as simple to use as the INNER join, but work a little differently. The Synax is the same as the above INNER join example, just replace “INNER JOIN” with “LEFT OUTER JOIN” or Right / Full, depending on what you want to achieve.

Again, with the OUTER Join’s, the word “Outer” can be omitted.

The way the LEFT JOIN works is by selecting all of the data from the table on the left, and returns all of the matchs from the table on the right. If there isn’t a match, then a NULL will be returned by the right table. Right Join is exactly the same, just reversed (returns everything on the right, and all possible matches on the left).

FULL JOIN will return every row from the left and right tables, matching where possible.

And lastly we have CROSS JOIN. This join will match every row from the left table, to every row in the right table. This means that if the left table has 100 rows, and the right table also has 100 rows, our result set would be a grand total of 10,000 rows! Can I think of an example where you would use this? No.

Hopefully the below simple diagram will help re-enforce what I’ve said above.

Joins Diagram

Scratchbox | Zach Stagers

SQL Server Developer and Administrator