SQLGeordie's Blog

Helping the SQL Server community……where i can!

Making sure your Triggers fire when they should — March 4, 2013

Making sure your Triggers fire when they should

As some of you may be aware, triggers are not my favourite thing in the world but like most things, it does have its place.

Whilst onsite with one of my clients, one of the processes fires a trigger on insert which ultimately runs a SSRS subscription to email a report. All sounding fairly feasible so far. However, this process is also used as part of a batch process overnight which would run a separate insert statement (actually another stored procedure in another job step) instead of the “onDemand” insert. Ok, still doesn’t sound like too much of an issue.

Now, they started experiencing occasional failures of this job during the day with the error relating to the fact that the SSRS subscription job was being called when it already was running. Interesting, this in theory shouldn’t ever happen because the process either ran the jobs based on the batch process or the one off onDemand.

Stepping through the process, it led me to an AFTER INSERT trigger. Upon opening it I spotted the issue straight away. Something that as I’ve found over the years as a consultant, a lot of DBA’s and developers have failed to understand that (from MSDN ):

These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.This is by design.

So, the issue was that step 3 ran a procedure which ultimately ran an insert statement for the onDemand insert, step 4 ran a procedure to insert for the overnight batch process which as it happens doesn’t have any records to insert but will in fact fire the trigger to run the SSRS subscription again! There is a number of ways to fix this but I’ve tended to stick with a basic check of the “inserted” table for results and RETURN out if no records are there to process.

I’ve supplied a bit of test code below for people to try this out.

Lets create a test table and an audit table:

USE tempdb
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type in (N'U'))
DROP TABLE [dbo].[TestTable]
GO
CREATE TABLE [dbo].[TestTable]
(
	TestTableID INT IDENTITY(1,1),
	TestTableDescr VARCHAR(20)
)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditTrigger]') AND type in (N'U'))
DROP TABLE [dbo].[AuditTrigger]
GO
CREATE TABLE [dbo].[AuditTrigger]
(
	AuditTriggerID INT IDENTITY(1,1),
	AuditTriggerDescr VARCHAR(20),
	DateCreated DATETIME
)
GO

INSERT INTO dbo.TestTable (TestTableDescr)
VALUES ('Test1'), ('Test2'), ('Test3');

SELECT * FROM dbo.TestTable;

Now lets create the trigger with no checking:

USE [TempDB]
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trTestTable]'))
DROP TRIGGER [dbo].[trTestTable]
GO

CREATE TRIGGER [dbo].[trTestTable] ON [dbo].[TestTable]
   AFTER INSERT
AS
BEGIN

	--Log the fact the trigger fired
	INSERT INTO [dbo].[AuditTrigger] (AuditTriggerDescr, DateCreated)
	SELECT 'Trigger Fired', GETDATE()

END
GO

Test Inserting a record that exists:

--Valid Insert
INSERT INTO dbo.TestTable (TestTableDescr)
SELECT TestTableDescr
FROM dbo.TestTable
WHERE TestTableDescr = 'Test1';

SELECT  *
FROM    [dbo].[AuditTrigger];

Test Inserting a record that doesn’t exist:

--Not a Valid Insert
INSERT INTO dbo.TestTable (TestTableDescr)
SELECT TestTableDescr
FROM dbo.TestTable
WHERE TestTableDescr = 'Test4';

SELECT  *
FROM    [dbo].[AuditTrigger];

You’ll now see that there are 2 entries in the AuditTrigger table due to the fact that the trigger fired even though no records were actually valid to insert.

So, lets amend the trigger to check for valid inserts:

USE [TempDB]
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trTestTable]'))
DROP TRIGGER [dbo].[trTestTable]
GO

CREATE TRIGGER [dbo].[trTestTable] ON [dbo].[TestTable]
   AFTER INSERT
AS
BEGIN

	--Check to see if any records were inserted
	IF NOT EXISTS (SELECT 1 FROM INSERTED)
		RETURN 

	--Log the fact the trigger fired
	INSERT INTO [dbo].[AuditTrigger] (AuditTriggerDescr, DateCreated)
	SELECT 'Trigger Fired', GETDATE()

END
GO

and test the inserts again:

Test Inserting a record that exists:

--Valid Insert
INSERT INTO dbo.TestTable (TestTableDescr)
SELECT TestTableDescr
FROM dbo.TestTable
WHERE TestTableDescr = 'Test2';

SELECT  *
FROM    [dbo].[AuditTrigger];

