SQL Server Developer

While SQL Server has a very mature set of tools, one thing that is lacking is the ability to send a simple email alert based on a pre-defined set of rules. At first glace, it appears that email notifications exist, but what becomes a problem to many people is the requirement for a MAPI subsystem [...]

Triggers are special types of Stored Procedures that are defined to execute automatically in place of or after data modifications. They can be executed automatically on the INSERT, DELETE and UPDATE triggering actions. There are two different types of triggers in Microsoft SQL Server 2000. They are INSTEAD OF triggers and AFTER triggers. These triggers [...]

As much as we try and prevent it, duplicate data still finds its way into corporate data systems, but the good news is that purging duplicate rows can be a relatively simple task. Prevention, through the use of good match code algorithms and constraints, will stop most duplicate data from entering your databases, but there [...]

There are lot of situations where you need an exact timestamp. For example, you may want to differentiate a recordsets’ creation dates, store technical measurements, or just to see how fast your latest SQL is running. Coding the 4th dimension is another common task, and therefore milliseconds are essential. But there is a problem receiving [...]

One of the best features of SQL Server 2000 is its user-defined functions (UDFs). UDFs offer many of the benefits of both views and stored procedures, and more. Unlike views, UDFs can take zero or more input parameters and return a scalar value, or can even return a table. Unlike stored procedures, the output of [...]

View A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is stored in the database. It is a query stored as an object. Hence, a view is an object that derives its data from one or more tables. These tables are [...]

This article uses the table we created in Part 1, called CUBE. Data is usually normalized into columns. For example, suppose we have a table of appointments. The table consists of 2 columns, DATE and APPOINTMENT. A common request is to display the data in a monthly calendar format. That is “pivot” the columns into [...]

In this article, we build upon our stored procedure and will demonstrate how to implement SORT and TOTAL row functionality. SORT Our sales manager is happy with our previous work. Now he requests the ability to sort the results, ascending and descending. In the web page, we provide an input select box where he can [...]

You give so much to your database . . . sometimes you just want something back. What do you give your database?  Plenty. You give it new records (INSERT). You give it changes to existing records (UPDATE/DELETE). Every now and then, you just look around (SELECT). When you do these things, it is often essential [...]

In this article, I’ll demonstrate how to give web users the ability to search and group data via web reporting pages –- using good old web scripting and SQL Server. No ETL/OLAP tool needed. Before we begin, I need to explain data warehousing. In general, as raw transaction level data accumulates, we summarize the data [...]