February 2008 Blog Posts
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
I saw this link today and thought you could have a laugh too!
http://msdn.microsoft.com/events/hero/sfbio/
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.
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
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 (
...