Triggers: The hidden logic that will strike back – TSQL Tuesday #106

https://voiceofthedba.com/2018/09/03/t-sql-tuesday-106-trigger-headaches-or-happiness/

This month’s T-SQL Tuesday is brought by Steve Jones (b | t) and he wants to know if triggers causes headaches or happiness to us.

This is the 106nd edition of TSQL2sDay – an Adam Machanic (b | t) brainchild.

 
Triggers are those kind of database objects that sometimes are the best bet but most of the times they will strike back.

“Why?” – You may ask.

From my experience triggers are objects that we “tend” to forget are there, and only when we hit a problem and, sometimes, after digging into the problem we end by saying something like

wait..but why is the value different from what I have used?! Ohh…maybe the table has some triggers.

Yes, it has already been uttered by me before. For me, triggers are a hole of possible business logic written and forgotten…forever.
Let me just clarify the meaning of “forever” here – until you hit a problem or you need to rewrite some logic and the result is not what you expect. Again, because the code is ‘hidden’.

Recent pain – A project to migrate Firebird SQL to T-SQL

When Steve mention the “I think I’ve ended up using triggers in 0.01% of my tables or less.” I remember this project and after the math I can say they used ~78,84% and ~63,32% (were two databases) of triggers on their tables.

When looking at their code it became obvious the type of use they are making, mainly to fill 1-2 columns with current datetime and/or to generate a new ID (because they didn’t use identity columns – supported on v3.0 or higher)

“You never used triggers, it’s what you are saying?”

No, it’s not. I have used them before and not only 0,01% but maybe less than 5%.

Let me share an story

Back in the earlier times when I was a full-time T-SQL developer (I think was on 2008/2009), I worked with an application that use triggers to insert data on some tombstone tables that help to go back and get the data at that time.
Nowadays, we can achieve this with much less code using temporal tables.

One day I hit an issue where the record on the main table wasn’t being inserted, I run the code, a stored procedure, with some parameters and I got back a new ID (we had identity columns) all good. When I ran with other parameters (the ones not working) I didn’t see the new ID, I went back and forward and seeing the gaps generated by the problematic parameter set, throw some debug messages into the stored procedure, also throwing more information on the catch block to understand the problem.

Then, after reading the message carefully I saw that the problem was on the insert on my tombstone table and not on my “visible” logic. Spent about one hour to get it…

Waste of time

Or not, from that day on, one of my first questions when I hear people have some problems like this is to ask “are there any triggers on the tables involved?”

With that I already saved some hours of work.

Wrap up

As you may understand I decided to not use triggers whenever possible, but, sometimes we pick the train already in movement and we may need them.
If you fit on this last scenario, please, please document it very well!

I’m eager to read other’s stories because I bet they also have some good points and on this interesting topic.

Thanks for reading.

I will be speaking @ SQLSaturday #795 Porto

On 29th September we will have SQL Saturday Porto and I’m proud to announce that I will be speaking!

I will be speaking about dbachecks PowerShell module – dbachecks – Checking SQL Server environments with PowerShell.

The event will be held at ISEP, Rua Dr. António Bernardino de Almeida, 431, 4249-015 Porto, Portugal and you can see the full schedule here.

Register, it’s free!

Hope to see you in Porto! If you see me, come and say hi!

Don’t cutoff yourself with dates in T-SQL – Did you know…

Almost all the applications we use have a lot of options. And, sometimes we even stumble across them by accident.
Who’s ever heard something like “Normal user don’t even use 5% of Excel capabilities!”?

Other options, we know they exist but because the default value it’s ok (so far) we tend to forget it. It is just sitting there, waiting for the day we want/need to change it.

The oddity

Few days ago I was talking with a friend that show me some “odd behavior” when working with dates on SQL Server.

Are you mad?! Is this SQL Server bugged?

In case you ask, this is my table definition:

CREATE TABLE DatesFormat
(
    dt DATETIMEOFFSET DEFAULT ('1900-01-01')
);
GO

Let’s take a closer look at the SELECT.

  1. On the 1st column, we can see the complete value of the column
  2. The 2nd column picks the year from the date column
  3. The 3rd one also picks the year from a datetime but declared as string.

But why don’t the 2nd and 3rd columns return the exact same value?!

What is your interpretation?

