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.
Read more →
I saw this link today and thought you could have a laugh too!
http://msdn.microsoft.com/events/hero/sfbio/
Legacy Comments
James R
2008-03-03
re: Microsoft SQL Server Action Figure This is actually something that MS made and sent; for those of you that think this is purely a joke (not being real that is).
Read more →
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.
Legacy Comments
Jason
2008-02-16
re: Finding table reference levels and simulating cascading deletes I understand why some people might want this, but I think it is truly a bad idea to even do something like this.
Read more →
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.
Read more →
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.
Read more →
CREATEFUNCTION dbo.fnSplitType ( @Data VARCHAR(200), @PartSize TINYINT ) RETURNSVARCHAR(8000) AS BEGIN DECLARE @Result VARCHAR(8000), @Alpha TINYINT, @OldPosition SMALLINT, @NewPosition SMALLINT SELECT @Result = '', @Alpha = 1, @OldPosition = 1, @NewPosition = 1 IF @Data LIKE '[0-9]%' SELECT @Result = REPLICATE(' ', @PartSize), @Alpha = 0 WHILE @NewPosition < LEN(@Data) SELECT @NewPosition = CASE @Alpha WHEN 1 THEN PATINDEX('%[0-9]%', SUBSTRING(@Data, @OldPosition, 8000)) ELSE PATINDEX('%[a-z]%', SUBSTRING(@Data, @OldPosition, 8000)) END, @NewPosition = CASE @NewPosition WHEN 0 THEN LEN(@Data) ELSE @OldPosition + @NewPosition - 2 END, @Result = @Result + CASE @Alpha WHEN 1 THEN LEFT(LTRIM(SUBSTRING(@Data, @OldPosition, @NewPosition - @OldPosition + 1)) + REPLICATE(' ', @PartSize), @PartSize) ELSE RIGHT(REPLICATE(' ', @PartSize) + RTRIM(SUBSTRING(@Data, @OldPosition, @NewPosition - @OldPosition + 1)), @PartSize) END, @Alpha = 1 - @Alpha, @OldPosition = @NewPosition + 1 RETURNRTRIM(@Result) END Here is the code to test with DECLARE@Sample TABLE (Info VARCHAR(200)) INSERT@Sample SELECT'S0C 4610' UNIONALL SELECT'S9C 113' UNIONALL SELECT'S1C 462' UNIONALL SELECT'112' UNIONALL SELECT'113' UNIONALL SELECT'MM20BC' UNIONALL SELECT'SSC 113' UNIONALL SELECT'SSC 201' UNIONALL SELECT'SSC 461' UNIONALL SELECT'SSC 4610' UNIONALL SELECT'SSC 462' UNIONALL SELECT'SSCPZ202C' UNIONALL SELECT'Z1' UNIONALL SELECT'Z100' UNIONALL SELECT'ZZ' SELECTInfo FROM@Sample ORDERBY dbo.
Read more →
I didn't read this until I noticed Mladens link. http://weblogs.sqlteam.com/jhermiz/archive/2007/12/17/What-If-The-Dream-Company.aspx
Legacy Comments
Jon
2007-12-17
re: Thanks Jon! Definately well deserved!
Read more →
Today, I was involved in an interesting discussion.
Someone asked for a moving average solution. I joined the discussion late. The previous solutions and mine were all set-based and very slow.
Read more →
declare
@sample table(p geography) insert@sample selectgeography::STGeomFromText('POINT(12.65100 65.34900)', 4619) unionall selectgeography::STGeomFromText('POINT(35.65100 23.34900)', 4619) unionall selectgeography::STGeomFromText('POINT(64.65100 12.34900)', 4619) DECLARE@h geography SET@h = geography::STGeomFromText('POINT(47.65100 -122.34900)', 4619) SELECTp.STDistance(@h) / 1000 as[km] from@sample
Read more →
In this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926I gave a suggestion how to get a more detailed error message when working with OPENROWSET.
But the same thinking is applicable for LINKED SERVER and other "outer referenced" data retreival methods.
Read more →
DECLARE@Sample TABLE (Col1 VARCHAR(6), Col3 VARCHAR(200)) INSERT @Sample SELECT'123', '125,124,126' UNION ALL SELECT'124', '127,21,245' --SELECT Col1, -- Data --FROM @Sample --CROSS APPLY fnParseList(',', Col3) SELECTa.Col1, SUBSTRING(',' + a.
Read more →
-- Prepare sample data DECLARE@Accounts TABLE (AccountNumber CHAR(11), ParentAccountNumber CHAR(11)) INSERT@Accounts SELECT'100-000-000', NULLUNION ALL SELECT'100-001-000', '100-000-000' UNIONALL SELECT'100-002-000', '100-000-000' UNIONALL SELECT'100-002-001', '100-002-000' UNIONALL SELECT'100-002-002', '100-002-000' DECLARE @Transactions TABLE (AccountNumber CHAR(11), Amount MONEY) INSERT@Transactions SELECT'100-001-000', 1000.
Read more →
Let’s play with the new OUTPUT operator!<o:p></o:p>
-- Setup TableA & TableB
CREATETABLE#TableA
(
i INT
)
CREATETABLE#TableB
(
i INT
)
CREATETABLE#TableC
Read more →
Sometimes you have a denormalized table with several BIT columns used as flags.
Say you want to select every row that has at least one flag set. That's easy.
SELECT * FROM Table1 WHERE Flag1 = 1 OR Flag2 = 1 OR Flag3 = 1.
Read more →
I was involved in a discussion today about the ISNUMERIC() function Someone proposed a nested solution like this
SELECT Column_Name from ( SELECT Column_Name FROM ( select 'staff' as Column_Name union all select '234000' as Column_Name union all select '12d1' as Column_Name union all select '45e0' as Column_Name union all select '$123.
Read more →
Consider this test data CREATE TABLE #Temp (ID INT, Directory TEXT) INSERT #Temp SELECT 1, 'Sports' UNION ALL SELECT 2, 'Sports/Football' UNION ALL SELECT 3, 'Sports/Football/American' UNION ALL SELECT 4, 'Sports/Football/American/College_and_University' UNION ALL SELECT 5, 'Sports/Football/American/College_and_University/NCAA_Division_III' UNION ALL SELECT 6, 'Sports/Football/American/College_and_University/NCAA_Division_III/Atlantic_Central_Football_Conference' UNION ALL SELECT 7, 'Sports/Football/American/College_and_University/NCAA_Division_III/Atlantic_Central_Football_Conference/Frostburg_State' UNION ALL SELECT 8, 'Sports/Darts' UNION ALL SELECT 9, 'Sports/Darts/Organizations' UNION ALL SELECT 10, 'Sports/Darts/Organizations/United_States' UNION ALL SELECT 11, 'Sports/Darts/Organizations/United_States/Alabama' Say you want to return all records that are at least three directories down, ie having at least 2 dividers.
Read more →
I came across this solution for some months ago on another forum. It is very handy, so I thought i should share it.
CREATE TABLE myTable(Document varbinary(max)) INSERT INTO myTable(Document) SELECT * FROM OPENROWSET(BULK N'C:\Image1.
Read more →
Today I investigated the potential benefits of horizontal partitioning for a client using SQL Server 2000. I profiled the client's queries to the database and found out that current quarter only is used in 72% of the queries, 21% of queries is current and previous quarter only and remaining 7% of queries is for any quarter.
Read more →
In previous topic here http://weblogs.sqlteam.com/peterl/archive/2007/09/20/Finding-records-in-one-table-not-present-in-another-table.aspx
Michael Valentine Jones suggested an alternative route to get all records from one table not found in another.
Here are the results (including my suggestion with only MIN(t1) = 1 as MVJ2)
Read more →
I got some response from same topic posted on September 20 http://weblogs.sqlteam.com/peterl/archive/2007/09/24/Finding-group-of-records-with-a-certain-status.aspx Hugo Kornelis posted an alternative code to my improvement and I promised to test it. Here is the new results (made on other machines so the absolute numbers do not match).
Read more →