Test Inserting a record that doesn’t exist

--Not a Valid Insert
INSERT INTO dbo.TestTable (TestTableDescr)
SELECT TestTableDescr
FROM dbo.TestTable
WHERE TestTableDescr = 'Test4';

SELECT  *
FROM    [dbo].[AuditTrigger];

No record will have been inserted with the final insert statement!

Lets clean up our tempdb:

USE [TempDB]
GO

--Clean up
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type in (N'U'))
DROP TABLE [dbo].[TestTable]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditTrigger]') AND type in (N'U'))
DROP TABLE [dbo].[AuditTrigger]
GO

Hopefully this will help point out the misconception that triggers only fire when records are actually inserted 🙂

As per usual, I’d like to hear peoples thoughts/experiences on this topic.

DBCC CheckTable, Spatial Indexes and incorrect compatibility mode….. — November 19, 2012

DBCC CheckTable, Spatial Indexes and incorrect compatibility mode…..

Just a very quick blog today regarding an issue that has arisen with one of my clients. During Integration it became apparent that one table in particular was failing during the weekly consistency checks, the error being output:

DBCC results for ‘sys.extended_index_1696529623_384000’.

There are 313423 rows in 1627 pages for object “sys.extended_index_1696529623_384000”.

DBCC results for ‘schema.Table’.

There are 312246 rows in 12192 pages for object “schema.Table”.

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

A bit of background. The server is running SQL Server 2008R2 SP1 CU2 and the database in question is still in compatibility 90 (SQL Server 2005). The table in question has a spatial index on a Geography column.

So, how do we fix this? Well there’s a couple of options.

  1. Change the compatibility to 100
  2. Install SQL Server 2008R2 SP1 CU3…

This is a documented issue (kb 2635827) and the fix can be found on Microsoft’s Support Pages.

FIX: Access violation when you run a DBCC CHECKDB command against a database that contains a table that has a spatial index in SQL Server 2008 or in SQL Server 2008 R2

As to which fix we deploy, well that’s for tomorrow’s fun and games 😉

Developing Microsoft SQL Server 2012 Databases (70-464) – My Thoughts — October 30, 2012

Developing Microsoft SQL Server 2012 Databases (70-464) – My Thoughts

I finally pulled my finger out and took the last exam of the MCSE SQL Server 2012 – Data Platform certification last week and passed with a score of 876 which is pretty respectable in my opinion 😉

