Systems Engineering and RDBMS

Archive for November, 2007

Multiple NULL values in a Unique index in SQL Server/DB2 LUW

Posted by decipherinfosys on November 30, 2007

Yesterday, when helping out a friend who was working on a project that required porting an application from Oracle v9.2.0.5 to SQL Server 2005, he ran into the same UNIQUE index issue as we had blogged before. Since that was a major requirement by the client, this project needed to support having multiple NULL values in the column and still have a UNIQUE constraint. That is allowed by Oracle but not in SQL Server and DB2 LUW.

There are two ways to make this work in SQL Server and DB2 LUW also –> one requires a work-around while the other one requires a trigger.

Consider this table:

CREATE TABLE TEST_UQ (COL1 INT IDENTITY(1,1) PRIMARY KEY, COL2 NVARCHAR(10) NULL)
GO

In this table, COL1 has been declared as the primary key but we want a UNIQUE constraint to be put on COL2 as well. Please note that COL2 is a nullable column and that SQL Server does not allow multiple NULL values in a UNIQUE index and treats them the same way. We can test it out prior to proceeding with the work-around:

Let’s create a unique index first:

CREATE UNIQUE INDEX TEST_UQ_IND_1 ON TEST_UQ (COL2)
GO

Now, let us try to insert these values:

insert into test_uq (col2) values (‘abc’);
insert into test_uq (col2) values (‘xyz’);
insert into test_uq (col2) values (Null);

All three will go in. After that, try to insert the NULL value again:

insert into test_uq (col2) values (Null);

and you will get the error:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.test_uq’ with unique index ‘TEST_UQ_IND_1′.
The statement has been terminated.

Solution 1:

The work-around is to have a computed column and define the unique constraint on it. Here is how you can do that:

1) First, let’s drop the existing unique index:

drop index test_uq.TEST_UQ_IND_1

2) Next, let’s add the computed column:

ALTER TABLE TEST_UQ ADD COL3 AS (CASE WHEN COL2 IS NULL THEN CAST(COL1 AS NVARCHAR(10)) ELSE COL2 END);

In this command, we are stating that whenever the value for COL2 is null, replace it with the primary key after casting it to the same data-type as that of COL2. By doing so, we will mae sure that COL3 is always NOT NULL and always have unique values. This approach will work well in this case as there should never be a clash of the values between COL1 and COL2. For example, what-if you needed to do this on a column that was also an interger data-type column? In that case, chances of clashes of the data can arise. If you suspect a clash, you can have additional logic like: (CASE WHEN COL2 IS NULL then -1 * COL1 ELSE COL2 END). That way, you can still maintain the logic and the uniqueness.

3) Now, create the unique index on this column:

CREATE UNIQUE INDEX TEST_UQ_IND_1 ON TEST_UQ (COL3)
GO

4) Next, let’s try to insert the NULL value again:

insert into test_uq (col2) values (Null);

This time it will go through. If we examine the contents of the table:

COL1        COL2       COL3
 ----------- ---------- ----------
 1           abc        abc
 2           xyz        xyz
 3           NULL       3
 5           NULL       5

As you can see, we have allowed multiple NULL values now for COL2 and still maintained the uniqueness. The data is not corrupted either since we maintain the multiple Null values in that column and the dummy scheme that we used only put the dummy values in COL3 which is not going to be used in any of the queries.  That is why we did not just insert the values 3, 5 directly in COL2.  We can next try to insert the value “abc” again and see if that preserves our uniqueness criteria:

insert into test_uq (col2) values (‘abc’);

This time, we will get an error:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.test_uq’ with unique index ‘TEST_UQ_IND_1′.
The statement has been terminated.

So, using this work-around, one can preserve the same behavior as Oracle. This might be useful to you as well in case you are working on a project that requires conversion from Oracle to SQL Server or Oracle to DB2 LUW.

Solution 2:

