SQL Server Compact Code Snippet of the Week #4 : select rows 50 to 60 from a table

For many scenarios it can be useful to be able to get only a subset of rows fro a long result set. SQL Server Compact version 4.0 adds support for the OFFSET – FETCH keywords, that allows you to retrieve a single “page” of data. Sadly, this is not possible with version 3.5. Notice, that this syntax is supported on the SQL Server Compact version used on Windows Phone, despite the 3.5 label of that runtime.

For 4.0 you can use:

SELECT [Order ID]
,[Product ID]
,[Unit Price]
,[Quantity]
,[Discount]
FROM [Order Details]
ORDER BY [Order Id]
OFFSET 50 ROWS FETCH NEXT 10 ROWS ONLY
GO

This statement returns only 10 rows.

For 3.5, you can only use TOP, and must then skip the rows that you do not need:

SELECT TOP (60) [Order ID]
,[Product ID]
,[Unit Price]
,[Quantity]
,[Discount]
FROM [Order Details]
ORDER BY [Order Id]
GO

This statement returns 60 rows, and you must manually skip the first 50.

SQL Server Compact Code Snippet of the Week #3 : locate the rowversion column in a table

The third data type that is only allowed to appear in a single column per table is the rowversion data type, also known as timestamp (no relation to datetime). Rowversion is a data type that exposes automatically generated, unique binary numbers in a database, and contains unique values across the entire database. The size is 8 bytes. You can use the @@DBTS function to get the most recent rowversion value in the database.

Here is the code to determine if a table has a rowversion column, and get the column ordinal (position in the table):

 public Int32 GetRowVersionOrdinal(string tableName)
        {
            object value = ExecuteScalar("SELECT ordinal_position FROM information_schema.columns WHERE TABLE_NAME = '" + tableName + "' AND data_type = 'rowversion'");
            if (value != null)
            {
                return (int)value - 1;
            }
            return -1;
        }

SQL Server Compact Code Snippet of the Week #2 : locate the IDENTITY column in a table

The next instalment in this series also concerns a column type that only exists once per table, the IDENTITY column. The type of the column must be either bigint or int, and you must also specify an initial value (seed) and the value for the difference between each value (the increment), the default values for both being 1.

In order to detect which column is the IDENTITY column, you can use the following SQL statement (an code):

        public bool HasIdentityColumn(string tableName)
        {
            return (GetIdentityOrdinal(tableName) > -1);
        }

        public int GetIdentityOrdinal(string tableName)
        {
            object value = ExecuteScalar("SELECT ordinal_position FROM information_schema.columns WHERE TABLE_NAME = N'" + tableName + "' AND AUTOINC_SEED IS NOT NULL");
            if (value != null)
            {
                return (int)value - 1;
            }
            return -1;
        }

SQL Server Compact Toolbox 3.2–Visual Guide of new features

After more than 150.000 downloads, version 3.2 of my SQL Server Compact Toolbox extension for Visual Studio 2012 and 2010 is now available for download. This blog post is a visual guide to the new features included in this release, many suggested by users of the tool via the CodePlex issue tracker

Export from SQL Server to SQL Server Compact 4.0 in a single workflow

Previously with the Toolbox, in order to export a SQL Server database to SQL Server Compact, you had to export a script, create a new blank 4.0 database file, and then run the script against this new file. This process has been simplified to a single workflow, where you define the database and tables to export, and then specify the 4.0 database file name.

To use the feature, right click the root node in the Toolbox:

Select the server database:

If selecting a LocalDB database, type (localdb)\v11.0 in the server name box, and type the database name, do not pick the database name from the dropdown list and do not press the test connection button. This is caused by the fact that this connection dialog (that supports SQL Server authentication) uses an old API to connect to SQL Server, which only partially works with LocalDB.

Select which tables to be scripted:

And type the name of the new SQL Server Compact database file:

Once the background process completes, you can see in the Visual Studio status bar that the export completed:

As this is a new feature, please provide any suggestion for improving it…

Option to preserve Server schema names as part of Server export files

There is now a new option that enables you to keep the Server schema name (for example dbo) as part of the exported table name:

Once this feature is enabled, the resulting CREATE TABLE script will look like this:

CREATE TABLE [dbo.Shippers] (

[Shipper ID] int NOT NULL IDENTITY (33,1)

, [Company Name] nvarchar(40) NOT NULL

);

rather than the usual:

CREATE TABLE [Shippers] (

[Shipper ID] int NOT NULL IDENTITY (33,1)

, [Company Name] nvarchar(40) NOT NULL

);

This does not mean that SQL Server Compact now supports or understands the concept of schemas, it simply means that the table name is now: “dbo.Shippers”

 

Red Gate SmartAssembly integration

As I blogged about earlier, SmartAssembly is now integrated with the Toolbox for error reporting and anonymous feature usage tracking. If you encounter an error that merits being reported to me, so I can fix it in a future release, please do so.

Once an error is encountered, you will see this dialog:

If the error is not expected, click “Send Error Report”:

Enter your email address if desired, and I will contact you regarding the error, and click send:

I have also added an option to opt out of feature tracking:

 

Improvements

SQL Server tables displayed are properly sorted by schema.name:

Notifying user that 3.5 SP2 is required for many features to work - this has now become an issue on new Windows 8 installation with Visual Studio 2012 only, as SQL Server Compact 3.5 SP2 is not installed on these systems:

Click the red link:

As always, you can use the About box (blue question mark on the toolbar above) to see what SQL Server Compact components you have installed (notice that the Visual Studio 2012 Server Explorer does not support SQL Server Compact 3.5, so no DDEX provider in VS 2012):

Improved SQL Server DGML diagram, the DGML feature now works with AdventureWorks2012.

Improved object tree view performance, in particular for databases with many objects.

Bug fixes

SQL query editor button texts now visible with Visual Studio 2012 dark theme:

Merge Replication dialog fixed to work with "Windows Integrated Authentication":

Updated scripting API, with foreign key related bug fixes, and various performance improvements

SQL Server Compact Code Snippet of the Week #1 : locate the ROWGUIDCOL column in a table

During the next many weeks, I plan to publish a short, weekly blog post with a (hopefully) useful code snippet relating to SQL Server Compact. The code snippets will come from 3 different areas: SQL Server Compact T-SQL statements, ADO.NET code and samples usage of my scripting API.

The ROWGUIDCOL column property is defined like this in Books Online:

Indicates that the new column is a row global unique identifier column. Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column. The ROWGUIDCOL property can be assigned only to a uniqueidentifier column.

ROWGUIDCOL automatically generates values for new rows inserted into the table.

(You can also use a default of NEWID() to automatically assign values to uniqueidentifier columns)

The ROWGUIDCOL is used by Merge Replication, all Merge Replicated tables must have a ROWGUIDCOL column.

Enough talk, show me the code snippet:

SELECT column_flags, column_name, table_name 
FROM information_schema.columns
WHERE column_flags = 378 OR column_flags = 282

I am using the undocumented “column_flags” column to determine the ROWGUIDCOL column, and the reason for the 2 different values is that a uniqueidentifier column can be either NULL or NOT NULL.