MERGE Stats

The new SQL Server Merge statements is super cool. I needed a good way to get the stats back from an executed merge statement to tell me how many rows were affected by each action type, so I posted to the SQL Server Programming newsgroup and got this response back from Plamen Ratchev . Thanks Plamen:

DECLARE @counts TABLE(action VARCHAR(20));

MERGE INTO Foo1 AS A
USING Foo2 AS B
ON A.keycol = B.keycol
WHEN MATCHED
THEN UPDATE SET datacol = B.datacol
WHEN NOT MATCHED
THEN INSERT (keycol, datacol) VALUES(B.keycol, B.datacol)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT $action INTO @counts;

SELECT action, COUNT(*) AS cnt
FROM @counts
GROUP BY action;

/*

Results:

action cnt
-------------------- -----------
DELETE 2
INSERT 1
UPDATE 2

*/

Ntbackup SQL Server Backup Chain Problems

We started having problems with our differential backups failing because the chain to the full backup was invalid. It turns out that something enabled the SQL Server VSS Writer Service, that gives the ntbackup access to backup up SQL Server files. With this service enabled, when ntbackup runs, it logs a "backup event" with sql server and your differential backups no longer have a chain back to your last full backup. The problem is resolved by disabling the SQL Server VSS Writer Service.

Here is the documentation from SQL BOL:

SQL Writer Service: "SQL Writer Service
The SQL Writer Service provides added functionality for backup and restore of SQL Server through the Volume Shadow Copy Service framework.
The SQL Writer Service is installed automatically. It must be running when the Volume Shadow Copy Service (VSS) application requests a backup or restore. To configure the service, use the Microsoft Windows Services applet. The SQL Writer Service installs on all operating systems but is only required on server systems."

MySQL - First Day of Month and Week

DATE_FORMAT(subdate(MB.InsertDTS, INTERVAL weekday(MB.InsertDTS) DAY), '%m/%d/%Y') AS InsertDTSWeek,

DATE_FORMAT(DATE_ADD(LAST_DAY(DATE_SUB(MB.InsertDTS, interval 30 day)), interval 1 day), '%m/%d/%Y') AS InsertDTSMonth,

MySQL - Default values in created and updated columns

Found this comment on the MySQL ref site very helpful for adding both a created and updated timestamp columns with default values.

MySQL :: MySQL 5.0 Reference Manual :: 10.3.1.1 TIMESTAMP Properties: "'TIMESTAMP columns are NOT NULL by default, cannot contain NULL values, and assigning NULL assigns the current timestamp.'

What I do, is declare my created_at column as NOT NULL, then assign the column as NULL when I do the insert.. which populates it with the current timestamp.

Then on updates, the second timestamp column with the ON UPDATE...etc updates on it's own. However, the update code must not alter the created_at column at all (obviously).

created_at timestamp NOT NULL default '0000-00-00 00:00:00'
updated_at timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

INSERT INTO table(created_at,updated_at) VALUES (NULL,NULL);

populates BOTH columns with same timestamp for creation."

Remove Hidden Characters

Here is great way to remove "hidden" characters from data being imported in SQL Server. These characters are common when importing flat file data.

MetaDeveloper: Remove Carriage Return, Line Feed, Tab in TSQL: "-- remove carriage return, line feed, and tab from a field in tsql
REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')"

COUNT(*) vs COUNT(1)

I have always used * for queries like this where columns don't really matter because they arent being returned:

SELECT COUNT(*) FROM ...
or
WHERE EXISTS (SELECT * FROM ...

Recently I've seen other SQL developers replacing the * with a 1 in these scenarios. Since the columns aren't really being returned, it there really any advantage to using the 1 over the *?

After researching the issue, I've found that it is really just personal preference. I'll stick with COUNT(*).

snaps & snippets: What's wrong with COUNT(*)?: "One thing is certain, though, select count(1) does not perform any better than select count(*). And I've tried using other constants as well – no change. Is that another myth busted? Can anyone confirm this or prove otherwise?"