Compare tables

I have no doubt that you've seen many ways/or third products to compare tables. I'd like to show a couple of examples that the first one I learned from Itzik Ben-Gan and the second one I used BINARY_CHECKSUM function.
1)
IF(SELECT CHECKSUM_AGG(checksum(*)) FROM t1)
<> (select CHECKSUM_AGG(checksum(*)) FROM t2)
Print'different'
ELSE
Print'probably the same'

/*
CHECKSUM_AGG
Returns the checksum of the values in a group. Null values are ignored.
*/

2)
SELECT a.Col, a.CheckSum
From (Select colid as "Col", BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.TableA ) a
Inner Join (
Select colid as "Col", BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.TableB) b
On a.Col= b.Col
Where a.CheckSum <> b.CheckSum

Actually , the second example may or may not return you inaccurate value, so please test it before using.

UDF vs CHECK Constraint

As you probably know there are more accurate/efficient ways to create a CHECK constrain without using UDF. However, there under some circumstances we have to.


CREATE TABLE Test (col VARCHAR(20))
GO

This UDF returns 0 when string is an empty or NULL

CREATE FUNCTION dbo.fn_check_null_or_blank
(
@StringToCheck VARCHAR(250)
)
RETURNS INT
AS
Begin
RETURN 1-SIGN(LEN(COALESCE(@StringToCheck,'')))
End

ALTER TABLE Test
ADD CONSTRAINT df_col CHECK (dbo.fn_check_null_or_blank(col)=0)

Usage

INSERT INTO Test VALUES (NULL)--Failed
INSERT INTO Test VALUES ('Hello World')--Works
INSERT INTO Test VALUES (' ')--Failed


DROP TABLE Test
DROP FUNCTION dbo.fn_check_null_or_blank

In the above example I wanted just to show you how you can define a CHECK constraint by using UDF.

Compressing .BAK file

There are many third products that do backup database and compressing file for you. I'd like to share some ideas about how to compress a huge .BAK file if you don't have a budget to buy third products.

Let me see we do a full backup of the database that creates a .BAK file plus a date when backup have been taken.

DECLARE FileName VARCHAR(100),@Date VARCHAR(10)
SET @Date =CONVERT(VARCHAR(10),GETDATE(),112)
SELECT @FileName = 'C:\BackupSQL\MyDataBase' + @Date+'.bak'

BACKUP DATABASE EXM_IscarData
TO DISK = @FileName

I forgot to mention that you need WINRAR program to be installed on the server.
Now that we have .BAK file ,create two batch files (one for compression, second one for restore) that contain
---To compress
WINRAR a C:\BackupSQL\MyDataBase%1 C:\BackupSQL\MyDataBase%1.BAK
DEL "C:\BackupSQL\MyDataBase%1.BAK " ---Delete .BAK file

---to restore from .RAR file
WINRAR x C:\BackupSQL\MyDataBase%1 c:\

Put those files in C:\Windows\system32 directory.
The last thing is to call this command at Job's step
Decide what you are going to do restore or backup operations.

DECLARE @Str VARCHAR(100)
SET @Str = 'BatchFileName.bat ' + @Date
EXEC master.dbo.XP_CMDSHELL @Str, NO_OUTPUT

I'm sure that you need to put in a logic to restore or backup database.So finally you will have compresed file with .RAR extention which saves disk's space.

CASE expression techniques

I'm sure that all of us know and do CASE expression in their projects and reports. I'd like to share a couple of methodes that I used rarely , but it looks useful

Using Case in JOIN query
CASE expression checks title's type column and assign an appropriate column to be referenced

SELECT a.au_lname, a.au_fname, a.address,
t.title, t.type
FROM authors a INNER JOIN
titleauthor ta ON ta.au_id = a.au_id INNER JOIN
titles t ON t.title_id = ta.title_id
INNER JOIN publishers p on t.pub_id =
CASE WHEN t.type = 'Business' THEN p.pub_id ELSE null END
INNER JOIN stores s on s.stor_id =
CASE WHEN t.type = 'Popular_comp' THEN t.title_id ELSE null END


---Or

SELECT FROM Territory AS t JOIN Customers AS c
ON
CASE WHEN t.countrycode = 'US' OR t.countrycode = 'CA' then t.Areacode ELSE t.countrycode END=
CASE WHEN c.countrycode = 'US' OR c.countrycode = 'CA' then c.Areacode ELSE c.countrycode END

