Dynamic SQL

Sessions finalised

Simon Sabin — Tue, 26 Aug 2008 09:48:59 GMT

We've finalised the sessions for SQLBits and they can now be found on the agenda page http://www.sqlbits.com/information/mainagenda.aspx.

We've got 20 sessions across all areas.If you've been holding off registering until the sessions have been decided nows your time to get registering.

http://www.sqlbits.com/information/registration.aspx

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/Sessions finalised

Early bird discsounts for SQLBits Training Days

Simon Sabin — Thu, 14 Aug 2008 12:58:20 GMT

Don't forget the early bird discount for the training days finishes this Friday (15th August)

Seminar	Register	Early Bird	Full Price
Turbo charge your TSQL with .Net inside SQL Server	click to register	£150	£200
Advanced SQL Server Troubleshooting	click to register	£150	£200
Making the most of data through business intelligence	click to register	£150	£200
Introduction to using SQL Server for the Jack of All Trades	click to register	£100	£150

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/Early bird discsounts for SQLBits Training Days

SQL Server Performance

Simon Sabin — Fri, 01 Aug 2008 15:51:16 GMT

If you are interested in SQL Server Performance then you attending a course by Kimberly Tripp and Paul Randal is a must.

We are fortunate to have Kimberly and Paul over in the UK and they are doing two sessions,

Best Practices in Performance and Availability for SQL Server 2005/2008 on the 1st - 3rd September 2008 in Hatfield

Indexing for Performance in SQL Server 2000/2005/2008 on the 8th - 9th September 2008 in Edinburgh >

The early bird discount is ending soon so if you want to get on these at an amazing price then get in soon.

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SQL Server Performance

SQLBits Session voting closing soon

Simon Sabin — Fri, 25 Jul 2008 16:01:34 GMT

We will be closing voting on the sessions for the next SQLBits very soon. If you haven't yet voted then go to http://www.sqlbits.com/information/PublicSessions.aspx to vote.

Once voting has closed we will be finalising the agenda.

Even when voting closes you will still be able to register by going to http://www.sqlbits.com/information/registration.aspx

Simon

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SQLBits Session voting closing soon

SQLBits Training Seminar registration is open

Simon Sabin — Tue, 22 Jul 2008 14:01:33 GMT

For SQLBits III (www.sqlbits.com ) we have decided to run a training day on the Friday before the free conference.

We have 4 seminars covering a range of SQL related topics.

You can find the details of the seminars on the Training Day page of the SQLBits site or by clicking on the links below.

Seminar	Register	Early Bird	Full Price
Using .Net inside SQL Server	click to register	£150	£200
Advanced SQL Server Troubleshooting	click to register	£150	£200
Making the most of data through business intelligence	click to register	£150	£200
Introduction to using SQL Server for the Jack of All Trades	click to register	£100	£150

Simon

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SQLBits Training Seminar registration is open

SQLBits Sessions - Don't forget submission closes today

Simon Sabin — Wed, 16 Jul 2008 08:11:08 GMT

Session submission for SQLBits III on 13th September closes today. You can still submit a session by going here http://www.sqlbits.com/information/SessionSubmission.aspx

Its also the last day for you budding Monets to get your logo submissions in. Please send these to logos@sqlbits.com if you want details of the competition you can find them here http://www.sqlbits.com/LogoCompetition.aspx. Don't forget you can win an XBox 360 if your logo wins the competition.

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SQLBits Sessions - Don't forget submission closes today

August London UG Meeting - All things LINQ

Simon Sabin — Mon, 07 Jul 2008 22:37:32 GMT

We have an exciting meeting planned for London in August.

I’m sure you’ve heard of LINQ, LINQ to SQL or Entity Framework, or you might have heard of over ORM solutions such as NHibernate. ORMs are all about being able to have a set of objects which developers can use with out having to worry about data access.

So what do these tools mean for the SQL Server Developer and DBA.

Mike Taulty has volunteered to do a couple of sessions on what LINQ is all about and what LINQ to SQL and the Entity Framework mean to you as a database professional. Is it going to kick you out of a job or provide you more work ???

The Agenda for the evening is.

6pm – 6:30 News and open session

Simon will do a stint on whats going on in the world, betas, release candidates service packs, best practices, training etc. And then will open up the floor to any one that has something to say.

6:30pm – 7:30 Whats LINQ all about? And how does LINQ fit in with LINQ to SQL and the Entity Framework Part 1

Mike will go through LINQ as programming feature and how it is used by Linq to SQL and the Entity Framework. He’ll also discuss what LINQ to SQL and the entity framework is.

7:30pm – 7:50 Beer and Pizza

7:50pm – 9pm Whats LINQ all about? And how does LINQ fit in with LINQ to SQL and the Entity Framework Part 2

The discussion, demos and presentation will continue from part 1.

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/August London UG Meeting - All things LINQ

Win an XBox 360

Simon Sabin — Mon, 07 Jul 2008 22:37:17 GMT

You may have seen my other post on session submissions for the next SQLBits. Well if you're not up for speaking how about a bit of drawing.