There is one more solution for this problem and it does not include a computed column. Senior developers must have already figured out the solution – Yes, usage of a trigger. The solution will basically mean that we do not create the unique index but just a regular index and we create a an insert and update action trigger which checks whether the inserted value exists or not and handle the uniqueness that way essentially doing a rollback when there is an attempt to insert a duplicate value. The overhead of the invocation of the trigger for every insert and update in this case will be minimal because the trigger code will be checking for only one single value of the indexed column. The advantage is that you do not have another additional computed column and your structure remains exactly the same as in the case of Oracle.  This is how the trigger definition would look like:

/*Table Definition – same as before*/
CREATE TABLE TEST_UQ (COL1 INT IDENTITY(1,1) PRIMARY KEY, COL2 NVARCHAR(10) NULL)
GO

/*Non Unique index*/
CREATE INDEX TEST_UQ_IND_1 ON TEST_UQ (COL2)
GO

Now, let us try to insert these values:

insert into test_uq (col2) values (‘abc’);
insert into test_uq (col2) values (‘xyz’);
insert into test_uq (col2) values (Null);

/*Trigger Definition*/
create trigger dbo.test_allow_unique
on test_uq
for insert, update
AS
set nocount on

IF EXISTS(select 1
from   inserted as i
inner join dbo.test_uq as tu
on i.col2 = tu.col2
group  by tu.col2
having count(tu.col2) > 1)
BEGIN
ROLLBACK
RAISERROR(‘Uniqueness Criteria for COL2 got violated.’, 16, 1)
END
GO

/*Insert another Null value – this will go through – this is a nullable column and the index is a non-unique index*/
insert into test_uq (col2) values (Null);

/*Insert a value that should result in a violation as per the trigger logic*/
insert into test_uq (col2) values (‘xyz’);

Msg 50000, Level 16, State 1, Procedure test_allow_unique, Line 14
Uniqueness Criteria for COL2 got violated.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

So, the trigger approach works as well and as pointed out before – the hit is fairly small since the look up happens on COL2 which will have unique values only in it.  Just make sure that the trigger is always in an enabled state.

Posted in DB2 LUW, Oracle, SQL Server | Leave a Comment »

Back to the Basics: Exception Handling in PL/SQL

Posted by decipherinfosys on November 29, 2007

In our previous blog posts, we had covered the scenarios of using multiple exceptions and using NO_DATA_FOUND exception when used with bulk collection. In this blog post, we will cover the basic exception handling scenarios.

Exceptions are constructs used in PL/SQL programming to handle error conditions. Oracle provides some pre-defined exceptions. These exceptions are very specific (to prevent duplicate record in the table) to more general (to handle any errors). Users can also define his or her own exceptions. When dealing with exceptions, we have to handle the specific exceptions first before handling the general exceptions. Let us start with example. We will first create the table.

CREATE TABLE TEST
(
TEST_ID NUMBER NOT NULL,
TEST_NAME VARCHAR(15) NOT NULL,
CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID)
)
/

Now we will try to insert two records with the same TEST_ID. Oracle provides pre-defined exception WHEN DUP_VAL_ON_INDEX to handle the error due to inserting duplicate record. Oracle also provides WHEN OTHERS exception to handle any error. Let us execute following PL/SQL block and observe the results.

SET SERVEROUTPUT ON;

BEGIN

INSERT INTO TEST VALUES(1,’Oracle’);
INSERT INTO TEST VALUES(1,’SQLServer’);

EXCEPTION

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error Occured..’);

WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM);

END;
/

Above block will give following error and will not get executed.

WHEN OTHERS THEN
*
ERROR at line 8:
ORA-06550: line 8, column 4:
PLS-00370: OTHERS handler must be last among the exception handlers of a block
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated

Since WHEN OTHERS is general exception, it should be last in the exception block if more than one exception are defined in the exception block. This makes sure that any specific exception is handled correctly and appropriate actions are taken once exception is caught. Now let us change the order and execute the PL/SQL block again.

SET SERVEROUTPUT ON;
BEGIN

INSERT INTO TEST VALUES(1,’Oracle’);
INSERT INTO TEST VALUES(1,’SQLServer’);

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM);

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error..’ || SQLERRM);

END;
/

This time, second insert statement will fail and control will go to exception block. We are raising exception so it will display following error (text returned by SQLERRM)

