DECLARE@Sample TABLE ( StudentID INT, Class VARCHAR(20) ) INSERT@Sample SELECT1, 'Maths' UNION ALL SELECT1, 'English' UNION ALL SELECT1, 'Science' UNION ALL SELECT2, 'Maths' UNION ALL SELECT2, 'English' UNION ALL SELECT2, 'Science' UNION ALL SELECT2, 'History' UNION ALL SELECT3, 'English' UNION ALL SELECT3, 'Maths' UNION ALL SELECT3, 'Science' UNION ALL SELECT3, 'RE' UNION ALL SELECT4, 'Science' UNION ALL SELECT4, 'Maths' UNION ALL SELECT4, 'English' UNION ALL SELECT4, 'History' UNION ALL SELECT4, 'French' ;WITHYak(ClassName, ClassPath, Combinations) AS ( SELECT Class, CAST(Class AS VARCHAR(MAX)), CAST(1 AS INT) FROM @Sample GROUP BY Class UNION ALL SELECT s.
Read more →
I had a strange scenario today and I can't reproduce it. I changed current database to adventureworks and ran following code DECLARE@SQL NVARCHAR(200) SET@SQL = 'SELECT DB_NAME()' EXECsp_executesql@SQL EXEC (@SQL) The sp_executesql statement returned "master" and exec statement returned "adventureworks".
Read more →
SELECTcpu_count AS [Logical CPUs], cpu_count / hyperthread_ratio AS [Physical CPUs] FROMsys.dm_os_sys_info
Read more →
Lately I have seen a number of questions regarding incremental update of some sort of counter placed in a central table. The original posters will for some reason not use an IDENTITY column which has a minimum overhead and use of system resources.
Read more →
Today I come across an interesting approach to a networking algorithm. The question was about how to use recursive to expand a network and still avoid circular reference. See topic here http://www.
Read more →
If you use sp_start_job you have noticed that the code will continue ro run and the job you started is run asynchrously. What if you want to wait for the job to finished?
Read more →
Yesterday I came across this question on another forum. I am trying to come up with a way to identify the half hour impact from several exceptions across multiple days.
Read more →
A good article about finding streaks in your data is this article by MVP Jeff Smith http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data. Also see http://weblogs.sqlteam.com/peterl/archive/2008/05/13/Lightning-fast-collapsed-date-ranges-and-missing-date-ranges.aspx
And this is an alternative way to find your streaks
Read more →
-- Prepare sample data DECLARE@Sample TABLE ( ID INT, col INT ) INSERT@Sample SELECT0, 1 UNION ALL SELECT0, 1 UNION ALL SELECT0, 2 UNION ALL SELECT1, 1 UNION ALL SELECT1, 2 UNION ALL SELECT1, 3 UNION ALL SELECT2, 5 UNION ALL SELECT2, 5 UNION ALL SELECT2, 5 UNION ALL SELECT3, 6 UNION ALL SELECT3, 6 UNION ALL SELECT5, 8 UNION ALL SELECT5, 9 UNION ALL SELECT4, 7 -- Pivot the source data SELECTID, MIN(col) AS col1, CASE COUNT(*) WHEN 1 THEN NULL WHEN 2 THEN MAX(col) ELSE SUM(col) - MIN(col) - MAX(col) END AS col2, CASE COUNT(*) WHEN 3 THEN MAX(col) ELSE NULL END AS col3 FROM@Sample GROUPBY ID ORDERBY ID
Legacy Comments
dineshrajan
2010-08-10
re: Easy sorted numeric pivoting with maximum three columns Good Post.
Read more →
See his blog entry here http://www.sqlmag.com/Article/ArticleID/100884/sql_server_100884.html
And this older http://www.sqlmag.com/Article/ArticleID/95734/sql_server_95734.html
Read more →
I come across a question today if SQL Server 2008 MERGE command call table triggers one per hit, or one per statement as it normally does. The short conclusion is that the MERGE command splits the source data into three “streams” and internally executes INSERT, UPDATE and DELETE statements.
Read more →
With respect to my old algorithm found here http://weblogs.sqlteam.com/peterl/archive/2008/08/12/How-to-sum-up-an-unknown-number-of-records.aspx which is about how to sum up an unknown number of items, I have come up with a new algorithm. This new algorithm is about finding all possible sums and how many combinations you have of each sum.
Read more →
IFEXISTS(SELECT * FROM YourTable WHERE Number = 0) SELECT 0.0E ELSE SELECT CASE IsNegativeProduct WHEN 1 THEN -EXP(theSum) ELSE EXP(theSum) END FROM ( SELECT SUM(LOG(ABS(Number))) AS theSum, SUM(CASE WHEN Number < 0 THEN 1 ELSE 0 END) % 2 AS IsNegativeProduct FROM YourTable ) AS d
Read more →
SELECT* FROM::fn_dblog(DEFAULT, DEFAULT) AS l INNERJOIN sysobjects AS so ON so.name = l.[transaction name] SELECTso.name AS ObjectName, so.type AS ObjectType, MAX(CAST(l.[Begin Time] AS DATETIME)) AS LogTime FROM::fn_dblog(DEFAULT, DEFAULT) l innerjoin sysobjects so on so.
Read more →
DECLARE@Value NVARCHAR(200) SET@Value = 'a+(6aaaa02.......()/)(/)=(#¤NIOU#H¤CÖIÖERFÖNKER)123456789/fffffffffffff' WHILE@Value LIKE '%[^0-9]%' SET @Value = REPLACE(@Value, SUBSTRING(@Value, PATINDEX('%[^0-9]%', @Value), 1), '') SELECT@Value
Legacy Comments
Uri Dimant
2008-11-12
re: Stripping out all non-numeric characters from a string declare @string varchar(200)
Read more →
SELECTGETDATE() AS theFullDateTime, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS theDateOnly, DATEADD(DAY, DATEDIFF(DAY, GETDATE(), 0), GETDATE()) AS theTimeOnly SELECTGETDATE() AS theFullDateTime, DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101') AS theDateOnly, DATEADD(DAY, DATEDIFF(DAY, GETDATE(), '19000101'), GETDATE()) AS theTimeOnly
Legacy Comments
Bill Curnow
2008-11-12
re: Getting date or time only from a Datetime value SELECT GETDATE() AS fullDateTime,
Read more →
-- Prepare sample data DECLARE@Master TABLE ( ID INT, Name VARCHAR(20) ) INSERT@Master SELECT1, 'Peso' UNION ALL SELECT2, 'SQLTeam' UNION ALL SELECT3, 'SQL' DECLARE @Child TABLE ( MasterID INT, ID INT, Value VARCHAR(20) ) INSERT@Child SELECT1, 1, 'Row 1 for Peso' UNION ALL SELECT1, 2, 'Row 2 for Peso' UNION ALL SELECT2, 3, 'Row 1 for SQLTeam' -- Display the XML SELECTePurchaseOrder.
Read more →
If your xml data contains a namespace, you also need to query the data using the same xml namespace. DECLARE@Sample TABLE ( rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, [xml] XML ) INSERT@Sample SELECT'<stringList xmlns="http://schemas.
Read more →
Sometimes you face the situation where you have some items ranked, and someone always tries to bump the ranking by voting a "perfect 100" to a particular item. And when you rank the items with average function or root mean square, that single "perfect 100" vote still bumps the item at top of ranking.
Read more →
DECLARE@s VARCHAR(100) SET@s = 'aardddvaaaarrkkkk' -- Highest frequency of same character SELECTTOP 1 WITH TIES [char], COUNT(*) AS cnt FROM ( SELECT SUBSTRING(@s, 1 + Number, 1) [char] FROM master.
Read more →