Who ever imagined Microsoft would be offering a free version of its enterprise database management platform? From the original SQL Server 2000 MSDE to the current SQL Server 2005 Express edition, we never before had a fully featured Management Studio to build and deploy SQL Server database -- until now. Couple SQL Server 2005 Express Edition with the Visual Web Developer 2005 Express Edition or the Visual Basic 2005 Express Edition and developers get an unprecedented free tool set.
Although SQL Server 2005 Express has some limitations as it pertains to the memory, CPU and database size parameters, it is rich in capabilities that enable fast application development, and Microsoft did not stop there. Fortunately for the SQL Server community, Microsoft recently released the SQL Server 2005 Express Edition with Advanced Services. This offering includes Reporting Services and Full-Text Search.
SQL Server 2005 – Management Studio Express
The Management Studio Express Edition is by no means a major step down from the version shipped with the Standard edition. The tool set has the same basic look and feel, allowing you to easily move from Express to Workgroup, standard or enterprise editions. Knowledge gained from the Express Edition can be transferred among the tools and versions.
Management Studio Express has the following main interfaces:
- Object Explorer
- Template Explorer
- Summary View
- Query Editor
Figure 1: Management Studio Express
T-SQL programming
It appears Microsoft did not limit T-SQL enhancements shipped with SQL Server 2005 Express Edition. Research and take advantage of the TOP clause, Common Table Expressions (CTE), TRY/CATCH, synonyms, PIVOT/UNPIVOT, etc.
Much to my surprise, Microsoft also provides a graphical interface to build tables as well as a full database design, as shown in Figures 2 and 3. I expected to have to solely rely on the T-SQL Template Explorer to build the database design. This tool set has a point-and-click interface to quickly build the database design.
Figure 2: Graphical table creation
Figure 3: Graphical database design
Common Language Runtime (CLR)
SQL Server 2005 Express integrates directly with the CLR, which extends the functionality of the relational database engine with the ability to execute any .NET code directly from SQL Server. This is accomplished by building the .NET code and then loading the assembly into SQL Server via the CREATE ASSEMBLY command. Just keep in mind that this functionality is disabled by default, but can be enabled via the Surface Area Configuration Manager. For more information about the CLR integration reference, check out this introduction to SQL Server 2005's CLR functions.
Service Broker
Service Broker is a new offering to SQL Server 2005 where messages are sent asynchronously between a sender and receiver via a queue. This enables applications to operate in a loosely coupled manner and exchange information. Service Broker is a vast and valuable topic. Reference the resources below as a starting point.
Synonyms
Synonyms serve as an alias for other objects and could used be for views, tables, stored procedures or functions. A Synonym provides a great deal of flexibility because the object does not have to be on the same server, database or schema. If you are faced with a situation where underlying objects are changing, this could serve as a simple means to maintain consistency in the code.
Figure 4: Synonyms
Reporting Services
To me, the most exciting development enhancement to the Express Edition is the addition of Reporting Services in the Advanced Services Edition. This enables developers to build reports with Express licensing. The Reporting Services Windows services are installed, and the Visual Studio interface can be used to develop and deploy the reports. For more information, visit the Reporting Services Developer InfoCenter.
Full Text Search
Not far behind Reporting Services in terms of excitement with the Advanced Edition is Full Text Search. This technology enables advanced keyword querying on an external index with the potential for vast performance improvements over the LIKE clause, but integrates directly in T-SQL via CONTAINS keyword. For more information, visit the Full-Text Search Developer InfoCenter.
Additional information
Tools: If development is also being conducted with Management Studio from SQL Server 2005 Standard or Enterprise Editions, and those tools are on your desktop, you can use them to manage the Express Edition. Just register the SQL Server 2005 Express Edition in Management Studio in order to complete all development in one interface. The only limitations in the interface would be imposed by SQL Server Express Edition.
Storage: Approximately 3 GB of storage is needed to support all of the features in SQL Server 2005 Express with Advanced Services.
Handy resources:
SQL Server 2005 Express Edition Support
SQL Server Express Developer Center
SQL Server 2005 Express Edition Service Pack 1
Register Your Edition of SQL Server 2005 Express Edition
Conclusion
What's that saying, 'There is no such thing as a free lunch'? Microsoft may have just disproved that with the feature-rich capabilities of SQL Server 2005 Express Edition. I encourage you to leverage Express for your own pursuit of SQL Server knowledge, proof of concept needs or to support small database needs. All of the insight you gain with this edition can be fully transferable to the Work Group, Standard or Enterprise Editions of SQL Server 2005.
About the author: Jeremy Kadlec is the principal database engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server users groups and nationally at SQL PASS. He is the author of the Rational Guide to IT Project Management. Kadlec is also the SearchSQLServer.com Performance Tuning expert. Ask him a question here.
