Analyzing SQL Server Compact queries using Visual Studio 2010 Premium/Ultimate

f you are the happy owner of Visual Studio 2010 Premium or Ultimate, there is a hidden tool that allows you to run and analyze queries against SQL Server Compact 3.5 and 4.0 databases. (Support for 4.0 requires Visual Studio 2010 SP1 + the SQL Server Compact Tools update). This blog post will walk through how to access and use this “hidden” tool.

NOTE: If you only have Visual Studio Professional, you can use my SQL Server Compact Toolbox in combination with the free SQL Server 2008 R2 Management Studio Express to perform similar query analysis.

To access the tool, go to the Data menu, and select Transact-SQL Editor, New Query Connection… (The tool is part of the so-called “Data Dude” features)

clip_image002

In the Connect to Server dialog, select SQL Server Compact:

clip_image004

You can select an existing database, or even create a new one. This dialog will automatically detect if the specified file is a version 3.5 or 4.0 file.

clip_image006

Once connected, you can perform functions similar to what you may know from SQL Server Management Studio:

clip_image008

clip_image010

New release of ExportSqlCe SSMS add-in and command line utilities–Script to SQL Azure

The new release (version 3.5) of my scripting utilities for SQL Server Compact includes a couple of bug fixes and a single new feature: Script Schema and Data for SQL Azure.

image

This new feature allows you to migrate your SQL Server Compact solution to a SQL Azure database. The SQL Azure compatible script includes the following changes compared to the standard, SQL Server Compact and SQL Server compatible script:

– Primary keys are scripted before any INSERTs, as a clustered index is required on all SQL Azure tables.

– INSERTs are batched (1000 per GO), to improve performance when running the script against a SQL Azure database.

– ROWGUIDCOL property is not supported, so it is not included.

The SQL Azure script is also SQL Server Compatible, but not SQL Server Compact compatible.The matrix below illustrates your options:

Script option SQL Server and SQL Server Compact SQL Azure and SQL Server
Schema and Data

X

N/A

Schema and Data for SQL Azure

N/A

X

Schema and Data with BLOB files

X (Compact)
(requires SqlCeCmd)

N/A

Schema only

X

(X)

Happy scripting!