ERROR at line 1:
ORA-20001: ORA-00001: unique constraint (DECIPHER.PK_TEST) violated
ORA-06512: at line 9

Other important thing to note is to raise an error when exception is caught using WHEN OTHERS exception. When RAISE or RAISE_APPLICATION_ERROR is used, execution stops at that point and exception is thrown back to its caller program. If RAISE is not used, then it may hide the error without stopping the execution. Following is an example.

SET SERVEROUTPUT ON;

BEGIN

INSERT INTO TEST VALUES(1,’Oracle’);
INSERT INTO TEST VALUES(NULL,’SQLServer’);

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM);

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error..’ || SQLERRM);

END;
/

Error..ORA-01400: cannot insert NULL into (“DECIPHER”.”TEST”.”TEST_ID”)

PL/SQL procedure successfully completed.

Above example is just for illustration. Even though we have caught the exception, execution completed successfully. This holds true even if error is logged into any other table. If there is no mechanism in place to examine the errors immediately after execution, user never know that error really occurred which may be very scary especially when errors occurred due to space limitation or any other database errors. This is why using RAISE is important for WHEN OTHERS exception.

Posted in Oracle | Leave a Comment »

SQL Server 2008: INSERT over a DML statement

Posted by decipherinfosys on November 28, 2007

In this post, we are going to cover another T-SQL enhancement in SQL Server 2008. We will be using the same set of tables that we did in one of our previous blog post in which we had talked about the MERGE command in SQL Server 2008.

Using the same set of tables, here is how the data looks like:

CREATE TABLE PRODUCT_CATALOG (PRODUCT_NAME NVARCHAR(100) NOT NULL, QTY INT NOT NULL CHECK (QTY > 0));
CREATE TABLE PRODUCT_SALE (PRODUCT_NAME NVARCHAR(100) NOT NULL, SALE_QTY INT NOT NULL);

INSERT INTO PRODUCT_CATALOG (PRODUCT_NAME, QTY) VALUES (‘TOY1′, 100);
INSERT INTO PRODUCT_CATALOG (PRODUCT_NAME, QTY) VALUES (‘TOY2′, 50);
INSERT INTO PRODUCT_SALE (PRODUCT_NAME, SALE_QTY) VALUES (‘TOY1′, 100);
INSERT INTO PRODUCT_SALE (PRODUCT_NAME, SALE_QTY) VALUES (‘TOY2′, 10);
INSERT INTO PRODUCT_SALE (PRODUCT_NAME, SALE_QTY) VALUES (‘TOY3′, 500);
GO

If you read that post, you will see that we had used the MERGE command to do the DML (Delete, Insert, Update) logic on the PRODUCT_CATALOG table. This was the first example of the MERGE command that we had used in that post:

MERGE PRODUCT_CATALOG PC /* target table */
USING PRODUCT_SALE PS /* source table */
ON PC.PRODUCT_NAME = PS.PRODUCT_NAME
WHEN MATCHED AND (Qty – SALE_QTY = 0) THEN
DELETE
WHEN MATCHED THEN
/* update stock if you still hold some stock*/
UPDATE SET Qty = Qty – SALE_QTY
WHEN NOT MATCHED THEN
/*insert a row if the stock is newly acquired*/
INSERT VALUES (PRODUCT_NAME, SALE_QTY)
/* output details of INSERT/UPDATE/DELETE operations
– made on the target table*/
OUTPUT $action, inserted.PRODUCT_NAME, inserted.QTY, deleted.PRODUCT_NAME, deleted.QTY;

Now, suppose that you want the output of this to be put into a separate audit or processing table but only when the update is done or when a certain other condition is met. It is possible to be able to now to a select atop the MERGE command. We will first need to create a table for storing that data.

CREATE TABLE TEST_INSERT_OVER_DML (ACTION_CODE NVARCHAR(50), I_PRODUCT_NAME NVARCHAR(100), I_QTY INT, D_PRODUCT_NAME NVARCHAR(100), D_QTY INT)
GO

