Thursday, October 11, 2007

What is ready to boost? - Windows Vista Ready Boost

While I rarely use my Windows Vista laptop at home, I happen to use it to convert my PowerPoint 2007 files to 2003 version and sometimes to copy slides from 2007 to 2003. As I was copying my slides from my USB thumb drive to the laptop, I noticed an unusual (for me, it is unusual) prompt aside from the typical do-you-want-to-open-this-in-Windows-Explorer type of question.
It gives users the option to use the USB thumb drive for ReadyBoost, a feature in Windows Vista which allows you to use a non-volatile flash memory, such as that on a USB flash drive, to improve performance without having to add additional physical memory. Now, this is cool especially when you don't have the "ca$h" to buy additional RAM or if your motherboard does not support additional physical RAM, like maybe limited to a maximum of 2GB. What I did notice is that there was some increase in performance using a 1GB USB thumb drive. The reason I didn't get to see this with my other USB thumb drives/SD cards is that it has to be at least 3.5MB/s before it can be used for ReadyBoost. But still, it's worth having a cheap USB thumb drive to be used as additional memory for your machine although performance is not as good as a real physical RAM

Wednesday, October 10, 2007

MERGE statement in SQL Server 2008

The MERGE statement in SQL Server 2008 is a new DML statement that combines multiple DML operations. It performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table. This statement enables you to merge two tables based on a set of criteria. In previous versions of SQL Server, you have to create separate statements if you need to insert, update, or delete data in one table based on certain conditions in another table or query. With MERGE, you can include the logic for these data modifications in one statement. You specify a source record set, which could be a table join or a view, and a target table, and the join condition between the two. You then specify the DML statement that you want to execute when the records between the two data are matched or are not matched. One typical use for this is bulk loading data warehouse tables or maybe executing DML statements during off-peak hours. To demonstrate how the MERGE statement works, here's a very simple example. I'll create two tables and populate them with values.

USE Northwind
GO
CREATE TABLE Table1 (ID int, [Value] varchar(20), Comment varchar(50))
CREATE TABLE Table2 (ID int, [Value] varchar(20), Comment varchar(50))
GO
INSERT INTO Table1 VALUES (1, 'A', 'Inserted'), (2, 'B', 'Inserted')
INSERT INTO Table2 VALUES (1, 'C', 'Inserted'), (3, 'D', 'Inserted')
GO


Here is where I'll be using the MERGE statement. What I'll do is I'll use Table1 as my source data and Table2 as my target table. I'll look at Table1 based on the ID column and, if a match is found, I'll update the Comment column with a value Match Found from Source. If the row from the Table1 does not match another row in Table2, I'll insert a new row and specify the Comment column with a value No Match Found on Target. If the row from Table2 does not match another row from Table1, I'll update the Comment column value to No Match Found from Source. The MERGE statement for this scenario looks like this

MERGE Table2 AS targetTable
USING (SELECT ID, [Value] FROM Table1) SourceTable
ON (targetTable.ID = SourceTable.ID)
WHEN MATCHED THEN UPDATE SET Comment = 'Match Found from Source'
WHEN TARGET NOT MATCHED THEN INSERT VALUES (ID, [Value], 'No Match Found on Target')
WHEN SOURCE NOT MATCHED THEN UPDATE SET Comment = 'No Match Found from Source';
GO


If you analyze the query, it will update the first record in Table2 with ID=1 and set the Comment field value to Match Found from Source as they both have the same values. Since the second record in Table1 has a an ID value of 2, it will update the Comment field value to No Match Found on Target on Table2 since the ID column has a value of 3. And since the Table2 has a record with no matching ID value from Table1, it will update Comment field value to No Match Found on Target.

Having explained how the concept of the MERGE statement works, let's look at a practical application. Using the Northwind database, I have created a script which will update the Products table based on the sales made for the day. It will subtract the total number of units for a specific product from the Order Details table from the UnitsInStock column of the Products table. You can run this query at the end of the day when there is not much queries running during the day. Normally, we would either create a trigger or a stored procedure which is wrapped in a transaction to solve this problem.

USE Northwind

MERGE Products AS P
USING (SELECT ProductID, SUM(Quantity) FROM [Order Details] OD
JOIN Orders O
ON OD.OrderID = O.OrderID
AND O.OrderDate = GETDATE()
GROUP BY ProductID) AS src (ProductID, OrderQty)
ON (P.ProductID = src.ProductID)
WHEN MATCHED AND P.UnitsInStock - src.OrderQty <> 0
THEN UPDATE SET P.UnitsInStock = P.UnitsInStock - src.OrderQty
WHEN MATCHED AND P.UnitsInStock - src.OrderQty = 0
THEN DELETE;


Check out my SQL Server 2008 videos at BlogCastRepository.com

So you get an Error 0x80244019 in your WSUS client

If you happen to check your WindowsUpdate.log file and found this error, you might want to check your WSUS' IIS settings especially if you have your URLScan.ini modified. If you install WSUS after you configured your URLScan.ini, it doesn't change the settings at all. You need to add the following setting in the URLScan.ini:

[Allow Extensions]
.exe

And remove ".exe" it from...
[Deny Extensions]
--
[Allow Verbs]
GET
HEAD
POST
OPTIONS

You can find out more about WSUS troubleshooting at this site. There may be more than this reason why your WSUS just wouldn't cooperate when you deploy your patches

Monday, October 8, 2007

Spot the difference - SQL Server 2008 TSQL enhancements

If you've had your hands on T-SQL for quite some time, it wouldn't be hard for you to identify the new enhancements available in SQL Server 2008. Now just for fun, try to identify what's unusual in this script, from a SQL Server 2000/2005 point-of-view:


DECLARE

@variable int = 1;
SET
@variable += 1
;

CREATE TABLE

table1 (col1 int);

INSERT
table1
VALUES (@variable),(@variable+1
);

If you try to execute this script in SQL Server 2005, you'll get an error similar to the one below

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@variable".
Now you start thinking, what could be wring with the script? The answer: T-SQL enhancements in SQL Server 2008. First, The DECLARE statement in the first line now has the capability to accommodate initialization of variables unlike before where it only does variable declaration and you need either a SET or SELECT statement to assign values to variables. This is something similar to what you do in .NET, particularly VB.NET (the language enhancement part, in this case) where you can declare and assign values to variables in a single line. You can still use the SET statement to assign values to variables. Next is the += operator (-= is also included). I've first encountered this in C++ (Java, C# and VB.NET also have these) where the expression is used to increment (or decrement) variable values. In this case, the value for the variable @variable is set to 2 after the second line because of the += operator. The third item is the existence of multiple row predicates in the VALUES clause. This also works for an UPDATE statement. This means you can now make your INSERT or UPDATE statements shorter with fewer codes. Not that you couldn't do these in previous versions but this simply means writing less code and getting more work done (although you might be in trouble as your boss might give you more work to do)
Check out my SQL Server 2008 videos at BlogCastRepository.com
Google