Sequence tables in SQL Server 2005

Interesting post by Paul White about Sequence Tables in SQL Server.

SQL Server indexing...

There's a series of articles called SQL Server Index Black Ops... and indeed they are!

Posts:

• Index Black Ops Part 1 – Locking and Blocking
• Index Black Ops Part 2 – Page IO Latch, Page Latch
• Index Black Ops Part 3 – Index Usage
• Index Black Ops Part 4 – Index Overhead and Maintenance
• Index Black Ops Part 5 – Page Splits
• Index Black Ops Part 6 – Fill Factor vs. Page Splits

Undocumented optimizer feature in SQL Server 2005/2008

An interesting series of posts has been done by Paul White...

This is the most interesting...

Running total in SQL Server...

Not really sure how good this is... but seems to do the job!

;with theData (rowNum, GroupA, GroupRowNum, theValue) as
(
   select row_number() over (order by MajorGroup, GroupOrder),
   DataValue,
   row_number() over (partition by MajorGroup
      order by MajorGroup, GroupOrder),
   GroupOrder
   from DataTable
)
select X.GroupA, X.GroupRowNum, X.theValue +
   coalesce(
      (select sum(theValue)
      from theData Y
      where Y.GroupA=X.GroupA and Y.GroupRowNum > X.GroupRowNum), 0
   )
from theData X

For some reason I can't get an execution plan as I get an error - I've filed a Connect bug with Microsoft.

List of query processing errors in SQL Server 2005

For my own reference: http://msdn.microsoft.com/en-us/library/ms172350.aspx

Guaranteeing order in views

In SQL Server 2005, using the TOP 100 PERCENT clause with an ORDER BY xxx doesn't gurantee that the results will be ordered by xxx. See this article for more info.

So what to do?

I believe that you can use the following syntax:

create view guaranteedOrderView
select xxx, row_number() over (order by xxx) as OrderNo
from exampleTable

NOTE: I've not tested this assumption. One of the extremely smart developers at my work told me that the optimizer might well... optimize... out this order.

varchar(MAX) truncating

I was reading Karen Delaney's excellent Inside SQL Server 2005 book about the SQL Server 2005 storage engine, and she explained quite a neat feature: varchar(MAX).

varchar(MAX) is a funny sort of varchar, only you aren't restricted to only 8000 bytes (or characters - to varchar it's all the same) but in fact the maximum length of the column is the maximum length of any LOB type (2^31). Basically, if a column is set to varchar(MAX) and you have less than 8000 bytes then SQL Server internally stores the column as a varchar column, and if you exceed 8000 bytes then it stores it as LOB data.

The interesting thing about varchar(max) is that it appears that unlike TEXT columns you can actually apply normal string manipulating SQL on it (i.e. replicate, left, right, etc). Yes, that's right, no more mucking about with TEXTPTR manipulation!

The trap to watch out for is that if you decide to do something like join a varchar(MAX) value to a varchar value in order to create a new varchar(MAX) value, if the new value becomes greater than 8000 characters then the new value will be truncated to 8000 bytes.

e.g.

declare @theText varchar(MAX)
set @theText = replicate('x', 7095) + 'the end bit'
select len(@theText)

(shamelessly stolen from this blog)

While you might think that the length of the string will show as 8006, the length is actually 8000.

Why does this occur?

The answer is actually found in SQL Server books online, under the section Precision, Scale and Length. This says that:

When two char, varchar, binary, or varbinary expressions are concatenated, the length of the resulting expression is the sum of the lengths of the two source expressions or 8,000 characters, whichever is less.

When two nchar or nvarchar expressions are concatenated, the length of the resulting expression is the sum of the lengths of the two source expressions or 4,000 characters, whichever is less.

Therefore, be warned! unless you cast the string to a varchar(MAX), then you might not get the results you expect!

For the above example, incidently, you would do the following:

declare @theText varchar(MAX)
set @theText = replicate(cast 'x' as varchar(MAX)), 7095) + cast('the end bit' as varchar(MAX))
select len(@theText)