/*************************************************
And now the insert over DML statement
**************************************************/
INSERT INTO TEST_INSERT_OVER_DML (ACTION_CODE, I_PRODUCT_NAME, I_QTY, D_PRODUCT_NAME, D_QTY)
select Action_Code, Inserted_Product_Name, Inserted_QTY, Deleted_Product_Name, Deleted_Qty
from
(
MERGE PRODUCT_CATALOG PC /* target table */
USING PRODUCT_SALE PS /* source table */
ON PC.PRODUCT_NAME = PS.PRODUCT_NAME
WHEN MATCHED AND (Qty – SALE_QTY = 0) THEN
DELETE
WHEN MATCHED THEN
/* update QTY if you still hold some QTY for that PRODUCT*/
UPDATE SET Qty = Qty – SALE_QTY
WHEN NOT MATCHED THEN
/*insert a row if the PRODUCT is newly acquired*/
INSERT VALUES (PRODUCT_NAME, SALE_QTY)
/* output details of INSERT/UPDATE/DELETE operations
– made on the target table*/
OUTPUT $action, inserted.PRODUCT_NAME, inserted.QTY, deleted.PRODUCT_NAME, deleted.QTY
) Change (Action_Code, Inserted_Product_Name, Inserted_QTY, Deleted_Product_Name, Deleted_Qty);

And after executing it, let’s take a look at the data in the PRODUCT_CATALOG and the TEST_INSERT_OVER_DML tables:

/*************************

 PRODUCT_CATALOG

**************************/

PRODUCT_NAME                                                                                         QTY

---------------------------------------------------------------------------------------------------- -----------

TOY2                                                                                                 40

TOY3                                                                                                 500
/*************************

 TEST_INSERT_OVER_DML

**************************/

ACTION_CODE I_PRODUCT_NAME   I_QTY       D_PRODUCT_NAME  D_QTY

----------- --------------   ----------- --------------- -----------

INSERT      TOY3             500         NULL            NULL

DELETE      NULL             NULL        TOY1            100

UPDATE      TOY2             40          TOY2            50

As you can see from above, the action history log has been captured and the DELETE, INSERT and UPDATE actions took place on the PRODUCT_CATALOG table since the product: TOY1 got deleted, the QTY for TOY2 got decremented by 10 and TOY3 record got added.

This new feature will help a lot in data warehousing ETLM operations and also for preparing audit logs for DML operations though CDC (Change Data Capture) would be a better option for auditing.

Posted in SQL Server | Leave a Comment »

Removing un-wanted text from strings in Oracle

Posted by decipherinfosys on November 27, 2007

Performing string manipulation is very common regardless of the RDBMS that we use. There are lots of string functions available like substr, instr, lpad, rpad etc. to perform string manipulation. We have also covered some of them in our previous blog posts. Recently at the client site, we came across the requirement where we have to remove the ‘-‘ from the phone number string before writing the record to flat file.  There are different ways of removing unwanted characters from the string using:

•    REPLACE function
•    TRANSLATE function
•    REGEXP_REPLACE function (10g and above)

We can use REPLACE function to remove the unwanted character from the string. It takes three arguments.

•    String from which we want to remove or replace the character.
•    Search string.
•    Replace string.

SQL> SELECT REPLACE(’1-770-123-5478′,’-’,”) COL1 FROM DUAL;

OR

SQL> SELECT REPLACE(’1-770-123-5478′,’-’,NULL) COL1 FROM DUAL;

COL1
—————
17701235478

Another way to do this is to use TRANSLATE function in such a way so that we can get rid of unwanted characters. It takes three arguments.
•    String from which we want to remove or replace the character.
•    Character which we would like to replace or remove from the string in the first argument
•    New character with which we want to replace the old character (2nd argument).

Let us see it with example. We will first try to replace ‘-’ with ‘*’ in the string using translate.

SQL>  SELECT TRANSLATE(’1-770-123-4567′,’-’,’*’) COL1 FROM DUAL;

COL1
————–
1*770*123*4567

In above example, we replaced character ‘-’ with ‘*’ but we were talking about removing ‘-’ from the string. Let us execute following query.

SQL> SELECT TRANSLATE(’1-770-123-4567′,’@-’,’@’) COL1 FROM DUAL;

COL1
—————
17701234567

