Creating a User Defined Aggregate with SQL Server 2005

SQL Server doesn't have a Product aggregate function. A recent blog post by Karen Watterson pointed out that Microsoft Knowledge Base article Q89656: Simulating a PRODUCT() Aggregate Function discusses how to achieve the Product aggregate functionality using the POWER function. We e-mailed about it and she challenged me to create a Product aggregate in SQL Server 2005. I decided to accept the challenge.

SQL Server 2000 Security - Part 6 - Ownership and Object Permissions

We have described, so far, authorization based on a predefined fixed server (determining a set of SQL server-wide privileges) and database (applying to database objects and activities) roles. We have also discussed application roles, which makes the level of permissions independent of those assigned to a SQL Server login or a database user account. Now it is time to look into permissions from the point of view of database objects. There are two main factors that play a role in determining how access rights to them are evaluated - their ownership and custom permissions. We will discuss the first one of these topics in this article and will continue with the other one in the next installment of this series.

Securing SQL Backups

SQL Server does many things very well, but securing itself is not one of them. While securing your server requires some effort, there is an area that many people forget. Securing your backups! Brian Kelley, our resident security expert, brings some advice and ideas for ensuring your data will not be stolen.

Creating a Daily Visual SourceSafe (VSS) Label with A SQL Agent Job

I worked in a team recently that was failing to put Visual SourceSafe (VSS) labels on projects after they were checked in we decided to look to automation for a solution. What we cam up with is to continue to require that labels be put on versions of code when major builds where created. But we decided to augment that with automated daily labels.

Help Improve Yukon Setup

SQL Server 2005 is coming soon and the setup team is looking for some feedback on their process. If you can spare a few minutes for a survey, they would greatly appreciate the time. And you will be helping others avoid the problems in the Betas.

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.

Performance Tips Part 2

SQL Server 2000 is a self tuning, self balancing application that performs wonderfully under a wide variety of loads and conditions with a minimal of administrative tuning. However there are some things that you can do when building T-SQL code and working with SQL Server to maximize performance. Leo Peysakhovich brings us part two of his series looking at some different scenarios and how you can structure your code for better performance.

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.