The exam consisted of 3 sections:

  • 1 – 6 Scenario based questions
  • 2 – 7 Scenario based questions
  • 3 – 32 Generic questions
  • So those of you who are clever enough to work out that’s a grand total of 45 questions. I’m pleased to say that once again the quality of the questioning was to Microsoft’s usual standard, not in terms of difficulty but more in terms of complete irrelevance!

    I’m bound by NDA so can’t go into specifics but I’ll try and provide and example of this complete and utter irrelevance without giving the game away:

    Q: What is your favourite holiday destination?

    A (Select one of the following):

  • Green
  • Green with a bit of Yellow
  • Green with a bit of Blue
  • Sky Blue with Pink dots
  • Hmmmm, bit of a tricky one here. From what I can tell, none of the answers bear any relevance whatsoever to the question…….hmmmmm……..suppose I’ll have to take a random guess and hope that the answer I select is the one they’ve set as being correct!!!

    It was just a good job i wasn’t on the threshold of pass/fail as this could potentially have been the difference. From the actual question, it was to be a simple answer but I suppose I’ll never know whether or not my random guess worked out or not :(. I just hope the comments I left regarding this issue are taken up and fixed so others don’t have the same issue.

    All in all I felt comfortable throughout the exam but do know for a fact that my knowledge of Assemblies / CLR’s has slipped significantly and I need a refresher.

    So that’s it. SQL Server 2012 MCSE done and dusted and no more exams……well for the next 3 years at least. So whats next? Not sure, there is the MCSM exams but I have to be honest, I’ve got no formal certifications in SQL Server 2005/2008 but I know for a fact that I have far more knowledge and experience of its features than I do for SQL Server 2012 so I suppose I kind of backed up my original issue I have with these exams. Yes I studied and yes I did learn a lot from doing so but in no way shape or form would I class myself as an “Expert” in SQL Server 2012 – I don’t believe anyone could!

    As per usual, any thoughts or comments are welcome.

    Implementing a Data Warehouse with Microsoft SQL Server 2012 exam (70-463) – My Thoughts — September 13, 2012

    Implementing a Data Warehouse with Microsoft SQL Server 2012 exam (70-463) – My Thoughts

    Well I finally got around to completing the MCSA aspect of the SQL Server 2012 Certification and I’m pleased to say i passed with flying colours. As some of you may be aware I managed to nab and pass 3 of the Beta exams (70-461, 70-462 and 70-465) back in April and decided to see the MCSE through.

    I really wasn’t sure how this exam was going to go as I’ve been working a lot recently with MDS 2012 and SSIS 2008 and revised the new 2012 features but went in with no real expectations. The exam consisted of 55 questions, again ranging from multiple guess, select the 3 things you’d do in order to a new feature i’ve not seen before and that is a drag n drop facility of a SSIS control flow which I thought was nifty. 

    The area I thought I’d struggle on was DQS but in fact found that aspect relatively simple, the difficult area for me was the “select the 3 things you’d do in order” relating to the new Project Deployment area of SSIS 2012. I’ve done a fair bit of “tinkering” with this over the last few months but its obvious I’m not as prolific as I thought as I found certain questions difficult to get my head around what it was suggesting in the answers. I obviously did ok in this area (according to the score sheet) but at the time i was sweating a bit.

    Anyone wanting hints and tips, I obviously can’t go into detail but I’d definitely brush up on the new features of SSIS 2012!!! 

    Oh, and anyone wanting to know, the pass mark is 700 – none of the Beta exams told you this and I know some have said it was actually 800……

    Now onto 70-464 – Developing Microsoft SQL Server 2012 Databases, to complete the SQL Server 2012 MCSE certification!!!!

    SSIS SCD vs MERGE Statement – Performance Comparison — July 3, 2012

    SSIS SCD vs MERGE Statement – Performance Comparison

    I wouldn’t class myself as an expert in SSIS but I certainly know my way around but came across something today which I thought I’d share. As with a lot of things there are “many ways to skin a cat”, none of which is something I’ll go into at the moment but what i will concentrate on is updating columns in a table where the data has changed in the source.

    One of the projects I’m currently working on requires this very process and when i set about doing so I created the T-SQL Merge statement to do the business. However, the question was raised as to why I didn’t use SSIS’s built in component Slowly Changing Dimension (SCD)? I didn’t really have an answer other than personal preference but decided to delve into it a bit further and compare the performance of each method.

    As a test, I created a source table with a Key and Name column:

    USE TempDB;
    
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.iSource') AND type in (N'U'))
    	DROP TABLE dbo.iSource;
    
    CREATE TABLE dbo.iSource
    (
       ID INT,
       Name varchar(100)
    );
    
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.iTarget') AND type in (N'U'))
    	DROP TABLE dbo.iTarget;
    	
    CREATE TABLE dbo.iTarget
    (
       ID INT,
       Name varchar(100)
    );
    

    and populated it with some dummy data:

    INSERT INTO dbo.iSource (ID,Name)
    SELECT TOP 10000
    ROW_NUMBER() OVER (ORDER BY t.object_id) AS rownumber
    ,'Name_'+convert(varchar(4),ROW_NUMBER() OVER (ORDER BY t.object_id))
    FROM sys.tables t
    CROSS JOIN sys.stats s;
    
    INSERT INTO dbo.iTarget (ID,Name)
    SELECT TOP 10000 
    ROW_NUMBER() OVER (ORDER BY t.object_id DESC) AS rownumber --Done in descending order
    ,'Name_'+convert(varchar(4),ROW_NUMBER() OVER (ORDER BY t.object_id))
    FROM sys.tables t
    CROSS JOIN sys.stats s;
    
    SELECT ID, Name FROM iSource;
    SELECT ID, Name FROM iTarget;
    

    So we now have a source and target table with different Names and we’ll look to update the iTarget table with the information coming from iSource.

    Method 1 – MERGE Statement

    MERGE dbo.iTarget AS target
    	USING (
    	SELECT ID, Name
    	FROM dbo.iSource
    	 ) AS  source (ID, Name)
    		ON (target.ID = source.ID)
    		WHEN MATCHED AND target.Name <> source.Name 
    		THEN 
    			UPDATE SET Name = source.Name
    	 WHEN NOT MATCHED THEN 
    		 INSERT (ID, Name)
    		 VALUES (source.ID, source.Name); 
    

    Using this method simply in SSMS for simplicity, profiler output 2 rows for Batch Starting and Batch Completing, CPUTime of 125ms and Duration of 125ms and it updated 6678 records. Top stuff, as expected.

    Method 2 – SSIS SCD Component
    I rebuilt the tables to put them back to where we started and set about creating the same thing in SCD setting ID as the business key and Name as the changing attribute and not setting inferred members, below is a screen dump of the outcome of this:

    BEFORE:

    I clear down the profiler and run the ssis package and the outcome is quite astounding.

    DURING/AFTER:

    The profiler output 13456 rows including 6678 rows of queries like this:

    exec sp_executesql N'SELECT [ID], [Name] FROM [dbo].[iTarget] WHERE ([ID]=@P1)',N'@P1 int',8

    as well as 6678 rows of queries similar to this:

    exec sp_execute 1,'Name_3304',3304

    Total Duration of 37 seconds (yes that’s seconds not ms!!)…….and this is on a table of only ~7k rows!

    Well I’ll be damned, the SCD basically runs a cursor looping each record checking for a match on ID and updating that record if so. I can’t actually believe that MS have built a component which performs in this way.

    So, to answer the question asked ” why I didn’t use SSIS’s built in component Slowly Changing Dimension (SCD)?”, I now have a definitive answer, it doesn’t perform!

    I’m sure SCD has its place but for me, the requirements and the datasets I’m working on I think I’ll stick with MERGE for now….. 🙂

    NOTE: This was done on SQL Server 2008R2 Developer Edition running on Windows 7 Ultimate, not sure if SQL Server 2012 has improved the SCD performance but I’ll leave that for another day.

    It’s that time of year…..Exceptional DBA Awards 2012 — June 25, 2012

    It’s that time of year…..Exceptional DBA Awards 2012

    Being a 2011 finalist I felt I should try and rally all those who truly are exceptional to get their nominations in and quick sharp as the closing date is getting close.

    I was lucky enough to be nominated for this award last year and wasn’t going to follow it through as I felt I didn’t really stand a chance but when I sat and thought about it, if someone is willing to think of you as being exceptional at what you do, enough so to nominate you then why not, what’s the worst that can happen!!??!!

    The level of talent out the is phenomenal and the 4 guys I was up against last year are up there with the best in the world. Don’t let that put you off though, I feel that this award is very much focused towards those in the USA and not many actually make it through to the finals from the UK (Kevan Riley Blog / Twitter and myself I think are the only two!) so I think we need to give a bigger push this year and try and get more than one finalist from the UK 🙂

    If you haven’t been nominated by one of your peers then nominate yourself, there’s no rule saying you can’t and in fact Redgate encourage it.

    Get entered, the questions answered and cross your fingers!

    Good luck!!!!

    And the results are in…..SQL Server 2012 beta exams —

    And the results are in…..SQL Server 2012 beta exams

    Well after being a bit late in trying to book the beta exams I managed to get three of the five I needed for the MCSE data platform booked, taken and I’m pleased to say passed!

    Unfortunately due to the fact I did 461, 462 and 465 means I don’t actually come away with any certification as I need 463 for the MCSA and then the 464 to complete the MCSE.

    From what others in the field have said about 463, it’s very SSIS orientated which as it happens works out well for me as the project I’m currently working on is primarily SSIS so when things quieter down a bit I’ll look to get it booked. As for 464, I may very well look to batter that one out around the same time so it’s done n dusted.

    I’ve never really been a big fan of Microsoft certification and my mindset hasn’t changed much. The questioning is still vague at times and in my opinion done in such a way that it’s a test of whether you can read a question and do what Microsoft believe is the best way to do things but I still don’t feel the questions always give enough information for you to give the best solution. In the real world there are a hell of a lot more questions I’d be asking in some of the scenarios before I could make a correct decision.

    Anyway, enough whinging. Time to do some proper work 🙂

    Querying Microsoft SQL Server 2012 Beta exam (70-461 / 71-461) – My Thoughts — April 13, 2012

    Querying Microsoft SQL Server 2012 Beta exam (70-461 / 71-461) – My Thoughts

    Well I’ve now done the final SQL Server 2012 exam I managed to get a slot booked for. The Querying Microsoft SQL Server 2012 exam wasn’t going to be my strongest subject as I’m more of a DBA than Developer but i felt it went quite well.
    The exam consisted of 55 questions, varying in structure from multiple guess to drag n drop. There were only about 5 or 6 questions i left comments about relating to the content not being clear, typo’s or in one instance an actual mistake in the question so all in all a better setup than the Administrator exam I took first off (70-462 / 71-462).

    The biggest issue I found was down to my own fault. I didn’t revise on the syntax of the new 2012 T-SQL functionality. Don’t get me wrong, I know i’ve got a lot of them right but with some, although I knew the answer was down to 2 of the 4, I didn’t know it well enough to be 100% certain as there was only 1 word different in the syntax which I’m a bit disappointed with……..but no-one to blame but myself 🙂

    I’m still not sure whether the pass mark is 70% or 80% and hoping I’ve answered enough of the non-2012 questions correctly to scrape through.

    As always, I’d be interesting to hear other peoples thoughts on any of the 2012 exams they’ve taken so far…..

    Designing Database Solutions for Microsoft SQL Server 2012 Beta exam (70-465 / 71-465) – My Thoughts — April 6, 2012

    Designing Database Solutions for Microsoft SQL Server 2012 Beta exam (70-465 / 71-465) – My Thoughts

    After sitting the Administering Microsoft SQL Server 2012 Databases Beta exam (71-462) on Monday, I was still a little disappointed with Microsofts approach to questioning for these exams. So I went into this exam with pretty much the same mindset that the questions were going to be vague and in some cases completely wrong.

    Much to my surprise, I found the questions in this exam far far better. The exam itself was split into sections. There were 44 in total, 26 the standard multiple choice and 5 further scenario based sections, each with either 3 or 4 questions. Section one was much the same as the 71-462 exam but I felt the questions were in the majority, more concise and in my opinion gave enough information to make a valid judgement when answering. I did leave a few comments as there were a few of the questions that could do with a bit more work and had a couple of typo’s.

    The scenario sections again provided enough information to select the relevant answers, the only criticism of these sections were on question 2 of my second scenario, there was a major typo on the answers of question which didn’t pry me away from the answer but requires sorting.

    Now, the main thing that really got me with this exam was the amount of SQL Azure questions in section one. Is was not mentioned as a skill measured so needs looking into in my opinion, either add it as a skill measured or remove it from the exam.

    A much more enjoyable exam than the administrator, mainly due to the higher level of quality in the questioning resulting in far fewer comments being left and for me, I love the scenario based questions!!

    As always, I’d be interesting to hear other peoples thoughts on any of the 2012 exams they’ve taken so far…..

    Administering Microsoft SQL Server 2012 Databases Beta (70-462 / 71-462) – My Thoughts… — April 4, 2012

    Administering Microsoft SQL Server 2012 Databases Beta (70-462 / 71-462) – My Thoughts…

    On Monday I did my first Microsoft SQL Server exam since I did my SQL Server 2000 exams many moons ago and I can’t believe the quality of questioning hasn’t changed one bit! Don’t get me wrong, it wasn’t all plain sailing but for me, what made it difficult was the vagueness of the questions and in some cases, blatant mistakes! I understand the SQL Server 2012 exams are still in Beta but some of these errors are not simple spelling mistakes – although there were in fact quite a few typo’s in my exam.

    As an example (without giving the game away), one question refers to SQL Server 2012 and SQL Server 2000, but the answers did not refer to SQL Server 2000 at all. I re-read the question over and over to see if I was missing something but I’m confident that it was a mistake in the question.

    The exam itself consisted of 56 questions and I felt the allotted time was sufficient for this but what I didn’t find sufficient was the time allocated for leaving comments which from speaking to others in the field they’ve felt the same. During the exam I wrote comments down for around 25 of the questions in prep for leaving comments at the end but you’re only given 10 (or was it 15?) minutes to actually enter the comments into the system and with the machine (or could have been the application) being as slow as it was, this meant I had to type quicker than I’ve ever typed in my life :).

    I didn’t get through all the comments so picked out the ones I felt needed highlighting the most and got those done first and managed to get through about 12 of them before the time ran out. I was a little disappointed with this as for me that time should be sufficient for examinees to relay their comments fully so Microsoft can take the points / mistakes on board and rectify them before they launch the live exams in June.

    I was also a little surprised that I wasn’t given the test score there and then (have to wait until they actually go live), not quite sure why Microsoft have chosen to do it that way but I’m sure they have their reasons. I’m guessing its so people can’t relay an answers to others who are taking the exam but no reason was given so I can only speculate.

    So, all in all a good test of your administrative knowledge but in my opinion there’s a lot of work to be done to rectify the issues before they go live. It would be interesting to hear other peoples thoughts on any of the 2012 exams they’ve taken so far…..