What do you read when you see some date in a format like “01-Jan-00 00:00:00.000”? Keep in mind that I’m talking about the output directly from the table and without any formatting.
1st of January seems to leave no doubt (just because there is no default date format starting with two digits for the year), but…what about the year part ’00’?
It stands for 1900 and the 3rd column is wrong?
Or it stands for 2000 and the DATEPART function is returning the wrong value?

Both are returning the correct value! Say hello to “Two Digit Year Cutoff” configuration

You can find it on the advanced tab in the Server Proprieties:

Or by running the sp_configure command:

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'two digit year cutoff';  
GO 

Or even using dbatools PowerShell module:

Get-DbaSpConfigure -SqlInstance sql2016 -ConfigName 'TwoDigitYearCutoff'

Output:

That’s right! This option is the one that makes the last column value being translated to 2000 and not 1900.

If we change the configuration to 1999 instead of 2049 (default value) the result of the DATEPART will be 1900 but having it as 2049 will convert the year as 2000 (from the date that is a string).

The test:

-- Check the running value
EXEC sp_configure 'two digit year cutoff';
GO

-- Notice the 3rd column
SELECT dt, DATEPART(YEAR, dt), DATEPART(YEAR, '01-Jan-00 12:00:00 AM +00:00') 
  FROM dbo.DatesFormat
GO

-- Change the configuration
EXEC sp_configure 'two digit year cutoff', 1999;  
GO  
RECONFIGURE ;  
GO 

-- Confirm the running value
EXEC sp_configure 'two digit year cutoff';
GO

-- See the different value on the 3rd column
SELECT dt, DATEPART(YEAR, dt), DATEPART(YEAR, '01-Jan-00 12:00:00 AM +00:00') 
  FROM dbo.DatesFormat

Output:

Remember, this only happens when you use a literal string.

To set a new value using dbatools:

Set-DbaSpConfigure -SqlInstance sql2016 -ConfigName 'TwoDigitYearCutoff' -Value 1999

Output:

What about the returning value?

Yeah, I know, why is the value of the first column returned on that format? You are used to seeing in the format of yyyy-MM-dd right?
I’ll explain this in a next post! Stay tuned.

Summary

Next time you have to work with dates in formats like dd-MMM-yy remember that “Two Digit Year Cutoff” configuration exists and may mislead you.

To complete the question…”Did you know that ‘Two Digit Year Cutoff’ configuration can trick you?” Now you do.

Thanks for reading.

I will be speaking @ Tuga IT 2018

Starting on 19th of July and during 3 days the Tuga IT 2018 Summer Edition will be happening in Lisbon!

This edition will have:

4 full-day workshops
38 breakout sessions (1 hour)
5 deep dive sessions (2 hours)
That is over 72 hours of content delivered by 44 awesome speakers, many of them recognized industry experts.

You can see the full schedule here. This edition will have sessions about: Cloud, Data Platform, Development, Integration, IOT, Office, PowerShell and Security.

I will be speaking on 19th about the dbachecks PowerShell module for SQL Server professionals. You can find more information about the session here: dbachecks – Checking SQL Server Environments With PowerShell.

The event will be held at Microsoft Headquarters in Lisbon.

What are you waiting for? Registration to the main event is free for all attendees, with the exception of the full-day workshops.
Main event registrations
Workshops registrations.

Hope to see you in Lisbon!

Did you know…you can change the default file encoding for new query on SSMS?

If you have been reading my last blog posts, you know that I’m currently working on a SQL code migration from Firebird to SQL Server.

The client provided the scripts with all modules (Stored Procedures, functions, etc) and the steps I’m following (roughly speaking) for converting are:

  1. Open new query window
  2. Copy and paste de object code
  3. Save the file
    This is how the file look like:

  4. Run a PowerShell script that does a find and replace based on a hashtable. Apply all the changes and save the file again.

  5. The file refresh on SSMS
    This is how the file look like after the find and replace:

  6. Unicode characters are broken 😦

So…what is happening?

The file that is used to create a new query window has ANSI encoding but when I save the file on the PowerShell script I save it as UTF-8 because the client have comments on the code with unicode characters.

On this process, the unicode characters are replaced by some symbols.

How to solve?

We can change the default file encoding in order to be the one we want in the first place. What I have done was change from ANSI encoding to UTF-8.

This way, when use the keyboard shortcut CTRL + N to open a new window and hit Save, I’m saving as UTF-8 which means that the PowerShell script will do the find and replace, save the file and preserve the unicode characters. 🙂

Where is that default file?