Above result indicates that we are able to remove unwanted character ‘-’ from the string.  In order to understand this, we have to understand how translate works. TRANSLATE function, takes each character from the ‘from string(2nd argument) and replace it with the corresponding character from the ‘to string(3rd argument). In our example, ‘@’ will be replaced with the ‘@’ and ‘-’ will be replaced with null value since we are not providing any corresponding character for it in the ‘to string’. This will become more clear with the following example.

SQL> SELECT TRANSLATE(‘DECIPHER’,’DE’,’12′) COL1 FROM DUAL;

COL1
—————
12CIPH2R

In above example, occurrence of ‘D’ is replaced with 1 and each occurrence of ‘E’ is replaced with 2.

Somebody might think that why can’t we, use NULL as a replacement character in the 3rd argument. Let us see what result we get when we execute following query.

SQL> SELECT TRANSLATE(’1-770-123-4567′,’-’,NULL) COL1 FROM DUAL;

COL1
———-

This is because when we pass NULL argument to TRANSLATE function, it returns null and hence we don’t get the desired result.

Other thing to note about TRANSLATE function is that it is case-sensitive. So if there is a case mismatch, translation will not take place. In following example, we are trying to replace ‘d’  with 1 but ‘d’ does not exist in the string and hence it will not be replaced. Only ‘E’ will be replaced with 2. Query and result is as shown below.

SQL> SELECT TRANSLATE(‘DECIPHER’,’dE’,’12′) COL1 FROM DUAL;

COL1
—————
D2CIPH2R

The way TRANSLATE function differs from REPLACE is that, TRANSLATE function provides single character one to one substitution while REPLACE allows you to replace one string with another.

Starting 10g, Oracle introduced regular expression function REGEXP_REPLACE. We can strip unwanted character from the string using this function as well.

SQL> SELECT REGEXP_REPLACE(’1-770-123-5478′,’-’,NULL) COL1 FROM DUAL;

COL1
———–
17701235478

Posted in Oracle | 3 Comments »

Oracle’s entry into virtualization

Posted by decipherinfosys on November 26, 2007

Oracle has also decided to join the virtualization arena. It is based on Xen open source hypervisor. If you recall, the same is true for SUN’s VM strategy which is also based on Xen, the open source hypervisor from XenSource.

You can read more on this at Oracle’s site: http://www.oracle.com/technologies/virtualization/index.html

And you can also read VMWare’s comments on Oracle’s approach to virtualization:

http://news.yahoo.com/s/pcworld/20071118/tc_pcworld/139759

Posted in Oracle | Leave a Comment »

Using a Server Alias to connect to SQL Server Instance(s)

Posted by decipherinfosys on November 26, 2007

When connecting to SQL Server from your PC or from your application/web servers, you can choose to create aliases for connectivity.  Before we start getting into the nitty-gritty details of aliases, let us see how you can set one up.  You can use SQL Server Connection Manager in SQL Server 2005 to set it up and in case the tools are not installed, then you can also use cliconfg.exe (note – there is no i in confg) which is located under C:\Windows\System32 folder.  If you use the connection manager, you will get a screen like this one:

alias-1.jpg

You can then create a new alias as shown in the next image:

alias-2.jpg

If you are using cliconfg.exe (the same is called in SQL Server 2000 when you use the SQL Server Client Network Utility), you will get a dialog box like the one shown below (showing the alias tab):

alias-3.jpg

As you can see from above, the same alias that we had created using the SQL Server Connection Manager is automatically visible using the cliconfg.exe as well – that is because both of them read this entry from the registry.  The place where these connection aliases can be found in the registry is:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

So, now that we have seen how to go about creating an alias and where it is stored, what exactly is the use of this feature?  There are a couple of benefits of using aliases:

1) An alias can be used to define a user-defined name for connecting to a server -even if the IP address changes later on, there are no changes to the connection string – you just need to update the alias and the application will keep on working as before.  You will not need to specify any instance name in the application connection string(s).  And using an IP address directly in the alias definition can also save you some time in doing the DNS look-up.

2) You can make connection to SQL Server using different protocols: TCP/IP, Named Pipes etc. and specify specific parameters like the TCP port or the pipe name etc.

