Download SQL 2016 Developer Edition for free

With the release of SQL Server 2016, folks like me get very excited to get their hands on the new software.  However, in the past it has always been quite expensive, or very time-limited for an individual to get hold of a full Developer license.

Not so anymore - As part of the Visual Studio Dev Essentials program, a full developer license for SQL Server 2016 SP1 is now available for free. This includes all the tools such as SSMS, SSDT and a host of other products too, including Microsoft R Server and even a few quids worth of Azure is being thrown in.

In order to access the downloads you'll need to sign up for the program first (free), and then download away:

https://www.visualstudio.com/dev-essentials/

Also note that a new product such as SQL 2016 requires new sample databases - say goodbye to AdventureWorks and hello to the brand new Wide World Importers!  Download all the latest samples from github here:

https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers

Interesting, however, is that there does not appear to be an SSAS solution build on the new samples.  I guess very little has changed is SSAS in this release, so MS felt they need not bother.  A bit irritating in my opinion, as it would be nice to have a consistent set of samples, and not have to switch back to old solutions to demo SSAS - gives the impression that SSAS is being less and less encouraged/supported. But maybe that is deliberate....

Happy downloading, and Happy New Year!

SQL Server Triggers – Which fields have been updated? – Part 2

In the last post (SQL Server Triggers – Which fields have been updated? – Part 1) we explored various methods to determine which columns in a table were being updated.  All those methods relied on a SQL trigger being fired.  Once fired, if a specific column in the table was updated we could carry out an appropriate action, such as inserting an audit record into another table.  However, what if the UPDATE statement was executed against the table, but the actual values of the fields were not changed?  Consider the following example:

CREATE TABLE MyTable (Col1 INT IDENTITY(1,1), Col2 varchar(10), Col3 INT)

GO

CREATE TRIGGER dbo.tr_MyTable ON dbo.MyTable  AFTER UPDATE

AS

BEGIN

       PRINT 'An Update has occurred'

END

GO

INSERT INTO MyTable

SELECT 'a',7 UNION ALL

SELECT 'b',8 UNION ALL

SELECT 'c',9 UNION ALL

SELECT 'd',10

GO

UPDATE MyTable

SET Col3 = 10 WHERE Col2 = 'd'

The UPDATE statement above has overwritten Col3 with the same value as it had before.  The table data has not actually been changed.  Nonetheless, because an UPDATE statement has been executed, the AFTER UPDATE Trigger is fired and will output the print statement.  In fact even if the UPDATE statement had not affected a single row, the trigger would still have been fired, outputting the print statement.

This is often not the desired behaviour – I don’t want to be inserting audit records if nothing has actually changed.  Depending on what logic has been implemented in the trigger and how often it is fired, this could have an adverse impact on performance.  Therefore the first improvement we can make is to ensure nothing happens if no records have been updated.  To do this we use the @@ROWCOUNT global variable (for more on this variable see my post on SQL Server Global Variables):

ALTER TRIGGER dbo.tr_MyTable ON dbo.MyTable  AFTER UPDATE

AS

BEGIN

       IF @@ROWCOUNT = 0 RETURN;

       PRINT 'An Update has occurred'

       --Perform UPDATE logic here

END

GO

This trigger will now immediately return, doing nothing, if no records have been updated.

Next we need ensure we only take action if field values have actually changed.  To do this we can make use of the special inserted and deleted tables.  These are temporary tables created and managed automatically by SQL Server.  While we cannot perform any DDL operations on these tables, they contain identical columns to the original table the trigger is created on, and we can refer directly to these columns in the code.  When a DML trigger fires, these two tables are populated with the deleted and inserted records from the underlying table respectively.  If records are updated then the original pre-updated record arrives in the deleted table and the post-updated record in the inserted table.  Therefore, if we want to know if previous values have actually been updated with new, different values we can compare the contents of these two tables:

ALTER TRIGGER dbo.tr_MyTable ON dbo.MyTable  AFTER UPDATE

AS

BEGIN

       IF @@ROWCOUNT = 0 RETURN;

       IF EXISTS(    SELECT *

                           FROM          inserted i

                           INNER JOIN    deleted d

                                   ON    i.Col1 = d.Col1

                           WHERE         d.col2  <> i.col2

                                  OR     d.col3 <> i.col3           

                     )            

       BEGIN        

              PRINT 'An Update has occurred'

       END

END

