sql server (alternate): I'm composing a comparison table of database capabilities of several
different products. I've read the Transact-SQL reference and have
gathered most of the information needed for SQL Server 2000 (MSSQL),
but there are some things that I'm not very sure of. Could someone
confirm or complete the points below?

* MSSQL can index binary data but only for BINARY & VARBINARY fields
and not IMAGE.

* ALTER TABLE can't rename column name/index name/constraint name.

* A functional index (index on expression/function) can be defined
using user-defined function, e.g. CREATE INDEX idx ON t (MY_FUNC(f))

* Maximum number of indexes per table?

* SEQUENCE (as in PostgreSQL) is not supported, but there is a data
type called TIMESTAMP/ROWVERSION that is 64-bit. SEQUENCE can also be
implemented using a 'counter table'.

* Regexp is not recognized, only standard SQL LIKE pattern.

* Maximum number of schemas per database?

* There is no BOOLEAN data type, but one can use BIT as an
alternative.

* Does MSSQL use a generational/MVCC/record-versioning architecture,
to allow writers to not block readers and vice versa?

* Can several user-defined functions be created with the same name but
with different signatures (argument type)? For example, REVERSE(text)
and REVERSE(varbinary).

* Does MSSQL have/use WAL (write-ahead logs)?

[quoted text, click to view]

Correct.

[quoted text, click to view]

Correct - sp_rename is used for this.

[quoted text, click to view]

First you create a computed column using the function, then index the
computed column.

[quoted text, click to view]

250 (1 clustered, 249 non-clustered)

[quoted text, click to view]

ROWVERSION is not the same as a sequence - it is used to detect changes to
data in a row. An IDENTITY column is the most common way to implement an
auto-incrementing (or decrementing) column in MSSQL.

[quoted text, click to view]

There is no native TSQL support for regexes, but it is possible to
instantiate the COM RegExp object in TSQL code.

[quoted text, click to view]

If you take schema to mean object owner, then there is no limit other than
available storage. But it isn't always possible to compare what 'schema'
means in different database platforms directly.

[quoted text, click to view]

Correct. Or a flag value (eg. Y/N) with a check constraint.

[quoted text, click to view]

No, it uses a locking-based architecture (as does DB2, I think). Yukon (next
release) will have something similar to an MVCC mode, although it may not be
suitable for all applications.

[quoted text, click to view]

You can use a sql_variant parameter, then use SQL_VARIANT_PROPERTY() within
the function to find the base data type and execute different logic
accordingly.

[quoted text, click to view]

Yes - transaction logs are always write-ahead.

[quoted text, click to view]

No, although you can put a view on the non-NULL values, then place a unique
index on the view, which achieves more or less the same thing.

Simon

[quoted text, click to view]