DECLARE@Stage TABLE ( RowID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED, Data VARCHAR(90), Section INT ) INSERT@Stage ( Data ) EXECxp_cmdshell 'ipconfig /all' DECLARE@Section INT SET@Section = 0 UPDATE @Stage SET@Section = Section = CASE WHENASCII(LEFT(Data, 1)) > 32 THEN @Section + 1 ELSE@Section END SELECTMAX(CASE WHEN x.
Read more →
CREATEPARTITION FUNCTION pfOrderDate ( DATETIME ) ASRANGE RIGHT FOR VALUES ( '20000101', '20010101', '20020101' ) GO CREATEPARTITION SCHEME psYak ASPARTITION pfOrderDate ALLTO ([PRIMARY]) GO CREATETABLE Orders ( OrderID INT NOT NULL, CustomerID VARCHAR(15) NOT NULL, OrderDate DATETIME NOT NULL ) ONpsYak(OrderDate) GO CREATECLUSTERED INDEX IX_OrderID ONOrders ( OrderID ) CREATENONCLUSTERED INDEX IX_OrderDate ONOrders ( OrderDate ) INCLUDE( OrderID, CustomerID ) GO INSERTOrders ( OrderID, CustomerID, OrderDate ) SELECT1, 'Peso', '20011225' UNIONALL SELECT2, 'Jennie', '20020314' SELECTOrderID, OrderDate FROMOrders WHEREOrderDate = '20011225' SELECTOrderID, OrderDate FROMOrders WHEREOrderID = 1 DROPTABLE Orders DROPPARTITION SCHEME psYak DROPPARTITION FUNCTION pfOrderDate
Read more →
The last two days I have been involved in a rather interesting discussion. The original poster wanted a fast way to get missing date ranges in a series of date pairs.
Read more →
I just played around with some different techniques to fetch relevant data from XML content.
DECLARE@XMLString XML, @Search VARCHAR(50) SELECT@XMLString = ' <Customers> <Customer> <FirstName>Kevin</FirstName> <LastName>Goff</LastName> <City type="aca">Camp Hill</City> </Customer> <Customer> <FirstName>Steve</FirstName> <LastName>Goff</LastName> <City type="acb">Philadelphia</City> </Customer> </Customers>', @Search = 'Camp Hill' -- Get all customers living in Camp Hill SELECTcust.
Read more →
This is an updated version for SQL 2005 and later to search all code for a specific keyword SELECT p.RoutineName, 'EXEC sp_helptext ' + QUOTENAME(p.RoutineName) AS [Exec] FROM ( SELECT OBJECT_NAME(so.
Read more →
I just helped a guy here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98346 with schema and thought that someone could benefit from this code
exec
sp_MSforeachtable"PRINT '? modify'; ALTER SCHEMA new_schema TRANSFER ?; IF @@ERROR = 0 PRINT '?
Read more →
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 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 →
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 →
-- 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 →
MERGE Production.ProductInventory AS [pi]USING ( SELECT ProductID, SUM(OrderQty) AS OrderQty FROM Sales.SalesOrderDetail AS sod INNER JOIN Sales.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID WHERE soh.OrderDate = GETDATE() GROUP BY ProductID ) AS src (ProductID, OrderQty) ON src.
Read more →