Matt Whitfield (9/21/2010)

---

David

Yes, the DriveInfo class does require UNSAFE because of the permissions, I said that - my bad.

However, I can't agree with the rest of what you've said. I don't see that the constructor of a simple class would outweigh the cost of using an ArrayList instead of yield as well as the cost of boxing and unboxing all of the values stored in that ArrayList...

The other worry would be garbage collection. By putting everything in a static, the DriveInfo instances that are created are not naturally garbage collected because the reference to them is held by the static array. By doing away with that, everything is up for garbage collection as soon as the function terminates...

M

David BAFFALEUF (9/21/2010)

---

And there's always something good to learn in the end.

SQueaLer1 (9/21/2010)

---

Hi David,
I appreciate the concept but I have to disagree with the approach. We programmers can come up with a lot of "nice" solutions, but they don't always work in the corporate world. This is a factor you haven't touched on in your article.

Remember, we have total control over our PC with our local SQL, but in large enterprise environments, the situation is completely different.

Your solution is not so practical because -
* I don't have access to the server's local drive - I need it to be able to copy the DLL.
* I am not allowed to even turn on the CLR option.
* The function is unsafe and in corporate environments I just don't have the permissions to install it.
* DBAs frown upon CLR.

I can come up with more reasons, but you get the idea.

Now, it so happens that I needed the same feature just last week.
I implemented it in PURE SQL using just one short stored procedure. Similar code has been out there for some time. I tweaked it for my needs. I think it is generic enough to be used by anyone.
I named it sp_fixeddrives and put it in master. This enables me to call it from any db.
I also created a job that executes it hourly, and checks the free space %. If it falls below a threshold, I send out an alert email.

SQueaLer1 (9/21/2010)

---

Hi David,
I appreciate the concept but I have to disagree with the approach. We programmers can come up with a lot of "nice" solutions, but they don't always work in the corporate world. This is a factor you haven't touched on in your article.

Remember, we have total control over our PC with our local SQL, but in large enterprise environments, the situation is completely different.

Your solution is not so practical because -
* I don't have access to the server's local drive - I need it to be able to copy the DLL.
* I am not allowed to even turn on the CLR option.
* The function is unsafe and in corporate environments I just don't have the permissions to install it.
* DBAs frown upon CLR.

I can come up with more reasons, but you get the idea.

Now, it so happens that I needed the same feature just last week.
I implemented it in PURE SQL using just one short stored procedure. Similar code has been out there for some time. I tweaked it for my needs. I think it is generic enough to be used by anyone.
I named it sp_fixeddrives and put it in master. This enables me to call it from any db.
I also created a job that executes it hourly, and checks the free space %. If it falls below a threshold, I send out an alert email.

David BAFFALEUF (9/22/2010)

---

The problem is because SQLCLR is not so well understood by the DBAs in general. And for a DBA, who carries the responsability of the corporation's data management and safety, what is not so well understood will wait on the doorway until it is, if not thrown away. I've been a production DBA for the past 10 years, I know how conservative we can be with new *cool* features.