Manipulate XML data with non-xml columns and not using variable

Some time ago, I displayed how to work with XML data when searching for data stored in a XML column.
Here Some XML search approaches and here Updated XML search (test case with variables).

Today I thought I should demonstrate how to insert and update XML columns with data from other columns and not using variables. Well, I do have some examples of using variables in here because I wanted to display the core difference.
And this is my first blog post (not counting the previous announcement) since I become a MVP, I thought this blog post should be about something necessary.

Much of the XML manipulations I have made by trials and errors, because I haven’t found a single good source of information about these things. If you do know of such information, please comment.

First, we create a sample table to hold some important data, like this


CREATE TABLE    #Sample
                (
                    RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
                    MemberID INT,
                    MemberData XML
                )

INSERT  #Sample
SELECT  1, '<meta><customergroup>F</customergroup><mosaic>Young educated man</mosaic></meta>' UNION ALL
SELECT  2, '<meta><age>24</age></meta>'

CREATE PRIMARY XML INDEX IX_PrimaryXML ON #Sample(MemberData)

CREATE XML INDEX IX_Element ON #Sample(MemberData)
 USING XML INDEX IX_PrimaryXML FOR PATH


As you see, I also created the primary XML index and a secondary XML index on the XML column.
Now let’s see what is stored in the important table.


SELECT  MemberID,
        MemberData,
        MemberData.value('/meta[1]/customergroup[1]', 'VARCHAR(8)') AS CustomerGroup,
        MemberData.value('/meta[1]/mosaic[1]', 'VARCHAR(200)') AS Mosaic,
        MemberData.value('/meta[1]/age[1]', 'TINYINT') AS Age,
        MemberData.value('/meta[1]/zipcode[1]', 'VARCHAR(5)') AS ZipCode,
        MemberData.value('/meta[1]/status[1]', 'VARCHAR(15)') AS [Status]
FROM    #Sample


The exercise now is to add one or more elements to the XML column, both with using a variable but also, more importantly, using a column. Create a helper table like this below.


DECLARE @Sample TABLE
        (
            MemberID INT,
            ZipCode VARCHAR(5),
            Mosaic VARCHAR(200)
        )

INSERT  @Sample
SELECT  1, '26737', 'SQLTeam' UNION ALL
SELECT  2, '12345', 'Smart woman in the countryside'


When an element doesn’t already exist, the element is added to the XML column. Beware that an additional element is created if one alerady exists!


-- New element for all
UPDATE      s
SET         MemberData.modify('insert <zipcode>{sql:column("x.ZipCode")}</zipcode> into (/meta)[1]')
FROM        #Sample AS s
INNER JOIN  @Sample AS x ON x.MemberID = s.MemberID


Now do the SELECT again and you will see that a zipcode element was added to both records.


SELECT  MemberID,
        MemberData,
        MemberData.value('/meta[1]/customergroup[1]', 'VARCHAR(8)') AS CustomerGroup,
        MemberData.value('/meta[1]/mosaic[1]', 'VARCHAR(200)') AS Mosaic,
        MemberData.value('/meta[1]/age[1]', 'TINYINT') AS Age,
        MemberData.value('/meta[1]/zipcode[1]', 'VARCHAR(5)') AS ZipCode,
        MemberData.value('/meta[1]/status[1]', 'VARCHAR(15)') AS [Status]
FROM    #Sample


As I said before, if an element already exist a new elemtent with same name is added. This behaviour can be avoided by using a WHERE MemberData.exists clause. Do the updates first since it only touches existing records with matching elements and then do the inserts with the MemberData.exists clause.


-- Update existing element
UPDATE      s
SET         MemberData.modify('replace value of (/meta/mosaic/text())[1] with sql:column("x.Mosaic")')
FROM        #Sample AS s
INNER JOIN  @Sample AS x ON x.MemberID = s.MemberID

-- Insert new element. Beware of duplicates.
UPDATE      s
SET         MemberData.modify('insert <mosaic>{sql:column("x.Mosaic")}</mosaic> into (/meta)[1]')
FROM        #Sample AS s
INNER JOIN  @Sample AS x ON x.MemberID = s.MemberID


