Indexing Bit Columns

In previous versions of SQL Server (prior to 2000), you were unable to index a column with a bit datatype ie: a true/false, yes/no, boolean value. In Books Online for SQL Server 2000, under the description of the bit data type, it still said: Remarks Columns of type bit cannot have indexes on them. Microsoft® SQL Server™ optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on. … Continue reading Indexing Bit Columns

Static/Shared Methods

I had been disappointed that when you use a CLR-based UDT, you didn’t have access to shared/static methods, only instance methods. In the zVarchar example, I thought it would be good to be able to use the following: DECLARE @TextVar zVarchar SELECT @TextVar.Compress(‘Some value           I       want        to          compress’) This would be similar to how you’d use Decimal.Round() in the framework. Well, Venkatesh (from MS) pointed out to me that you can. The shared/static methods are available via the older :: syntax directly off the class name. That’s the syntax that was used for all method calls in Beta 1. So … Continue reading Static/Shared Methods

zVarchar example

As mentioned in a previous entry, I decided to try to build a compressed text data type as an example of a UDT. So here it is. I built a datatype called zVarchar. To keep things as a reasonable demo, I didn’t incorporate complex compression, just a simple run length encoding scheme and built it in VB so most should be able to read it. But it’s interesting. If you want to try it, start a new SQL Server project in VS2005, add a User Defined Type and include the code below. After building and deploying it, you can do … Continue reading zVarchar example

Yukon Ascend

In Sydney this week for the Yukon (SQL Server 2005) Ascend training. Covering a lot of the scalability and high-availability aspects of the product. I continue to be impressed by it. In the material on partitioning tables, it says you can’t use more than a single column in the partition function. The idea is that you could partition a sales table on say, the region column and potentially store the data from each partition on a separate filegroup. However, we noticed today that you can use a calculated column as the input to your partition function, as long as you … Continue reading Yukon Ascend