Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

June 2009 Blog Posts

Microsoft Connect - Enhanced Syntax For Insert Into Statement

I've posted a feedback on Microsoft Connect about how to enhance the INSERT INTO syntax. Especially for INSERT INTO ... EXEC ... Sometimes when you need the result from a stored procedure, the SP itself returns two (or more) resultsets. And it's only possibly to fetch and store the first resultset. What I have suggested is an enhanced syntax for INSERT INTO ... EXEC, like this INSERT INTO Table1 (Col1, Col2), Table2 (ColX, ColY, ColZ) EXEC usp_MyStoredProcedure @Param1, @Param2 In this example, usp_MyStoredProcedure returns three resultsets, of which I want to store the two first. First resultset has two columns, and second resultset has three columns. Let Microsoft know...

posted @ Saturday, June 27, 2009 10:06 PM | Feedback (6) | Filed Under [ Optimization SQL Server 2008 Miscellaneous ]

Microsoft Connect - SSMS Debugger Issue

I've found an issue with the Debugger for SQL Server 2008 Management Studio a while ago. This is my way to ask you to endorse a fix https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=374183 Please let Microsoft know what you think about this suggestion.

posted @ Saturday, June 27, 2009 10:01 PM | Feedback (0) | Filed Under [ SQL Server 2008 Miscellaneous ]

Extended Get Nth Weekday of period

Recently I posted a function which returned the Nth weekday of a month, either from the beginning of month or from the end of month. Function is found here http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx I have fiddled around with it and have now extended the function to find the Nth weekday not only for a month, but also for a quarter or a year. Below is the function. CREATE FUNCTION dbo.fnGetNthWeekdayOfPeriod (     @theDate DATETIME,     @theWeekday TINYINT,     @theNth SMALLINT,     @theType CHAR(1) ) RETURNS DATETIME BEGIN     RETURN (                 SELECT theDate                 FROM    (                             SELECT DATEADD(DAY, theDelta +(@theWeekday + 6 - DATEDIFF(DAY, '17530101', theFirst) % 7) % 7, theFirst) AS theDate                             FROM    (                                         SELECT CASE UPPER(@theType)                                                     WHEN 'M' THEN DATEADD(MONTH, DATEDIFF(MONTH, @theNth, @theDate), '19000101')                                                     WHEN 'Q'...

posted @ Thursday, June 18, 2009 11:34 AM | Feedback (3) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

How to get the Weekday and Nth from a date

You call this function with a date. The function returns a table with one record and 3 columns. First column is Weekday; Monday = 1, Tuesday = 2, Wednesday = 3; Thursday = 4, Friday = 5, Saturday = 6 and Sunday = 7. Second column is number of occurencies of that date since beginning of selected period type. Third columns is number of occurencies left of that period type. CREATE FUNCTION dbo.fnGetWeekdayAndNths (     @theDate DATETIME,     @theType CHAR(1) ) RETURNS TABLE AS RETURN (   SELECT 1 + DATEDIFF(DAY, -53690, @theDate) % 7 AS theWeekday,                     1 +(theDelta - 1) / 7 AS Beginning,                     DATEDIFF(DAY, DATEADD(DAY, -1, thePeriod), @theDate) / 7 - 1...

posted @ Thursday, June 18, 2009 2:28 AM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

How to get the Nth weekday of a month

You call this function with three parameters: 1. Any date of the month in question 2. The weekday to calculate; Monday 1, Tuesday 2, Wednesday 3, Thursday 4, Friday 5, Saturday 6 and Sunday 7 3. The choice of weekday count; a positive number means from the beginning of month and a negative number means from the end of month If a valid date cannot be calculated, NULL is returned. For an extended version which, besides month, also handles quarter and year, see new blog post here http://weblogs.sqlteam.com/peterl/archive/2009/06/18/Extended-Get-Nth-Weekday-of-period.aspx CREATE FUNCTION dbo.fnGetNthWeekdayOfMonth (     @theDate DATETIME,     @theWeekday TINYINT,     @theNth SMALLINT ) RETURNS DATETIME BEGIN     RETURN  (                 SELECT  theDate                 FROM    (                             SELECT  DATEADD(DAY, 7 * @theNth...

posted @ Wednesday, June 17, 2009 9:05 PM | Feedback (9) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Timings of different techniques for finding missing records

I did some tests today to measure the different approaches for finding records present in one table but not in another. The results of CPU and Duration are presented below with some help from SQL Profiler. Number of reads are equal between methods but different depending how many record in #TableB. If there are other methods I haven't included, please let me know. Method      TableA   TableB   CPU  Duration ----------  -------  -------  ---  -------- GROUP BY    1000000  1000000  748       754 LEFT JOIN   1000000  1000000  328       321 NOT EXISTS  1000000  1000000  265       288 NOT IN      1000000  1000000  296       293 EXCEPT      1000000  1000000  312       288 GROUP BY    1000000   500000  577      2984 LEFT JOIN   1000000   500000...

posted @ Friday, June 12, 2009 3:22 PM | Feedback (11) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Extract XML structure automatically, part 2

For some time ago, I posted an algorithm how to get the XML structure automatically. Today I stumbled across another approach which seems to be faster. Reservations though I haven't tested this against large xml data yet. However, the previous algorithm relied on a WHILE loop here http://weblogs.sqlteam.com/peterl/archive/2009/03/05/Extract-XML-structure-automatically.aspx but this new algorithm doesn't. It's all xml internal thingies going on. DECLARE     @Nodes TABLE             (                          NodeID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,                          ParentNodeName NVARCHAR(64),                          NodeName NVARCHAR(64)             ) DECLARE @Data XML SET @Data = ' <root>         <elementGroup>                         <element>                                      <stuff>                                                  <comment>Stuff comment</comment>                                      </stuff>                                      <comment>Element comment</comment>                         </element>                         <comment>Element group comment</comment>             </elementGroup>             <comment>Root comment</comment> </root>' INSERT      @Nodes             (                          ParentNodeName,                          NodeName             ) SELECT      e.value('local-name(..)[1]', 'VARCHAR(MAX)') AS ParentNodeName,            ...

posted @ Thursday, June 04, 2009 1:30 PM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

I am proud and humble

A few months back, Adam Machanic launched a competition about "Grouped string concatenenation" here http://sqlblog.com/blogs/adam_machanic/archive/2009/02/27/t-sql-challenge-grouped-string-concatenation.aspx Now Adam has publish his results here http://sqlblog.com/blogs/adam_machanic/archive/2009/05/31/grouped-string-concatenation-the-winner-is.aspx and luckily my fourth suggestion was considered the overall winner! When seeing my competitors, I am humble to see that my suggestion performed better than other suggestions made by such people as Itzik Ben-Gan, Remus Rusanu, Rob Farley and others. Of course I am proud to win the MSDN Premium subscription, and now I have a hunch that next competition will be with Microsoft to get this baby activated   ;-) Thanks to all who participated. I have learned a few new tricks. And I...

posted @ Monday, June 01, 2009 10:44 AM | Feedback (7) | Filed Under [ Miscellaneous ]

Powered by:
Powered By Subtext Powered By ASP.NET