Sql Server
Over the last year we've had a lot of internal discussions about the database platforms we intend to support for our software. One of the things that's come up time and time again is that our enterprise customers don't feel SQL Server is an "enterprise scale database". Given this, it shouldn't be all that surprising that I found this report comparing the security of Oracle and SQL Server very interesting.
The conclusion is clear – if security robustness and a high degree of assurance are concerns when looking to purchase database server software – given these results one should not be looking at Oracle as a serious contender.
That wasn't exactly what I was expecting.
I use stored procedures for a lot of the data access I do within the applications I develop. Overall I like using stored procedures for data access, however, at times it makes “things” more difficult.
One of the “things“ that it makes more difficult is using optional parameters. In dynamic SQL we can handle optional parameters easily, simple don't append parameters that aren't needed. With stored procedures it isn't quite as straight-forward. My Optional Parameters in SQL Server Search queries post provides a solution.
Another task that is more difficult when using stored procedures is sorting. In many instances we want to be able to pass in the field that the records should be sorted on. Often times this is accomplished by using nested IF/ELSE statements. Although nested IFs do address the problem it also introduces a lot of duplication which we all know is bad. Fortunately there is a better way. Rather then introducing nested IF/ELSE statements to control the ORDER BY of your query use a CASE statement in the ORDER BY of your query.
CREATE PROCEDURE ap_OrderSearch
(
@SortBy varchar(50)
)
AS
select * from orders
ORDER BY
CASE
WHEN @SortBy = 'customerid' THEN CustomerID
WHEN @SortBy = 'shipname' THEN ShipName
WHEN @SortBy = 'shipaddress' THEN ShipAddress
END,
CASE
WHEN @SortBy = 'orderdate' THEN OrderDate
WHEN @SortBy = 'shippeddate' THEN ShippedDate
END,
CASE
WHEN @SortBy = 'orderid' THEN OrderID
WHEN @SortBy = 'employeeid' THEN EmployeeID
END
The above query returns records from the Orders table in the Northwind database. It accepts one parameter which is used to specify the field to sort the results by. The fields are grouped into 3 separate CASE statements by their data type to avoid conversion errors during the execution of the query.
Yesterday I was adding some paging to a datagrid listing that was getting out of control. Since the number of records was pretty significant using the DataGrid's built in paging wasn't an option. While I was working on the paging I decided to take a peak at the queries being used to bring back the results. I didn't write them so I wanted to get a feel for “the look“ of the data being returned. I opened up query analyzer and ran the query. Then I waited....and waited....and waited. As I sat there waiting for the query to return I thought to myself, “man there must be a frigging bazillion records here”. Then the results popped up with a whopping 8 rows of data. I sat there asking Query Analyzer “Are you telling my I just waited 50 seconds for 8 records!?!”, no answer. It was clear Query Analyzer was too embarrased to even try and give an explanation.
After a little investigation a colleage and I found that the problem was due to some missing indexes on the tables being queried. After adding the indexes the query brought back the 8 records almost immediately. The moral of the story...make sure you have your indexes right!
We just had an interesting discussion among our team regarding the best way to remove a very large number of rows from a database without having the transaction log grow out of control. The scenario is something like this:
- Need to delete 50 billion rows from a table
- Need to do it when there is only 4 G of hard drive space on the server
- Cannot turn off logging for the entire database
At this point the only option that we've come up with is creating a stored procedure that deletes a range of records and then shrinks the database, as well as the transaction log. The execution of the stored procedure would delete approximately 250,000 records at a time.
Unfortuntely when posed with the question of how to best accomplish this feat this is all I (as well as others) could come up with. Is there a better way?
Recently one of my co-workers dropped me an IM asking how he could find what stored procedures depended on a certain table. After a little digging we came up with the following query which will return a listing of stored procedures that depend on a particular table (T_TableName).
select distinct so.name from syscomments sc
inner join sysobjects so on sc.id = so.id
where xtype = 'P' AND charindex('T_TableName', text) > 0
It would be cool if within SqlBuddy, VS.NET, SQL Enterprise Manager, (name your favorite DB tool) you could right click on a table and view stored procedure dependencies!
Update: For those of you interested in the real way to do this use sp_depends, thanks Darrell!