Exploring 'ON DELETE SET NULL' in SQL Server 2005

At first glance, the 'ON DELETE SET NULL' option in SQL Server 2005 may not seem like a big deal, but once you've seen how it can be used, I think you will be impressed. The basics? If Order.CustomerID has a foreign key to Customer.CustomerID and a customer is deleted, 'ON DELETE SET NULL' causes the CustomerID field in each of that customer's orders to be set to NULL. It allows the "parent" record to be deleted and sets the "child" record's column to NULL. A normal foreign key says Order.CustomerID must match a record in Customer. An 'ON DELETE SET NULL' foreign key says that if Order.CustomerID is not null, then its value must match a record in Customer.

MDX Essentials: Set and String Functions: The GENERATE() Function

In this lesson, we will introduce a function that provides us the ability to derive a set by systematically applying a set expression to each of the members of a set we define. GENERATE() is a potent function in our MDX toolset, in that it enables us to select precisely only certain members of a dimension level. It effectively operates upon two sets to create a new set, based upon the members of a second set that are also in a primary set.

Quick! Which specific SQL Server user is logged into which database?

SQL Server's master database stores tables with important and constantly updated information about the state of the SQL server. One of these tables, sysprocesses, can be used to track (in SQL Server itself) which SQL Server users are currently logged in and which database(s) they are accessing.

The quick way to find out if a particular user is accessing a particular table is with a query.

Full Text Search on SQL 2000 Part 4

This article concentrates on using Full Text Search to query text located inside Microsoft Office documents. In previous articles, Microsoft Search was introduced as an add-on service to enable advanced text queries. Catalogs, the physical storage units for search, were created and indexed. The TSQL keywords CONTAINS, FORMSOF, and INFLECTIONAL were used to query the newly created Catalogs. Population Schedules along with Change Tracking options were discussed as methods to keep the Catalogs up to date with the underlying database.

Programming Replication from Heterogeneous Data Sources

Microsoft® SQL Server™ enables third-party products to become Publishers within the SQL Server replication framework. The Replication Distributor Interface allows replication from heterogeneous databases that provide 32-bit OLE DB drivers to Subscribers running SQL Server 2000. Heterogeneous data sources include:

* Oracle databases

* DB2 databases

* Microsoft Access databases

* Other databases that comply with SQL Server ODBC or OLE DB Subscriber requirements

SQL Server 2005 Integration Services: Lessons from Project REAL

In Project REAL we are using large volumes of real data and scenarios from real companies to implement business intelligence systems using early releases of Microsoft SQL Server 2005. In the process, best practices are being developed and potential problems uncovered. This article reports some of the lessons learned while working on the data extraction, transformation, and loading (ETL) portion of the first phase of Project REAL.

The value of experienced coders

I was about 40 the first time I was told I should not continue coding. A well-meaning vice president at my company pulled me aside at a party and told me that I was getting too old to code. He had been a coder himself, knew that he was getting too old, and moved over to sales. He was sure that I should be moving over to another part of the business. I told him that perhaps not only did he have a problem coding when he reached 40, he probably had the same problem at 38. I said that I was perfectly happy as a coder, and had every intention of continuing as long as someone was willing to pay me.

Automatically Utilizing XML's Untapped Semantic Goldmine

A mind is a terrible thing to waste and so is the vast quantity of untapped data structure meta information available free for the using in any XML document. This semantic data available in XML is like gold lying on the ground ready to be scooped up and cashed in with little or no effort. This is meta information that can be utilized immediately to significantly increase the value of the raw XML data. By automatically utilizing this valuable structure meta information, hierarchical fourth generation languages (4GLs) can nonprocedurally process XML's complex multi-leg hierarchical structure. This significantly increases the usefulness of the data and makes it immediately available for use at its enhanced value. This article describes the semantics of hierarchical data structures and how they can be automatically utilized by 4GLs.

Dr. Tom's Workshop: Multiple-Child Aggregation

Aggregation, which is commonly used to deliver reports to users, often also delivers poor performance. To help guarantee that you're delivering the best performance possible, you should explore different versions of the query and pick the best one. This month, look over Tom Moreau's shoulder as he shows you how to work with multiple-child aggregations.

Reporting Strictly for Developers: Microsoft SQL Server Reporting Serv

With the release of SQL Server Reporting Services, Microsoft finally makes a commitment to developer-centric reporting. They've integrated the design-time environment integration with Visual Studio and enabled report delivery through Web browsers, PDFs, Excel, and XML. Learn how to make this tool work for you.