netstat -a

Now, how does commiting affect this read consistency?
While updating, undo information is saved. After a commit, this undo information is no longer protected.
This doesn’t have to be a problem, becaust Oracle can still access the information in the undo segment to create a read consistent view of the data.
But, the moment a another transaction reuses this undo space, the undo is overwritten by other undo and there is no longer read consistency: you’ll get the ‘ORA-01555: snapshot too old’ error.

Be aware that it doesn’t need to be another user to reuse undo: it might be yourself updating the next set.
Large and many undo segments will make it less likely to hit that error, but are no garantee.
You can have lots of undo space, but still run into this error, because after a commit a rollback segment will not grow but reuse the freed up space.
If your next transaction happens to use the same rollback segment, you’re hit the ORA-01555

To do it in one large update, how big should your rollback segment be?
That is hard to predict how much you need, I would suggest to just go for it.
If it fails, no damage is done. You can enlarge your undo tablespace and/or rollback segment and retry it.
If it doesn’t, fail, you’re home free.

I tried this update in my own test environment:
update dps_user set password = lower(password);
It took about 5 minutes, updated 3.7 million records and used 560 MB (70K undo blocks * 8K (db_block_size)).

I’m happy enough with that.

Cheers,

“CATUI.CATUserControls.ucMetadataProfiles”  full identifier of the usercontrol

Type theType = Assembly.GetExecutingAssembly().GetType(“CATUI.CATUserControls.ucMetadataProfiles”);
if (theType != null)
{
Object newObj = Activator.CreateInstance(theType);
splitContainerMain.Panel2.Controls.Add((UserControl)newObj);
}

Do not miss your data: the pitfalls of DATE and TIME in T-SQL.

• By Alfred Yomtov

One of the frequently occurring querying problems in T-SQL is date time handling.

While SQL Server 2005 is still not ANSI-92 standard compliant
in supporting the standard set of DATE/TIME data types (that is DATE; TIME; DATE + TIME),
querying data with the DATETIME (millisecond accuracy) and
SMALLDATETIME (minute accuracy) outcome in rather erroneous results.

Consider the following script that will create Tmp_Orders table with default order date:

CREATE TABLE dbo.Tmp_Orders
(
OrderID int NOT NULL IDENTITY (1, 1),
OrderDate smalldatetime NOT NULL CONSTRAINT
DFTmpOrders_OrderDate DEFAULT (getdate()),
ProductID int NOT NULL
);

Therefore, on every occasion the order is added,
current date and time are inserted in OrderDate field.

DECLARE @COUNTER AS TINYINT
SET @COUNTER = 0
WHILE (@COUNTER < 5)
BEGIN
INSERT INTO Tmp_Orders (OrderDate, ProductID)
VALUES (DATEADD(dd, FLOOR(RAND()*10), GETDATE()), FLOOR(RAND()*10 +1))

SET @COUNTER = @COUNTER + 1
END;

DATEADD(dd, -FLOOR(RAND()*10), GETDATE()) will give us some random date with
FLOOR(RAND()*10 +1) will give us random Product IDs

Let us select that is just entered:
SELECT OrderID, OrderDate,ProductID
FROM Tmp_Orders
ORDER BY OrderDate;

• -your resulted data will be different
• But assume those are added orders.

Now let us set some criteria on Order Dates:

SELECT OrderID, OrderDate,ProductID
FROM Tmp_Orders
WHERE (OrderDate BETWEEN ’16-Jun-2008′ AND ’25-Jun-2008′)
ORDER BY OrderDate;

So, where is the order number 3, dated 2008-06-25?
It still belongs to 25th of June 2008, but it is not in our result list!

Let us recall that internally DATETIME stored as 8-byte FLOAT number
with decimal part representing number of dates since 01-Jan-1900 and fraction as a time.

So then querying our data with only DATE format (like BETWEEN ’16-Jun-2008′ AND ’25-Jun-2008′) the time part is just sliced out.

To get the proper outcome, just convert our Order Date to float like that

SELECT OrderID, OrderDate,ProductID
FROM Tmp_Orders
WHERE (CAST(FLOOR(CAST( OrderDate AS FLOAT )) AS DATETIME) BETWEEN ’16-Jun-2008′ AND ’25-Jun-2008′)
ORDER BY OrderDate;

And the correct results will follow:

Remember, DATETIME stored internally as a number, and converting it to something other than that is a slip-up
and will leads only to inability of using any index on the DATETIME column.

Original Post here

In dealing with NULL values in SQL Server, the developer is often faced with the requirement to evaluate the content of a field, and when said field in null, return another value (or field). There are two functions in SQL Server which support this requirement: ISNULL() and COALESCE.()
ISNULL
COALESCE
ISNULL vs. COALESCE
Performance Considerations
Further Reading

ISNULL()

ISNULL is a TSQL Function which is built into SQL Server. It is NOT a function defined by ANSI-92 – rather it is a feature which Microsoft has elected to include in TSQL in addition to the ANSI SQL standard.
ISNULL() accepts two parameters. The first is evaluated, and if the value is null, the second value is returned (regardless of whether or not it is null). The following queries will return the second parameter in both cases:

SELECT ISNULL(NULL, 1)
--Returns 1
SELECT ISNULL(NULL, NULL)
--Returns NULL

COALESCE()

COALESCE() is a TSQL function which, like ISNULL, is built into SQL Server. Unlike ISNULL, COALESCE is also a part of the ANSI-92 SQL Standard. Coalesce returns the first non-null expression in a list of expressions. The list can contain two or more items, and each item can be of a different data type. The following are valid examples of COALESCE:

SELECT COALESCE(NULL, 1)
--Returns 1

SELECT COALESCE(NULL, 3, NULL, 1)
--Returns 3

ISNULL vs. COALESCE

Whenever multiple methods exist for addressing a single problem, the inevitable question is: which method is better? There are a few differences between the two functions which make COALESCE come out on top more often than not:

- COALESCE is ANSI-92 compliant. In the event that you need to port your code to another RDBMS, COALESCE will not require rework.

- COALESCE accepts greater than two expressions, whereas ISNULL accepts only two. In order to compare three expressions with ISNULL, you would have to nest expressions:

SELECT ISNULL(ISNULL(Col1, Col2), Col3)

- ISNULL constrains the result of a comparison of parameterrs to the datatype of the first value. For example, the following query will produce some often undesirable results using ISNULL, however it will behave as expected with COALESCE:

DECLARE @Field1 char(3), @Field2 char(50)
SET @Field2 = 'Some Long String'

SELECT ISNULL(@Field1, @Field2)
--Returns 'Som'
SELECT COALESCE(@Field1, @Field2)
--Returns 'Some Long String'

Note: In other situations, COALESCE will produce unexpected results. COALESCE by nature promotes it’s arguments to the highest datatype among compatable arguments (arguments which are not explicitly case, and which aren’t compatable, will of course throw an error). When using COALESCE on an integer and a datetime, in that order, COALESCE will cast the integer as a datetime. For example:

SELECT COALESCE(5, GETDATE())

Will not return 5, it will return 1900-01-06 00:00:00.000 (5 as a datetime).

Performance

For most purposes, ISNULL and COALESCE perform in an almost identical fashion. It is generally accepted that ISNULL is slightly quicker than COALESCE, but not sufficiently to outweigh it’s inherent limitations. As with any performance related issue, if performance is a significant concern, write it both ways, and test!