Author Archives: admin

Four SQL MVPs at LobsterPot – including three in Australia

Today LobsterPot Solutions sets a new first. We are the only company to ever employ three current Australian SQL MVPs, giving us four awardees in total. Congratulations to Martin Cairney who joins Julie Koesmarno (AUS), Ted Krueger (USA) and me (AUS) as recipients of this prestigious award. This demonstrates LobsterPot’s ongoing commitment to the SQL Server community, that show that our consultants are truly influential in the SQL world.MVP_FullColor_ForScreen

From Microsoft’s website about MVPs:
Microsoft Most Valuable Professionals, or MVPs are exceptional community leaders who actively share their high-quality, real-world deep technical expertise with the community and with Microsoft. They are committed to helping others get the most out of their experience with Microsoft products and technologies.
Technical communities play a vital role in the adoption and advancement of technology—and in helping our customers do great things with our products. The MVP Award provides us with an opportunity to say thank you and to bring the voice of community into our technology roadmap.

This fits very closely with LobsterPot’s desire to help people with their data story. We help with the adoption and advancement of SQL Server, and help customers do great things with data. It’s no surprise that we see a high proportion of LobsterPot consultants are MVP awardees.

Getting Started with SSIS: Get the Software and Tools

People often ask me for tips on getting started with SSIS. My advice is always the same: Get a copy of SQL Server Developer Edition. At the time of this writing, the current version of SQL Server is SQL Server 2014. If you work in an organization that uses SQL Server, you may be able to obtain a copy of SQL Server 2014 Developer Edition from your organization. If not, I highly recommend purchasing your own copy. SQL Server 2014 Developer Edition is available at Amazon.com for about $50 USD. Developer…(read more)

Line Split Issues when Copying Data from SQL Server to Excel

Some of our developers noticed an issue a few days after celebrating our successful SQL Server 2012 upgrade. When they run a query in SSMS and copy and paste the results to Excel, each row gets split into many rows in Excel instead of one row like it used to do. They immediately started blaming SQL Server 2012, because no other changes were made, why is this happening?

Options to Improve SQL Server Bulk Load Performance

As a DBA I am always looking for ways to improve performance. Bulk loading data while it seems like a pretty straightforward task does have some different options that can be implemented to both improve the speed at which data is loaded as well as decrease the total amount of resources used to perform the data load. This tip will look at a few of those options.

SQL Server DML Triggers – Tips & Tricks – Part 1 (“Intro” & “Triggers activation order”)

This is the first blog post in a series of posts about Tips & Tricks of SQL Server DML Triggers.

Introduction

Triggers are often one of the main reason of quarrel between Developers and DBAs, between those who customize a database application and those who provides it. They are often the first database objects to be blamed when the performance degrades. They seem easy to write, but writing efficient Triggers is not easy. They have many side effects, but also a very important characteristic: they allow solving problems that cannot be managed in any other application layer.

A Trigger is a special type of stored procedure: it is not called directly, but it is activated on a certain event with special rights that allow you to access in-coming and out-coming data that are stored in special virtual tables called Inserted and Deleted. Triggers exist in SQL Server since the version 1.0, even before CHECK constraint. They always work in the same unit-of-work of the T-SQL statement that has called them. There are different types of Triggers: Logon Trigger, DDL Trigger and DML Trigger; the most known and used type is Data Manipulation Language Trigger, also known as DML Trigger. This series of posts treats only aspects related to DML Triggers.

There are many options that modify run time Triggers’ behavior, they are:

  1. Nested Triggers
  2. Disallowed results from Triggers
  3. Server Trigger recursion
  4. Recursive Triggers

Each of these options has, of course, a default value in respect to the best practices of Triggers development. The first three options are Server Level Options and you can change their default value using sp_configure system stored procedure, whereas the value of the last one can be set at the Database Level.

What do you think about Triggers? In your opinion, based on your experience, are they useful or damaging?

You will meet people who say: “Triggers are absolutely useful” and other people who say the opposite. Who is right? Reading the two bullet lists you will find the main reasons of the two different theory about Triggers.

People say that Triggers are useful because with them:

  • You can develop customize business logics without changing the user front-end or the Application code
  • You can develop an Auditing or Logging mechanism that could not be managed so efficiently in any other application layer

People say that Triggers are damaging because:

  • They are sneaky!
  • They can execute a very complex pieces of code silently!
  • They can degrade performance very much
  • Issues in Triggers are difficult to diagnose

As usual the truth is in the middle. I think that Triggers are a very useful tool that you could use when there are no other ways to implement a database solution as efficiently as a Trigger can do, but the author has to test them very well before the deployment on the production environment.

Triggers activation order

SQL Server has no limitation about the number of Triggers that you can define on a table, but you cannot create more than 2.147.483.647 objects per database; so that the total of Table, View, Stored Procedure, User-Defined Function, Trigger, Rule, Default and Constraint must be lower than, or equal to this number (that is the maximum number that will be represented by the integer data type).

Now, supposing that we have a table with multiple Triggers, all of them ready to fire on the same statement type, for example on the INSERT statement: “Have you ever asked yourself which is the exact activation order for those Triggers?” In other worlds, is it possible to guarantee a particular activation order?

