SQL Timestamp and Access

The timestamp field in a SQL Server table holds a unique value that is updated every time a record is updated. In a recent post in a SqlServerCentral forum, there was a suggestion that when you are using MS-Access as the front-end, you should add a timestamp column to every linked table - and thus prevent write-conflict messages.

If you use the SQL Server Migration Assistant (SSMA) to convert your Access database, it will automatically add a column SSMA_timestamp to every table that has either a floating-point field (Single or Double) or has a Boolean field that allows nulls. This is supposed to help SQL Server manage updates to records. For more details you can refer to the Microsoft article Move Access data to a SQL Server database by using the Upsizing Wizard.

Now, if you want to follow the original suggestion, but some tables already have a timestamp, you could write a script to generate the update scripts, like this:

SELECT
'ALTER TABLE [' + OBJECT_NAME(t.object_id) + ']
ADD my_timestamp TIMESTAMP'
FROM sys.tables t
WHERE NOT EXISTS
(
SELECT 1
FROM sys.columns
WHERE system_type_id =
(
SELECT system_type_id
FROM sys.types
WHERE name = 'timestamp'
)
AND object_id = t.object_id
)
AND t.type = 'U'
ORDER BY 1

...and when you run this, you get one ALTER TABLE statement for each table that does not have a timestamp column. NOTE that this does not change the tables, it only creates the scripts to make the table changes. Then, you can review the scripts & remove any tables (e.g. staging or import tables) where adding a column might break the application. This also documents the changes being made. When you actually execute the script, it will add the timestamp columns as needed.