Now do the SELECT again and you will see that the element mosaic was updated for member 1 and added to member 2. This is also true for member 1 because now this member has two elements with same name. 


SELECT  MemberID,
        MemberData,
        MemberData.value('/meta[1]/customergroup[1]', 'VARCHAR(8)') AS CustomerGroup,
        MemberData.value('/meta[1]/mosaic[1]', 'VARCHAR(200)') AS Mosaic,
        MemberData.value('/meta[1]/age[1]', 'TINYINT') AS Age,
        MemberData.value('/meta[1]/zipcode[1]', 'VARCHAR(5)') AS ZipCode,
        MemberData.value('/meta[1]/status[1]', 'VARCHAR(15)') AS [Status]
FROM    #Sample

And this was my reason for this blog post. How to add and replace elements with values from another column. So why not display how to do this with variables too?


DECLARE @Status VARCHAR(18)

SET         @Status = 'Shipped'

-- Update single record without prior element
UPDATE      #Sample
SET         MemberData.modify('replace value of (/meta/status/text())[1] with sql:variable("@Status")')
WHERE       MemberID = 1

-- Create new element for single record
UPDATE      #Sample
SET         MemberData.modify('insert <status>{sql:variable("@Status")}</status> as last into (/meta)[1]')
WHERE       MemberID = 2


As you can see now, same rules apply. Member 1 did not get a new Status element since we wanted to update previous value. But member 2 did get a new element.


SELECT  MemberID,
        MemberData,
        MemberData.value('/meta[1]/customergroup[1]', 'VARCHAR(8)') AS CustomerGroup,
        MemberData.value('/meta[1]/mosaic[1]', 'VARCHAR(200)') AS Mosaic,
        MemberData.value('/meta[1]/age[1]', 'TINYINT') AS Age,
        MemberData.value('/meta[1]/zipcode[1]', 'VARCHAR(5)') AS ZipCode,
        MemberData.value('/meta[1]/status[1]', 'VARCHAR(15)') AS [Status]
FROM    #Sample


Don’t forget to do your housecleaning and drop the important table.


DROP TABLE  #Sample


Besides doing update and insert, you can also use the delete syntax to remove elements if you jus don’t to clear them.
I hope you liked this post.

Cheers!

posted @ 7/3/2009 3:57 PM by Peter Larsson with 0 comment(s)

WCF-SQL Adapter fails while inserting rows to a table that has an identity column.

I was working on a Biztalk integration project that perform a CRUD operation using old SQL adapter (the new one is available via Microsoft Adapter Pack 2.0), everything works fine on the development environment but when we've tried to deploy it to test environment which have the same structure with the production server we stumble again on MSDTC issues.

To cut the story short it was too complicated and too many settings (firewall, NETBIOS etc) needs to be change since the database server is located on DMZ. The next day while I'm configuring the mySAP adapter, I found out that license has expired, since we've downloaded the trial version of BizTalk adapter pack 1.0. So I have to look for another setup, it so happen that for some reason I can't find the 1.0 version that's why I've installed the Adapter pack 2.0. I was a little bit surprise to see that there's a new SQL Adapter included. So I've installed it and give it a try and it works fine.

Two important sql binding settings that needs to be set are:

1) useAmbientTransaction = false, to solve MSDTC issue.

2) allowIdentityInsert = true, if you're trying to add rows to a table that has identity column.

1)

 

posted @ 7/3/2009 4:58 PM by Randy Aldrich Paulo with 0 comment(s)

Microsoft MVP

Today it happened. I received the Microsoft MVP Award for my contributions to the Microsoft SQL Server community.
I am very honored by the award and I will continue to work hard for the community to keep their trust in me.

I will continue to update this blog and help users out in the SQLTeam forums, SqlServerCentral forums, SQL Server Magazine forums and SQL Server Developer Center forums among a few other.

Thank you to everyone who reads this blog and leave comments. I still learn something new every day.