The Production.Product table in the AdventureWorks2014 database has no Triggers by design. Let’s create, now, three DML Triggers on this table, all of them active for the same statement type: the INSERT statement. The goal of these Triggers is printing an output message that allows us to observe the exact activation order. The following piece of T-SQL code creates three sample DML AFTER INSERT Triggers on Production.Product table.

USE [AdventureWorks2014];
GO

-- Create Triggers on Production.Product
CREATE TRIGGER Production.TR_Product_INS_1 ON Production.Product AFTER INSERT
AS
  PRINT 'Message from TR_Product_INS_1';
GO

CREATE TRIGGER Production.TR_Product_INS_2 ON Production.Product AFTER INSERT
AS
  PRINT 'Message from TR_Product_INS_2';
GO

CREATE TRIGGER Production.TR_Product_INS_3 ON Production.Product AFTER INSERT
AS
  PRINT 'Message from TR_Product_INS_3';
GO

Let’s see all Triggers defined on Production.Product table, to achieve this task we will use the sp_helptrigger system stored procedure as shown in the following piece of T-SQL code.

USE [AdventureWorks2014];
GO

EXEC sp_helptrigger 'Production.Product';
GO

The output is shown in the following picture.

 

Picture 1 – All Triggers defined on Production.Product table

Now the question is: Which will be the activation order for these three Triggers? We can answer to this question executing the following INSERT statement on Production.Product table, when we execute it, all the DML INSERT Triggers fire.

USE [AdventureWorks2014];
GO

INSERT INTO Production.Product
(
  Name, ProductNumber, MakeFlag, FinishedGoodsFlag, SafetyStockLevel,
  ReorderPoint, StandardCost, ListPrice, DaysToManufacture, SellStartDate,
  RowGUID, ModifiedDate
)
VALUES
(
  N'CityBike', N'CB-5381', 0, 0, 1000, 750, 0.0000, 0.0000, 0, GETDATE(),
  NEWID(), GETDATE()
);
GO

The output returned shows the default Triggers activation order.

Message from TR_Product_INS_1
Message from TR_Product_INS_2
Message from TR_Product_INS_3


As you can see in this example, Triggers activation order coincides with the creation order, but by design, Triggers activation order is undefined.

If you want to guarantee a particular activation order you have to use the sp_settriggerorder system stored procedure that allows you to set the activation of the first and of the last Trigger. This configuration can be applied to the Triggers of each statement (INSERT/UPDATE/DELETE). The following piece of code uses sp_settriggerorder system stored procedure to set the Production.TR_Product_INS_3 Trigger as the first one to fire when an INSERT statement is executed on Production.Product table.

USE [AdventureWorks2014];
GO

EXEC sp_settriggerorder
  @triggername = 'Production.TR_Product_INS_3'
  ,@order = 'First'
  ,@stmttype = 'INSERT';
GO

At the same way, you can set the last Trigger fire.

USE [AdventureWorks2014];
GO

EXEC sp_settriggerorder
  @triggername = 'Production.TR_Product_INS_2'
  ,@order = 'Last'
  ,@stmttype = 'INSERT';
GO

Let’s see the new Triggers activation order by executing another INSERT statement on Production.Product table.

USE [AdventureWorks2014];
GO

INSERT INTO Production.Product
(
  Name, ProductNumber, MakeFlag, FinishedGoodsFlag, SafetyStockLevel,
  ReorderPoint, StandardCost, ListPrice, DaysToManufacture, SellStartDate,
  RowGUID, ModifiedDate
)
VALUES
(
  N'CityBike Pro', N'CB-5382', 0, 0, 1000, 750, 0.0000, 0.0000, 0, GETDATE(),
  NEWID(), GETDATE()
);
GO

The returned output shows our customized Triggers activation order.

Message from TR_Product_INS_3
Message from TR_Product_INS_1
Message from TR_Product_INS_2

In this first part you have learnt how to set the activation of the first and of the last Trigger in a multiple DML AFTER INSERT Triggers scenario. Probably, one question has come to your mind: “May I set only the first and the last Trigger?” The answer is: “Yes, currently you have the possibility to set only the first Trigger and only the last Trigger for each statement on a single table”; as a friend of mine says (he is a DBA): “You can set the activation only of the first and of the last Trigger because you should have three Triggers maximum for each statement on a single table! The sp_settriggerorder system stored procedure allows you to set the first and the last Trigger fires, so that the third one will be in the middle, between the first and the last”.

All examples in this post are based on AdventureWorks2014 database that you can download from codeplex website at this link.

In The Cloud: Azure Operational Insights for Server Monitoring

Microsoft is now offering a preview version of Azure Operational Insights, which is a cloud based server monitoring solution and replacement for System Center Advisor. I decided to try it out on my desktop’s default installation of SQL Server 2014. I picked this SQL Server instance because it’s not configured how I would configure a production server. I wanted to see what things Azure Operational Insights would find. I went to http://azure.microsoft.com/en-us/services/operational-insights/ to get…(read more)