3.0 – SQL Server 2005 Mobile Edition. ▻ 3.1 – SQL Server 2005 Compact
Edition. ▻ 3.5 – SQL Server Compact 3.5. + Local transaction scope. + LINQ to
SQL.
Lukáš Navrátil, Administrace Microsoft SQL Serveru 2011
Embedded relational database
Support transactions and subset of T-SQL…
ADO.NET, OLE DB, Entity framework, LINQ to SQL Formerly known as SQL Server for Windows CE and SQL Server Mobile Edition
2.0 3.0 3.1 3.5
3.5 SP1
3.5 SP2
- SQL Server 2000 Windows CE Edition – SQL Server 2005 Mobile Edition – SQL Server 2005 Compact Edition – SQL Server Compact 3.5
+ Local transaction scope + LINQ to SQL
+ Entity Framework + 64-bit support + Data replication with SQL Server + Change tracking + Windows 7 and Windows Server 2k8 R2 support
4.0 – SQL Server Compact 4.0 + ASP.NET support + Multi user access (up to 256 connections) + OFFSET-FETCH syntax - LINQ to SQL - Windows Mobile/Windows Phone - SQL Management studio - Data replication with SQL Server - SQL Server Integration Services
Runs in-process, not as a service Stored in .sdf file, max size 4GB, can create temporary DB (.tmp) for internal use
Supports transactions, referential integrity, encryption and password protection, query optimalizer Doesn‘t support stored procedures, triggers, views, user defined functions, full text, CLR integration, DISTINCT in aggregates…
VS (2005 and higher) can work with SQL CE up to version 3.5 SQL CE 4.0 requires .NET 4, VS 2010 SP1and Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 (maybe not required for Ultimate, Premium and Professional version) SQL Server Compact Toolbox - add-in for Visual studio
Data source Password Encrypt Max buffer size (default 640kB) Max database size (default 128MB) Mode (Read Write, Read Only, Exclusive, Shared read) Default lock timeout Default lock escalation Flush interval (default 10s) Autoshrink threshold (default 60%) Temp path Temp file max size (default 128MB) Locale identifier Case sensitive (default false)
3 Isolations levels ◦ ◦ ◦
Locks are managed internally by SQL CE Following resources can be locked: ◦ ◦ ◦ ◦ ◦
Read commited (default) Repeatable read Serializable
Rows 4kB data or index pages Table schemas Tables Databases
Locking granularity can be changed Default lock escalation is 100, can be changed - SET LOCK_ESCALATION 1000; (affects all tables) Maximum number of locks per session – 262143 sys.lock_information view Does not have deadlock detection, handled by lock timeout (but does not roll back or cancel any transaction that contains the statement) - SET LOCK_TIMEOUT 2000;
Explicit transactions
Works in autocommit mode until BEGIN TRANSACTION starts explicit transaction
SQL CE does not process statements in batches
◦ BEGIN TRANSACTION, COMMIT/ROLLBACK TRANSACTION ◦ ADO.NET: BeginTransaction(), Commit(), Rollback()
◦ CREATE TABLE t (col int); ◦ INSERT INTO t VALUES (1); ◦ INSERTT INTO t VALUES (1);
◦ SQL Server - Syntax error ◦ SQL CE - 2 executed queries, syntax error in third statement
System.Transactions.TransactionScope (.NET 2.0 and higher)
using (TransactionScope transScope = new TransactionScope()) { using (SqlCeConnection conn = new SqlCeConnection(connStr)) { /* Opening conn automatically enlists it in the TransactionScope as a lightweight transaction. */ conn.Open(); // Do work in the connection... } // The Complete method commits the transaction. transScope.Complete(); }
Distributed transactions are not supported in SQL CE
System.Data.SqlServerCe.SqlCeEngine
Methods ◦ ◦ ◦ ◦ ◦ ◦
CreateDatabase Update Shrink Repair Verify …
string connStr = @"Data Source = Test.sdf; Password = "; SqlCeEngine engine = new SqlCeEngine(connStr); engine.CreateDatabase(); engine.Dispose();
Server explorer -> connect to selected database -> right click -> Database properties -> Shrink and repair Shrink – delete free pages Compact – reclaims empty space in pages Verify – checks all DB pages checksums (every page is checked when is readed) Repair – repair corrupted pages Autoflush Backup/restore/drop – copy/delete .sdf file
Server explorer (VS) -> Data connections -> Add connection -> Select data source and database file-> …complete wizzard…
Install SQL CE runtime on desktop with MSI installer ClickOnce deployment ◦ Solution explorer -> right click on selected project -> Properties -> Publish -> Prerequisites -> add SQL CE -> …continue with ClickOnce…
Microsoft Sync Framework SqlCeClientSyncProvider Add new item in project -> Data -> Local database cache -> …
To migrate data in Visual Studio 2010 SP1 Create a new Web application in Visual Studio. Connect to Microsoft SQL Server Compact 4.0 through a database connection. In Solution Explorer, right-click the project, and then click Properties. On the Properties page, click the Package/Publish SQL tab. On the Package/Publish SQL tab, click Import from Web.config. Visual Studio reads the application Web.config file to find connection strings. For each connection string in the Web.config file, Visual Studio creates a row in the Database Entries grid. By default, the name in the Database Entries grid is the connection string name plus a -Deployment suffix. The ApplicationServices-Deployment row is created in the Database Entries. You can also create your own SQL Server Script that will run during deployment.
An improved SQL Server Express New in SQL Server 2012 Runs in single process started by application 10GB DB size supported, multiple files Supported T-SQL, Stored procedures, views, triggers…
http://msdn.microsoft.com/enus/library/hh334581(v=SQL.10).aspx – SQL CE Books Online http://keithelder.net/2007/09/23/syncservices-for-sql-server-compact-edition-3-5in-visual/ - Data replication http://erikej.blogspot.com/2011/01/comparison -of-sql-server-compact-4-and.html Comparison of SQL Server Compact, SQL Server Express 2008 R2 and LocalDB http://erikej.blogspot.com/2011/01/sql-servercompact-40-released.html - blog posts about SQL CE
