Difference Between SQL Server 2008 and Express

SQL Server is a relational model database server produced by Microsoft. And SQL Server Express is a scaled down version of SQL Server which is free, but has limited features compared to the full version. Latest version of SQL Server is the SQL Server 2008 R2 and its corresponding Express Edition is SQL Server Express 2008.

Microsoft SQL Server primarily uses T-SQL (which is an extension to SQL) and ANCI SQL, as its query languages. It supports Integer, Float, Decimal, Char, Varchar, binary, Text and few other data types. User-defined composite types (UDTs) are also allowed. A database may contain views, stored procedures, indexes and constraints other than tables. The data are stored in three types of files. Those are .mdf files, .ndf and .ldfextension files to store primary data, secondary data and log data, respectively. In order to make sure the database will always revert to a known consistent state, it uses the concept of transactions. Transitions are implemented using the write-ahead log. SQL Server also supports concurrency. Querying using T-SQL is the main mode of data retrieval. SQL Server performs query optimization for improved performance. It also allows stored procedures, which are parameterized T-SQL queries stored in the server itself and are not executed by the client application like the normal queries. SQL Server includes the SQL CLR (Common Language Runtime) which is used to integrate the server with the .NET Framework. Because of this, you can write stored procedures and triggers in any .NET language such as C# or VB.NET. Also UTDs can be defined using .NET languages. Classes in ADO.NET can be used to access the data stored in the database. ADO.NET classes provide functionality of working with tabular or single row of data, or internal metadata. It also provides XQuery support, which provides access to XML features in SQL Server. SQL Server also provides additional services such as a Service Broker, replication Services, analysis services, reporting services, Notification Services, Integration Services and Full Text Search.

As mentioned above, the SQL Server Express is a scaled down, freely downloadable edition of SQL Server. Therefore, it obviously has some limitations compared to the full version. Thankfully, there is no limitation on the number of databases or the number of users supported by the server. But, the Express edition can only utilize a single processor, 1GB memory and 10GB database files. It is suitable for XCOPY deployment since the entire database is kept in a single file of which the type is .mdf. Another technical restriction is the absence of Analysis, Integration and Notification services. But all in all, the Express edition is great for learning purposes because it can be used free for building small scale desktop and web applications.