Category: "Useless for most people..."

Found prove that SQL Server is the Devils Engine - ERROR 666

Msg 666, Level 16, State 2, Line 1 The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 1234567890. Dropping and re-creating the index may resolve this; otherwise, use another clustering key. It all started a few hours ago with the discussion on how Sql Server uses uniqueifiers to handle none unique clustered index keys. After consulting SQL Server Internals books the answer was that SQL Server adds 4 extra bytes per row. So naturally the next question came up: signed or unsigned integer in the 4 bytes... No answer to that question so a test was mandadory I will not show the source because nobody should do that. But i will dicribe the process: Create a table with one tinyint column create a clusterd index on the column (do not specify unique). Insert a row with the value 1. Repeat the last step 214783647 times and enjoy the error message above and it's a signed int... The uniqueifier is per value so even after you could no longer insert a value of 1 into the table you could insert 2 billion and change records with the values 2 and so on.

increasing the performance of count(*)

 

I got a very strange request: We have to constantly count the rows in a table can we increase the performance of count(*)

So lets analyze the problem step by step.

The table is a typical table the actual columns have no effect so I replaced them with just one column in the example to make things easier; Let's start by creating a heap

 

CREATE TABLE  MyTable

(

ID intNOTNULLIDENTITY(1,1),

Payload varchar(300)NOTNULL

)

 

and fill it with random data

 

DECLARE @i ASint= 1;

WHILE (@i < 1000)

BEGIN

INSERTINTO MyTable(Payload) VALUES (REPLICATE('ABC', 20 + @i % 80));

SET @i += 1;

END

 

Now lets see where we are on a heap

 

STATISTICSIOON

 

SELECT COUNT(*)FROM  MyTable

 

Table 'MyTable'. Scan count 1, logical reads 25, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

To count the rows SQL Server must do a full table scan and reads each page exactly once.

Let's see if an index will help us…

 

CREATE  CLUSTEREDINDEX CI_MyTable ON MyTable(ID); SELECT COUNT(*)FROM MyTable;

 

Table 'MyTable'. Scan count 1, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

SQL Server now performs a clustered index scan which is in fact a table scan plus the overhead to find the first page. Actually a little bit more work than the table scan.

The trick to get it faster is to create an index that uses less space that the table (SQL Server will always select the smalest index to execute a COUNT(*))

 

The Smallest INDEX you can create on a table that has a clustered index is a secondary index that is composed just out of  the clustered key:

 

CREATE INDEX IX_ID ON MyTable(ID);

 

SELECT COUNT(*)FROM MyTable;

 

 

Table 'MyTable'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

Now it just has to Scan the leaf nodes of the secondary index

 

Careful this kind of index has very limited use besides the example above.

Other uses would be for example: SELECT xyz WHERE uvw  IN (SELECT ID FROM MyTable)