In the above trigger, inside the IF EXISTS function, we are joining the inserted and deleted tables together on Col1, the identity column.  Because these tables are effectively the “before” and “after” state of the data, we are only interested where the values in the remaining fields do not match, indicating that data has changed in those fields.  This is logic is carried out by the WHERE clause.  Only if such rows exist do we then perform the desired UPDATE logic, in this case the PRINT statement.  

The above trigger will now only do anything if data has actually changed in the underlying table.  This has the potential of saving a lot of time and unnecessary processing of data on those occasions when an UPDATE has occurred but no overall change has actually taken place.

SQL Server Triggers – Which fields have been updated? – Part 1

When updating a table it can often be very useful to know not just that an update has taken place, but exactly which columns have been updated. For example, in an HR department you may have an employee table that contains customer names and bank account numbers.  If the bank account number for an employee changes, either through a front end application, a DML query or any other method, it should be audited – by which we mean an audit table should be inserted with a corresponding record.  While if an employee middle name is changed, an audit is not required.

Below we’ll look at a number of ways for testing for specific column updates, and in Part 2 we’ll look at determining if values have indeed actually changed or not – they could have been updated with the same value as before.

UPDATE()

One way of determing which column has been updated is by using an AFTER UPDATE trigger.  Within this trigger we could use the UPDATE() function to detect the column that has been updated and apply the audit logic accordingly:

CREATE TRIGGER tr_Employee ON Employee AFTER UPDATE

AS

       IF ( UPDATE (BankAccountNo))

       BEGIN

              INSERT INTO EmployeeAudit ( Column1, Column2,... ModifiedBy, ModifiedDate)

              SELECT inserted.Column1, inserted.Column2,... USER_NAME(), GETDATE()

       END

  

But what happens if we have additional columns, some requiring audit (such as SortCode, NationalInsuranceNo etc), and some not (eg marital status)?  Or perhaps the non-sensitive columns don’t require audit but do require some other logic, eg an additional table to be amended in line with this one.  We could append multiple IF UPDATE() statements:

IF UPDATE(BankAccountNo)

BEGIN

       --apply audit logic

END

IF UPDATE (MaritalStatus)

BEGIN

       --apply other logic

END

--next IF…

Or we could combine the groups of logic together into fewer statements:

IF (UPDATE(BankAccountNo) OR UPDATE(SortCode))

BEGIN

       --apply audit logic

END

