June 2008 - Posts

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



-
Posted by simonsabin | 1 comment(s)
Filed under: ,

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

 



-
Posted by simonsabin | with no comments
Filed under: ,

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.

 



-

Most applications are written nowadays using optimistic concurrency. Locks aren’t placed on a piece of data for the duration of someone viewing that data in their application. Instead what you do is when someone wants to save the data you check to see whether someone else as changed it since the time you read the data. If someone has you generally don’t want to save the data because you are likely to overwrite the change that had been made. The check normally consists of checking the value of each column against the values you got back when you read the data or comparing the value of a rowversion against what you read (a rowversion column gets changed every time a row is changed, that means you can use this column to detect a change rather than checking all the columns). Imagine buying a

What has this got to do with the MERGE statement. Well optimistic concurrency is very easy to do when you are dealing with a single row of data as you only have to do one check. If you identify the row has changed you can return success or failure. However with a MERGE statement what happens if you are updating 10 records and 4 have already been updated. In this case you have a number of options one is to update those that haven’t changed and return to the client those that have changed. This can be done very nicely using a trick with MERGE and the OUTPUT clause .

Assuming you have a rowversion (timestamp) column on your table (you can use the same approach if you don’t, in which case you will have to compare all the columns ) the following is an example MERGE statement with a TVP (Table Valued Parameter). We are going to save a set of OrderLines

The classic MERGE statement is as follows

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 

              ,.....

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



To add optimistic checking you can check at the start within a serialised transaction or check at the end to see if the number of rows affected is the same as the number passed in i.e.

 

SET TRANSACTION ISOLATION SERIALIZABLE

BEGIN TRANSACTION

IF NOT EXISTS (SELECT 1

             FROM Sales.SalesOrderDetail OrderDetail

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

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

            WHERE OrderDetail.Rowversion <> Chng.Rowversion)

    BEGIN

        MERGE Sales.SalesOrderDetail OrderDetail

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

        .......

    END      

ELSE

    RAISERROR(‘data has changed since it was read....)

COMMIT TRANSACTION

Or

BEGIN TRANSACTION

 

    MERGE Sales.SalesOrderDetail OrderDetail

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

    .......

   

IF @@ROWCOUNT<> (SELECT Count(1) FROM @ChangedOrderDetail)

       BEGIN

    RAISERROR(‘data has changed since it was read....)

       ROLLBACK TRANSACTION

       END

ELSE

    COMMIT TRANSACTION

   

The final way is to do a partial update and report the records that have changed . The OUTPUT clause only contains records that have been inserted, updated or deleted. So to get the records out you need to UPDATE them, however you can UPDATE the columns to their existing values (i.e. not change anything ) and that way you can get these rows in the OUTPUT clause. You can then use a nice feature of the MERGE OUTPUT clause and compare the deleted rowversion value with that in the source data. If they are different it means the record wasn’t changed.

 

MERGE Sales.SalesOrderDetail OrderDetail

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

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

                                  

WHEN MATCHED THEN

    UPDATE SET [SalesOrderID]            = CASE WHEN Chng.Rowversion = OrderDetail.Rowversion          

                                                THEN Chng.SalesOrderID           

                                                ELSE OrderDetail.SalesOrderID

                                           END

              ,[CarrierTrackingNumber]   = CASE WHEN Chng.Rowversion = OrderDetail.Rowversion          

                                                THEN Chng.CarrierTrackingNumber 

                                                ELSE OrderDetail.CarrierTrackingNumber

                                           END

              , .....

 

OUTPUT CASE WHEN Chng.Rowversion = deleted.Rowversion THEN 1 ELSE 0 END Changed

      , Chng.SalesOrderDetailID

Note: For simplicity I have removed most of the columns from the update statement. You would need to include these in your application.

You can further extend this by using another new feature of SQL Server 2008, Composable DML. This allows you to consume the output from one statement into another statement. With SQL Server 2008 you are limited to only being able to consume the data in an INSERT statement. There are also restrictions on the structure of the table you are inserting into. Refer to BOL for exact details.

Using composable DML you can store only those records that failed update into a table variable and then return those records.

 

DECLARE @ChangedOrderDetail SalesOrderDetail_Type

DECLARE @SalesOrderID int

 

DECLARE @t TABLE (Changed int, ID int)

 

INSERT INTO @t

SELECT Changed, SalesOrderDetailID

FROM (MERGE Sales.SalesOrderDetail OrderDetail

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

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

                                           

       WHEN MATCHED THEN

        UPDATE SET [SalesOrderID]            = CASE WHEN Chng.Rowversion = OrderDetail.Rowversion          

                                                    THEN Chng.SalesOrderID           

                                                    ELSE OrderDetail.SalesOrderID

                                               END

                  ,[CarrierTrackingNumber]   = CASE WHEN Chng.Rowversion = OrderDetail.Rowversion          

                                                    THEN Chng.CarrierTrackingNumber 

                                                    ELSE OrderDetail.CarrierTrackingNumber

                                               END

       OUTPUT CASE WHEN Chng.Rowversion = deleted.Rowversion THEN 1 ELSE 0 END Changed

            , Chng.SalesOrderDetailID) da

WHERE CHanged = 0

 

SELECT *

  FROM @t



-

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



-
Posted by simonsabin | with no comments

 

Table valued parameters along with MERGE are in my view the two killer features and the two that I believe will get most uptake when people start to use SQL 2008.

Table valued parameters enable you to send a set of data as a single parameter to your code whether it be adhoc SQL, a stored procedure or function. This is beneficial as it reduces the number of calls that have to be made to the database and so you minimise the overhead associated with each call. Imagine saving 1000 items into a table the historical way was to either, issue 1000 database calls to do the inserts or construct a single batch that consisted of all the insert statements. (This was the behaviour with ADO). Even with the latter approach where you only have 1 round trip to the database you still have the overhead associated with a single insert. I compare it to photo copying a set of pages, you can either copy each page one at a time, by lifting the lid on the copier, putting the paper on the platen and then copying and repeating for each sheet or the quicker way is to put them all in the sheet feeder and let the machine process the whole set of pages in one go.

Table valued parameters really come into their own when you have a distributed application and the speed of the link between application and database isn’t very fast. In the scenario above if the latency in the network results in a call to the db taking 10 ms (irrespective of what you are doing), then it will take 10s to save the data. Converting to a table valued parameter would reduce this down to 1 x 10ms overhead for the database call.

The MERGE statement further enhances table valued parameters by enabling you to pass a single set of data consisting of inserts, updates and deletes and save those to the database in a single statement. This has the benefit in that it simplifies code, you have a single atomic transaction and so the complexities of serialized transactions may be avoided, you get performance benefits from the fact that the index structures are being modified in a single set based approach rather than 3 individual statements performing inserts, updates and deletes. (In one test I did I found using MERGE to do purely INSERTs was quicker than the equivalent insert statement, so it is by no means a compromise).

Using AdventureWorks we have the following demo.

Firstly we need to create a TABLE TYPE for use by our Table Valued Parameter. In this case we have copied the structure of the SalesOrderDetail type.

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, PRIMARY KEY (SalesOrderId,SalesOrderDetailID))

GO

 

Secondly we create our procedure that uses the Table Type. We can then call this procedure from TSQL or client code passing in a set of data.

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



-

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.



-
Posted by simonsabin | with no comments