Thursday, November 11, 2010

Often you need to know if a sql tables data has changed. The change can be a new row, deleted row or updated row. To determine if a row has changed you can use the following command that will return a checksum identifying a change to the table.
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM <table name> WITH (NOLOCK);
You can also use a where clause. For example get the checksum on just part of the table.

CHECKSUM

Returns the checksum value computed over a row of a table, or over a list of expressions. CHECKSUM is intended for use in building hash indices.

Syntax

CHECKSUM ( * | expression [ ,...n ] )

Arguments

*
Specifies that computation is over all the columns of the table. CHECKSUM returns an error if any column is of noncomparable data type. Noncomparable data types are text, ntext, image, and cursor, as well as sql_variant with any of the above types as its base type.expression
Is an expression of any type except a noncomparable data type.

Return Types

int

Remarks

CHECKSUM computes a hash value, called the checksum, over its list of arguments. The hash value is intended for use in building hash indices. If the arguments to CHECKSUM are columns, and an index is built over the computed CHECKSUM value, the result is a hash index, which can be used for equality searches over the columns.
CHECKSUM satisfies the properties of a hash function: CHECKSUM applied over any two lists of expressions returns the same value if the corresponding elements of the two lists have the same type and are equal when compared using the equals (=) operator. For the purpose of this definition, NULL values of a given type are considered to compare as equal. If one of the values in the expression list changes, the checksum of the list also usually changes. However, there is a small chance that the checksum will not change.
BINARY_CHECKSUM and CHECKSUM are similar functions: they can be used to compute a checksum value on a list of expressions, and the order of expressions affects the resultant value. The order of columns used in the case of CHECKSUM(*) is the order of columns specified in the table or view definition, including computed columns.
CHECKSUM and BINARY_CHECKSUM return different values for the string data types, where locale can cause strings with different representation to compare equal. The string data types are char, varchar, nchar, nvarchar, or sql_variant (if its base type is a string data type). For example, the BINARY_CHECKSUM values for the strings "McCavity" and "Mccavity" are different. In contrast, in a case-insensitive server, CHECKSUM returns the same checksum values for those strings. CHECKSUM values should not be compared against BINARY_CHECKSUM values.

Examples

Using CHECKSUM to build hash indices

The CHECKSUM function may be used to build hash indices. The hash index is built by adding a computed checksum column to the table being indexed, then building an index on the checksum column.

The checksum index can be used as a hash index, particularly to improve indexing speed when the column to be indexed is a long character column. The checksum index can be used for equality searches.

/*Use the index in a SELECT query. Add a second search
condition to catch stray cases where checksums match,
but the values are not identical.*/
SELECT *
FROM Products
WHERE checksum(N'Vegie-spread') = cs_Pname
AND ProductName = N'Vegie-spread'

Creating the index on the computed column materializes the checksum column, and any changes to the ProductName value will be propagated to the checksum column. Alternatively, an index could be built directly on the column indexed. However, if the key values are long, a regular index is not likely to perform as well as a checksum index.

If you have a WP7 Samsung Focus from AT&T you can use the device as a tethered USB modem.
1. Open phone dial pad
2. dial “##634#”
3. Hit send, this will install a diagnostic app in your application directory for future access.
4. When the diagnostics interface comes up dial “*#7284#”5. You will see the “Micro USB Test” screen options for Zune Sync, Modem tethered call and modem USD Diag. Select modem tethered. 6. Restart phone7. Connect your phone to your computer via the USB8. The drivers will be installed via the device USB.9. Locate the modem on your computer and change the settings to prompt for user name and these values;number *99***1#
user name WAP@CINGULARGPRS.COM
password CINGULAR1

The SQL server team at MS just released the first CPT of the next version of SQL Server code named Denali.
A few of the areas that the team focused on were;
· Enhanced mission-critical platform: An enhanced highly available and scalable platform through the new SQL Server AlwaysOn for greater flexibility in achieving increased availability and data protection and new Column-Based Query Accelerator for huge performance gains in data warehousing.
· Developer and IT Productivity: A new unified development experience for data developers called SQL Server Developer Tools code-named “Juneau”, new beyond relational enhancements including FileTable for file storage within the SQL Server database, easier of use delivered via SQL Server AlwaysOn, data integration tools and features such as security & supportability.
· Pervasive Insight: Expand the reach of BI to business users via Project code-name “Crescent”, a highly interactive, web-based data exploration and visualization tool built on the breakthrough analytical performance of the VertiPaq technology. Meanwhile, holistic data integration and management tools through enhanced Master Data Services and new Data Quality Services will help ensure organizations can deliver the right data to the right users at the right time.
· Select capabilities of the new integrated high availability solution, SQL Server code-named “Denali” AlwaysOn, including availability groups, single active secondary for offloading read-only workloads and multi-site clustering
· Flexible server roles to allow administrators to create custom roles for ease separation of duties
· Simplified development and standardized deployment, configuration and management for SQL Server Integration Services
· Column-Based Query Accelerator will help dramatically increase query performance ~10x and reduce performance tuning through interactive experiences with data for near instant response times and streamlined setup which removes the need to build summary aggregates.
You can download the CPT at; http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9&displaylang=en
Referencehttp://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspxhttp://blogs.technet.com/b/dataplatforminsider/archive/2010/11/10/microsoft-dives-deeper-into-sql-server-code-named-denali-the-next-version-of-sql-server.aspx

For users not having Visual Studio 2010 Pro or higher installed, an add-in for 3.5 with similar functionality for the free SQL Server 2008 Management Studio Express and also command line versions are available here:http://exportsqlce.codeplex.com

Tuesday, November 02, 2010

While I was registered and packed for PDC10, I was sidelined by being behind on a critical project and a wife with a broken foot the day I was suppose to fly to SEA. My disappointment in missing the event is only surpassed by the controversy over Silverlight/HTML5.

Did Microsoft do a poor job presenting there strategy or did we do a poor job listening? I think a little of both.

My team and I have invested a lot in the Silverlight stack and plan on continuing for some time. Below are some links to articles, blogs and such that I think address the confusion head on and reinforce my idea that Silverlight is here to stay. At the same time HTML is undergoing dramatic changes that we need to keep a close eye on.