SQL Server 2008 Table Partitioning -

-

i have huge database has several tables hold several million records. it's holding engineering data client , continually grows. impacting performance, optimised indexing. i've been looking @ partitioning.

however, looking @ partitioning on version held in table. in it's simplistic form table comprises of:-

versionid int
sheetid int
creationdate datetime
somedate nvarchar(255)
version int

and data like:-

1, 1, 2010-09-01, blah, 1
2, 1, 2010-09-02, more blah, 2
3, 1, 2010-09-04, blah, 3
4, 2, 2010-09-02, more blah, 1

for every new change 'sheet' in system, table has new entry added new version. ideally want partition table have top 2 versions each 'sheet'. table above i'd want versions 2 & 3 sheet id 2, , version 1 sheet id 2, rest moved partition. i've read doesn't seem possible. right or wrong?

if i'm wrong, following on have bunch of tables link table. these hold various versions of data entered. can partition these based on partition of 'main' table, or partition have based on column table refers to?

nb i'm not au fait sql developer, apologies if daft question!

for quantity of 'several million records' - partitioning overkill - suspect better looking @ query plans of poor performing queries , reviewing indexes satisfying them.

the goal of partitioning provide way of aging older data without large scale deletions, partition elimination performance gain by-product , can still outpaced suitable clustered index - depending on index size / table size.

you can range partitioning within sql, quite lot of overhead implement such variable moving window, if judge requirements correctly.

in terms of final question, partitioned on same value, value have appear in both tables, although technically can partition on computed column if had schema bound function used compute corresponding value, suffice. (however, think overkill, given small number of rows.)


Comments

Post a Comment

Popular posts from this blog

c++ - How do I get a multi line tooltip in MFC -

-
right now, have tool tip pops when hover on edit box. problem tool tip contains multiple error messages , in 1 long line. need have each error message on own line. error messages contained in cstring new line seperating them. my existing code below. bool ontooltiptext(uint, nmhdr* pnmhdr, lresult* presult) { assert(pnmhdr->code == ttn_needtexta || pnmhdr->code == ttn_needtextw); // need handle both ansi , unicode versions of message tooltiptexta* pttta = (tooltiptexta*)pnmhdr; tooltiptextw* ptttw = (tooltiptextw*)pnmhdr; // tchar szfulltext[256]; cstring strtiptext=_t(""); uint nid = pnmhdr->idfrom; if (pnmhdr->code == ttn_needtexta && (pttta->uflags & ttf_idishwnd) || pnmhdr->code == ttn_needtextw && (ptttw->uflags & ttf_idishwnd)) { // idfrom hwnd of tool nid = ::getdlgctrlid((hwnd)nid); } //m_errprojaccel[ch] contains 1 or more error messages each...
Read more

asp.net - In javascript how to find the height and width -

-
i having .aspx , masterpage page. in masterpage loading aspx page . in aspx page design using generating table structure using code-behind server in javascript how find height , width . regards, kumar if you're looking table height , width, should it var tbl = document.getelementbyid('tbldemo'); alert(tbl.offsetwidth + '\n' + tbl.offsetheight);
Read more

c# - DataTable to EnumerableRowCollection -

-
i have datatable according client requirement can contain invalid date "00/00/1999",null,empty string.(typed datset) when collect them enumerables wish convert of invalid forms empty string. (i.e) enumerablerowcollection<datarow> query = order in _table.asenumerable() select new { orderdate= string.isnullorempty(convert.tostring(order.field<datetime>("orderdate")) || (how check date invalid date) ? string.empty : order.field<datetime>("orderdate") } how check whether date valid date or not? datetime.tryparse...like given current code: datetime date; enumerablerowcollection<datarow> query = order in _table.asenumerable() select new { orderdate= string.isnullorempty(convert.tostring(order.field<datetime>("orderdate")) || !datetime.tryparse(convert.tostring(order.field<datetime...
Read more