Summary: SQL Server Everywhere Edition Frequently Asked Questions

Visual Studio 2005

Can RDA handle SQL Server 2005 Schema Qualified Table names?

Yes it can but you must take special steps for it to work. See RDAWithSchemaQaulifiedServerTableNames for details.

Is there a limit to the amount of data that can be affected by a single transaction?

Yes there is a limit.
*SSCE 3.1: A transaction cannot exceed 1GB of pages.
*SSCE 3.5: A transaction cannot exceed 17.5TB of pages - few applications are likely to encounter this limit.

Why does SQL Server Compact Edition crash when I execute a query that has a compound WHERE clause that references the same column more than once?

SSCE 3.1 has a bug that causes it to crash when an indexed column is used in multiple conditions within a WHERE clause and those conditions are combined with an OR. Here's an example (assume that DeptId is an indexed column)

		 SELECT * FROM Orders, Departments 
		  WHERE [Orders.PrimaryDeptId] = [Departments.DeptId] 
		  OR [Orders.SecondaryDeptId] = [Departments.DeptId]
	

To work around this bug, use an IN clause instead.

		 SELECT * FROM Orders, Departments WHERE [Departments.DeptId] IN [(Orders.PrimaryDeptId,] [Orders.SecondaryDeptId)]
	

Note: This bug still exists in the version of SSCE 3.5 that ships with Visual Studio 2008 Beta 2 but is expected to be fixed in the final release of SSCE 3.5.

How do I determine if a SSCE table exists?

You can find whether a table already exists by searching for the table in the virtual system table INFORMATION_SCHEMA.TABLES. The following is a simple function that encapsulates all of the necessary code.

C#

		 const string _tableExistsSql = 
		   "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tableName";
		 bool [DoesTableExist(string] tableName)
		 {
		     bool tableExists = false;
		     // Assumes that "Connection" is a reference to an open connection
		     using [(SqlCeCommand] cmd = new SqlCeCommand(_tableExistsSql, Connection))
		     {
		         cmd.Parameters.Add("@tableName", tableName);
		         int tableCount = [(int)cmd.ExecuteScalar();]
		         tableExists = tableCount > 0;
		     }
	

		     return tableExists;
		 }
	

VB

		 Const _tableExistsSql As String = _
		 "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tableName"
	

		 Function [DoesTableExist(ByVal] tableName As String) As Boolean
		     Dim tableExists As Boolean
		     Dim cmd As [SqlCeCommand] = Nothing
		     Try
		         ' Assumes that "Connection" is a reference to an open connection
		         cmd = New SqlCeCommand(_tableExistsSql, Connection)
		         cmd.Parameters.Add("@tableName", tableName)
		         Dim tableCount As Integer = [cmd.ExecuteScalar()]
		         tableExists = tableCount > 0
		     Finally
		         If Not cmd Is Nothing Then
		             cmd.Dispose()
		         End If
		     End Try
	

		     Return tableExists
		 End Function
	

What is the difference between SqlCeEngine.Shrink and SqlCeEngine.Compact

*Shrink - Cleans up the database at the page level. Moves empty and unallocated pages to the end of the file and then truncates the file.
*Compact - Reclaims all wasted space in the database by creating a new database file. The recoved space includes empty and unallocated pages, and also includes empty or unused space within pages as well. In addition to producing a database file with the unused space reclaimed, the data records in all of the tables in the resulting database file are stored in primary key order.