Thanks Gilamonster.

this is the first place ive been where ids aren't in some form of integer, i think this is a combination of legacy & lazyness. Your explanation makes total...

GilaMonster (10/5/2015)

---

Please post the most common queries.

Also, why Datetimeoffset? It's not a common data type choice, especially since it's not daylight savings aware, which reduces the usefulness of storing a...

Ill have a chat with them and clarify this. Once i wrap my sql in sp's i can add the calculation.

Thanks ever so much guys 🙂

Its another team that are in-charge of the source db so Im not sure why these datatypes have been selected, I know c# guids are used all over the place...

Current table create statement:

CREATE TABLE [dbo].[StockTransaction](

[Id] [nvarchar](128) NOT NULL,

[CreatedDate] [datetimeoffset](7) NULL,

[StockTransactionType] [nvarchar](max) NULL,

[UserId] [nvarchar](max) NULL,

[StockItem_Id] [nvarchar](128) NULL,

[WarehouseLocation_Id] [nvarchar](128) NULL,

[UserLocationId] [nvarchar](128) NULL,

PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE =...

Oh OK

somehow ive picked this up wrong. I was under the impression that partitioning the data would give benefit in the form of sql server not having to look at...

Thanks Jeff

That's interesting points.

I wasn't partitioning the production table. Incrementally im pulling each days transaction over to an exact replica of the table in an archive database. My plan...

Thanks,

I thought i read somewhere that you can move data from one partition to another. So in my example at the end of the year all the data in the...

ok try this link to my skydrive.

https://onedrive.live.com/redir?resid=F16A00370CA83923!60116&authkey=!ABYu99It012qkds&ithint=folder%2ctxt%5B/u%5D

hmmm, Ive removed them and re-added them.

Thanks Chris There is a CreateScript.txt attached to the post, cant you see it?

Not quite. if you look at a specific stock item using the query below you can see there is one valid sequence..

SELECT

*

FROM

[dbo].[StockTransaction] sub1

WHERE sub1.StockItem_Id='45a8025c-6977-470e-95a5-78a91473913c' order by...

John Rowan (8/11/2015)

---

I would suggest that you consider using SSAS. Developing a cube for your data gives you the control over how the data is put together and how...

many thanks guys ISOWEEK was spot on, never knew about that.

Thanks

yes totally agree with you, but i inherited this issue and the time scales. we are a seasonal business so data fix has to wait until our low season, frustrating...