Systems Engineering and RDBMS

Archive for February 16th, 2007

Unique Index

Posted by decipherinfosys on February 16, 2007

This is another one of those small things that people run into when porting over their application from Oracle to SQL Server/DB2 LUW.  A unique index on a column in Oracle allows multiple NULL enteries.  That is not true though in the case of SQL Server and DB2 LUW.  In SQL Server and DB2 LUW, you can have only one single NULL value in the unique index and in the case of covered indexes (indexes on more than one column), you can have only one unique set and NULL is counted as a value for the purposes of a UNIQUE INDEX.

So, for an example…in SQL Server and in DB2 LUW:
create table test (col3 int, col4 int);
create unique index test1 on test (col3, col4);

1 null -> unique
2 null -> unique
2 null -> duplicate –> will give an error
null null -> unique
null null -> duplicate –> will give an error
null 1 -> unique.

Posted in DB2 LUW, Oracle, SQL Server | 2 Comments »

Effectively Repairing Windows 2003 Server

Posted by decipherinfosys on February 16, 2007

Every now and then a Windows 2003 Server’s operating system will become corrupted, and repair procedures to bring the server back online must be initiated. These procedures vary from one organization to the next, but there is one step that all system administrators should add to their checklists if they have not done so already – the Windows repair feature. This feature is accessed by booting the damaged server from the Windows 2003 Server installation CD.

 Repair vs. Recovery

The Repair feature should not be confused with the Recovery Console, which is also accessed from the boot CD. The Recovery Console is a much less comprehensive repair tool, and requires that you  either use a repair (floppy) disk or manually copy files from a CD or floppy to the server’s %Windows% directory manually via a command line. Most of you have probably tried both with limited or no success. The repair disks must be constantly updated to account for changes to the server’s configuration, otherwise the recovery will fail. Manually copying system files is only effective if you know exactly which files are corrupt and need to be replaced, which is nearly impossible. In addition, if dozens or hundreds of files need to be replaced, manually copying them via a command line can become quite tedious.

The option to actually repair the operating system is a little deeper into the Windows installation process. Once the system has been booted with the Windows 2003 installation CD, you will see the option to repair the system via the Recovery Console first, as shown below:

windowsrepair1b.jpg

At this point you will want to select Enter to set up Windows now. After agreeing to the licensing disclaimer you will arrive at the following screen:

windowsrepair2b.jpg

Here you will select “R” to repair the Windows installation. What follows looks suspicously like a full reinstall, but have no fear – it is not. The repair simply overwrites the Windows system files, and will allow the system to recover at least 90% of the time. The repair will save all system and application settings, so you will not need to re-specify the server’s name. In addition,  you will not have to reinstall any of the applications running on the server. The author has personally had success with this repair procedure, and has been able to recover systems suffering from a number of ailments, including damage caused by viruses, TCP/IP stack problems, as well as system files corruption resulting from poorly fragmented disks.

Posted in Windows | 3 Comments »

UNION vs UNION ALL

Posted by decipherinfosys on February 16, 2007

This is another one of those small things that beginners usually make a mistake in.  UNION and UNION ALL are both used to club together results from different sets.  UNION eliminates the duplicates between the two sets that it is joining while UNION ALL preserves the duplicates.  Example:  Say a table “TableA” has 2000 records:
Select a, b, c from tableA

union

select a, b, c from tableA

This will return back 2000 records.  If you had used UNION ALL instead of UNION, it would have returned back 4000 records.

The other key things are:

1) the number of columns should be the same in all the sets (i.e. one set cannot have 3 columns while the other one has 4 –> you will get an error),
2) the data types should be the same for the columns in all the sets (for the same order of columns) else implicit data-type conversions can cause issues

Example (SQL Server syntax):

CREATE TABLE T1 (COL1 INT, COL2 VARCHAR(10), COL3 DATETIME)
GO
CREATE TABLE T2 (COL1 INT, COL2 SQL_VARIANT, COL3 NUMERIC(8,3))
GO
INSERT INTO T1 VALUES (1, ‘ABC’, GETDATE())
INSERT INTO T1 VALUES (2, ‘XYZ’, GETDATE() + 1)

INSERT INTO T2 VALUES (1, ‘ABC’, 123.12)
INSERT INTO T2 VALUES (2, GETDATE(), 12345.21)
GO

–see the values in T2
–Note the numeric values in the last column

SELECT COL1, COL2, COL3 FROM T2
go

COL1        COL2                     COL3
———– ————————- ———-
1           ABC                      123.120
2           2007-01-21 18:39:23.990  12345.210

/*
Now, since the data-types are incompatible between the same
column order columns in the 2 sets, implicit conversion forces
the numeric data types to change
*/
SELECT COL1, COL2, COL3 FROM T1
UNION ALL
SELECT COL1, COL2, COL3 FROM T2
GO

COL1        COL2                    COL3
———– ———————– ——————————————————
1           ABC                     2007-01-21 18:39:23.990
2           XYZ                     2007-01-22 18:39:23.990
1           ABC                     1900-05-04 02:52:48.000
2           2007-01-21 18:39:23.990 1933-10-20 05:02:23.997

In this case, one would then need to cast the data-types to the same data-type to prevent implicit conversions from ruining the output, example:

SELECT COL1, COL2, CONVERT(VARCHAR(23), COL3, 121) AS COL3 FROM T1
UNION ALL
SELECT COL1, COL2, CAST(COL3 AS VARCHAR(10)) FROM T2
GO

COL1        COL2                   COL3
———– ———-             ———————–
1           ABC                    2007-01-21 18:39:23.990
2           XYZ                    2007-01-22 18:39:23.990
1           ABC                    123.120
2           2007-01-21 18:39:23.990     12345.210
From a performance perspective, UNION ALL is better than UNION since UNION forces a big sort and de-duplication i.e. a removal of the duplicate values.  And many times, the usage of UNION is not even needed and is used by programmers because they do not understand the difference between these two operators.

Posted in DB2 LUW, Oracle, SQL Server | 4 Comments »