Best Regards,
Peter Larsson

posted @ 7/1/2009 6:33 PM by Peter Larsson with 7 comment(s)

DTLoggedExec 0.2.2.0: Here comes CSV Logging!

In these last days I've released the latest version of DTLoggedExec. Now it has reached version 0.2.2.0.

I planned the release to be done in May, but I added a l lot of stuff and then I realized that logging so much information without being able to automatically import it into SQL Server for further analsys has no clue.

So I decided to hold on a little bit and also improve the DTLoggedExec db that I firstly released with version 0.2.1.4 just as an example of what can be done with the Data Profiling featture, and that news has grown to be official repository of all the data the DTLoggedExec can log.

Of course with the added capability to log to CSV file, this feature was really needed.

This new feature brought also the need to be able to associate CSV Logging with DataFlow Profiling, so I had to change the code a little bit so that everything can get a consistent and unique Execution ID, which allows the data to be tied all togheter, not matter if it comes from DataFlow Profiling or CSV Logging.

Add this with some bug fixed and a very handy feature that allows you to get rid of the complex SET syntax to make yout package parametric and you can imagine that some work had to be done.

Plus, add that before releasing any new release I want to test it for a while on myself, just to be sure that very basic bug does get unnoticed, at voilà, some months of delay.

Anyway, now its there, and I'm sure you'll enjoy the new features:

• Added logging of expression used by variables for which the "EvaluateAsExpression" property is true.
• Fixed the bug the prevented to log properly Custom Events (CodePlex id# 20513)
• Fixed a bug that prevented to log properly all the container properties in case of error
• Added support for "Params" commandline options
• Released CSV log provider
• Handled exceptions when loaded configuration tries to configure non-existing objects
• Changed the ILogProvider interface to allow Log Provider to get the ExecutionGUID value from the main program
• Changed the Console and Null log providers to support the new ILogProvider interface
• Changed the header of the *.dtsDataFlowProfile in order to store package, version and execution GUIDs
• Changed .sql files to import Profiled DataFlow data in SQL Server
• Updated DTLoggedExecDB database
• Released scripts to load CSV data proceduced by CSV Log Provider into SQL Server

I'm now in the process of updating the documentation. I plan to be able to do it during this week and weekend, but using the new feature is quite easy and as usual I have included in released code some samples to show how to use them.

So, if you cannot wait, go and play, otherwise just hold on a while, while I update the documentation here:

http://dtloggedexec.davidemauri.it/MainPage.ashx

One last notice about the new CSV Log Provider. This is completely functional and you can start to use it right now, but is just the first release. I plan to improve it more and more. I have to deep test it for performance impact, so play with it freely but be warned that performance may be lower then the Console Log Provider. Inside it pack data into XML format so that it can be stored in SQL Server easily, but this has some costs (You know, XML is flexble, standard, whatever you want, but surealy is not performant).

In future I plan to change a little bit the Log Provider interface again so that everything can be redirected to the Log Provider, also the initial messages, so that in the CSV log can be put everything can currently be found in the Console Log Provider, event the initialization messages, so inside SQL Server you can have the complete picture of what happened, right from the initialization of DTLoggedExec, just as it now happens with the Console Log Provider. Anyway, and I wanted to share it with you so that you can have an idea of future improvements, but for now just playing with the CSV log provider can solve a lot of nice problems. For example Auditing packages :).

More on that coming.... ;)

posted @ 6/29/2009 11:40 PM by Davide Mauri with 0 comment(s)

Forced Parameterization: A Turbo Button?

I never had the need to turn on the PARAMETERIZATION FORCED database option until this week.  We pretty much use only stored procedures for our internal applications so the execution plans are almost always in cache and reused.  This practice of using parameterized stored procedure calls, together with attention to detail in query and index tuning, allows us to comfortably handle several thousand requests per second on commodity hardware without taking special measures.

The Perfect Storm

