Navigation

Thursday, November 18, 2010

How to update a large SQL tables without killing the server!

Often I have the need to update a very large (>1 million rows) and a simple sql statement like this takes a long time;

update mytable set myfield = getdate() where myfield is null

This statement provides better performance (if you have triggers on the table disabling them if you can provides better performance also);

DISABLE trigger [dbo].[myTrigger] ON  [dbo].[myTable];
go
SET ROWCOUNT 10000

WHILE 1=1
BEGIN
UPDATE myTable
SET myField = GETDATE()
WHERE myField is null
IF @@ROWCOUNT = 0 BREAK

END
go
enable trigger [dbo].[myTrigger] ON  [dbo].[myTable];
go

By changing the “Set RowCount” value you can tune the statement as needed.

Wednesday, November 17, 2010

Must have Visual Studio Tools

Below are a list of must have Visual Studio Tools.  They are all free and from Microsoft!
Visual Studio 2010 Feature Packs http://msdn.microsoft.com/en-us/vstudio/ff655021.aspx
Feature Packs enable you to extend Visual Studio 2010 with capabilities that enhance and complement the existing tools.
Visual Studio 2010 Feature Pack 2 (MSDN Subscribers Only) Cumulative feature pack that extends testing, code visualization and modeling capabilities in Visual Studio 2010.
Testing features:
Use Microsoft Test Manager to capture and playback action recordings for Silverlight 4 applications.
Create coded UI tests for Silverlight 4 applications with Visual Studio 2010 Premium or Visual Studio 2010 Ultimate.
Edit coded UI tests using a graphical editor with Visual Studio 2010 Premium or Visual Studio 2010 Ultimate.
Use action recordings to fast forward through manual tests that need to support Mozilla Firefox 3.5 and 3.6.
Run coded UI tests for web applications using Mozilla Firefox 3.5 and 3.6 with Microsoft Visual Studio 2010 Premium or Visual Studio 2010 Ultimate.
Code visualization and modeling features (requires Visual Studio 2010 Ultimate):
Use the Generate Code command to generate skeleton code from elements on UML class diagrams. You can use the default transformations, or you can write custom transformations to translate UML types into code.
Create UML class diagrams from existing code.
Explore the organization and relationships in C, C++, and ASP.NET projects by generating dependency graphs.
Import elements from UML sequence diagrams, class diagrams, and use case diagrams as XMI 2.1 files that are exported from other modeling tools.
Create links and view links from work items to model elements.
Create layer diagrams from C or C++ code and validate dependencies.
Write code to modify layer diagrams and to validate code against layer diagrams.

Productivity Power Tools http://visualstudiogallery.msdn.microsoft.com/en-us/d0d33361-18e2-46c0-8ff2-4adea1e34fef
A set of extensions to Visual Studio Professional (and above) which improves developer productivity. In this third major release of the Power Tools we have made significant improvements to the Solution Navigator and the Tab Well to address our top customer requests.
PowerCommands for Visual Studio 2010 http://visualstudiogallery.msdn.microsoft.com/en-us/e5f41ad9-4edc-4912-bca3-91147db95b99
PowerCommands 10.0 is a set of useful extensions for the Visual Studio 2010 adding additional functionality to various areas of the IDE. Visit the VSX Developer Center at http://msdn.com/vsx for more information about extending Visual Studio.

Thursday, November 11, 2010

How to determine if a table has changed in SQL

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.
-- Create a checksum index.
SET ARITHABORT ON
USE Northwind
GO
ALTER TABLE Products 
ADD cs_Pname AS checksum(ProductName)
CREATE INDEX Pname_index ON Products (cs_Pname)


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.


See Also


BINARY_CHECKSUM


CHECKSUM_AGG








Reference; http://msdn.microsoft.com/en-us/library/aa258245%28SQL.80%29.aspx

WP7 Samsung Focus USB Tethering support

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 phone
7.  Connect your phone to your computer via the USB
8.  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

****WARNING; USE THIS HACK AT YOUR OWN RISK****



Getting Started with Windows Phone

If your looking at getting started developing applications on the Windows 7 Phone platform here are some links to get you started;
Getting Started with Windows Phone (MSDN)http://msdn.microsoft.com/en-us/wp7trainingcourse_wp7gettingstarted_unit.aspx
Silverlight for Windows Phone; http://msdn.microsoft.com/en-us/wp7trainingcourse_wp7silverlight_unit.aspx
Get Started with Silverlight for Windows Phone Video; http://www.silverlight.net/learn/videos/all/get-started-with-silverlight-for-windows-phone/
MCSD Magazine Getting Started with Windows Phone Development Tools; http://msdn.microsoft.com/en-us/magazine/gg232764.aspx
Windows Phone 7 Training Course; http://msdn.microsoft.com/en-us/wp7trainingcourse.aspx

Microsoft SQL Server Denali CPT1 Released!

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
Reference
http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx
http://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

Thursday, November 04, 2010

SQL Server Compact Toolbox

SQL Server Compact Toolbox add-in for Visual Studio 2010. Supports both version 3.5 and 4.0 of SQL Server Compact.
Adds several features to help your SQL Server Compact development efforts:
- Script tables, including data
- Script entire schema, optionally with data, both of SQL Server Compact and SQL Server 2005 or later databases
- Import to SQL Server Compact from a SQL Server 2005/2008 database or a CSV file
- Migrate from SQL Server Compact to SQL Server and SQL Azure
- Basic, free form query execution
- Parse SQL scripts
- Display graphical estimated execution plan
- Generate detailed DGML files for visualizing table columns and relationships (requires VS 2010 Premium or higher to view)
- Create and manage Merge Replication subscriptions

For support, full source code, a standalone version for 4.0 and feedback, go to:http://sqlcetoolbox.codeplex.com

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

New in version 1.6:
- Export to SQL Azure
- Launch the Toolbox directly from Server Explorer!
- Beta testing of Db Diff (scripting of database differences)
- other bug fixes, see http://sqlcetoolbox.codeplex.com/SourceControl/list/changesets

alt

alt

alt

alt

Tuesday, November 02, 2010

Silverlight is dead! Long live Silverlight!

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.

Bob Muglia on Microsoft's Silverlight Strategy, http://jpda.me/cw8p3r

Time Heuer’s Blog, http://jpda.me/9hljn0

Mary Joe Foley, http://jpda.me/aTIpCi

Steve Ballmer on PDC, http://jpda.me/aHy3qE

Scott Guthrie; The state of Silverlight, http://jpda.me/cW9dfx

Blog post on silverlight, http://jpda.me/apsNHd

Silverlight, HTML5 and your future blog post, http://jpda.me/cCzyHo