The following CASE expression we use more 'frequently'
This CASE expression returns 1 or 0 (True ,False)
SELECT
columname,
CASE
WHEN EXISTS (
SELECT * FROM Table1 t1
WHERE t1.col1= 1 AND t1.col2= t2.col2)
THEN 1
ELSE 0
END AS Alias
FROM Table2 t2
ORDER BY columname

Date tricks and tips

At this time I'd like to share some techniques to deal with dates. Many of us have been dealing with dates , and for example this question I have seen in the newsgroup and was asked by many developers "How do I get rid of time?"

Well, first thing is I am going to use CONVERT system function to do that

1)SELECT CAST(CONVERT(VARCHAR(20),GETDATE(),112)AS DATETIME)
2)SELECT CAST(FLOOR(CAST(GETDATE() AS REAL)) AS DATETIME)

It's OK, we got it. In the above statement we need to convert the date twice in the first one and more than twice in the second one to get the result.


The following technique I learned from Steve Kass

SELECT DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)

Pretty simple and work faster, believe me.

Also,by using this technique , you can a last /first day of current/next... month

SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0)-1

Also, as I have seen we have been rarely using these functions, but they do exist.It also works on SQL Server 2005 (sp2)

SELECT {fn extract(minute FROM getdate())}
SELECT {fn dayname( GetDate()) }
SELECT {fn CURRENT_DATE()}
SELECT {fn CURRENT_time()}

Week of Month

I have been experiencing many time with such requests like 'Give me number of week fo given date' , do you?
Actually we use here a simple logic.
Week number for given date minus a formated given date to the first date of the month (20070201) plus 1

CREATE FUNCTION dbo.WeekOfMonth (@now_day DATETIME)
RETURNS INT
AS
BEGIN
RETURN DATEPART(week, @now_day) - DATEPART(week, CONVERT(CHAR(6), @now_day, 112)+'01') + 1
END
GO
--Usage
SET DATEFIRST 1
SELECT dbo.WeekOfMonth('20070201') AS [1st]
SELECT dbo.WeekOfMonth('20070209') AS [2nd]

Dealing with duplicates

A this time I'd like to show some techniques to deal with duplicates
CREATE TABLE TableWithDups
(
row_id int identity(1,1),
col1 int,
col2 int
)
INSERT INTO TableWithDups (col1 ,col2 ) VALUES (1,10)
INSERT INTO TableWithDups (col1 ,col2 ) VALUES (1,10)
INSERT INTO TableWithDups (col1 ,col2 ) VALUES (4,2)
INSERT INTO TableWithDups (col1 ,col2 ) VALUES (3,3)
INSERT INTO TableWithDups (col1 ,col2 ) VALUES (3,3)
INSERT INTO TableWithDups (col1 ,col2 ) VALUES (9,20)

PRINT 'Duplicates in Table'
SELECT * FROM TableWithDups
WHERE EXISTS
(SELECT * FROM TableWithDups TWD
WHERE TWD.row_id <> TableWithDups.row_id
AND TWD.col1 = TableWithDups.col1
AND TWD.col2 = TableWithDups.col2)

PRINT 'Duplicates to be Deleted'
SELECT * FROM TableWithDups
WHERE EXISTS
(SELECT * FROM TableWithDups TWD
WHERE TWD.row_id < TableWithDups.row_id
AND TWD.col1 = TableWithDups.col1
AND TWD.col2 = TableWithDups.col2 )


PRINT 'Executig Deletion of Duplicates '
DELETE FROM TableWithDups
WHERE EXISTS
(SELECT * FROM TableWithDups TWD
WHERE TWD.row_id < TableWithDups.row_id
AND TWD.col1 = TableWithDups.col1
AND TWD.col2 = TableWithDups.col2 )

SELECT * FROM TableWithDups

If you are using SQL Server 2005 take a look at the below script
WITH Duplicates
AS
( SELECT *, ROW_NUMBER()OVER(PARTITION BY col1 , col2 ORDER BY col1 , col2 ) AS cnt FROM TableWithDups)
DELETE FROM Duplicates WHERE cnt> 1;

Recently my good friend Doron Farber wrote great article about how efficiently remove duplicates on huge tables based on real world example.
http://www.dfarber.com/computer-consulting-blog/2011/12/26/remove-duplicate-records-in-sql.aspx