My path to the file is C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql where the 140 stands for the SSMS v17 (in my case, right know I’m using the v17.5).
Inside this folder we can find the file SQLFile.sql.

We just need to open it, for example with notepad, do the Save As and choose the encoding we want.

Now if you go to the SSMS, open a new query window and hit save, you can confirm that the file is saved by default with the new encoding you have setup.

With this I have eliminated one tedious step that forced me to do the Save As and choose the UTF-8 every single file I wanted to save. I have hundreds of objects so, this small improvement times hundreds of iterations save me a good amount of time!

Thanks for reading.

SQL Saturday #731 Athens 2018


In less than two weeks (on 16th June) it’s time for SQL Saturday Athens and I’m proud to announce that I will be speaking!

This will be my first SQL Saturday ever both as speaker and attendee outside Portugal so I’m especially thrilled and excited to be part of it!

I will be presenting my Administrate SSRS/PBIRS with PowerShell session and I can share in first hand that it will include new demos with some fixes and enhancements that will be available on the next version of the module later this week.

The event will be held at Microsoft Hellas , 221 Kifisias Ave, Marousi, Attica, 151 24 and you can see the full schedule here.

Hope to see you in Athens! If you see me, come and say hi!

Learn, Evolve and Giving Back – TSQL Tuesday #102

https://scribnasium.com/2018/05/giving-back-t-sql-tuesday-102-invite/

This month’s T-SQL Tuesday is brought to us by Riley Major‏ (b | t) and he encourage us to talk about how we are helping by giving back to our community.

This is the 102nd edition of TSQL2sDay – an Adam Machanic’s (b | t) brainchild.

Let’s go back for a moment:

I have heard about SQL Server the first time back in 2003 when I was on the high school and I had a database class. Was a slow start and, at that moment I haven’t imagined that would follow that path professionally.

Learn

At that time I didn’t even know that a community exists, but when I went to professional course (level IV – it’s a degree before bachelors) on 2006 that was based on Microsoft technologies, .NET (Web and Windows forms) and I had a database class using SQL Server 2000!
That was when I started using more and more the internet for study, found some blogs posts and the SQLServerCentral. So I can say that I started consuming the knowledge from the community back on 2006.

A year later I started my intership in what was my first IT job. At the time I needed to work with a bit of everything but more focused on SQL Server development. The guy that was on my place before I arrived took a manager position but he was a big lover of SQL Server and I learnt a LOT from him! At the time he subscribed the SQLServer Magazine in paper! (later the name changed for SQLServer Pro).

SQLServer Magazine October 2007

SQLServer Magazine
October 2007


You can found the maganize archives in PDF format here.

That was when I start knewing who people like Itzik Ben-Gan and Kalen Delaney were!

Evolve

During about five years I was a compulsive consumer of blog posts related with SQL Server development but most of the time was when I had a problem and wanted some guidance on the solution. During that time I haven’t shared too much knowledge on the internet.

Then, 2012, I went to my first SQL Port user group meetings and my first ever SQL Saturday, 115 Portugal. Those were my first real contact with SQL Server community and where I met some speakers and started to join the montly meetings regulary.

Giving back

At 2015 I joined Twitter and decided that I wanted to share my knowledge even more. So after being approached several times I took courage and did my first public presentation for SQL Port user group in Lisbon and right after I submitted a session for SQL Saturday Oporto 2015 and I was accepted.
You know, start small and grow…so I started talking in Portuguese for the user group and then went to SQL Saturday where I did the first public speech in English.

Now, we were in 2016 and was the first edition of TugaIT (the CFS is open for this year – Summer Edition) and at that time I was “just” a volunteer but I had a gut feeling – I have discovered, few months earlier, that dbatools was a thing and decided to go and talk with the creator Chrissy LeMaire – I had written a couple of PowerShell scripts to help me and decided to ask her if she has the intention to extend the module to best practices and we talk for like 1 hour, exchange contacts and started talking furiously about dbatools and then…I was doing a PR with a full command (Expand-DbaTLogResponsibly) and this was the time I felt I was doing my first “more international” contribution to the community.

Since then I have being more active on the community mainly with SQLServer and PowerShell stuff.

Some contribution points:

What you will do?

As you can see I didn’t born inside the comunity, I have grown because of it and decided to start giving back less then 3 years ago.
It hurst? Nothing!
It helps? A lot!
Help me be a better professional? For sure, I have learned so much from many different sources and every day is a new learning day!

Thanks for reading.