Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1205
  • Last Modified:

partition by a column of type datetime.

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
enrique_aeo
Asked:
enrique_aeo
  • 8
  • 6
  • 6
4 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
dwkorCommented:
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
 
enrique_aeoAuthor Commented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
enrique_aeoAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
enrique_aeoAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
enrique_aeoAuthor Commented:
ok, then
rename the original table = create a NEW table with the same original name, but using the partitioning function
something?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what exactly are you missing?
0
 
enrique_aeoAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
enrique_aeoAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
dwkorCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
dwkorCommented:
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
 
dwkorCommented:
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
 
dwkorCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
dwkorCommented:
angelIII, yes. I would not recommend it on the huge tables (your way is faster) but it's available.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 8
  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now