June 2009 Blog Posts
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...
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.
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'...
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...
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...
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...
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,
...
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...