Simons SQL Blog

We are very fortunate that Michael Rys has decided to come and speak at a UG event whilst he is in the UK for a W3C working group.

Michael is Program Manager in the SQL Server Engine Team at Microsoft and is responsible for the XML features in SQL Server.

If you use XML in SQL Server then this is the one User group meeting you have to come to.

Click here to register http://sqlserverfaq.com/?eid=126

He is the man that knows XML and XQuery inside and out. On the few occasions I've met him I've always felt humbled by the intellect of this man.

You can find his blog here http://blogs.msdn.com/mrys/ and his old CV can be found here http://infolab.stanford.edu/~rys/cv/ (nice picture)

Registration is at 6.00, evening will commence at 6:30pm and finish 9pm.


6:30pm - 6.45pm - Introduction, news and gossip.
Whats going on in the world,

6:45 - 9pm Michael Rys on non-relational data in SQL Server
I'm sure Michael has some great demos on non relational features and will answer any questions you have around XML, filestream, Ful Text etc.
Pizza will be served at some point in the evening

Click here to register http://sqlserverfaq.com/?eid=126

Note: Due to the late notice of this meeting the precise details might change slightly,
however we need you to register asap to make sure its worth us running the meeting

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

As promised here is a follow up to the questions and points raised during the meeting tonight.

We didn't get to cover full text but here are my posts on iFTS in SQL 2008 which works very differently to how it does in SQL 2008.

Compression of a large table, how much space do you need?

The compression process, compresses the table 1 page at a time so you don't need to have enough free space to hold a copy of the table you are compressing. Which is good.

Viewing and importing Spatial data.

There aren't any products on the market at present however the belief is there will be within a short period of time. There are many mashup style apps that work with spatial data in Virtual Earth, the interface to VE and Google maps is not via any of the formats that SQL understands and so you have to build some translation mechanism in between. This is largely because VE and GM both allow shapes on maps to have additional data like push pins, colours, popups.

Importing data is a different story as the GIS market is already established (MS is a bit late to the game) there are many providers that can import data and convert data. ESRI is the largest player in terms of source data many other companies exist to handle data from different sources

Slowly Changing Dimension transform in SSIS

The component in SSIS doesn't use SSIS. it would be good if it did but it doesn't. Largely becuase TVPs really need to be supported as well which they are not and also the component would be a SQL specific component which wouldn't be a god thing. Not sure if they are going to be developing one. It wouldn't be too difficult to develop a script component that did it.

There are also a number of scenarios that the MERGE statement doesn't handle.

SQL2000 to SQL 2008

This has been discussed in other forums and the general advice is to do a side by side upgrade, it is supported in an inplace upgrade with the exception of Itanium 64 servers. The jump from SQL 2005 to SQL 2008 isn't as vast as that from SQL 2000 to SQL 2005 and so going straight to SQL 2008 is not a bad choice. The setup process is much better and generally features have been stuck on the side, not many features have been rewritten (one big exception beng iFTS), this means you don't have to use many of the 2008 features if you don't want to.

Database size increase with encryption.

I've been told that the database size doesn't increase when you turn on TPE (Transparent Data Encryption) see  http://msdn.microsoft.com/en-us/library/bb934049(SQL.100).aspx .

Everything else

If there is anything else I've missed off then feel free to contact me.

Simon

The MERGE statement and Table Valued Parameters (TVPs) are two new features in SQL Server 2008 that make the process of doing DML (Data Manipulation i.e. INSERT, UPDATE and DELETE) on multiple rows very easy. This article shows how you can adapt

Historically to save data using the DataAdpater you configure it with insert, update and delete statements, pass it a DataTable of changed data and it will then look at the DataTable and fire off the relevant insert, update or delete statement.

This is fine except it fires off a statement for each change. This isn’t very efficient due to, overheads with the network, making a connection to the database and performing a single row change in the database. We ideally want to reduce network calls and perform SET based operations in the database. TVPs and the MERGE statement allow us to do just that.

So how does the DataAdapater work using a TVP. The short answer is, it doesn’t. Fortunately its fairly easy to amend your code to use TVPs

If you’ve read the article on handling DELETEs with MERGE you will know that the best way to handle DELETES with MERGE is to pass them as soft deletes, i.e. flag the row as DELETED using an extra column in the table.

To do that we take the DataTable containing our changes and we add a column. We now need to populate this column for DELETED rows (we don’t need to populate it for the INSERTS or UPDATEs). When you DELETE a row in a DataTable you can’t make any further modifications to the row. Therefore we have to reject the change, which allows us to change the rows, and then update the column with a “D” for DELETED

DataTable ChangedDT = SourceDT.GetChanges (DataRowState.Added | DataRowState.Modified | DataRowState.Deleted|DataRowState);

