ICYMI What does it mean?

For a while now I have seen ICYMI splattered around the internet. I had no idea what it meant so just ignored it but I have finally had enough and I googled for a definition and I now know what it means.

ICYMI – In Case You Missed It

ICYMI is often used on social media sites to indicate articles that you might have missed.In Case You Missed It (icymi)

Software Development is littered with acronyms and technical terms that I don’t always know the meaning of.

When reading it is easy to ignore them and try and understand the rest of the article. However there are loads of resources out there that can explain technical terms in a wide variety of different ways.

I have tried to explain a few different terms and concepts on this blog. In the process of writing them I often do a bit of research and read a variety of different definitions, some confuse the heck out of me, while others make me go ahh that makes sense now.

When reading to learn, don’t be afraid to dig deeper and understand it all, don’t read the same terms again and again not understanding them like I did with ICYMI.

I might actually like SQL Server

How did this happen I think I may actually like SQL Server now?download

I remember a few years back when I used to grumble about writing a SQL query that included a JOIN. For some reason back then I could never get my head around JOINs.

Today is a totally different story. I wrote a deployment script that did the following:

1) dropped a database,
2) restored a database from a backup file,
3) Created a database table,
4) copied the contents of a table into this table,
5) disabled and re-enabled some triggers,
6) dropped some constraints and columns from a table
7) and renamed some columns.

It’s not like I woke up this morning with SQL Server knowledge. For many years I have been adding extra features and changing functionality to our line of business database and over time my SQL confidence has grown.

For every single one of the steps above I googled and looked up the SQL syntax (every time I write an insert or update statement I look up the syntax, one day it will stick in my brain!) I think the main reason is once you have used SQL for a while you get to see how it works and can split it down into small steps.

As I blogged the other day I am currently working on improving a bad database and today I wanted to test the deployment process. All my changes are in a SSDT project so I took a backup of my database and tried to publish.

Error! Your changes will result in data loss, no surprises there as I was expecting that error. The main culprit for this was a trigger I wrote but I didn’t find that out until the end of the day.

As I was working on a backup I could do what I like including destructive changes. I tried creating a pre deployment script. I thought if that runs first, I can persuade SSDT that it matches SQL Server and therefore no data is being lost.

This didn’t work, but I had created the start of my script mentioned above. I had steps 3 and 4. Lets try running my script first and then try running the deployment of my SSDT project. To get this to work I am going to have to rebuild my database a few times, I did this manually with SQL Management studio a couple of times until I thought, I could add this to my script – that’s step 1 and 2 done.

Then I got some errors about triggers so I tried disabling them and re-enabling them after it had finished. That’s step 5.

Finally I needed to drop some columns, this resulted in an error about a constraint. A few trial and error run through’s to find out which constraints I needed to drop and then I could drop the columns.

The last step was to rename some columns. I thought after this I can run publish from visual studio. No it still complained about data loss. I dropped another trigger and then I could run everything without any errors.

Wohoo! Aren’t I clever. A simplified version of my code is below

USE master –can’t drop the database if its open!
–Step 1
DROP DATABASE DBName

–Step 2
RESTORE DATABASE DBName FROM DISK=’E:\DBName.bak’
WITH
MOVE ‘DBName_dat’ TO ‘C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DBName.mdf’,
MOVE ‘DBName_log’ TO ‘C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DBName.ldf’,
REPLACE,
STATS=10
USE DBName –swap back to the database you just restored

–Step 3
CREATE TABLE TName2 (
ID INT NOT NULL,
Name NVARCHAR(50) NOT NULL,
Address NVARCHAR(50) NOT NULL,
City NVARCHAR(50) NOT NULL,
Postcode NVARCHAR(50) NOT NULL)

–Step 5
DISABLE TRIGGER TR_Trigger ON TName1
GO

–Step 4
INSERT INTO TName2 (Id,Name)
SELECT Id, Name FROM TName1
GO

–Step 6
ALTER TABLE TName1
DROP CONSTRAINT [DF_Id], [DF_Name]
GO
ALTER TABLE TName1
DROP COLUMN Id, Name
GO

–Step 7
sp_RENAME ‘TName1.City’, ‘Area’ , ‘COLUMN’
GO

–drop that last trigger
DROP TRIGGER TR_Trigger2
GO

–Step 5
ENABLE TRIGGER TR_Trigger ON TName1