Microsoft SQL Server 2005, Compact Edition

Microsoft SQL Server 2005, Compact Edition

Released July 2007
Publisher(s): Sams
ISBN: 9780672329227

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

SQL Server Compact Edition is a slimmed down version of SQL Server that you can use on Pocket PCs, Smart phones, Tablet PCs, and Desktops. With this book, you will learn how to build applications for resource constrained devices. You’ll discover how the SQL Server Compact Edition database can also be used as an embedded in-proc database for desktop-based applications. You’ll learn how to manage the SQL Server Compact Edition database using SQL Server Management Studio, Visual Studio, and Query Analyzer.

Laptops, Tablet PCs, and mobile devices enable information workers to access enterprise data from remote locations. With the step-by-step exercises provided in this book, you’ll learn how to synchronize data between SQL Server and SQL Server Compact Edition. You’ll also learn Remote Data Access–a lightweight method to exchange data between SQL Server and SQL Server Compact Edition. Also in this book are code samples to help you write database applications for mobile devices and for desktop PCs. Throughout the book you’ll find expert tips for building and deploying enterprise applications.

Understand how to…

  • Build mobile and desktop applications using SQL Server Compact Edition

  • Set up the development and production environment with Visual Studio, SQL Server, and SQL Server Compact Edition

  • Optimize performance and plan for security

  • Manage databases using SQL Server Management Studio, Visual Studio, and Query Analyzer

  • Develop applications using ADO.NET and OLEDB provider for SQL Server Compact Edition

  • Upgrade from earlier versions of SQL CE, .NET Compact Framework, and Visual Studio

  • Use Merge Replication to synchronize data between SQL Server and SQL Server Compact Edition

  • Exchange data between SQL Server and SQL Server Compact Edition using Remote Data Access

    • Introduction           1

    1              Getting Started with SQL Server 2005 Compact Edition                    7

    2              Platform Support and Installation35

    3              Getting to Know the Tools  63

    4              Managing the SQL Server 2005 Compact Edition Database     109

    5              Defining Database Structure                   131

    6              Manipulating the Database               175

    7              Programming SQL Server 2005 Compact Edition with ADO.NET             205

    8              Introducing Native Access 247

    9              Using Operators in SQL Server 2005 Compact Edition                     279

    10            Using the Built-In Functions                     295

    11            Upgrading from a Previous Version                327

    12            Synchronizing Data with Merge Replication   345

    13            Synchronizing Data with Remote Data Access             411

    14            Securing the SQL Server 2005 Compact Edition Database          459

    15            SQL Server 2005 Compact Edition Performance Tuning           505

    Index      551

    Table of contents

    1. Copyright
      1. Dedication
    2. About the Authors
      1. Prashant Dhingra
      2. Trent Swanson
    3. Acknowledgments
    4. We Want to Hear from You!
    5. Introduction
      1. Overview of the Book
        1. Chapter 1: Getting Started with SQL Server 2005 Compact Edition
        2. Chapter 2: Platform Support and Installation
        3. Chapter 3: Getting to Know the Tools
        4. Chapter 4: Managing the SQL Server 2005 Compact Edition Database
        5. Chapter 5: Defining Database Structure
        6. Chapter 6: Manipulating the Database
        7. Chapter 7: Programming SQL Server 2005 Compact Edition with ADO.NET
        8. Chapter 8: Introducing Native Access
        9. Chapter 9: Using Operators in SQL Server 2005 Compact Edition
        10. Chapter 10: Using the Built-In Functions
        11. Chapter 11: Upgrading from a Previous Version
        12. Chapter 12: Synchronizing Data with Merge Replication
        13. Chapter 13: Synchronizing Data with Remote Data Access
        14. Chapter 14: Securing the SQL Server 2005 Compact Edition Database
        15. Chapter 15: SQL Server 2005 Compact Edition Performance Tuning
      2. How This Book Will Help You
        1. Defining Architecture
        2. Designing Database
        3. Developing Application
        4. Administrating Server and Client
    6. 1. Getting Started with SQL Server 2005 Compact Edition
      1. Design Goals
        1. Support Data Synchronization
        2. Providing Rich Functionality with a Small Size and Footprint
        3. Rapid Application Development
      2. Features
        1. Relational Database Functionality in a Small Footprint
        2. Transaction Support
        3. Robust Data Support
        4. Small Footprint
        5. Security Features
        6. Multiuser Access
        7. Integration with SQL Server 2005 and Visual Studio 2005
        8. Common Programming Model
        9. Single Data file
        10. Easy Deployment
        11. Data Synchronization
        12. Locking and Isolation Levels
        13. Result Set
        14. Named Parameter
        15. Cost-Based Query Optimizer
        16. Integration Services Support
      3. Evolution of SQL Server Compact Edition
        1. SQL Server Compact Edition Version 1.0
        2. SQL Server Compact Edition Version 2.0
        3. SQL Server Mobile Edition Version 3.0
        4. SQL Server Compact Edition Version 3.1
        5. Naming Convention for SQL Server Compact Edition
        6. Obtaining SQL Server Compact Edition 3.x
      4. Differences Between Versions 3.1 and 3.0
        1. Desktop Restrictions Are a Thing of the Past
        2. Data Directory and ClickOnce Deployment
        3. Branding
          1. Difference in SQL Server Dialogs
          2. Server Type
          3. Strings
          4. Tabs
          5. Tool Tips
          6. Summary Heading
          7. Connection Properties
          8. Differences in the Visual Studio 2005 Dialog Boxes
          9. Data Source
      5. Architectural Fundamentals
        1. Logical Architecture
        2. Connectivity Components View
          1. Client Agent
          2. Server Agent
          3. The Storage Engine
        3. Client Components View
          1. SQL Server Compact Edition Database Engine
          2. Storage Engine
          3. Query Processor
          4. OLE DB
          5. ADO.NET
        4. The Development View
        5. Deployment View
      6. Compact Edition Features Related to SQL Server 2005 Features
        1. Choosing SQL Express Versus SQL Server Compact Edition
      7. Summary
    7. 2. Platform Support and Installation
      1. Software and Hardware Requirements
        1. Platform Support for SQL Server Compact Edition
          1. Mobile Device
          2. Tablet PC
          3. Desktop
        2. Platform Support for SQL Server
        3. Platform Support for IIS
        4. Platform Support for Visual Studio
      2. Obtaining SQL CE
        1. SQL Server Compact Edition Packages
      3. Setting Up the Environment
        1. The Development Environment
        2. Production Environment
          1. Client
          2. Backend SQL Server
          3. IIS Web Server
      4. Installing SQL Server Compact Edition
        1. Installing SQL Server Compact Edition with SQL Server 2005
        2. Installing SQL Server Compact Edition with Visual Studio 2005
          1. Installing Windows Mobile SDK
          2. Windows Mobile Branding
          3. Installing Windows Mobile 5.0 Pocket PC
          4. Installing Windows Mobile 5.0 Smartphone SDK
        3. Installing ActiveSync
        4. Installing IIS and SQL Server Compact Edition Server Tools
      5. Getting Familiarized with SQL Server Compact Edition Components on a Developer Computer
        1. SQL Server Compact Edition Client Components for Desktop and Tablet PC
        2. SQL Server Compact Edition Readme File
        3. SQL Server Compact Edition End-User License Agreement
        4. SQL Server Compact Edition Cabinet Files for Devices
      6. Deploying and Distributing SQL Server Compact Edition Applications
        1. Deploying on a Desktop and Tablet PC
        2. Deploying on a Device
          1. Deploying a Managed Application
          2. Deploying a Native Application
      7. Packaging the Application
      8. Summary
    8. 3. Getting to Know the Tools
      1. Using SQL Server Management Studio
        1. Starting SQL Server Management Studio
        2. Getting Connected to SQL Server Compact Edition Database
        3. Using Object Explorer
          1. Disconnecting from the SQL Server Compact Edition Database
          2. Connecting to SQL Server Compact Edition Database from Object Explorer
          3. Registering a Database Server
          4. Connecting to a Registered Database
          5. Using Tables Node
          6. Using Views Node
          7. Tables
          8. Columns
          9. Indexes
          10. Key_Column_Usage
          11. Table_Constraint
          12. Referential_Constraints
          13. Provider_Types
          14. Using the Programmability Node
          15. Using the Replication Node
        4. Using the Query Editor
        5. Using a Graphical Execution Plan
          1. Using the Estimated Execution Plan
          2. Using the Actual Execution Plan
        6. Managing the Database
        7. Using Replication Wizards
          1. Using the Publication Wizard
          2. Using the Subscription Wizard
          3. Using the Configure Web Synchronization Wizard
      2. Using the Query Analyzer
        1. Installing the Query Analyzer
        2. Getting Connected to the SQL Server Compact Edition Database
        3. Using the Tools Menu
          1. Refresh
          2. Logging
          3. Fonts
          4. About
          5. Exit
        4. Using Tabs
          1. Objects
          2. SQL
          3. Saving SQL Statements
          4. Using a Saved SQL Statement
          5. Grid
          6. Notes
      3. Using Visual Studio
        1. Creating a Smart Device Project
          1. Setting Up References
          2. Using Data Sources
          3. The Data Source Window
          4. Configuration Wizard
          5. Adding SQL Server CE to a Visual Studio Project
          6. Deploying Your Smart Device Application Using Visual Studio
          7. Deploying Multiple Times
          8. Building a CAB File
          9. Changing Target Platform
        2. Creating a Desktop Project
        3. Using DataDirectory
        4. Connecting to an Emulator
          1. Cradling the Emulator
          2. Configuring ActiveSync for Emulator
        5. Using Windows Mobile 6.0
          1. New Features in Windows Mobile 6.0 SDK
      4. Summary
    9. 4. Managing the SQL Server 2005 Compact Edition Database
      1. Creating an SQL Server Compact Edition Database
        1. Creating a Database Using SSMS
        2. Creating a Database Using Visual Studio
        3. Creating a Database Using Query Analyzer
        4. Creating a Database Using SQL
        5. Creating a Database Programmatically
      2. Deleting the Database
        1. Deleting the Database Using Query Analyzer
      3. Verifying a Database
      4. Repairing a Database
        1. Repair a Database Using SQL Server Management Studio
        2. Repair a Database Programmatically
      5. Compacting a Database
        1. Compact the Database Using SQL Server Management Studio
        2. Compacting a Database Using Query Analyzer
        3. Compacting a Database Programmatically
      6. Shrinking a Database
        1. Shrinking a Database Using SQL Server Management Studio
        2. Shrinking a Database Programmatically
        3. Shrinking a Database Automatically
      7. Securing a Database
      8. Creating a Backup and Restoring the Database
      9. Summary
    10. 5. Defining the Database Structure
      1. Creating a Table
        1. Creating a Table Using the SQL Statement
        2. Defining Columns
          1. Column Data Type
          2. Column Constraint
          3. NULL or NOT NULL
          4. Column Level PRIMARY KEY Constraint
          5. Column Level UNIQUE Constraint
          6. Identity Column
          7. Default Value
        3. Defining a Table Constraint
          1. Table Level PRIMARY KEY Constraint
          2. Table Level UNIQUE Constraint
          3. Foreign Key
          4. REFERENCES, ref_table, ref_column
          5. ON DELETE
          6. ON UPDATE
        4. Creating a Table Using SQL Server Management Studio
        5. Creating a Table Using Visual Studio
        6. Creating a Table Using Query Analyzer
      2. Updating a Table Definition
        1. Updating a Table Using SQL Server Management Studio
        2. Updating the Table Using Visual Studio 2005
        3. Updating a Table Definition Using Query Analyzer
      3. Dropping a Table
        1. Dropping a Table Using an SQL Statement
        2. Dropping a Table with a Foreign Key Constraint
        3. Dropping a Table Using SQL Server Management Studio
        4. Dropping a Table Using Visual Studio
        5. Dropping a Table Using Query Analyzer
      4. Creating Indexes
        1. Create an Index Using an SQL Statement
        2. Create an Index Using SQL Server Management Studio
        3. Creating an Index Using Visual Studio
        4. Create an Index Using Query Analyzer
      5. Viewing and Modifying Index Properties
        1. Viewing and Modifying Index Properties Using SQL Server Management Studio
        2. Viewing and Modifying Index Properties Using Visual Studio
      6. Deleting an Index
        1. Deleting an Index Using SQL Server Management Studio
        2. Deleting an Index Using Visual Studio
        3. Deleting an Index Using Query Analyzer
      7. Using Templates
      8. Using Metadata
      9. Creating a Database Project Using Visual Studio
        1. Create a Database Project
      10. Summary
    11. 6. Manipulating the Database
      1. Connecting to the Database
        1. Connecting to the Database Using SQL Server Management Studio (SSMS)
        2. Connecting to the Database Using Query Analyzer
        3. Connecting to the Database Using Visual Studio
        4. Connecting to the Database Programmatically
        5. Temporary Database
      2. Considerations for Multiuser Access
        1. Single Connection
        2. Multiple Connections
        3. Multiple Applications
        4. Locking, Concurrency, and Isolations
          1. Lock Granularity
          2. Resources for Locking
          3. Mode for Locking
          4. Shared (S)
          5. Update (U)
          6. Exclusive (X)
          7. Schema (Sch-M, Sch-S)
          8. Intent
          9. Setting Mode
          10. (1) Read Write
          11. (2) Read Only
          12. (3) Exclusive
          13. (4) Shared Read
          14. Setting Isolation Level
          15. Serializable
          16. Repeatable Read
          17. Read Committed
      3. Querying the Database
        1. Retrieving Data from a Table
          1. Select
          2. ALL
          3. DISTINCT
          4. { column_name | expression } [ [ AS ] column_alias ]
          5. Column Name
          6. Column Alias
          7. { table_name | table_alias }.*
          8. Table Name
          9. Table Alias
          10. WHERE
          11. JOIN
          12. Inner Join
          13. Left [Outer] Join
          14. Right [Outer] Join
          15. On
          16. Sorting Data
          17. Grouping Data
          18. HAVING
      4. Modifying a Database
        1. Inserting Data into Tables
          1. INSERT
          2. INTO
          3. Table Name
          4. Column List
          5. VALUES
          6. DEFAULT | NULL | Expression
          7. Derived Table
        2. Updating Data in Tables
          1. Update
          2. With
          3. Set
          4. Column Name
          5. Where
          6. OPTION
        3. Deleting Data from Tables
          1. DELETE
          2. FROM
          3. WHERE
      5. Using SQL Server Integration Services
        1. Create a Project
        2. Define Data Flow Source
        3. Define Data Transformation
        4. Define Data Flow Destination
        5. Execute Package
      6. Summary
    12. 7. Programming SQL Server 2005 Compact Edition with ADO.NET
      1. Introducing ADO.NET
        1. Namespace
          1. System.Data
          2. System.Data.Common
          3. System.Data.SqlClient
          4. System.Data.SqlServerCe
        2. Introducing the ADO.NET Object Model
          1. Understanding Data Providers
          2. Connection Object
          3. Command Object
          4. Command Object Properties
          5. Data Reader
          6. Data Adapter
          7. Understanding DataSet
          8. Using a DataSet Relationship
          9. Using DataView Object
          10. Updating Data with DataSet
        3. Considering ADO.NET Additional Features
          1. Understanding Data Binding
          2. Understanding Connectionless
        4. Understanding Updateable Cursor
          1. SqlCeResultSet
          2. SqlCeUpdateableRecord
          3. SqlCeEngine
        5. Understanding Parameterized Queries
          1. SqlCeParameterCollection
          2. SqlCeParameter
        6. Understanding Transactions
          1. SqlCeTransaction
          2. SqlCeTransactionInProgressException
        7. Using Error and Exceptional Classes
          1. SqlCeException
          2. SqlCeError
          3. SqlCeErrorCollection
      2. Building Applications
        1. Developing Desktop Applications with SQL Server Compact Edition
          1. Create an Email .sdf File
          2. Create a Windows Project
        2. Developing Disconnected Smart Device Applications with SQL Server Compact Edition
      3. Summary
    13. 8. Introducing Native Access
      1. Understanding the Difference between Managed and Unmanaged Access
        1. Memory Management
        2. Intermediate Language
        3. No Garbage Collection
        4. Language Interoperability
        5. Error Handling
      2. Managing the Database
        1. The CreateDatabase Method
          1. Local Connection String Properties
          2. Data Source
          3. Database Password
          4. Encrypt Database
          5. MAX Buffer Size
          6. Max Database Size
          7. Mode
          8. Default Lock Timeout
          9. Default Lock Escalation
          10. Flush Interval
          11. Auto Shrink Threshold
          12. Temp File Directory
          13. Temp File Maximum Size
          14. Locale Identifier
        2. Compact Database
        3. The Repair Method
        4. Using Error Records
          1. SSCEErrors Collection
          2. Count
          3. SSCEError
      3. Understanding OLE DB
        1. Introducing the OLE DB Object Model
          1. Understanding the Data Source Object
          2. Differences in Data Source Object
          3. Changing the Current Data Source
          4. Current Catalog
          5. Knowing the Session Object
          6. Knowing the Command Object
          7. Using the Rowset Object
          8. Knowing the Cursors Object
          9. Forward Only Cursor
          10. Base Table Cursor
          11. Scrollable Cursor
          12. Using the Index Object
          13. Using the Parameters Object
          14. Using the Constraint Object
          15. Using the Transaction Object
          16. Understanding the Difference in Transaction Support
        2. Understanding the OLE DB Data Type
        3. Standard OLE DB Interfaces for SQL Server Compact Edition
        4. Understanding the OLE DB Interfaces Differences
          1. Accessor Performance
          2. Changing Table Properties
          3. Managing Database
          4. Using Views
          5. Setting the Transaction Level
          6. IAlterIndex
        5. The OLE DB Property Set
        6. Using Provider-Specific Properties
          1. DBPROPSET_SSCE_DBINIT Property Set
          2. DBPROP_SSCE_MAXBUFFERSIZE
          3. DBPROP_SSCE_DBPASSWORD
          4. DBPROP_SSCE_DEFAULT_LOCK_ESCALATION
          5. DBPROP_SSCE_ENCRYPTDATABASE
          6. DBPROP_SSCE_TEMPFILE_DIRECTORY
          7. DBPROP_SSCE_DEFAULT_LOCK_TIMEOUT
          8. DBPROP_SSCE_AUTO_SHRINK_THRESHOLD
          9. DBPROP_SSCE_FLUSH_INTERVAL
          10. DBPROP_SSCE_MAX_DATABASE_SIZE
          11. DBPROP_SSCE_TEMPFILE_MAX_SIZE
          12. DBPROPSET_SSCE_SESSION
          13. DBPROP_SSCE_LOCK_TIMEOUT
          14. DBPROP_SSCE_LOCK_ESCALATION
          15. DBPROP_SSCE_TRANSACTION_COMMIT_MODE
          16. DBPROP_SSCE_COLUMN
          17. DBPROP_SSCE_COL_ROWGUID
          18. DBPROPSET_SSCE_ROWSET
          19. DBPROP_SSCE_LOCK_HINT
          20. Using OLE DB Properties
          21. Using Column Properties
          22. DBPROP_COL_AUTOINCREMENT
          23. DBPROP_COL_DEFAULT
          24. DBPROP_COL_FIXEDLENGTH
          25. DBPROP_COL_INCREMENT
          26. DBPROP_COL_ISLONG
          27. DBPROP_COL_NULLABLE
          28. DBROP_COL_SEED
          29. Using the Data Source Information Property Group
          30. DBPROP_ALTERCOLUMN
          31. DBPROP_COLUMNDEFINITION
          32. DBPROP_DBMSNAME
          33. DBPROP_DBMSVER
          34. DBPROP_DSOTHREADMODEL
          35. DBPROP_IDENTIFERCASE
          36. DBPROP_MULTIPLESTORAGEOBJECTS
          37. DBPROP_NULLCOLLATION
          38. DBPROP_OLEOBJECTS
          39. DBPROP_OPENROWSETSUPPORT
          40. DBPROP_PROVIDERFILENAME
          41. DBPROP_PROVIDEROLEDBVER
          42. DBPROP_PROVIDERVER
          43. DBPROP_STRUCTUREDSTORAGE
          44. DBPROP_SUPPORTEDTXNDDL
          45. DBPROP_SUPPORTEDTXNISOLEVELS
          46. DBPROP_SUPPORTEDTXNISORETAIN
          47. Using Initialization Properties: DBPROP_INIT_DATASOURCE
          48. DBPROP_INIT_LCID
          49. DBPROP_INIT_MODE
          50. Using Rowset Properties
          51. DBPROP_ABORTPRESERVE
          52. DBPROP_ACCESSORDER
          53. DBPROP_BLOCKINGSTORAGEOBJECTS
          54. DBPROP_BOOKMARKS
          55. DBPROP_BOOKMARKTYPE
          56. DBPROP_CANFETCHBACKWARDS
          57. DBPROP_CANHOLDROWS
          58. DBPROP_CANSCROLLBACKWARDS
          59. DBPROP_CHANGEINSERTEDROWS
          60. DBPROP_COMMITPRESERVE
          61. DBPROP_DEFERRED
          62. DBPROP_DELAYSTORAGEOBJECTS
          63. DBPROP_IACCESSOR
          64. DBPROP_ICOLUMNSINFO
          65. DBPROP_ICONVERTTYPE
          66. DBPROP_ILOCKBYTES
          67. DBPROP_IROWSET
          68. DBPROP_IROWSETUPDATE and DBPROP_IROWSETCHANGE
          69. DBPROP_IROWSETINFO
          70. DBPROP_IROWSETINDEX and DBPROP_IROWSETCURRENTINDEX
          71. DBPROP_IROWSETBOOKMARK
          72. DBPROP_ISEQUENTIALSTREAM
          73. DBPROP_ISUPPORTERRORINFO
          74. DBPROP_IMMOBILEROWS
          75. DBPROP_LOCKMODE
          76. DBPROP_MAXOPENROWS
          77. DBPROP_MAXROWS
          78. DBPROP_OTHERSINSERT
          79. DBPROP_OTHERSUPDATEDELETE
          80. DBPROP_OWNINSERT
          81. DBPROP_OWNUPDATEDELETE
          82. DBPROP_QUICKRESTART
          83. DBPROP_REMOVEDELETED
          84. DBPROP_REPORTMULTIPLECHANGES
          85. DBPROP_ROWTHREADMODEL
          86. DBPROP_SERVERDATAONINSERT
          87. DBPROP_UPDATABILITY
          88. Using Index Properties
          89. DBPROP_INDEX_AUTOUPDATE
          90. DBPROP_INDEX_NULLCOLLATION
          91. DBPROP_INDEX_NULLS
          92. DBPROP_INDEX_TYPE
          93. DBPROP_INDEX_UNIQUE
        7. Managing the Database with OLE DB
          1. Creating a Database Using OLE DB
          2. Securing the Database Using OLE DB
          3. Encrypting Database Using OLE DB
          4. Deleting a Database
      4. Summary
    14. 9. Using Operators in SQL Server 2005 Compact Edition
      1. Arithmetic Operators
        1. The + (Add) Operator
          1. Syntax
        2. The – (Subtraction) Operator
          1. Syntax
        3. The * (Multiply) Operator
          1. Syntax
        4. The / (Divide) Operator
          1. Syntax
        5. The % (Modulo) Operator
          1. Syntax
      2. The Assignment Operator
        1. The = (Equal To) Operator
      3. Bitwise Operators
        1. The & (And) Operator
          1. Syntax
        2. The | (Or) Operator
          1. Syntax
        3. The ^ (Exclusive Or) Operator
          1. Syntax
        4. The ~ (Not) Operator
          1. Syntax
      4. The Comparison Operator
        1. The = (Equals) Operator
          1. Syntax
          2. Example
        2. The < > or ! = (Not Equal To) Operator
          1. Syntax
        3. The > (Greater Than) Operator
          1. Syntax
        4. The > = (Greater Than or Equal To)
          1. Syntax
        5. The ! > (Not Greater Than) Operator
          1. Syntax
        6. The < (Less Than) Operator
          1. Syntax
          2. Example
        7. The < = (Less Than or Equal To) Operator
          1. Syntax
        8. The ! < (Not Less Than) Operator
          1. Syntax
      5. Logical Operators
        1. And Operator
          1. Syntax
        2. Or Operator
          1. Syntax
        3. Not Operator
          1. Syntax
        4. In Operator
          1. Syntax
          2. Subquery
          3. Example
        5. Exists Operator
          1. Syntax
          2. Subquery
          3. Example
        6. Like Operator
          1. Syntax
          2. Pattern
        7. Between and And Operators
          1. Syntax
        8. Some/Any Operator
          1. Syntax
          2. Comparison Operator
          3. Subquery
        9. All Operator
          1. Syntax
          2. Comparison Operator
          3. Subquery
      6. String Concatenation Operator
        1. The + (Add) Operator
          1. Syntax
      7. Unary Operators
        1. The + (Positive) Operator
          1. Syntax
        2. The – (Negative) Operator
          1. Syntax
      8. Precedence Rules
        1. Operator Precedence
        2. Data Type Precedence
        3. Precision, Scale, and Length
      9. Summary
    15. 10. Using the Built-In Functions
      1. Mathematical Functions
        1. ABS (Absolute)
          1. Syntax
          2. Result
        2. ACOS
          1. Syntax
          2. Result
        3. ASIN
          1. Syntax
          2. Result
        4. ATAN
          1. Syntax
          2. Result
        5. ATN2
          1. Syntax
          2. Result
        6. CEILING
          1. Syntax
          2. Result
        7. COS
          1. Syntax
          2. Result
        8. COT
          1. Syntax
          2. Result
        9. DEGREES
          1. Syntax
          2. Result
        10. EXP
          1. Syntax
          2. Result
        11. FLOOR
          1. Syntax
          2. Result
        12. LOG
          1. Syntax
        13. LOG10
          1. Syntax
        14. PI
          1. Syntax
        15. POWER
          1. Syntax
          2. Result
        16. RADIANS
          1. Syntax
          2. Result
        17. RAND
          1. Syntax
          2. Result
        18. ROUND
          1. Syntax
          2. Result
        19. SIGN
          1. Syntax
          2. Result
        20. SIN
          1. Syntax
        21. SQRT
          1. Syntax
        22. TAN
          1. Syntax
      2. Aggregate Functions
        1. AVG
          1. Syntax
          2. Result
        2. COUNT
          1. Syntax
          2. Result
        3. MAX
          1. Syntax
          2. Result
        4. MIN
          1. Syntax
          2. Result
        5. SUM
          1. Syntax
          2. Result
      3. String Functions
        1. NCHAR
          1. Syntax
          2. Result
        2. CHARINDEX
          1. Syntax
          2. Result
          3. Result
        3. LEN
          1. Syntax
          2. Result
        4. LOWER
          1. Syntax
          2. Result
        5. LTRIM
          1. Syntax
          2. Result
        6. PATINDEX
          1. Syntax
          2. Result
          3. Result
        7. REPLACE
          1. Syntax
          2. Result
        8. REPLICATE
          1. Syntax
        9. RTRIM
          1. Syntax
          2. Result
        10. SPACE
          1. Syntax
          2. Result
        11. STR
          1. Syntax
          2. Result
        12. STUFF
          1. Syntax
          2. Result
        13. SUBSTRING
          1. Syntax
          2. Result
        14. UNICODE
          1. Syntax
          2. Result
        15. UPPER
          1. Syntax
          2. Result
      4. DATETIME Functions
        1. DATEADD
          1. Syntax
          2. Result
        2. DATEDIFF
          1. Syntax
          2. Result
          3. Result
          4. Result
        3. DATEPART
          1. Syntax
          2. Result
          3. Result
          4. Result
        4. DATENAME
          1. Syntax
          2. Result
          3. Result
        5. GETDATE
          1. Syntax
      5. System Functions
        1. @@IDENTITY
          1. Syntax
        2. COALESCE
          1. Syntax
        3. DATALENGTH
          1. Syntax
          2. Result
      6. Others
        1. CASE
          1. Syntax
          2. Result
          3. Result
        2. CONVERT
          1. Syntax
          2. Result
          3. Result
        3. NEWID
          1. Syntax
      7. Summary
    16. 11. Upgrading from a Previous Version
      1. Options for Upgrade
        1. Upgrade Options Overview
        2. Upgrading a Database Using Replication or RDA
          1. Database Upgrade Process
        3. How the Upgrade Tool Works
          1. Database Upgrade Tool Prerequisites
          2. Platform Consideration for Upgrade
          3. Using the Upgrade Tool
          4. Options for Running Command Line Applications on Windows Mobile Devices
      2. Upgrading the Application
        1. Using Conversion Wizard of Visual Studio 2005
        2. Using the Upgrade Project Option of Visual Studio 2005
        3. Changing the SQL CE Assembly Reference
      3. Planning the Migration
        1. Migrating Development Environment
        2. Migrating Runtime Environment
        3. Migrating Development and Runtime Environment
      4. Summary
    17. 12. Synchronizing Data with Merge Replication
      1. Merge Replication Scenarios and Features
        1. Scenarios
        2. Features
          1. Download Database from SQL Server to SQL Server Compact Edition
          2. Data Manipulation at Central SQL Server
          3. Data Manipulation at Remote SQL Server Compact Edition
          4. Data Synchronization Capability Between SQL Server and SQL Server Compact Edition
          5. Programmable Data Synchronization
          6. Schema Changes
          7. Conflict Resolution at the Time of Synchronization
          8. Data Filtering
          9. Applying Additional Business Logic for Synchronization
          10. Tools and Wizards
          11. Compression
          12. Encryption
          13. Security
          14. Synchronization Recovery
          15. GPRS Connectivity
      2. Merge Replication Architecture Components
        1. Distributor
        2. Publisher
        3. Subscriber
        4. Client Agent
        5. Server Agent
        6. SQL Server Reconciler
        7. SQL Server Replication Provider
        8. Snapshot Agent
        9. Merge Agent
        10. The SQL Server Compact Edition Database Engine
      3. Using Merge Replication
        1. Installing SQL Server Compact Server Components
        2. Configuring the Web Server
        3. Data Publishing
        4. Distributor Server Details
        5. Snapshot Folder
        6. Publication Database
        7. Type of Publication
        8. Articles
        9. Filtering
        10. Snapshot Agent Schedule
        11. Account Name for Snapshot Agent
        12. Publication Name
        13. How to Create a Publication
        14. Publication Wizard
        15. Stored Procedure
        16. Data Subscribing
        17. Specify Publication and Publisher Database
        18. Specify Subscriber or Subscriber Host Name
        19. Specify Authentication Mechanism for Web Server
        20. Copying a Subscription to Multiple Clients
        21. Subscribing to Multiple Publications
        22. How to Create a Subscription
        23. Subscription Wizard
        24. AddSubscription Method
        25. Data Synchronizing
        26. ReinitializeSubscription
        27. DropSubscription
      4. Merge Replication Exercise
        1. Setting Up a Backend SQL Server 2005 Database
        2. Installing SQL Server Compact Server Components
        3. Creating a Publication
          1. Specifying a Distributor
          2. Specifying Snapshot Folder
          3. Specifying Publication Database
          4. Specifying Publication Type
          5. Specifying Subscriber Type
          6. Specifying Articles
          7. Specify Rows Filtering
          8. Scheduling the Snapshot Agent
        4. Configuring Snapshot Agent Security
        5. Complete Publication
      5. Configuring SQL Server Compact Edition Server Components
        1. Starting the Configure Web Synchronization Wizard
        2. Choosing a Subscriber Type
        3. Providing Web Server Details and Creating a Virtual Directory
          1. Specify Virtual Directory
          2. Configuring Security and Authentication Requirements
        4. Completing the Web Synchronization Configuration
        5. Creating a Subscription
          1. Connecting to SQL Server Compact Database
          2. Starting Subscription Wizard
          3. Choosing the Authentication Mechanism for the Web Server
          4. Completing the Subscription
        6. Monitoring Replication
        7. Viewing Snapshot Agent Status
        8. Viewing and Modifying Publisher Properties
        9. Creating a Device Application in Visual Studio 2005
          1. Creating a Project
          2. Add Database File
          3. Create the User Interface
          4. Build and Deploy Application
      6. Synchronizing Data with SQL Server 2000
      7. Developing Connected Smart Device Applications with SqlClient
        1. Enable Remote Connections
        2. Configure a Firewall
          1. Create a Smart Device Project
      8. Summary
    18. 13. Synchronizing Data with Remote Data Access
      1. Remote Data Access Scenarios
        1. Download Only
        2. Upload Only
        3. Download, Change, and Upload
        4. Submitting SQL Statements
      2. Remote Data Access Features
        1. The Pull Feature
        2. Tracking the Changes
        3. The Push Feature
        4. Batch Changes
        5. Submitting SQL
        6. Error Logging
      3. Remote Data Access Architecture
        1. Server Agent
        2. Web Server
        3. Client Agent
        4. The SQL Server Compact Edition Database Engine
      4. Using Remote Data Access
        1. Installing the Server Agent Component
        2. Configuring the Virtual Directory
          1. Security Options
        3. Programming for RDA
          1. Setting the SqlCeRemoteDataAccess Project Properties
          2. Pulling Remote Data into a Local Database
          3. Using Pull Method
          4. Tracking Changes
          5. Restrictions on Tracking
          6. Data Type Conversion
          7. Error Logging
          8. Changing the Pulled Table
          9. Restrictions
          10. Allowed Changes
          11. Pushing Local Changes Back to Remote Database
          12. Using Push Method
          13. Batch Option
          14. Handling of Conflict
          15. Executing SQL Statements on Remote Database
          16. Using SubmitSQL Method
          17. Restrictions in RDA
      5. Remote Data Access Exercise
        1. Installing SQL Server Compact Server Components
        2. Setting Up a Backend SQL Server 2005 Database
        3. Configuring the Web Server for RDA
        4. Creating a Device Application in Visual Studio 2005
          1. Create Database
          2. Create a Project
          3. Add Database File
          4. Create User Interface
          5. Build and Deploy Application
      6. Connectivity Options
      7. Feature Comparison
        1. Conflicts
          1. Types of Conflict
          2. Conflicts Logging
          3. Conflicts Resolution
        2. Single Versus Multiple Table Pull
        3. Referential Integrity Transfer
        4. Identity Columns
        5. Tools
        6. Server Side Changes
        7. Configurations
        8. Synchronization Control
        9. Server Side Schema Changes
        10. Bidirectional Versus Single Directional Changes
        11. Download Only Articles
        12. Filtering Tables
        13. Summary of RDA and Merge Replication Features
      8. Advantages and Disadvantages of Using Web Service as Synchronization Mechanism
        1. Advantages
        2. Disadvantages
      9. Synchronizing Microsoft Access Data with SQL Server Compact Edition
        1. Installing the Data Access Synchronization Tool
        2. Synchronizing Data
      10. Summary
    19. 14. Securing the SQL Server 2005 Compact Edition Database
      1. Securing the Client Database
        1. Using a Password
          1. Password String
        2. Encryption
        3. Setting a Password and Encryption
          1. Setting a Password with SQL Management Studio
          2. Setting a Password with Query Analyzer
          3. Setting a Password with the SQL String
          4. Setting a Password with ADO.NET
          5. Setting a Password with OLE DB
        4. Accessing a Password-Protected Encrypted Database
      2. Exploring the Web Server Security Options
        1. IIS Authentication
          1. Anonymous Authentication
          2. Basic Authentication
          3. Windows Integrated Authentication
        2. Secure Socket Layer
          1. Establishing a Secure Connection
          2. HTTPS
          3. Using SSL
          4. Creating a Certificate Signing Request File
          5. Obtaining the SSL Certificate
          6. Installing Test CA Root Certificate
          7. Installing SSL Certificate
      3. SQL Server Security Options
        1. SQL Server Authentication
        2. Integrated Windows Authentication
      4. Security Planning for Connectivity
        1. Securing Web Server
          1. Choose Channel for Transmission
          2. Using HTTP
          3. Using HTTPS and Secure Socket Layer
          4. Configuring Security and Authentication Requirements
          5. Setting for Anonymous Access
          6. Setting for Basic Authentication
          7. Setting for Integrated Windows Authentication
          8. Virtual Directory Permission
          9. Authorization for Accessing the Server Agent
          10. Setting Replication and RDA Object Properties for Web Server
        2. Configuring the SQL Server Database
          1. Setting Up Security for Merge Replication
          2. Securing Publisher for Merge Replication
          3. Verify Change Permission on Publisher
          4. Setting Replication Object Properties for Publisher
          5. Securing Distributor for Replication
          6. Setting Replication Object Properties for Distributor
          7. Setting Up Security for RDA
        3. Programming for Secured Web Server and Backend Database
          1. Setting Replication and RDA Properties for Client Database
          2. Programming for Anonymous and SQL Server Access
          3. Replication
          4. RDA
          5. Programming for Anonymous and Windows Integrated Access
          6. Replication
          7. RDA
          8. Programming for Basic Authentication and SQL Server Authentication Access
          9. Replication
          10. RDA
          11. Programming for Basic and Windows Integrated Access
          12. Replication
          13. RDA
          14. Programming for Windows Integrated and SQL Server Authentication Access
          15. Replication
          16. RDA
          17. Programming for Windows Integrated Access Both at Web Server and SQL Server
          18. Replication
          19. RDA
        4. Recommended Practices
      5. Summary
    20. 15. SQL Server 2005 Compact Edition Performance Tuning
      1. Query Optimizer
        1. Query Execution Plan
          1. Estimated Execution Plan
          2. Actual Execution Plan
        2. Cost-Based Optimization
      2. Database Schema Design Considerations
        1. Tables and Columns
          1. Fixed- and Variable-Length Columns
          2. Normalization versus De-normalization
          3. Row Length
          4. Store Computed Columns
        2. Indexes
          1. Index Length
          2. What to Index
          3. sp_show_statistics
          4. Example:
          5. sp_show_statistics_columns
          6. Sp_show_statistics_steps
          7. Read-Only Tables
          8. Multiple-Column Indexes
          9. Keys
          10. Filter Clauses
          11. Order-By, Group-By, and Distinct
          12. MAX and MIN
          13. Table Size
          14. Low Density
          15. Time to Create an Index
      3. Programming Considerations
        1. Queries
          1. Subquery versus Join
          2. Exists versus Count (*)
          3. Outer Join
          4. Join on Multiple Tables
        2. Using Hints
          1. Table Hint
          2. Query Hint
        3. ADO.NET Considerations
          1. Use of Cursor
          2. SqlCeDataReader
          3. SqlCeResultSet
          4. Parameterized Queries
          5. Open Objects
          6. Number of Columns
          7. Order By, Group By, and Distinct
          8. Usage of ExecuteScalar
          9. Base Table Cursor
          10. Seek/Set Range
        4. Get the First Response
        5. Materialization
      4. Performance for Synchronization Considerations
        1. Publish Required Data
        2. Download Only Article Type
        3. Well-Partitioned Group
        4. Filters and Index
        5. Complex Join
        6. Interval of Replication
        7. Disk Drive for Logs
        8. Considerations for Subscribers
      5. Storage Media Considerations
      6. Summary

    Product information

    • Title: Microsoft SQL Server 2005, Compact Edition
    • Author(s):
    • Release date: July 2007
    • Publisher(s): Sams
    • ISBN: 9780672329227