Horizontal Partitioning Question
Jul 20, 2005
I recently came across a database where the data are horizonally partitioned
into 4 tables. I'm not sure if this was a poor design choice, or if it was
done for valid performance reasons. The schema of the tables are essentially
the same, it's just that they are named differenly and the columns are named
differenlty to differentiate the data from a business usage perspective. The
tables could easily be combined inot one by adding a new colum to the
clustered index that would be used to differentiate the business usage. I am
trying to evaluate whether combining the tables would improve performance or
if it would be better to leave them the way they are. Many queries that run
against these tables do not request records from more than one of the
tables, which is good. However, there are a number of processes that query
against all of the tables on the identical clustered index range. I am not
sure exactly how many rows are in the tables but I'm fairly certain the
entire database is < 50 GB.
View 13 Replies
ADVERTISEMENT
Mar 7, 2008
Hello frnds......what does mean by Horizontal Partitioning ?
View 4 Replies
View Related
Jan 17, 2001
HI, I'm testing out partitioning for an upcoming project and I'm a little confused over the query plan im generating.
I have a 13 million row table I want to break in to date ranges:
tables are calljrnl,calljrnl_1999 and calljrnl_2000. The calljrnl table contains data for the last six months. one view, calljrnl_view is a union all of these three tables.
alter table calljrnl add constraint dt_constraint check (cj_created_dt >= '2000-10-01 00:00:00')
alter table calljrnl_1999 add constraint dt_constraint_1999 check (cj_created_dt <='1999-12-31 23:59:59')
alter table calljrnl_2000 add constraint dt_constraint_2000 check (cj_created_dt >='2000-01-01 00:00:000')
alter table calljrnl_2000 add constraint dt_constraint_2000_2 check (cj_created_dt <='2000-09-30 23:59:59')
The problem I'm having is that when I query on any date range outside the constraints set on the calljrnl_2000 table, It still wants to scan the calljrnl_2000 table.
I'm assuming this has something to do with how the constraints are structured, but I am not able to find the right combination.
ALL HELP IS GREATLY APPRECIATED!!!!
View 4 Replies
View Related
Feb 3, 2005
If I have the raw data dumped into a big table as following:
Date P R M E Date P R M E Date P R M E Date P R M E
1/1/90 1 2 3 4 1/1/90 2 3 4 5 1/1/90 3 4 5 6 1/1/90 4 5 6 7
...
1/1/05 1 2 3 4 1/1/05 2 3 4 5 1/1/05 3 4 5 6 1/1/05 4 5 6 7
And this table has a repeating block [D, P, R, M,E] 300 times. Is it possible to write a loop query/stored procedures/triggers (or whatever it is) to read each repeating block and stack them on top of each other to insert into another table which has the same structure as following?
Look like this?
Date P R M E
1/1/90 1 2 3 4
...
1/1/05 1 2 3 4
1/1/90 2 3 4 5
...
1/1/05 2 3 4 5
1/1/90 3 4 5 6
...
1/1/05 3 4 5 6
If there is a solution would you please elaborate, example?
Thank you for the help.
shiparsons
View 14 Replies
View Related
Jul 20, 2005
Hello all,Thinking about building a new database in the enterprise addition ofsql server and using some horizontal parititioning techniques in orderto accomaodat what will eventually be a monster huge database.Can you share some hard earned experience, gotchas, etc...with me? Wewill be setting up this server on a SAN array that will be made up ofjust one or two huge virtual RAID10 volumes and I am also wonderingabout the wisdom of this? Its simple and should work, but is thatconfig relevant in any way specifically to our plan to partition?
View 2 Replies
View Related
Mar 7, 2008
Sorry for the earliear question as itself is wrong ?
This is a right questions and what does it mean by ?
View 5 Replies
View Related
Jul 8, 2006
how can i use concatenation to query the results horizontally?
example output:
CustomerID=1002, Name=Mr Anderson, City=NY
View 10 Replies
View Related
Apr 16, 2002
I am testing horizontal partitions to see whether it is a feasible
option for a project. IF I have a composite Primary Key and the
constraint column (a part of the Primary Key) that helps the
partitioned view is defined with DateTime Data Type, select on a restricted set of data through a partitioned view still tries to access all the tables instead of just one table that contains the data. Is this the case or am I
missing something ?
Any help on this is appreciated.
Here is what I am trying to do.
************************************************** **************
CREATE TABLE [dbo].[tst01] (
[Dt] datetime NOT NULL ,
[TID] int NOT NULL ,
[Nm] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tst02] (
[Dt] datetime NOT NULL ,
[TID] int NOT NULL,
[Nm] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tst01] ADD
CONSTRAINT [PK_tst01] PRIMARY KEY CLUSTERED
(
[Dt], [TID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tst02] ADD
CONSTRAINT [PK_tst02] PRIMARY KEY CLUSTERED
(
[Dt], [TID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tst01] ADD
CONSTRAINT [CK_tst01] CHECK (Dt between '11/1/2002' and '11/30/2002')
GO
ALTER TABLE [dbo].[tst02] ADD
CONSTRAINT [CK_tst02] CHECK (Dt between '12/1/2002' and '12/31/2002')
GO
insert into tst01 values('11/1/2002', 1, 'SS')
insert into tst01 values('11/2/2002', 2, 'KK')
insert into tst01 values('11/3/2002', 3, 'DD')
Go
insert into tst02 values('12/1/2002', 1, 'LL')
insert into tst02 values('12/2/2002', 2, 'MM')
insert into tst02 values('12/3/2002', 3, 'GG')
Go
CREATE VIEW vtst AS
SELECT * FROM tst01
UNION ALL
SELECT * FROM tst02
Go
SELECT * FROM vtst WHERE dt = '11/1/2002'
************************************************** *********
When I look at the Execution Plan, it shows that clustered index seek
would be performed on both the tables.
StmtText
---------------------------------------------------------------------------------------------------------------------------------
|--Concatenation
|--Filter(WHERE:(STARTUP EXPR(Convert([@1])<='Nov 30 2002
12:00AM' AND Convert([@1])>='Nov 1 2002 12:00AM')))
| |--Clustered Index
Seek(OBJECT:([test].[dbo].[tst01].[PK_tst01]),
SEEK:([tst01].[Dt]=Convert([@1])) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR(Convert([@1])<='Dec 31 2002
12:00AM' AND Convert([@1])>='Dec 1 2002 12:00AM')))
|--Clustered Index
Seek(OBJECT:([test].[dbo].[tst02].[PK_tst02]),
SEEK:([tst02].[Dt]=Convert([@1])) ORDERED FORWARD)
Thanks,
Sathish
View 1 Replies
View Related
Nov 29, 2012
I'm running SQL Server 2008 Standard.I need to create a query that has data from multiple columns (Columns 1-6 with coresponding Date started and Date Completed data) displayed vertically, but also has the column name the preeceeding column to identify it...along with other data (Record number, status).
Record Number, Status, Column Name, Date Started, DateCompleted
1, Open, Column 1, 1/1/2012, 2/1/2012,
2, Hold, Column 2, 1/3/2012, 3/1/2012,
1, Open, Column 3, 2/5/2012, 4/6/2012,
3, Closed, Column 4, 5/10/2012, 7/25/2012,
2, Hold, Column 5, 3/9/2012, 4/1/2012,
1, open, Column 6, 10/10/2012, 12/12/2012,
View 5 Replies
View Related
Feb 24, 2004
I'm considering using horizontal partitions to separate my data by year.
For example, SomeTable_2004, SomeTable_2003, etc. This works well for backups, maintenance, etc. because I'm working with 150+ GB of data. I'll be a partitioned view for queries.
However, I'm new at this and have a few questions. I would also like to do partitioned updates or inserts. But I need to make sure that the tables don't use similar primary keys. Does that make sense? I need to make sure that the primary keys from the first table are not used again in the second table.
SomeTable_2003
primary keys: 1,5,8,9,15
SomeTable_2004
primary keys: 2,3,4,10
I don't really care what keys are used on what table, as long as they are different. I have apps that already use this data, and I don't want to change the application logic.
Thanks,
T
View 1 Replies
View Related
Jun 13, 2008
id name
--- -----------------
236 SERVICE REQUEST
236 HARDWARE
236 Desktop
336 Loan
id name
-- ----------------------------
236 SERVICE REQUEST/ HARDWARE/ Desktop/Laptop/ Loan
View 5 Replies
View Related
Jan 7, 2015
I have a table like this:
weight type factory1 factory2 factory3 factory4.... to factory 150
1kg goods 5.00 5.50 5.20 5.00...
2kg goods 6.00 6.20 6.15 6.30...
3kg goods 4.00 4.50 5.00 4.30...
...
and would like to extract data this way:
producer type weight price
factory1 goods 1kg 5.00
factory1 goods 2kg 6.00
factory1 goods 3kg 4.00
factory1.....
then
factory2 goods 1kg 5.50
factory2 goods 2kg 6.20
and so on for all factories.
I tried with UNPIVOT but it does not allow it (I'm using Navicat 8), saying "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near UNPIVOT...".
View 2 Replies
View Related
Mar 15, 2008
Hi all
I dont want to use functions or proceedures but with select itself I want to get the above mentioned result. Can somebody help
SURESH IYER
View 2 Replies
View Related
Jul 20, 2005
I have 2 installations:1. W2k with MS SQL 2000 sp2.2. W2003 with MS SQL sp3.There are two databases in all instalations: "maindb" and "userdb". Thetable with data is in maindb.In the 1'st installation I restict user access to all data in table in thisway:- create user's login and user in databases maindb and userdb.- create view in userdb with "where" clause as dbo (dbo is owner of thisview). This clause restict access to data. (create view userdb.dbo.table asselect * from maindb.dbo.table where ...)- add user to group "Public" in maindb.- add role "data reader" to user in userdb.- effect: user can access data only by view, can not access any data inmaindb.I do the same in secound installation:- effect: user can not access data by view - message like this: "userhave not permission to select on maindb.dbo.table"Is this bug in sp3 or in sp2 ?Is there another way to horizontal restrict access data in tables?In Sybase ASE this method (restict by view) works ok. And there is newproperty of ASE 12.5.1 - administrator can define context of login - the"where" clause will be added automatically to any select.Please help me. Thank You for any advice.
View 2 Replies
View Related
Dec 5, 2007
Anyone know why the horizontal scroll bar doesnt show up when the data is too large to fit into the given window? The vertical scroll bar will show up but never the horizontal.
Any ideas?
Thanks
View 9 Replies
View Related
Jul 1, 2005
Hi,
I have a table with 52 million rows which resides on Primary file group in my database. Because of huge number of rows the performance has gone very down and I would like to break the table into parts.
Can anyone suggest me the steps for doing the same and the number of parts that should be made. It is named as Account_Transactions and contains information of Policies in an insurance database.
Rajat
View 2 Replies
View Related
May 25, 2006
I have a table as follows
opendate (datetime) callnumber (int) closed (bit)
I want to find how many calls were opened today and of those how many are closed
I have come up with the code below but again am looking for
1. a more elegant solution
2. a way to generalise this to show the same information for x number of days
create table #holdit1
(opencount int)
create table #holdit2
(closedcount int)
insert into #holdit1
SELECT count(*) as opencount
FROM [dbo].[problog]
WHERE datediff(dd, opendate, getdate()) = 0
AND closed = 0
group by closed
insert into #holdit2
SELECT count(*) as closedcount
FROM [dbo].[problog]
WHERE datediff(dd, opendate, getdate()) = 0
AND closed = 1
group by closed
select #holdit1.opencount AS CallsOpen, #holdit2.closedcount AS CallsClosed, #holdit1.opencount + #holdit2.closedcount AS AllCalls
from #holdit1 cross join #holdit2 #holdit2
DROP TABLE #holdit1
DROP TABLE #holdit2
this gives me
CallsOpen CallsClosed AllCalls
----------- ----------- -----------
1 3 4
View 4 Replies
View Related
Jan 10, 2005
I have the following SQL query that I like to view the out put in horizontal format:
Select ID, First_name, Last_name from ABC
Instead of getting out-put like
Id First_name Last_name
1Jim Smith
2Tom Jones
I like to see the out-put like:
Id1 2
First_nameJimSmith
Last_nameTomJones
Please advice.
Thanks in advance,
Abrahim
View 1 Replies
View Related
Jul 7, 2004
Hi,
What I need is the following, a StoredProc that make a Vertical (normal table) into a Horizontal Table :
So From:
CustNo__Item
__1______1
__1______3
__1______4
__2______2
__2______3
__2______7
To:
CustNo___1__2___3___4___7
_1_______X______X___X
_2__________X___X_______X
Anny help would be verry appriciated
View 1 Replies
View Related
May 14, 2014
I have a client with a fairly simple table as illustrated in my sample code. Their interesting requirement is that the records would be listed horizontally, end-to-end as I hope you can visualize based on what I'm providing here. Two other characteristics/specs of note, 1) there could be as few as two records end-to-end in a row or as many as 100 and 2) of course the column headings would have to be unique.
create table #tmpEndToEnd
(
ID int identity,
ClientID char(10),
Agency varchar(20),
Clinician varchar(20),
Goal varchar(50)
[code]....
View 3 Replies
View Related
Feb 14, 2008
Hello,
We are generating RDL file on the fly using a XSLT. We are trying to implement an auto width to all the columns dislpayed in the Report. Is Automatic horizontal width for column supported in RDL? If so how? If not is there is any workaround for it?
We tried using CanGrow and CanShrink tags, but those are for only vertical.
Thanks in advance.
View 1 Replies
View Related
Jul 5, 2007
I would like to display a couple of records horizontally via a table or list, but don't think this is possible. Am I right? The given recordset should only have two records and the formatting would be much better if they were displayed left to right rather than one on top of the other. Is this just not possible with SSRS? Thanks,
Levi
View 2 Replies
View Related
Sep 19, 2007
I'll admit I am fairly new to the reporting services, but I managed to figure most things out so far except this one. I have a matrix report where I have column groupings of:
Sales person
Region
Basically, the report shows sales by sales person and region, and I added subtotals to each so each salesman/region combination gets subtotals, as does each salesman entry. Now, the one piece missing is the 'total of the subtotals' so to speak. So if I have something like:
Salesman Region Sales Jan Sales Feb Sales Mar
001 A $100 $175 $50
B $100 $200 $100
C $200 $100 $50
Total $400 $475 $200
002 A $100 $175 $50
B $100 $200 $100
C $200 $100 $50
Total $400 $475 $200
Grand Total $800 $950 $400
What I want to do is sum up the totals and add another column like so:
Salesman Region Sales Jan Sales Feb Sales Mar
001 A $100 $175 $50
B $100 $200 $100
C $200 $100 $50
Total $400 $475 $200 $1075
002 A $100 $175 $50
B $100 $200 $100
C $200 $100 $50
Total $400 $475 $200 $1075
Grand Total $800 $950 $400 $2150
And that is where I am stuck. In Report Designer, with the existing matrix report as above, how do I get these horizontal totals of the subtotals?
View 5 Replies
View Related
May 19, 2015
I want to append vertical row result into horizontal
CREATE TABLE #mable(mid INT, token nvarchar(16))
INSERT INTO #mable VALUES (0, 'foo')
INSERT INTO #mable VALUES(0, 'goo')
INSERT INTO #mable VALUES(1, 'hoo')
INSERT INTO #mable VALUES(1, 'moo')
Actual output
[code]....
View 14 Replies
View Related
Aug 28, 2006
Hi,
Is there a report viewer property that needs to be set to enable the horizontal scroll bar. My reports are being cut off at the end of the iframe. I have several other reports that do show the horizontal scroll bar, but for some reason all my reports in the newly created project do not allow horizontal scrolling. Vertical scrolling is still available.
Thanks
Umer
View 3 Replies
View Related
Jul 9, 2004
From:
Cust | Qstion | Answer
_________________________________
1,1,Bike
1,4,Blue
1,6,No ensurance
2,1,Car
2,3,silver
2,9,ensurance
2,11,Yes
To the following
Cust______1______3______4______6______9______11
__________________________________________________ __________
1_______Bike___________Blue__No ENsur_________
2_______car____silver_________________Ens____Yes"
View 5 Replies
View Related
Apr 15, 2015
I have a query that calculates sales by sales person, but it displays horizontally across my query window. Is their a way in SQL Server to have the data display vertically down the window instead?
This is my current query
Code:
Select
count(case when salesman Like 'Geo%' then id else null end) As [George]
,count(case when salesman Like 'Li%' then id else null end) As [Lisa]
,count(case when salesman Like 'Jor%' then id else null end) As [Jorge]
,count(case when salesman Like 'Ri%' then id else null end) As [Richard]
,count(case when salesman Like 'Geo%' then id else null end)+count(case when salesman Like 'Li%' then id else null end) As [Team 1 Sales]
,count(case when salesman Like 'Jor%' then id else null end)+count(case when salesman Like 'Ri%' then id else null end) As [Team 2 Sales]
from sales.southeastregion
Which of course shows the results as such
George --- Lisa --- Jorge --- Richard --- Team 1 --- Team 2
100 50 10 90 150 100
And I want the data to be displayed like
George - 100
Lista - 50
Jorge - 10
Richard - 90
Team 1 - 150
Team 2 - 100
This is SQL Server 2008 if that matters.
View 5 Replies
View Related
May 14, 2014
create table #temp1
(
col1 float,
col2 float,
col3 float,
clo4 float)
insert into #temp1 values (1.5, 1.6,1.7,1.8)
insert into #temp1 values (1.9, 1.0,1.2,1.8)
o/p should display as below is there a way we can do this with cte or some other option
col1 1.5 1.9
col2 1.6 1.0
col3 1.7 1.2
col4 1.8 1.8
View 1 Replies
View Related
Mar 31, 2008
TaskID PID task Milestone
83manasi task2
83manasi task3
83manasi task4
Above is the query of the result
I want to show this resultset horizontaly
for that I have to use the cursor such that
@M1=Where Milestone=1
@M2=Where Milestone=2
@M3=Where Milestone=3
and putting this into a temp table with its projectid and taskid.
Plz guide me that how to achiev this exactly
Swati
View 1 Replies
View Related
Dec 14, 2006
Hi all.
I've created a horizontal report using a matrix according to the quite useful article Horizontal Tables, but there's one thing I'm trying to do that I'm not having any luck figuring out how to do, and that is creating a footer column for a column group that contains a sum of the values in some of its columns.
In other words, here's how I would like to have it look...
Jan
Feb
Mar
Quarter 1 Total
Apr
May
Jun
Quarter 2 Total
Tier 1
$100
$100
$100
$300
$0
$0
$100
$100
Tier 2
$50
$50
$0
$100
$100
$100
$100
$300
The fields in the dataset are Month, Tier1, Tier2, Year and Quarter. I've created a group on Year & Quarter, tried right clicking and selecting Subtotal, but that seems to add a subtotal column after every month, not at the end of the quarter group as I want.
Any ideas on what I need to do? Is this even possible?
TIA
View 1 Replies
View Related
Dec 18, 2006
Hi. I'm currently having some troubles with the Reporting Services charts and I was hoping I could get some help here.
The issue here is that I'm using charts with horizontal bars, and I can't seem to be able to control the alignment of the Point Values. I've read that the alignment can't be set while using stacked bar charts, but I'm not using a stacked bar chart, just a simple horizontal bar chart.
A workaround for this would be to use different colors for the values displayed outside the bars than from those displayed inside the bars (for better visibility). I had expected RS to display the values inside the bars when they are greater than a set percentage of the chart range (e.g. 95%). Alas, this is not the case, as it seems to consider the number of digits the values have AND the grouping. At least.
So, does anybody have an idea about what could be done in this particular situation?
View 1 Replies
View Related
Jul 24, 2015
Month Discount
Expenses GST
<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="d14582be-eecd-4def-be7e-0cfe00b13c80" id="4279c12f-e03f-4b38-9fc8-eb1a991c25ac"><gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="7f0da910-3775-4ee4-821e-1a0e7ee2ce0b"
id="92ce074f-ee0b-4794-839b-ee50c88d380c">ServiceCharge</gs></gs>
[Code] ....
View 2 Replies
View Related
Jun 20, 2006
Is it possible to align a text box to display text with rotation? For example can I display it rotated 90 degrees left so that it reads from the bottom towards the top of the page? All I can find is left/right alignment and top/middle/bottom alignment. I want to rotate the text.
Thanks.
View 37 Replies
View Related