We acquired a third-party application which had to sustain thousands of batch requests per second in order to keep up with our peak demand.  Our first attempt to use the application out-of-the box failed miserably when the 16-core database server quickly hit 100% CPU and stayed there.  An examination of the most frequently run query soon revealed why CPU was so high.  Not only was the moderately complex query not parameterized, each invocation required a full table scan.  The schema (EAV model, missing primary keys and indexes), application code (ad-hoc, non-parameterized queries) and inattention to indexing seemed the perfect storm to guarantee failure. 

Our hands were tied in what the vendor could/would do to address our performance concerns.  We worked with the vendor to optimize indexes and this brought the CPU down to about 65% but the batch requests/sec rate and slow response time was still unacceptable.   We needed to increase performance by at least an order of magnitude to meet SLAs.

The Perfect Fix

I then recalled an experience that SQL Server MVP Adam Machanic shared not long ago:

CPU was 95%+ at peak time (several thousand batch requests/second, via an ASP (classic) front end), and the peak time lasted 8+ hours every day.  The server was one of the big HP boxes -- not sure if it was a Superdome or some other model -- with something like 56 cores and 384 GB of RAM.  The database itself was only 40 or 50 GB, as I recall, so the entire thing was cached.  Long story short, I logged in during peak load, did a quick trace and noticed right away that none of the queries were parameterized.  I decided to throw caution to the wind and just go for it.  Flipped the thing into Forced Parameterization mode and held my breath as I watched the CPU counters *instantly* drop to 7% and stay there. I thought I'd broken the thing, but after checking my trace queries were running through the system same as before, and with the same number of errors (another story entirely <g>). Luckily the head IT guy happened to be watching his dashboard right as I made the change, and after seeing such an extreme result thought I was a god...

 

I knew of PARAMETERIZATION FORCED but never realized how big a difference the option could make until I learned of Adam's experience.  I'm not quite as adventuresome as he is so I restored the production database to a separate environment for some cursory testing.  To my amazement, I watched the rate of my single-threaded test jump from a few dozen batch requests/sec to several hundred immediately after I executed "ALTER DATABASE...SET PARAMETERIZATION FORCED".  CPU dropped by half even with the tenfold increase in throughput. 

The production improvement was even more impressive - the 16 core Dell R900 hasn't exceeded 8% CPU since the change.  Response time is excellent, we have happy users and plenty of CPU headroom to spare.

A Turbo Button?

Despite anecdotal success with PARAMETERIZATION FORCED, I wouldn't turn it on indiscriminately.  When the PARAMETERIZATION FORCED database option is on, all queries are parameterized, including complex ones.  This is good in that compilation costs are avoided due to cache hits.  The bad news is that a single plan might not be appropriate for all possible values of a given query.  Worse overall performance will result when higher execution costs (due to sub-optimal plans) exceed compilation savings so you should understand the query mix before considering the option.

In contrast, SQL Server parameterizes only relatively simple "no brainer" queries in the default PARAMETERIZATION SIMPLE mode.  This behavior promotes reuse of plans for queries that will yield the same plan anyway regardless of the literal values in the query.  Complex queries are not parameterized automatically so that the optimizer can generate the optimal plan for the values of the current query in the event of a cache miss.  The downside with simple parameterization, as Adam and I observed, is that complex queries not already in cache will incur costly compilation costs that are a CPU hog in a high-volume OLTP workload.

There is also middle ground between PARAMETERIZATION SIMPLE and PARAMETERIZATION FORCED.  One can use plans guides with PARAMETERIZATION SIMPLE to avoid compilation for selected queries while other complex queries are compiled as normal.  In my case, a plan guide may have been a better option because the culprit was a single query rather than many different unpredictable ones.

In my opinion, the best solution is to use stored procedures and/or parameterized queries in the first place.  These methods provide the performance benefits of PARAMETERIZATION FORCED and add other security and application development benefits.  Unfortunately, third-party vendors are notorious for not following parameterization Best Practices so DBAs need to keep PARAMETERIZATION FORCED and plan guides in their tool belt.

 

posted @ 6/27/2009 4:47 PM by Dan Guzman with 2 comment(s)

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 what you think about this suggestion.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=470881

