SQL Server 2005 System Tables and Views

When a SQL Server object is created, its properties are called metadata. The metadata is stored in special System Tables. For example, in SQL 2000, when a new column was created, the column name and data type could be found in an internal System Table called syscolumns. All SQL objects produce metadata. Every time SQL 2000 Enterprise Manager or SQL 2005 SQL Server Management Studio is browsed, the information displayed about database, tables, and all objects, comes from this metadata.

Using ANSI SQL as a Conceptual Hierarchical Data Modeling

This paper will explain and show how standard ANSI SQL processors can naturally model and automatically process complex multi-leg hierarchical data structures at a full conceptual hierarchical level. This also means the query user does not need to have structure knowledge of the hierarchical structures involved. The data modeling capability includes dynamically combining logical hierarchical relational and physical XML data structures at a full hierarchical level. This also includes the ability to link below the root of the lower level structure intuitively forming a valid unified hierarchical structure. As will be shown, ANSI SQL’s high level hierarchical data processing allows the flexible conceptual control of hierarchical node promotion, fragment processing, structure transformation, and variable structure creation.

Using CROSS APPLY in SQL Server 2005

My interest in writing this article was started by an MSDN article titled SQL Server 2005: The CLR Enters the Relational Stage. The article shows how to write a function that returns the top three countries per category. That's always been something that was difficult to do in SQL so I was curious about the approach. The article started out well but I was very unhappy by the end. It's just soooo much easier to do this in SQL Server 2005 using the new CROSS APPLY clause in Transact-SQL. So I'm going to write a query to return the top 3 orders for each customer and I'm going to do it in about 10 lines of SQL. (UPDATE: An alert reader found an even better approach!)

Usage Scenarios for SQL Server 2005 Native Web Services

Get a detailed look at how to set up SQL Server 2005 for Web service access in a heterogeneous environment, and learn more about key scenarios for Web services in SQL Server 2005.

SQL Server Performance Monitor

You can monitor the system performance by using the Performance monitor console and its related counters in Windows 2000. These counters allow you to view or save information about the overall performance of your server. When you install Microsoft SQL Server, additional Performance monitor objects and counters are automatically installed. While you must have administrative access to your SQL Server to use these objects, SQL Server admins should find them invaluable in monitoring and tuning the database server. Furthermore, the Performance monitor can be used either locally or remotely, which allows admins greater control in monitoring SQL Server. I am going to show you how to use the Performance monitor to keep a close watch over your SQL Server systems.

Featured Interview with Gert Drapers from Microsoft

An interview with Gert Drapers, SQL Server MVP and valuable SQL Server contributor.

Keep Bad Guys at Bay with the Advanced Security Features in SQL Server

In this article I'll explore the most interesting security enhancements in SQL Server 2005 from a developer's viewpoint. I covered admin security features in the Spring 2005 issue of TechNet Magazine. But there are plenty of dev-specific security enhancements I can explore, such as endpoint authentication and support for the security context of managed code that executes on the server.

Database Migration from Pocket Access to SQL Server CE

This article is about migrating Pocket PC applications that are written in eMbedded Visual Basic to the .NET Compact Framework. The migration includes a change from using ADO CE for accessing data in a Pocket Access database to using ADO.NET for accessing data in SQL Server CE. The sample code is supplied in both Visual Basic and C#.

SOA, Multi-Tier Architectures and Logic in the Database

If you are a developer creating Web services, a webmaster creating database-enabled pages or a database administrator (DBA) tuning SQL queries for a 24x7 web site, you've probably experienced the phenomenon known as "web time." The computer industry has never been quiet, but recent years have been particularly frenetic. The popularity of the web produced a flurry of software-development activity. New versions, new technologies, and new products appeared seemingly overnight. Web time became a useful phrase for describing compressed development cycles between new product releases and documentation that is obsolete before it arrives from the printer.

How to Asynchronously Execute a DTS package from ASP or ASP.NET

The Data Trasformation Services are a powerful tool, and sometime its features are so useful that you’d like to invoke a DTS package not only from SQL Server but from an external program.

To do this you have several choices: you can use the DTSRun.exe tool or you can do it leveraging the SQL-DMO features.

Unfortunately if you’re developing a web application (ASP, ASP.Net or whatever you use) none of them seems to be the right choice: too much problems, too much effort and a very modest results. In addition none of these solutions can be called asynchronously: if you just need to implement a “fire-and-forget” technique, you just cannot do that!