Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

February 2008 Blog Posts

Efficient pagination for large set of data?

This is what I pondered about today. Maybe I also will have some time to test it.   CREATE PROCEDURE dbo.uspPaginate ( @PageNumber INT, @RecordsPerPage TINYINT = 50 ) AS SET NOCOUNT ON DECLARE @MaxRows INT SET @MaxRows = @PageNumber * @RecordsPerPage SELECT SomeColumns FROM ( SELECT TOP (@RecordsPerPage) SomeColumns FROM ( SELECT TOP (@MaxRows) SomeColumns FROM YourTable ORDER BY SomeCase ASC/DESC ) ORDER BY SomeCase DESC/ASC ) ORDER BY SomeCase ASC/DESC Topic is here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97550  

posted @ Tuesday, February 19, 2008 4:12 PM | Feedback (3) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Microsoft SQL Server Action Figure

I saw this link today and thought you could have a laugh too! http://msdn.microsoft.com/events/hero/sfbio/  

posted @ Tuesday, February 19, 2008 9:27 AM | Feedback (1) |

Finding table reference levels and simulating cascading deletes

I worked with this topic recent weekend and posted the final functions here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97454   The general idea is to have a generic purge functionality.

posted @ Saturday, February 16, 2008 7:32 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Curiosity found, the wrap

After a good nights sleep when almost all pieces fit together here weblogs.sqlteam.com/peterl/archive/2008/02/06/Curiosity-found.aspx I realized this morning that this behaviour also explains why there are gaps in identity sequences when inserting a record that violates a contraint. It seems that identity values are assigned to complete insert-set before checking for constraints such us unique indexes. DECLARE @Items TABLE (i INT IDENTITY(1, 1), j INT PRIMARY KEY) INSERT  @Items SELECT  1 UNION ALL SELECT  2 SELECT  * FROM    @Items INSERT  @Items SELECT  1 INSERT  @Items SELECT  2 INSERT  @Items SELECT  3 SELECT  * FROM    @Items

posted @ Thursday, February 07, 2008 9:15 AM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Curiosity found

For a few days I have been wondering about some annoying INSERT statement. I am aware of heaps and clustered indexes but I still can't get the last pieces together. This is the original code CREATE TABLE  #Sample               (                      RowID TINYINT IDENTITY(1, 1),                      z TINYINT,                      A TINYINT,                      B TINYINT               ) CREATE UNIQUE NONCLUSTERED INDEX IX_A ON #Sample (A) WITH (IGNORE_DUP_KEY = ON) CREATE UNIQUE NONCLUSTERED INDEX IX_B ON #Sample (B) WITH (IGNORE_DUP_KEY = ON) INSERT        #Sample               (                      z,                      A,                      B               ) SELECT        TOP 100 PERCENT               c.z,               c.A,              c.B FROM         (                     ...

posted @ Wednesday, February 06, 2008 10:48 PM | Feedback (2) |

Powered by:
Powered By Subtext Powered By ASP.NET