posted @ 6/27/2009 10:06 PM by Peter Larsson with 5 comment(s)

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 @ 6/27/2009 10:01 PM by Peter Larsson with 0 comment(s)

"join me on July 23rd and learn how to quickly deliver actionable information to your company's leaders"

Learn how to strengthen your company's overall health and help your business thrive in this tough economy - without ever leaving your desk.

• Attend this exclusive series and discover how to quickly deliver actionable information without any significant software costs or long running data warehouse projects.
• Cut excessive costs, identify your most profitable customer's attributes, improve operations, and reap additional business value from your organization's existing data assets
• Get real-life tips and to-the-point training on how to leverage Microsoft BI to reduce uncertainty and generate quick ROI for your company.

Join industry gurus Derek Comingore and Barry Ralston for 3 lessons + live Q&A and learn how to:

• leverage Excel and Data Mining technologies that exist today
• support the democratization of BI in your organization using Excel and SharePoint technologies
• quickly provide enterprise visibility into your organization's key metrics 

See you there!

http://windowsitpro.com/elearning/index.cfm?fuseaction=dynamic&v=5168&p=5209&code=&eventid=29&code=EPspeakersBIeLearnJul09

• 

posted @ 6/25/2009 8:53 PM by Derek Comingore with 0 comment(s)

Cost to Compile a Query

It’s pretty easy to determine the CPU and disk resources that a given query or stored procedure will use.  It’s more difficult to determine the resources that were used to compile that query plan.  You can start by looking at sys.dm_exec_cached_plans.  It has a column called “size_in_bytes” that will tell you how much memory the query plan is using.

If you generate an XML query plan through SSMS or Profiler you can get some additional information.  The XML plan includes this snippet:

<QueryPlan CachedPlanSize="196" CompileTime="53" CompileCPU="53" CompileMemory="1896">

If you review the schema for the XML query plan you can find a little bit about these values.

• CachedPlanSize is in kilobytes.
• CompileTime is in milliseconds (1/1000th of a second) and was introduced in SQL Server 2005 SP2.
• CompileCPU is in milliseconds (1/1000th of a second) and was introduced in SQL Server 2005 SP2.
• CompileMemory is in kilobytes and was introduced in SQL Server 2005 SP2.

There are also entries for MemoryGrant (KB) and DegreeOfParallelism but I haven’t used those as much.

posted @ 6/24/2009 6:49 AM by Bill Graziano with 2 comment(s)

Defragmenting/Rebuilding Indexes in SQL Server 2005

I have modified isp_ALTER_INDEX, which is the stored procedure that I use to defragment/rebuild indexes in SQL Server 2005.  The changes include two bug fixes and one feature request. 

The first bug fix was reported by Fedor Baydarov.  He found that @lobData was not being re-initialized to zero after a LOB data type was encountered.  This meant that the rest of the indexes to be processed were being done offline even if the online option was available.  D’oh!

The second bug fix is that it didn’t check for LOB data types of the included columns in a non-clustered index.  The online option is not available for this type of index, so the bug caused the stored procedure to fail when such a condition was encountered.  This bug fix might have been found by a blog reader, but I’m unable to find an email regarding it.  I came across the bug recently on a system that had such an index.

The feature request was to add the option to do the sort operation in the tempdb database.  This is recommended if your tempdb is optimized according to best practices, such as by having a tempdb data file for each of the CPUs.  See ALTER INDEX topic in SQL Server Books Online for more details.

You can download the new version of the stored procedure here. 

Let me know if you run into any issues with it.  I’d also be interested to hear if it works on SQL Server 2008. 

posted @ 6/23/2009 2:48 PM by Tara Kizer with 5 comment(s)

Moving

Over the past several years, I've regularly blogged about SQL Server and other technologies, and sometimes about life in general. I enjoy sharing my experiences and love it when something I've written evokes a good conversation in the comments section of the blog.

For the past couple of years, I've hosted my blog right here on SQLTeam with my good friend and fellow SQL Server MVP, Bill Graziano. Bill's a wonderful guy with a true passion for the SQL Server community.

