SQL Server

If you have a table with a datetime column and you want to group the rows by month, then this can help:

select count(*), datediff(m, datetime_col, getutcdate())
from table
group by datediff(m, datetime_col, getutcdate())
order by 2

Seems to be more of a publicity stunt this, or at best a place for users to discuss problems with each other. Not sure anyone with any real power or knowledge from Microsoft takes any notice of it. Take this synonym bug in SQL Server. It's marked as fixed, but there is only a workaround posted. In my mind a workaround is not a fix, it's a workaround. I assume it's marked as fixed to make someone's performance stats look better. Just corporate bullshit.

If you want to have 2 columns in the same table and syncrhonize their values, then this can be achievied with triggers. That is, you have 2 columns, col1 and col2, and when col1 is changed you want to update col2, and vice versa. This can be useful for migration amongst other things. In this example I make col1==col2, but that need not be the case: you could have col2 == col1 / 2 and the same structure of triggers could be used:

You need 2 triggers examples of which are below. I do not use @@ROWCOUNT, as it contains the effect of the last SQL statement, so if there is a trigger than is on the same table and that trigger runs before these 2, then the value in @@ROWCOUNT is not useful. I've also done a little fudge for NULL values, which you may or may not be able to use, depending on whether you can think of a value that will never occur in the column. If not you will have to expand the check to see if the value is different to cope with NULLs. Remember that NULL <> anything(including null) is false as is NULL = anything.


CREATE TRIGGER TR1 ON [dbo].[t1]
FOR INSERT, UPDATE
AS
IF UPDATE(col1)
BEGIN
DECLARE @rc int

SELECT @rc = COUNT(*) FROM INSERTED
IF @rc = 0
RETURN

UPDATE OSS_VOYAGE
SET col2 = i.col1
FROM t1, INSERTED i
WHERE t1.ok = i.pk
AND ISNULL(t1.col2, 'xyz') != ISNULL(i.col1, 'xyz')
END


and

CREATE TRIGGER TR2 ON [dbo].[t1]
FOR INSERT, UPDATE
AS
IF UPDATE(col2)
BEGIN
DECLARE @rc int

SELECT @rc = COUNT(*) FROM INSERTED
IF @rc = 0
RETURN

UPDATE t1
SET col1 = i.col2
FROM t1, INSERTED i
WHERE t1.pk = i.pk
AND ISNULL(v.col1, 'xyz') != ISNULL(i.col2, 'xyz')
END

We use the Redgate SQL Schema compare tool at work to help promote db changes from dev through test/oat/live. As we share the database we another team we need to communicate the db changes to them. Redgate is a good tool for propogating the changes and can also generate a report in HTML. This is good, but it a bit lengthy.

Fortunately they have used CSS to style the report making it easy to hide all the parts of the script that are equal. It has an inline css style sheet and adding

display: none;

to the .srcequal class removes the sections which have not changed and make the report quite concise (Thanks Paul).