3) Aliases are also good for performance reasons.  Since an alias has a pre-defined protocol, it can help you speed up the connection.  Think about what happens when a connection is made to SQL Server from a client application.  It has to try various protocols in the order that they are defined in your SQL Server Connection Manager (SQL 2005) or the SQL Server Client Network Utility (SQL 2000).  If you already know which protocol and port your server is listening on, by configuring an alias you can by-pass the discovery phase that SQL Browser service goes through.  In addition, if you know and have benchmarked your application using a specific protocol, you can standardize the alias technique across the board.

Posted in SQL Server | 7 Comments »

Calculating Running Totals

Posted by decipherinfosys on November 25, 2007

One frequent requirement that you will face would be a need to generate running totals. There are a couple of ways to do this in different RDBMS. Let us cover some of those options using an example (script is written with Oracle’s SQL syntax):

CREATE TABLE DEC_TEST_ACCOUNT_TXN
(
DEC_TEST_ACCOUNT_TXN_ID NUMBER(10) NOT NULL,
ACCOUNT_MASTER_ID NUMBER(10) NOT NULL,
TXN_DATE_TIME DATE NOT NULL,
TXN_AMOUNT NUMBER(9,2) NOT NULL,
TXN_TYPE_ID NUMBER(1) NOT NULL,
BRANCH_MASTER_ID NUMBER(10) NULL,
CONSTRAINT PK_DEC_TEST_TXN PRIMARY KEY (DEC_TEST_ACCOUNT_TXN_ID)
)
/

where:
a) ACCOUNT_MASTER_ID is the foreign key to the ACCOUNT_MASTER table,
b) TXN_DATE_TIME is the date and time the transaction took place,
c) TXN_AMOUNT is the total amount of the transaction,
d) TXN_TYPE_ID is the type of the transaction (1 for Deposit, 2 for Withdrawal and 3 for Transfer), and
e) BRANCH_MASTER_ID is the FK to the Branch in case the transaction was done in person.

And using a before trigger and a sequence, the DEC_TEST_ACCOUNT_TXN_ID is an automatically DB generated key and is also the PK of the table.

After populating it with data, here is how the data looks:

SELECT * FROM DEC_TEST_ACCOUNT_TXN;

DEC_TEST_ACCOUNT_TXN_ID ACCOUNT_MASTER_ID TXN_DATE_TIME           TXN_AMOUNT                              TXN_TYPE_ID BRANCH_MASTER_ID
----------------------- ----------------- ----------------------- --------------------------------------- ----------- ----------------
1                       1                 2007-11-23 07:41:43     10000.00                                1           NULL
2                       1                 2007-11-24 07:41:43     1000.00                                 1           NULL
3                       1                 2007-11-25 07:41:43     7000.00                                 2           NULL
4                       1                 2007-11-26 07:41:43     3000.00                                 1           NULL
5                       1                 2007-11-27 07:41:43     5000.00                                 2           NULL
6                       1                 2007-11-28 07:41:43     100000.00                               1           NULL

There are a couple of ways of generating the running totals:

1) Using a sub-select,
2) Using a self join,
3) Using a cursor,
4) Using analytic functions

We will pick the #1 and the #4 options as examples here:

/**************************************
Using a sub-select
***************************************/
SELECT
ACCOUNT_MASTER_ID,
TXN_DATE_TIME,
TXN_AMOUNT,
(SELECT SUM(CASE WHEN TXN_TYPE_ID IN (2,3) THEN -1 ELSE 1 END * TXN_AMOUNT)
FROM DEC_TEST_ACCOUNT_TXN A2
WHERE A2.TXN_DATE_TIME <= A1.TXN_DATE_TIME) RUNNING_BALANCE
FROM dbo.DEC_TEST_ACCOUNT_TXN A1
WHERE A1.ACCOUNT_MASTER_ID = 1
/