(BTW in the statement "ADD my_timestamp TIMESTAMP" I've left out the "NOT NULL" clause, simply because the timestamp data type by definition cannot be NULL.)

On the other hand, if you take a look at SQL Timestamp field for Access use there is a discussion of how the timestamp can actually cause write-conflicts if you have a form / subform combination that uses columns from the same table, which is not an uncommon practice.

So, before you make radical changes to your database, remember to backup everything, and run some test cases including measuring response times - this gives you a baseline after which you can apply the mods & evaluate the results.

T-SQL Computed Columns, part 3

My last two posts described how to create a computed column, and how to control the data type and size.

In the first example, the computed column would be "A" if there is an approval date, otherwise it would be "".

The general rule is that the computed column can only refer to other columns within the same table. However, it is possible to use a scalar function to perform a lookup from another table. This might have a dramatic effect on performance, depending on table size, row count etc. but it is an option according to the BOL.

So then, what if that approval date actually resides in a different table?

Assuming that my_table has a primary key PK int, and other_table has the related column ID int, we could write a lookup function, like this:

CREATE FUNCTION dbo.fnLookup
(
@PK int
)
RETURNS varchar(1)
AS
BEGIN
DECLARE @Lookup varchar(1)

SELECT @Lookup =
CASE
WHEN approval_date IS NOT NULL
THEN 'A'
ELSE ''
END
FROM other_table
WHERE ID = @PK

RETURN @Lookup
END

...and then use that function in our computed column, like this:

ALTER TABLE my_table
ADD A_display
AS
dbo.fnLookup(PK)

Again, this could have an impact on the performance - any query run against my_table will execute the function for every row - but this is one more option for computed columns.

A better solution might be to create a column in my_table to hold the A_display data, and then create a trigger on other_table that would keep A_display in sync. We'll explore that in a future post.

T-SQL Computed Columns, part 2

My previous post presented the basics of using computed columns in SQL Server. Here we have a slightly more complex example that also deals with data types and sizes.

I have a table of locations in the city - each location has a direction, street, and cross street, such as

NB Austin Blvd @ Cermak Rd
SB Austin Blvd @ Cermak Rd
EB Cermak Rd @ Austin Blvd
WB Cermak Rd @ Austin Blvd

If you examine the list, you see that all four locations are actually the same street intersection - Austin and Cermak - and in the application (MS-Access) we need the ability to sort by the intersection, displayed in a format like street @ cross_street.

The original app used an expression in the query to place the streets in alphabetical order, like this:

IIf([Street] > [Cross_Street],
[Cross_Street] & " @ " & [Street],
[Street] & " @ " & [Cross_Street])

...and the result for all four locations would be:
Austin Blvd @ Cermak Rd
which provides the right answer & works fine, but takes a lot of typing, so it was eventually converted to a VBA function.

Now that the data has been moved into SQL Server, we can avoid using IIf() or VBA functions, which use resources in the GUI, and instead take advantage of the power of T-SQL.

We can start by creating a computed column like this:

ALTER TABLE
my_table
ADD
Intersection_Name
AS
CASE
WHEN Street > Cross_Street
THEN Cross_Street + ' @ ' + Street
ELSE Street + ' @ ' + Cross_Street
END

...which gives the correct result; however, Street and Cross_Street are both nvarchar(255) so our calculated column comes out as nvarchar(513) - why? it is the sum of the sizes of the two columns, plus the ' @ ' expression - and MS-Access regards that as a Memo field, so we cannot sort by that.

(Street and Cross_Street were originally defined as Text(255) in MS-Access, but the data is never that long - 100 would have been a better choice; but that's a topic for another day.)

We could force the computed column to nvarchar(255) to accommodate the limitations of MS-Access, like this:

ALTER TABLE
my_table
ADD
Intersection_Name
AS
CAST(
CASE
WHEN Street > Cross_Street
THEN Cross_Street + ' @ ' + Street
ELSE Street + ' @ ' + Cross_Street
END
AS nvarchar(255)
)

...but now we have a potential bug - we don't expect this will ever happen - but - if the concatenation is ever more than 255 characters, the CAST will fail. The solution is to shorten the street names to 126 characters, so that 126 + 3 + 126 = 255 to ensure the computed column will never fail:

ALTER TABLE
my_table
ADD
Intersection_Name
AS
CAST(
CASE
WHEN Street > Cross_Street
THEN LEFT(Cross_Street, 126) + ' @ ' + LEFT(Street, 126)
ELSE LEFT(Street, 126) + ' @ ' + LEFT(Cross_Street, 126)
END
AS nvarchar(255)
)

One final note - when you work with computed columns, it is not possible to ALTER a computed column; in order to change it you must DROP the column & then ADD the new column definition. As a result, the column to move to the last position in the table.

T-SQL Computed Columns

A Computed Column in SQL Server allows you to create a read-only calculated field that performs some logic but occupies no space in the database.

We have an Access front-end with a SQL Server database & I was looking for ways to improve the user experience. I noted that the query behind one of the forms opened "OK" but scrolling thru the records was a little sluggish. The form itself is a continuous form. I suspected that an IIf() statement was the culprit.

The IIf() statement drives a control that displays "A" if the record has an approval date, like this:

=IIf(IsNull([approval_date]),"","A")

I created a computed column in the SQL table using this statement:

ALTER TABLE my_table
ADD A_display
AS
CASE WHEN approval_date IS NOT NULL
THEN 'A'
ELSE ''
END

...and then in the application, I replaced that IIf() statement with the new column A_display; thus duplicating the Access statement. This did speed up the query quite a bit.

Now, looking back at the modified table, I can see that A_display shows as

varchar(1) NOT NULL

...which is fine for my purpose. But you can modify that behavior by adding to the statement. For example, we might want the output to be nullable - so if you set the control's background property to Transparent (instead of Normal) then you can see through it, if there's no approval date.

To make the column nullable, we could just change the original logic like this:

CASE WHEN approval_date IS NOT NULL
THEN 'A'
ELSE NULL
END

...but if your statement is more complex, you might use NULLIF:

NULLIF(
CASE WHEN approval_date IS NOT NULL
THEN 'A'
ELSE ''
END
,'')


You can also control the data type and size by using CAST:

CAST(
CASE WHEN approval_date IS NOT NULL
THEN 'A'
ELSE
NULL
END AS nvarchar(1)
)

which gives you: nvarchar(1) NULL

sp_executesql

The system stored procedure sp_executesql can be used to run dynamic sql. It also provides a way to use input and/or output parameters. However, the BOL examples seemed to be mighty complicated so I put this together for a quick reference.

For the example, we have a simple table "Cities" like this:

City nvarchar(50),
ST nvarchar(2),
County nvarchar(50)

If all you want to do is to look up the County for Phoenix AZ you could just write

SELECT County
FROM Cities
WHERE City = 'Phoenix'
AND ST = 'AZ'

To make this more versatile, you could write a stored procedure like this

CREATE PROC Get_County
(
@City nvarchar(50),
@ST nvarchar(2)
)
AS
SELECT County
FROM Cities
WHERE City = @City
AND ST = @ST
GO

However, pulling data from from a stored procedure might require that you create a temp table and then use the INSERT...EXEC syntax, which does work, but nesting of sp's is limited.

If we turn this into dynamic sql, we can feed in different parameters. It does seem a bit complicated but it is flexible once you get it set up...

DECLARE
@County nvarchar(50),
@County_out nvarchar(50),
@City nvarchar(50),
@ST nvarchar(2),
@sql nvarchar(4000)

SET @sql = 'SELECT @County_out = County FROM Cities WHERE City = @City AND ST = @ST'

EXEC sp_executesql
@sql,
N'@City nvarchar(50), @ST nvarchar(2), @County_out nvarchar(50) OUTPUT',
@City = 'Phoenix',
@ST = 'AZ',
@County_out = @County OUTPUT

SELECT @County AS County

So the steps are:
+ declare all of your input and output variables
+ declare a variable that's used locally for the lookup value
+ 1st argument = the sql string
+ 2nd argument = comma-separated list to declare of all of the in/out parameters
+ next argument(s) = input parameter(s)
+ last argument = output parameter