When Identity Matters

In a perfect world identity columns should have no real-world meaning and should never be exposed to the user. However, this is not always the case. I've seen systems which use identity columns for booking numbers (which are then exposed to customers) and other systems where records in dimension tables start to be commonly refered to by their surrogate key values, rather than by natural key or name.



As soon as such values start to matter then a potential problem looms if you need to introduce a row with a particular value for the identity column. You can't just go ahead and issue a simple T-SQL INSERT statement, doing so will result in an error.



In order to successfully perform this type of insert you'll need to turn IDENTITY_INSERT on for the table in question. Like so,



SET IDENTITY_INSERT myTable ON



GO



INSERT myTable(ID, Name, Level)

VALUES (2315, 'A new test value', 5)



GO



Once you've finished inserting rows be sure to toggle IDENTITY_INSERT off for the table.



SET IDENTITY_INSERT myTable OFF



GO



Incidently if you are using SSIS to insert data using a Data Flow then take a look at the Advanced options for the SQL Destintion. Ticking the Keep Identity option on this page will allow you to insert values into an identity column.

SSRS Page Sizes and Orientations

One of the things I found odd about Reporting Services when I first used it was the lack of an option to set the paper/page size to common paper sizes such as Letter, A4, A3, etc. This is common in some other reporting tools such as Crystal Reports.

In SSRS page sizes are set by PageSize property of the Report object. You can get to this property from the Report Properties option in the Report menu. From here just look on the Layout tab and you'll find what you need. The measurements will either be in cm or inches (depending upon the Regional Settings of your computer). The most common paper sizes you will need to print to are Letter and A4. The values for these two (in potrait orientation) are

Letter: 8.5in, Height 11in
A4: Width 21cm, Height 29.7cm

If you need to use Landscape orientation simply swap the width and height values.

You can also set the size of the four margins here as well.

In order to maximise the amount of report real estate you have to work with you should also set the Size property of the [Report] Body object to the following:

[Body] Width = [Report] Width - Left Margin - Right Margin
[Body] Height = [Report] Height - Top Margin - Bottom Margin

Accurately setting these values will allow you to make the most of the available space without getting print overruns on to the next page or finding every second page blank due to a page size which has been set slightly too wide.

Querying Data for Report Parameters

Often there's a need to dynamically get data to populate the list of options for a report parameter. There are a couple of ways to go about this. One I often see is to query a transaction table to get a distinct list of items to appear in a drop down parameter. Something along the lines of

SELECT DISTINCT city FROM orders
ORDER BY city

In my opinion this is a bad idea for two reasons:

1. It places unneccessary load on the database. Querying thousands, maybe even millions, of rows to return a handful of rows might not be the best idea if there is a reference table (perhaps just listing cities or even listing all customers - with city information as part of the customer's address). The situation is even worse if the column being queried from the transaction table isn't included in a suitable index.
2. Looking at a transaction table for this information will only return a resultset for those items which have been involved in transactions. If it's important for the report user to be able to query against any city then there's a problem!

So, whereever possible I advocate querying small reference tables to populate report parameters. Look at the data model as a whole to try and find suitable tables and ask questions of key super-users, DBAs, application developers if need be - you might be suprised to find there's a table you can use - it just may be that it was named in a way that didn't make its purpose obvious.

The Logical Operator Less Used

Most people who need to work with SQL Server databases will be familiar with the basic logical operators such as AND, OR, BETWEEN, IN. A good number might also be across the use of the EXISTS operator. Perhaps less known are three others ALL, ANY and SOME.

ALL will return true if all of a set of comparisons are true.
ANY will return true if any of a set of comparisons are true.
SOME will return true if some of a set of comparisons are true.

There are numerous areas where these operators can be used. One case is where there is the need to compare a value against a range of other values from . It can take away the need for a string of ANDs and ORs in WHERE clause.

The syntax is along the lines of:

IF @valueToCheck <> ALL (SELECT referenceValue FROM referenceTable)

-- do something

ELSE

-- do something else

In this case the first code branch will be executed only if the valueToCheck is less than all of the values in the reference table.

New Behaviour When Modifying SQL Server tables from Management Studio

Today I struck some behaviour I'd not seen before. I was attempting to change the nullability of a column from inside Management Studio when up popped a message with the following text:

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that requie the table to be re-created.

Hmm, now I realise that this type of change needs the drop and re-create of the table, but I'm sure in earlier versions of SQL Server this type of change was possible from the designer with the extra work being taken care of silently in the background. Now, in 2008, it seems to be blocked entirely - ideally I'd have liked to be able to make the change in the designer and then generate the change SQL script from there rather than having to write the script myself from scratch.

So, I did a little digging looking for the option mentioned in the error mesage. I found it under the Tools->Options menu in the Designers section.

Simply uncheck the box and you'll be able to make these types of table changes and / or generate the change scripts from Management Studio.

Dynamic Filenames for FlatFiles with SSIS

Importing from flatfiles with SSIS is relatively quick and easy to setup. But what if you need to load from many files with the same structure but all with different filenames? You could do this by manually changing the properties of the connection, running the SSIS package to load one file and then repeating until all of the files had been loaded. It works, and I've done it myself when there have only been a handful of files to load into the database. Recently I was faced with a situation where there were in excess of 100 files which would all need to be loaded on an ongoiong basis. The manual approach wouldn't do the trick here - some automation was required.



The answer lay in using a dynamic filename for the source file. This is actually quite quick and easy to do once you know where to look. Here's what you need to do.


Bring up the properties on the connection to the flatfile and click on the plus sign to expand the list of expressions. Use the elipse button to bring up a window to allow you to add a new expression.

Select the ExcelFilePath option from the property dropdown list and then use the elipse button beside the expression column to build the expression that will return the value of the path and filename for the source file. In my case I concatenated together two package level variables I had created earlier.

You can use the Evaluate Expression button to check the value you get from the expression is what you expected to see.

From here you can build a data flow to load data (using this connection) just as you would using a hard coded connection.

This example has used an Excel flatfile. You can use a similar approach with text files. Rather than set an expression for the ExcelFilePath property, set the expression for the ConnectionString property.