ACCOUNT_MASTER_ID TXN_DATE_TIME           TXN_AMOUNT                              RUNNING_BALANCE
----------------- ----------------------- --------------------------------------- ---------------------------------------
1                 2007-11-23 07:41:43     10000.00                                10000.00
1                 2007-11-24 07:41:43     1000.00                                 11000.00
1                 2007-11-25 07:41:43     7000.00                                 4000.00
1                 2007-11-26 07:41:43     3000.00                                 7000.00
1                 2007-11-27 07:41:43     5000.00                                 2000.00
1                 2007-11-28 07:41:43     100000.00                               102000.00

/**************************************
Using Analytic Function
***************************************/
SELECT
ACCOUNT_MASTER_ID,
TXN_DATE_TIME,
TXN_AMOUNT,
SUM(CASE WHEN TXN_TYPE_ID IN (2,3) THEN -1 ELSE 1 END * TXN_AMOUNT) OVER (ORDER BY TXN_DATE_TIME) RUNNING_BALANCE
FROM DEC_TEST_ACCOUNT_TXN A1
WHERE A1.ACCOUNT_MASTER_ID = 1
/

When the data set is large, using a sub-select or a cursor is typically not a good option. It also depends upon the
filter criteria that you have available in your WHERE clause – if you have an index seek operation and have narrowed down your results to a few records, then either of the options would be fine. Usage of the analytic functions is by far the best choice in terms of performance especially when dealing with large record sets.

Please note that as we had indicated in one of our previous blog post as well, in SQL Server 2005, the ORDER BY clause cannot be used within the OVER() clause when using aggregate functions. Hopefully, this restriction will be lifted in SQL Server 2008. We haven’t tested it yet on CTP5.

Posted in DB2 LUW, Oracle, SQL Server | Leave a Comment »

SQL Server 2005 Assessment Configuration Packs from MSFT

Posted by decipherinfosys on November 24, 2007

Last month, we were helping one of our clients to get the SOX compliance. How many of you knew that there is a SQL Server 2005 Assessment configuration pack for Sarbanes-Oxley Act (SOX) from Microsoft? And that is not the only assessment configuration pack that is available…there are others as well for HIPAA, EUDPD etc. Here are the links – I would encourage you to go over these:

  1. Microsoft SQL Server 2005 Assessment Configuration Pack for Sarbanes-Oxley Act (SOX)
  2. Microsoft SQL Server 2005 Assessment Configuration Pack for Health Insurance Portability and Accountability Act (HIPAA)
  3. Microsoft SQL Server 2005 Assessment Configuration Pack for European Union Data Protection Directive (EUDPD)
  4. Microsoft SQL Server 2005 Assessment Configuration Pack for Federal Information Security Management Act (FISMA)
  5. Microsoft SQL Server 2005 Assessment Configuration Pack for Gramm-Leach Bliley Act (GLBA)

Posted in SQL Server | Leave a Comment »

Auto-Shrink Issues

Posted by decipherinfosys on November 23, 2007

This is one of the common problems that we have seen in many of our client engagements. The database files are not sized appropriately and the client relies on the auto-grow feature – this can cause some issues and we had discussed those here.

In almost all of those implementations, we had also seen that the client had also enabled the “Auto-Shrink” feature in order to get a handle on the file-sizes. And this auto-grow and the auto-shrink leads to multitude of issues. Besides the ones mentioned in the link above, you can also run into issues in the middle of the production hours when either auto-grow or auto-shrink kicks in. In this post, we will cover another reason why auto-shrink should not be used for any of your production databases. It causes fragmentation. Let’s see that using an example:

We will create a brand new database for this work and create a dummy table in it and an actual table in it and populate them with a bunch of data.  The reason for creating the dummy table is so that we can then drop it and thus create up some free space so that when we manually run the shrinking of the database, we can show how the fragmentation takes place.  Both the dummy table and actual table are identical in nature.

USE MASTER;
GO

CREATE DATABASE DEC_TEST_SHRINK;
GO

ALTER DATABASE DEC_TEST_SHRINK SET AUTO_SHRINK OFF
GO

/********************************
Create the database for demo
*********************************/
USE DEC_TEST_SHRINK;
GO

/********************************
Hide the messages
*********************************/
SET NOCOUNT ON;
GO

