Solved

partition by a column of type datetime.

Posted on 2011-03-03
20
1,181 Views
Last Modified: 2012-05-11
this is my new table:
CREATE TABLE [dbo].tabla_demo
(  
id int identity,    
c varchar(200) NULL,
fecha datetime
)

Previous example I added the date column, it stores information from the year 2000, if you wanted to partition the table as it should modify the code?
0
Comment
Question by:enrique_aeo
  • 8
  • 6
  • 6
20 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 375 total points
ID: 35027580
this tutorial shows how to do it:
http://weblogs.sqlteam.com/dmauri/archive/2005/07/18/7147.aspx

the key is the partitioning function. ...
0
 
LVL 13

Expert Comment

by:dwkor
ID: 35029265
As the side note, if you have identity ID increasing the same way with fetcha date column, consider to partition by ID instead. Even if it introduces more work, in some cases it gives you some benefits. Mainly because you don't need to introduce another column in the clustered and non-clustered indexes. As the downside, it would not help SQL Server to eliminate partitions if you queries have fetcha as the filter there
0
 

Author Comment

by:enrique_aeo
ID: 35033677
ok, I understand
 1. we're on stage that the date and the id increases
 2. You should get the number of records per year, so that according to that made my partition function
 3. It is good practice to try to partition the primary key is the physical arrangement of a table?
0
 

Author Comment

by:enrique_aeo
ID: 35033682
angelIII, thanks for the url. But the scenario I need is when a table has data, ie a non-partitioned table to a partitioned table
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35034211
you cannot change a table from non-partitioned to partitioned or vice-versa.

so, you have to rename + recreate the table, and reinsert the table's data from the renamed table.
0
 

Author Comment

by:enrique_aeo
ID: 35034979
Now if I'm confused. In the original example
 1. I think a table is not partitioned
 2. full data
 3. I think the partition function,
 4. I think the partition scheme,
 5. remove the primary key
 6. re-create the primary key
 7. and then the data appear in their respective partition
 What do you mean?
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 375 total points
ID: 35035008
no, not recreate the priamry key. you have to recreate the full table.
and in order not to loose data, you just rename the original table, create a NEW table with the same original name, but using the partitioning function.
then, you insert the data to the (new) table from the renamed table ...
0
 

Author Comment

by:enrique_aeo
ID: 35035028
ok, then
rename the original table = create a NEW table with the same original name, but using the partitioning function
something?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35035040
what exactly are you missing?
0
 

Author Comment

by:enrique_aeo
ID: 35035064
I believed that renaming a table is renamed, for example
 before: mytable
 Now: mitabla2

And another question
All partitions must be made using the primary key?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35035339
no, partitions can be made using any field, as it's actually the partitioning schema (function) the decides.
and indeed, the primary key is most often not the best for the partitioning
0
 

Author Comment

by:enrique_aeo
ID: 35036224
angell ok, then back to my question, as you would to partition a data table with a column of type datetime. In the example of the url is when the table is empty, but when it is full as I do?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35036578
the partitioning schema would depend on the needs and facts.
if you have only hundreds of calls per day, you could do a partitioning per month.
if you have thousands of calls her hour, the partitioning shall be by day
but that are only extreme examples...

and again: it does not matter if the table is empty or not: you cannot change it from non-partitioned to partition without dropping (aka renaming) it first. the only difference if the table is empty or not is that you don't need to rename it to keep the data. ...
0
 
LVL 13

Expert Comment

by:dwkor
ID: 35036844
Angelii, why do you think he cannot simply drop and recreate clustered index? This would work without additional table.

Of course, this route (one table) would be slower on the large tables due extra table rebuild (when you drop clustered index). But it would work.

So in his scenario he can:

 1. I think a table is not partitioned
 2. full data
 3. I think the partition function,
 4. I think the partition scheme,
* Drop all non clustered indexes
 5. remove the primary key
 6. re-create the primary key
* Recreate non clustered indexes - make them aligned - i.e. include partitioned column to the index.
 7. and then the data appear in their respective partition
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 375 total points
ID: 35036903
the question is about how to partition the table ...
so, 2 scenarios:
* the table is empty (or the data does not matter)
  solution: drop the table, and recreate it with the proper (partitioning) code

* the table is not empty, and the data is to be kept:
  solution: rename the table, create a new one with the proper (partitioning) code, and copy over the data, drop the nenamed table

recreating a (clustered) index or primary keys does not matter, in that regards.

of course, one could consider to keep the table non-partitioned, and have a clustered index on the field "c" (url, I presume) instead, so you can check stats per url easier...
0
 
LVL 13

Expert Comment

by:dwkor
ID: 35036950
See below
create table T
(
	ID int not null,
	Col int not null,
	Placeholder char(100),
	
	constraint PK_T
	primary key clustered(ID)
)
go

create partition function pfT(int) 
as range left 
for values(0, 100)
go

create partition scheme psT
as partition pfT
all to ([primary])
go

alter table T
drop constraint PK_T
go

alter table T
add constraint PK_T
primary key clustered(ID, Col)
on psT(Col)
go

Open in new window

0
 
LVL 13

Expert Comment

by:dwkor
ID: 35036979
AngelIII - I'm commenting this statement: "and again: it does not matter if the table is empty or not: you cannot change it from non-partitioned to partition without dropping (aka renaming) it first. the only difference if the table is empty or not is that you don't need to rename it to keep the data. ..."

You can change it from non-partitioned to partitioned. This is not optimal solution if table has a lot of data because of additional table rebuild but it could make sense in a lot of cases. Think about security, etc.
0
 
LVL 13

Assisted Solution

by:dwkor
dwkor earned 125 total points
ID: 35037101
Enrique, one other thing - why do you want to partition the table? This is probably the most important question. This is really useful in the case if you want to implement sliding window scenario although for the other cases it does not automatically means that it improves performance. It could, especially if parallelism would be involved, but same time it could hurt. For example, if you have CI (ID, Date) and partition by Date, query like: select  from T where ID > ? would introduce more IO than non-partitioned table.

Check http://aboutsqlserver.com/2010/12/22/sql-server-and-table-partitioning-part-2-when-partitioning-is-you-enemy/ if you need more details.

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35037258
>You can change it from non-partitioned to partitioned.
I presume I missed the relevant info, then....
sorry that I posted wrong information. was this possible in sql 2005?
0
 
LVL 13

Expert Comment

by:dwkor
ID: 35037306
angelIII, yes. I would not recommend it on the huge tables (your way is faster) but it's available.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now