Database Vulnerabilities: SQL Server 2005 vs. Oracle

I'm not usually one to grab the coattails of other bloggers, but this post is worth mentioning. It indicates that SQL Server 2005 has not had a single disclosed vulnerability in its first year of release, which is an impressive feat. Heck, I've found vulnerabilities and errors in software I've released in the first ten minutes.

SQL Server 2005 - 1 Year And Not Yet Counting...

SQL Trick: Finding Integers in Character Columns

Q: What WHERE condition do you use to find all values in a column that contain only numbers? (No decimal points, no minus signs, just 0-9.

A: [column name ] NOT LIKE '%[^0-9]%'

This is the kind of thing that I'll forget a year from now when I need it again.

UPDATE: 7/11/06

I ran into the need for this again, and I thought I should note that this technique can be used to check that a column contains only characters from any subset, not just numbers. I just used it to verify U.S. Bankruptcy case numbers, which contain only numbers and dashes. My search condition was [CaseNo] NOT LIKE '%[^-0-9]%'.

Free Intellisense for SQL Server!

I read this morning that Red Gate Software has released a *free* program that adds Intellisense to your standard SQL editors (Query Analyzer, Enterprise Manager, Management Studio and VS). I tried it out, and had it up and running in less than five minutes. Since I write a lot of queries everyday, and often find myself retyping the same things over and over, this is sure to be a huge timesaver. You can download the installer from here:

http://www.red-gate.com/products/SQL_Prompt/index.htm

Thanks to Dan Wahlin for writing this up and posting it to DotNetKicks.com, where I found it.

Moving SQL Server Objects

Well, I'm preparing for a significant move to production tomorrow morning, including a bunch of new database code. I really can't remember how I was confident that I moved everything before I discovered Red Gate's SQL Compare and SQL Data Compare. These tools make database migration a breeze, and I recommend them for anyone who needs to move DDL or domain data between environments.

I still need an easy way to archive database code along with the rest of the source for my application. VS 2003 just seems to take forever to script all of my objects. Maybe VS 2005 and Team System will have something for me, but for now, I'm busy doing billable work, so I don't have much time to explore.

5 Reasons to use Indexed Views

I love indexed views in SQL Server! My current project is a fairly large system for a government entity, containing over 70 live tables, and so the queries that I have written are intense. Many of them are so intense, in fact, that the database server's processor has decided to take its sweet old time producing results. I've been able to mitigate (but not eliminate, unfortunately) a lot of the query latency using indexed views, and I want to share some of the reasons that I chose to use them in this project. So here they are:

1. BECAUSE I CAN: For some time, I believed that you could only use indexed views in SQL Server Enterprise Edition, but this is not true. You can use indexed views in any edition of SQL Server. The only difference between editions is that the query optimizer in Enterprise Edition will automatically consider indexed views when appropriate, whereas you must explicitly specify when to use them in other editions. You can specify when to use an indexed view using the (NOEXPAND) view hint.

   
   
   

2. Narrowing a table: There are a few tables in this application that are updated infrequently and are very wide. The width of these tables, as well as the large number of records in them, caused them to slow down any query that they took part in. So I decided to create an indexed view on one of the tables, including the primary key and the few columns that are used in most of the queries referencing the table. Viola! By using the view instead of the base table, I improved query performance significantly, and the only costs were some disk space and a minimal performance hit at 3:00 AM when the tables are updated.

   
   

   Although I'm not doing so in my current application, you could also use indexed views to narrow a table horizontally. This approach requires some savvy, though, since you as a query developer need to know in advance that all of the rows you care about are in the indexed view. When a vertically-narrowed view doesn't have a column that your application needs, the query compiler will complain. When a horizontally-narrowed view doesn't have the row, end users will complain. I think you get the picture.

   
   
   

3. Using multiple clustering keys: There is one table in my current application that is used in a number of different queries. I was trying different clustered indexes on the table in an attempt to boost performance, but I was getting nowhere. When I chose one set of columns as my clustered index, then half of my queries ran quickly while the other half were dogs. When I chose another set of columns, the second set of queries started to perform while the first set slowed to a crawl. My solution: create an indexed view on the table that is the equivalent of SELECT * FROM <table>, create a clustered index on the first table containing the columns that make the first set of queries sing, and create the clustered index on the view to the columns that work for the second set of queries. By changing the second set of queries to use the view, both sets of queries now perform well.

   
   

   Now let me say that this view doubled the amount of space that my data in that table is using, since my database now has two copies of the table. Every insert, update, and delete will take twice as long, since both the real table and the indexed view need to be changed. But in this application, the performance benefit outweighed the performance cost.

   
   

4. Aggregating lots of data: Here's why indexed views were thought up, if you ask me. In this case, I have several "accounts", each of which has several credit and debit transactions. I want to be able to instantly retrieve the balance on any of my accounts, so I created an indexed view that looks something like the following:

   
   

   CREATE VIEW vwAccountTransactionBalance_Indexed
   AS
   SELECT
   AccountID,
   SUM(Amount) AS TransactionBalance,
   COUNT_BIG(*) AS TransactionCount
   FROM
   tblTransaction
   GROUP BY
   AccountID

   
   

   Simple, right? The one "gotcha" in this scenario is that an account that doesn't have any transactions won't show up in your view at all. So to make sure that all of your accounts are included, you should probably create another view like this:

   
   

   CREATE VIEW vwAccountTransactionBalance
   AS
   SELECT
   AccountID,
   ISNULL(TransactionBalance, 0) AS TransactionBalance,
   ISNULL(TransactionCount, 0) AS TransactionCount
   FROM
   tblAccount a
   LEFT JOIN
   vwAccountTransactionBalance_Indexed atbi (NOEXPAND)
   ON a.AccountID = atbi.AccountID

   
   

   Now you have a view that gives you the account balance for every account without needing to go to the transaction table at all. And there's one more benefit to using nested views like this. You can add the indexed view at any point in your development cycle. Go ahead and implement vwAccountTransactionBalance the "normal" way during initial development. Then, if performance of your queries becomes a problem, create an indexed view and reference it from within your original view. Your application performs better, and you don't need to change another line of code.

   
   
   

5. Space is cheap: There are two primary drawbacks to using indexed views, as there are with using any index: data modification performance and disk space. You ought to be mindful of the impact your indexed views are having on data modification performance, but my point here is that the disk space drawback is often a non-factor. I'm sure that the database I've been working with could be made smaller by eliminating some indexed views, but then the database server would just have that more empty disk space. If your disk is full, go buy another one! The performance benefits to your application will be well worth the price.

   
   

So that's why you should consider using indexed views. Please note though that indexed views are not the answer to every one of your query performance problems. If you create indexed views on tables that are frequently updated, you could pay a serious performance price in your data modification queries. In the application that I've been working on, though, data modification tends to be narrowly targeted: one row here, two rows there, rather then many (or even few) hundreds of rows at once. In this scenario, it's difficult to notice the delay that comes from SQL Server needing to change indexed view data as a result of my base table data changes.

Avoiding Cursors with Superclass-Subclass Tables

Ever since I read Itzik Ben-Gan's article in SQL Server Magazine about changing cursor-based logic to set-based logic, I've avidly sought to avoiding using cursors in my SQL procedures. One of the biggest problems that I've faced on this front is the issue of inserting records into a superclass-subclass table structure. Here's an example of the table structure that I'm talking about:


I spent hours trying to think of a solution to this problem that didn't use cursors, but all I could ever come up with is something like the following:

// Give every employee a bonus!
DECLARE @curEmployees CURSOR
SET @curEmployees = CURSOR FOR SELECT EmployeeID, BonusAmount FROM tblEmployee

DECLARE @employeeID int
DECLARE @bonusAmount money

OPEN @curEmployees
FETCH NEXT FROM @curEmployees INTO @employeeID, @bonusAmount
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @transactionID int

INSERT INTO tblTransaction (TransactionClass, [Date], Amount)
VALUES ('PR', '11/21/2005', @bonusAmount)

SET @transactionID = @@IDENTITY

INSERT INTO tblPayrollTransaction (TransactionID, TransactionClass, EmployeeID)
VALUES (@transactionID, 'PR', @employeeID)

FETCH NEXT FROM @curEmployees INTO @employeeID, @bonusAmount

END

CLOSE @curEmployees

After writing blocks of code like this about twenty times, I finally thought up a solution that avoids cursors. To be honest, I haven't done exhaustive performance testing on the solution or anything, but I did notice performance improvements with my new method. Check it out:

// Give every employee a bonus more quickly!
DECLARE @tblEmployee TABLE
(

EmployeeID int PRIMARY KEY,
BonusAmount money,
RowNum int IDENTITY(0, -1)

)

INSERT INTO @tblEmployee (EmployeeID, BonusAmount)
SELECT EmployeeID, BonusAmount FROM tblEmployee

INSERT INTO tblTransaction (TransactionClass, [Date], Amount)
SELECT 'PR', '11/21/2005', BonusAmount FROM @tblEmployee ORDER BY RowNum

INSERT INTO tblPayrollTransaction (TransactionID, TransactionClass, EmployeeID)
SELECT @@IDENTITY + RowNum, 'PR', EmployeeID FROM @tblEmployee ORDER BY RowNum

The critical elements of the new code are as follows:

1. The table variable must contain the most granular instance of an item to be inserted. In this example, each employee has a single transaction, so employees are stored in the table variable.
   
2. The table variable must have an identity column that starts at zero and counts backwards. (The assumption here is the TransactionID on tblTransaction is an IDENTITY(1,1) column.)
   
3. Both insert statements must have identical ORDER BY clauses that include the RowNum column.

If all of these assumptions hold true, then the two insert statements should "line up" and cause each superclass record to be properly joined to the corresponding subclass record.