/**************************************************************************************************
Let us now create a table and populate it with records – we will fill it so
that we can cross the threshold for the shrink after dropping it and create some free space ***************************************************************************************************/

CREATE TABLE TEST_TBL_DROP (COL1 INT IDENTITY NOT NULL, COL2 NVARCHAR(4000) NOT NULL)
GO

/*Now, run the script to populate the data for 10000 records*/

DECLARE @I INT;
SELECT @I = 1;
WHILE (@I < 10000)
BEGIN
INSERT INTO TEST_TBL_DROP (COL2) VALUES (REPLICATE (‘X’, 1000));
SELECT @I = @I + 1;
END;
GO

/**************************************************************************************************
Create and populate the test table which is the actual table we will check the fragmentation on
***************************************************************************************************/
CREATE TABLE TEST_TBL (COL1 INT IDENTITY NOT NULL, COL2 NVARCHAR(4000) NOT NULL)
GO
/*Create the clustered index*/
CREATE UNIQUE CLUSTERED INDEX TEST_TBL_IND_1 ON TEST_TBL (COL1)
GO

/*Now, run the same script to populate it with the data for 10000 records*/

DECLARE @I INT;
SELECT @I = 1;
WHILE (@I < 10000)
BEGIN
INSERT INTO TEST_TBL (COL2) VALUES (REPLICATE (‘X’, 1000));
SELECT @I = @I + 1;
END;
GO

/* Now, let us check the fragmentation level BEFORE shrinking the database */
SELECT
avg_fragmentation_in_percent,
fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID (‘DEC_TEST_SHRINK’), OBJECT_ID (‘TEST_TBL’), 1, NULL, ‘LIMITED’);
GO

avg_fragmentation_in_percent fragment_count

—————————- ——————–

0.36                                            10

As expected, the count is pretty low right now on the TEST_TBL.

/*Now, drop the dummy table to create up the free space*/
DROP TABLE TEST_TBL_DROP
GO

/* Now, let’s just use the SHRINKDATABASE command to shrink the database*/
DBCC SHRINKDATABASE (DEC_TEST_SHRINK);
GO

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
—— ———– ———– ———– ———– ————–
10 1 2656 144 2648 2648
10 2 63 63 56 56

/* Now, let us check the fragmentation level again AFTER shrinking the database */
SELECT
avg_fragmentation_in_percent,
fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID (‘DEC_TEST_SHRINK’), OBJECT_ID (‘TEST_TBL’), 1, NULL, ‘LIMITED’);
GO

avg_fragmentation_in_percent fragment_count

—————————- ——————–

99.96                                       2500

As you can see from above, after the shrinking of the database, the total fragmentation percentage went up. The shrink operation starts at the very end of the data file and it moves one page at a time and moves it to the free space that is below the marked shrink threshold. This means that as part of the shrinking process, it reverses the actual physical order of the pages which make up your leaf level of the index. That is what causes it to fragment.

So, in summary, here are the problems that auto-shrink can cause:

1) It could kick in at peak production times thus causing IO and CPU contention issues.
2) When it kicks in, it can easily flush out your pages from the buffer pool and thus cause performance issues – this can also result into IO timeouts.
3) When used in combination with auto-grow, it works like a see-saw…any operational database requires free-space and this if you run shrink to free up the space and then it has to grow again and then you shrink it again via auto-shrink…and so on and so forth…
4) It causes fragmentation which you would need to fix to avoid performance issues.

So, our recommendation is to never use this option for your production environments.

Posted in SQL Server | Leave a Comment »

Some more Virtual Server Resources

Posted by decipherinfosys on November 22, 2007

Here are some good virtual server resources from MSFT:

Virtual Server Operations Guide:

http://technet2.microsoft.com/windowsserver/en/library/ce2499f6-f592-4eec-946f-56ff352e3e291033.mspx?mfr=true

Virtual Server Technical Reference:

http://technet2.microsoft.com/windowsserver/en/library/c3663e92-0992-4f6f-9870-513040f5f2be1033.mspx?mfr=true

Virtual Server Community:

http://www.microsoft.com/windowsserversystem/virtualserver/community.aspx

Posted in Virtual Server | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 74 other followers