Recently I finally finished porting my professional web site over to a platform that allows me to host my own blog content. This will afford me more flexibility and will give me a unified presence on the web - making it easier for me consulting, mentoring, and training clients to find and follow me on the web.

I've already begun posting blogs to the new site. Here are a few that may be of interest to you.

• Book Review - Learning SQL Server 2008 Reporting Services
• I'll have a #4 combo with a side of wifi
• Live from DevTeach 2009 in Vancouver
• Finding a good hobby
• Security - Don't overlook the obvious

Bill's agreed to keep this blog and all its posts in place so that search engines and hard coded links will continue to direct traffic to content.

If you've been a subscriber to this blog via rss, THANK YOU!!! I truly appreciate it.

ACTION: To keep receiving updates, you'll need to modify your rss reader's settings for my blog. The new feed is http://webbtechsolutions.com/feed/

Thanks to Bill and SQLTeam for hosting my blog these years. It's been great!

Joe

posted @ 6/23/2009 11:02 AM by Joe Webb with 0 comment(s)

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' THEN DATEADD(QUARTER, DATEDIFF(QUARTER, @theNth, @theDate), '19000101')
                                                    WHEN 'Y' THEN DATEADD(YEAR, DATEDIFF(YEAR, @theNth, @theDate), '19000101')
                                                END AS theFirst,
                                                CASE SIGN(@theNth)
                                                    WHEN -1 THEN 7 * @theNth
                                                    WHEN 1 THEN 7 * @theNth - 7
                                                END AS theDelta
                                        WHERE   @theWeekday BETWEEN 1 AND 7
                                                AND (
                                                        @theNth BETWEEN -5 AND 5
                                                        AND UPPER(@theType) = 'M'
                                                    OR
                                                        @theNth BETWEEN -14 AND 14
                                                        AND UPPER(@theType) = 'Q'
                                                    OR
                                                        @theNth BETWEEN -53 AND 53
                                                        AND UPPER(@theType) = 'Y'
                                                )
                                                AND @theNth <> 0
                                    ) AS d
                        ) AS d
                WHERE   CASE UPPER(@theType)
                            WHEN 'M' THEN DATEDIFF(MONTH, theDate, @theDate) 
                            WHEN 'Q' THEN DATEDIFF(QUARTER, theDate, @theDate) 
                            WHEN 'Y' THEN DATEDIFF(YEAR, theDate, @theDate) 
                        END = 0
            )
END

posted @ 6/18/2009 11:34 AM by Peter Larsson with 0 comment(s)

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

posted @ 6/18/2009 2:28 AM by Peter Larsson with 0 comment(s)

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 - 7 * SIGN(SIGN(@theNth) + 1) +(@theWeekday + 6 - DATEDIFF(DAY, '17530101', DATEADD(MONTH, DATEDIFF(MONTH, @theNth, @theDate), '19000101')) % 7) % 7, DATEADD(MONTH, DATEDIFF(MONTH, @theNth, @theDate), '19000101')) AS theDate
                            WHERE   @theWeekday BETWEEN 1 AND 7
                                    AND @theNth IN (-5, -4, -3, -2, -1, 1, 2, 3, 4, 5)
                        ) AS d
                WHERE   DATEDIFF(MONTH, theDate, @theDate) = 0
            )
END

posted @ 6/17/2009 9:05 PM by Peter Larsson with 4 comment(s)

SQL Server 2008 for Developers live meeting presentation

I’ll be talking about the following topics:

What should developers know about database design so they don't have performance and logical problems?

What's new in SQL Server 2008 that helps solve some business problems that sometime required "hacking" before.

Concurrency design models and isolation levels.

 

The presentation will start on Tuesday June 16th at 2:00 PM EST / 6:00 PM UTC / 8:00 PM CET

More info at PASS Application Development SIG

 

UPDATE: you can view the presentation here.

posted @ 6/15/2009 11:22 PM by Mladen Prajdić with 0 comment(s)