IF (UPDATE(MaritalStatus) OR UPDATE(MiddleName)

BEGIN

       --apply other logic

END

COLUMNS_UPDATED()

If you like working with binary and bitwise logic, COLUMNS_UPDATED() can be used to achieve similar results and much less code. COLUMNS_UPDATED() returns a varbinary bit pattern indicating which columns in a table have been updated. From BOL:

COLUMNS_UPDATED returns one or more bytes that are ordered from left to right, with the least significant bit in each byte being the rightmost. The rightmost bit of the leftmost byte represents the first column in the table; the next bit to the left represents the second column, and so on. COLUMNS_UPDATED returns multiple bytes if the table on which the trigger is created contains more than eight columns, with the least significant byte being the leftmost.

So for a simple four column table, when COLUMNS_UPDATED() is used in the trigger it may return a binary value of 1010.  This would indicate that columns two and four (as listed in syscolumns) have been updated, but one and three have not.  We can therefore use the value returned by this function in our trigger to determine if a specific combination of columns in our table have been updated.  However in order to do this we need to first convert the binary number into decimal.

As each column (right to left) represents an incremental increase in the power of 2 (ie, right most column is 2^0, next to the left is 2^1, then 2^2), a binary value of 1010 can be translated to decimal as:

Column Name	Col4	Col3	Col2	Col1
Column Position	4	3	2	1
COLUMNS_UPDATED()	1	0	1	0
Bin to dec conversion	2^3	2^2	2^1	2^0
Decimal value	8	0	2	0

Binary 1010 = ( 0 x 2^0) + (1 x 2^1) + (0 x 2^2) + (1 x 2^3) =  0 + 2 + 0 + 8 = 10

When working with binary we need to use SQL Server bitwise operators, in this case ‘&’ (Bitwise AND) to compare two values or we may get unexpected results.  The ‘&’ function looks at each bit (that is each 1/0) in the first binary number and compares it to the corresponding bit in the second number.  If the first bit in the first number is 1 AND the second bit in the second number is 1, then the first bit of the output is 1.  If either bits are 0 then the output is zero. Then it moves onto the second bit in both numbers and starts again. The output number can be converted back to decimal again.  In the first table below, the binary number 1010 is ‘AND-ed’ with itself and the result is shown. In the second table it is ‘AND-ed’ with a different number.

	Col4	Col3	Col2	Col1	Decimal
Binary Value 1	1	0	1	0	10
Binary Value 2	1	0	1	0	10
Output of ‘AND’	1	0	1	0	10

    

	Col4	Col3	Col2	Col1	Decimal
Binary Value 1	1	0	1	0	10
Binary Value 2	0	1	1	0	6
Output of ‘AND’	0	0	1	0	2

The above tables show that using the bitwise AND operator:  10 & 10 = 10,  10 & 6 = 2.  Therefore to we can say that in our table if COLUMNS_UPDATED() = 10 we know that columns two and four have been updated.

The resulting trigger for this example would look like this:

CREATE TRIGGER tr_Employee ON Employee AFTER UPDATE

AS

IF (COLUMNS_UPDATED() & 10 = 10)

BEGIN

       --apply audit logic

END

Note that the IF statement above will return TRUE if columns two and four or more are updated, not just two and four alone.

Advantages of this approach are that for a very wide table, the number of IF statements, or alternatively the number of conditions within the IF statements could be huge and time consuming to write, while using COLUMNS_UPDATED() reduces it to just one short line.  However, using this function will mean that the functionality of the code is heavily dependent on the order of the columns in the table.  If the table is dropped and recreated with columns reordered, the trigger may no longer function as expected.  In addition, while being concise, this code is more difficult to understand and debug.  Therefore I would recommend the earlier approaches unless absolutely necessary.

In Part 2  we’ll look at how to analyse the records that have been updated and determine if any update has actually been made at all – an Update trigger can be fired even if no values have actually changed.

Manually Execute SSRS 2012 Subscriptions

By far the easiest place to setup a SQL Server 2012 SSRS subscription for a report is through the Reporting Services Report Manager. A simple GUI guides users through the process of selecting a subscription type, where to save or send the output and on what schedule to execute. For example, you can specify the report to execute every morning at 9am and email the report as a pdf to a set of users, or to save the output file to a fileshare.

Now suppose you don’t want this report subscription to email or save the file on an automated schedule, but rather to be manually/actively triggered. Perhaps you want to test the subscription is working or you want it to be triggered by a third party application. There’s more than one way of doing this, and here I will cover two methods:

Method 1 – SQL Agent Job

When a subscription is created in the SSRS Reports Manager, a corresponding SQL agent job is also created on the SQL server. Unfortunately there is no way to control the name of the job and, once created, it cannot be modified without affecting the subscription. The name of the job will be an UID similar to the highlighted item below:

Right-click on this job and select “Start Job at step…” to run it. This will cause the subscription to execute, and you will see in the SQL Server Reporting Services Report Manager that the Last Run field for the subscription has been updated. This is the simplest approach and is particularly useful for testing the item:

Alternatively, a job request can be made using SQL code:

EXEC dbo.sp_start_job N'AAB7E0BD-8089-4330-A3D6-8B95ACD90132'

This will have exactly the same effect as right-clicking to execute the job.

The drawback to this approach is that SQL Agent job execution requests for a single job do not queue or run in parallel. If the job is already running then the subsequent job execution request will fail:

Request to run job AAB7E0BD-8089-4330-A3D6-8B95ACD90132 refused because the job already has a pending request

Therefore if multiple users are going to be issuing requests then this may not be the best approach.

Method 2 – Stored Procedure

If you open the SQL Agent Job used for the subscription and examine the step that has been defined, you will see that it is executing a stored procedure:

The AddEvent stored procedure is being called:

exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='a7fd8d5f-0af9-49ec-ad8e-b273d44f1bb0'

This stored procedure inserts a record in the ReportServer.dbo.Events table. The Events table is polled every few seconds by the server and any entries in this table will trigger the subscription to be executed. Note that the @EventData variable value is the same as the SQL Agent Job name.

This same SQL stored procedure can in fact be run manually against the SQL Server msdb database directly and will have exactly the same effect – it will trigger the subscription to execute. However, as we are no longer using the SQL Agent to call the procedure, we can actually run this code multiple times as quickly as we like. Each time it will insert a record into the events table, and every entry will trigger a new subscription execution, even if it is the same subscription. Effectively, subscription requests will be now be queued, and run sequentially.

If a subscription execution request needs to be made actively, or occur multiple times in a short space of time, and each request needs to be completed, then this method is a good approach to handling the scenario.

How to Execute SQL Script Asynchronously

SQL code generally runs sequentially.  One line of code must complete before the next line is executed.  We can redirect the path that is followed through the code using conditional logic or GOTO statements, but ultimately we cannot progress to the next step until the previous one has completed.

However, there are situations where we may want the code to continue to step 2 without waiting for a particularly time-consuming step 1 to complete.  If step 1 and step 2 do not impact each other then it might be beneficial to have the SQL execute asynchronously or concurrently, saving overall execution time.

For an example let's create a table to insert data into for step 1, and a stored procedure that will insert data into it:

CREATE TABLE [dbo].[InsertSomething](
       id                   INT IDENTITY(1,1) NOT NULL,
       Col1                 VARCHAR(32) NULL,
       InsertedDate         DATETIME NULL
)
GO

CREATE PROC DoSomething @Data VARCHAR(32) = ''
AS
INSERT INTO InsertSomething (Col1, InsertedDate) VALUES(@Data + ' - Start', GETDATE())
WAITFOR DELAY '00:00:10:00'
INSERT INTO InsertSomething (Col1, InsertedDate) VALUES(@Data + ' - End', GETDATE())
GO

The stored proc DoSomething will insert two rows into the table InsertSomething.  After the first row is inserted there will be a delay of ten seconds before the second row is inserted.  Therefore the time for the procedure to complete is approximately ten seconds, which in this example represents a long running piece of code.

EXEC DoSomething 'Test 1'

INSERT INTO InsertSomething (Col1, InsertedDate) VALUES('Test 2', GETDATE())
SELECT * FROM InsertSomething

Results

id          Col1                             InsertedDate

----------- -------------------------------- -----------------------

1           Test 1 - Start                   2014-12-30 12:38:40.023

2           Test 1 - End                     2014-12-30 12:38:50.030

3           Test 2                           2014-12-30 12:38:50.037

We can see from the timestamps that the "Test 2" record was inserted only after the stored procedure had completed inserting the Test 1 records. IE the sql script is executing sequentially, as expected.

In the above example we would prefer the "Test 2" insert statement not to have to wait for the stored procedure to complete. We would like the two sql commands to run concurrently.  To do this we need to create a second stored procedure to call the first via a new thread:

CREATE PROC [dbo].[AsyncInsert] @Data    VARCHAR(32)
AS

DECLARE @rc          INT
DECLARE @object      INT
DECLARE @osql_cmd    VARCHAR(1000)
DECLARE @ServerName  VARCHAR(256) = (SELECT @@SERVERNAME)

DECLARE @ExecStmt    VARCHAR(MAX) = '"EXEC DoSomething @Data='''+@Data + '''"'

EXEC @rc = sp_oacreate 'wscript.shell', @object OUT

SET @osql_cmd = 'osql -E -dMyDB -S' +@ServerName+' -Q'+ @ExecStmt

EXEC @rc=sp_oamethod @object, 'run', null, @osql_cmd

EXEC sp_oadestroy @object

GO  

Test the new procedure by running the following code:

EXEC [AsyncInsert] @Data = 'Test 1 Async'

WAITFOR DELAY '00:00:01:00'

INSERT INTO InsertSomething (Col1, InsertedDate) VALUES('Test 2 Async', GETDATE())

WAITFOR DELAY '00:00:10:00'

SELECT * FROM InsertSomething

Results
id          Col1                             InsertedDate
----------- -------------------------------- -----------------------
1           Test 1 - Start                   2014-12-30 12:38:40.023
2           Test 1 - End                     2014-12-30 12:38:50.030
3           Test 2                           2014-12-30 12:38:50.037
4           Test 1 Async - Start             2014-12-30 12:39:01.967
5           Test 2 Async                     2014-12-30 12:39:02.930
6           Test 1 Async - End               2014-12-30 12:39:11.987

In the results above we can see that for the Async tests the timestamps show that the Test 2 insert  occurred while the stored procedure (Test 1) was still running  - ie after the "Test 1 Async - Start" insert command completed but before the "Test 1 Async - End" insert was executed.

We have generated a new thread to execute the stored procedure that does not require us to wait for it to complete before continuing.  Therefore the SQL script is now effectively running asynchronously.