SQL Server 2005 introduced the new DMV views. They are great and a real improvement to debug and optimize queries.
I find sys.dm_db_index_physical_stats very useful and often write this type of code
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 →
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 →
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 →