DataColumn DelCol = ChangedDT.Columns.Add("action",typeof(string));

foreach (DataRow ChangedRow in ChangedDT.Rows )

{

    if (ChangedRow.RowState == DataRowState.Deleted)

    {

        ChangedRow.RejectChanges();

        ChangedRow ["action"] = "D";

    }

}

Now we have a DataTable only containing the changes and with our DELETED rows flagged with the D we need to create a SqlCommand object that will call the stored procedure we are using.

This is a normal SqlCommand with the commandText set to the name of the procedure. Creating the parameter is slightly different to normal, the SqlDbType of the parameter has to be SqlDbType.Structured. This is the type for all table valued parameters. In addition to this you have to specify the TypeName, this is used to say which TABLE TYPE your table valued parameter is. From my testing the TypeName is only required if your CommandType is NOT CommandType.StoreProcedure (So in the example below setting TypeName isn’t required), I guess this is because for StoredProcedures the type of the table is defined in the stored procedure definition.

Once the SqlCommand  is setup you can execute it as you would any other SqlCommand.

SqlCommand UpdateCommand = new SqlCommand();

UpdateCommand.Connection = conn;

UpdateCommand.CommandText = "dbo.up_OrderDetail_SaveSet";

UpdateCommand.CommandType = CommandType.StoredProcedure;

UpdateCommand.Parameters.AddWithValue("@ChangedOrderDetail", ChangedDT);

UpdateCommand.Parameters[0].SqlDbType = SqlDbType.Structured;

UpdateCommand.Parameters[0].TypeName = "SalesOrderDetail_Type";

conn.Open();

UpdateCommand.ExecuteNonQuery();

The stored procedure used by this code is derived from the MERGE example in the “Handling Deletes with MERGE” article.

The thing to note is that your TABLE TYPE has to have the additional column in it so the action can be passed through.

CREATE TYPE SalesOrderDetail_Type  as TABLE (

    [SalesOrderID]          [int] NOT NULL ,

    [SalesOrderDetailID]    [int] IDENTITY(1,1) NOT NULL,

    [CarrierTrackingNumber] [nvarchar](25) NULL,

    [OrderQty]              [smallint] NOT NULL,

    [ProductID]             [int] NOT NULL,

    [SpecialOfferID]        [int] NOT NULL,

    [UnitPrice]             [money] NOT NULL,

    [UnitPriceDiscount]     [money] NOT NULL,

    [LineTotal]             AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),

    [rowguid]               [uniqueidentifier] ROWGUIDCOL  NOT NULL,

    [ModifiedDate]          [datetime] NOT NULL,

    Action                  char(1),

    PRIMARY KEY (SalesOrderId,SalesOrderDetailID))

We then create our stored procedure to use this type,

CREATE PROCEDURE up_OrderDetail_SaveSet

  @ChangedOrderDetail SalesOrderDetail_Type READONLY

 ,@SalesOrderID       int

AS

    MERGE Sales.SalesOrderDetail OrderDetail

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

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

    WHEN MATCHED AND Chng.Action = 'D' THEN

        DELETE

    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);

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.

DECLARE @ChangedOrderDetail SalesOrderDetail_Type

DECLARE @SalesOrderID int

MERGE Sales.SalesOrderDetail OrderDetail

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

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

--Note DELETEs are now done here

WHEN MATCHED AND Chng.Action = 'D' THEN

    DELETE

----

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);

This results in a much better query plan as the query engine only as to find those OrderLines that match those in the @ChangedOrderLines table parameter.

Using this pattern does require you to do some additional work on your client, its not difficult and I will show you how you can do that in the next article.

NOTE: This examples use the new syntax for MERGE introduced in RC0 of SQL SERVER 2008.

Hello,

You may be aware that there are a few organisations that help usergroups. One of these is Culminis which SQL Bits is registered with. To tell Culminis how great SQL Bits is we need you to complete the Culminis survey selecting SQLBits as the usergroup.

The survey is here http://survey.culminis.com/takeSurvey.asp?surveyID=235 and consists of 4 questions, one of which is the usergroup question.

We would grealty appreciate you completing this survey, the more people that complete the survey the more funding we can get from culminis.

Cheers

Wow thats been a long break.

I rebuilt my laptop a while back and since then haven't had my blogging tool installed so no blogging. I've not got it up and running and I have a huge back log of blog posts to get out of the door. Not to mention some great things I'm working on like the next SQLBits, a new ventuer called SQL Know How, training on SQL 2008 and much more.

Keep posted.

Oh and I've finally got my head round how you should implement hierarchyId in SQL 2008.

See you soon, hopefully on Thursday at the UK UG meeting we've got 124 registered, so far which is going to be one hell of a party.