Can I compress a SQL Server .DAT device/file?

A. Yes you can as long as you stop SQL first - NT needs exclusive access to a file in order to compress it. With SQL 6.5 and below then in theory everything should be fine as SQL isn't interested in what is happening down at the filesystem level. 

However, in practice, this is absolutely NOT recommended (even with 6.5) for the following reasons :-

1. It is not recommended/supported by MS. Reason enough in itself! PSS are unlikely to want to help you unless you can re-create your problem on an uncompressed database - so why bother?

2. It can prevent the ability to recover due to SQL not really knowing how much disk space there is.

3. Performance - especially if you compress an empty database/device and then start filling it up - there is then a lot of overhead in NTFS expanding the file as previously it had compressed very well due to the large number of binary zeroes used to pad empty pages. (I have seen systems halve in speed because the SQL devices/files were compressed)

4. SQL Server 7.0 depends on sector aligned writes for the logfile, which NT compressed files do not guarantee.

5. Many people have reported problems with SQL7 when doing disk intensive updates like index creation. Maybe due to the previous reason.

6. With a compressed drive the NT writes are always done in a "lazy" asynchronous manner. For an application like SQL that MUST know when an i/o has completed to the disk and not to some unprotected memory area this is unacceptable.

See Q231347 - "INF: SQL Server Databases Not Supported on Compressed Volumes" for more information and the official Microsoft line.

As for compressing data inside a table, SQL Server has no functionality to do this. You could do it yourself using code at the client end, or with an extended-stored procedure, but that's it.