James Rowland Jones has kindly donated an XBox 360 for our SQLBits logo competition.

All you have to do is come up with a new logo for SQLBits.

Details of the competition and the currently submitted logos can be found here http://www.sqlbits.com/LogoCompetition.aspx

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/Win an XBox 360

Get your session in for SQLBits Cubed

Simon Sabin — Mon, 07 Jul 2008 22:27:02 GMT

If you didn't know the next SQLBits is going to be on the 13th September 2008.

We have had an overwhelming response for sessions for the next SQLBits buts there is still time for you to get your session in, especially if you fancy doing something on Business Intelligence.

To submit a session its dead easy

1. Go to the submit a session http://www.sqlbits.com/information/SessionSubmission.aspx
2. If you're not registered then register (link available on the page)
3. Create a speaker BIO
4. Submit your session

I look forward to seeing more sessions.

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/Get your session in for SQLBits Cubed

SQLBits III - Submit your sessions now

Simon Sabin — Mon, 23 Jun 2008 23:36:39 GMT

Have you got something you've found out that you think others might be interested in. Then submit a session for the next SQLBits on 13th September 2008.

http://www.sqlbits.com/information/SessionSubmission.aspx

You need to be logged in to fill in a BIO and submit a session.

Once we have all the sessions in we will open voting to get the community to decide which sessions they would like to see.

If you're stuck for ideas have a look at the sessions from the previous events.

http://www.sqlbits.com/information/event2/PublicSessions.aspx

http://www.sqlbits.com/information/event1/PublicSessions.aspx

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SQLBits III - Submit your sessions now

SQL Server 2008 - Handling deletes with MERGE

Simon Sabin — Wed, 18 Jun 2008 17:23:04 GMT

The MERGE statement is a new statement introduced in SQL Server 2008 that allows you to perform inserts, updates and deletes in one statement. You use a rowset as the source for the MERGE and as with all rowsets this can have any number of rows in it. For an introduction see “MERGE an introduction”.

The challenge with deletes is that on the client the row has been deleted, how do you handle the fact you no longer have the row when you use MERGE.

Assuming you are passing a set of data to your MERGE statement there are two way of handling deletes.

The first is the classic example that you will see in most examples. In this solution you work on the principle that if the row doesn’t exist in the source rowset then it must have been deleted and so you perform a DELETE.

This is done by using the WHEN TARGET NOT MATCHED clause of the MERGE statement

DECLARE @ChangedOrderDetail SalesOrderDetail_Type

DECLARE @SalesOrderID int

MERGE Sales.SalesOrderDetail OrderDetail

USING @ChangedOrderDetail Chng ON Chng.[SalesOrderDetailID] = OrderDetail.[SalesOrderDetailID]

AND Chng.[SalesOrderID] = OrderDetail.[SalesOrderID]

WHEN MATCHED THEN

UPDATE SET [SalesOrderID] = Chng.SalesOrderID

,[CarrierTrackingNumber] = Chng.CarrierTrackingNumber

,[OrderQty] = Chng.OrderQty

,[ProductID] = Chng.ProductID

,[SpecialOfferID] = Chng.SpecialOfferID

,[UnitPrice] = Chng.UnitPrice

,[UnitPriceDiscount] = Chng.UnitPriceDiscount

,[rowguid] = Chng.rowguid

,[ModifiedDate] = Chng.ModifiedDate

WHEN NOT MATCHED THEN

INSERT ([SalesOrderID],[CarrierTrackingNumber],[OrderQty],[ProductID],[SpecialOfferID],[UnitPrice]

,[UnitPriceDiscount],[rowguid],[ModifiedDate])

VALUES (Chng.SalesOrderID,Chng.CarrierTrackingNumber,Chng.OrderQty,Chng.ProductID

,Chng.SpecialOfferID ,Chng.UnitPrice,Chng.UnitPriceDiscount,Chng.rowguid,Chng.ModifiedDate);

WHEN NOT MATCHED IN SOURCE

DELETE

What this does is perform a scan of the target table (OrderLines) looking for rows in the source data (@ChangedOrderLines). You will notice I said scan and thats what it has to do, it has to check every row in the target table against the source data. This is a very expensive operation as most scans are. Furthermore it only works if the source data should represent ALL the rows in the target table.

Imagine you have an OrderLines table with 1 million rows. You are not going to retrieve all 1 million rows from your client application to update and then send back to the database. Instead you will return all the OrderLines for a specific Order.

If you use the DELETE pattern above, what will happen is that, your source data will only have data for one Order and won’t have any data for ALL the other Orders in the system. This will mean that ALL the OrderLines for the other orders will NOT MATCH IN TARGET and so be deleted.

This is obviously not ideal.

So whats the solution?

The solution is to use soft deletes. This means that you don’t DELETE the record but instead flag the record as deleted. We can then use the additional feature of MERGE which allows a condition to be added to the WHEN clause (a SQL Server specific extension not in the standard).

This turns our MERGE statement into the following.