convert a non-partitioned tables into partitioned Options

Hi,
we have a large database containing many tables and lots of data. now
because of the number of records we decided to use partitions.
for this we have created partition function and partition scheme. the
problem is : How can we convert non-partitioned table into
partitioned one according to a partition scheme?
in the documentation it introduced 2 methods but I don't know are the
usable or not? if they are, how can I use them?
the clustered index is the primary key and there are some
foreign keys which are dependent on this one so it can't be droped.
and the base field which the table is going to be partitioned on that
is not primary key.
the other question is: when creating a table if we do not have primary
key , it
accepts the "ON PartitionScheme(FieldName)" but whenever we have
primary key then it says it is created on [primary] istead of the
partition scheme provided.
by the way, the field which the table is going to be partitioned
based
on it is a foreign key.
Thanks a lot,
Ali
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme.
Just because you have a lot of rows doesn't mean you need to partition.
Partitioning can help manageability of tables that are difficult to manage
due to size and can help performance of specialized operations. If your
tables are large and performance is slow, index and query tuning is the best
initial approach. Adding partitioning to the mix without tuning can slow
queries further.

> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be dropped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
The definition of a partitioned table is either a partitioned heap or a
table with a partitioned clustered index. If you want to partition this
table on the FK column, you'll need to either add the partitioning column to
the primary key or create a partitioned clustered index that includes the
partitioning column and change the primary key to a non-partitioned
non-clustered index. Note that when you partition non-clustered indexes
differently than the base table, you lose some of the manageability
benefits.

> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
The clustered primary key specification takes precedence over the table
create ON clause. If the primary key is non-clustered, then you'll end up
with a partitioned table and a non-partitioned index.
In addition to the Books Online, see Kimberly Tripp's white paper for a
thorough partitioning discussion:
[url]http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20I I.htm[/url]
Hope this helps.
Dan Guzman
SQL Server MVP
"Ali" <nikzad.a@.gmail.com> wrote in message
news:4bace43a-e047-4305-bf7a-de96542feb5b@.v4g2000hsf.googlegroups.com...
> Hi,
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme. the
> problem is : How can we convert non-partitioned table into
> partitioned one according to a partition scheme?
> in the documentation it introduced 2 methods but I don't know are the
> usable or not? if they are, how can I use them?
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be droped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
>
> by the way, the field which the table is going to be partitioned
> based
> on it is a foreign key.
>
>
> Thanks a lot,
> Ali
>

convert a non-partitioned tables into partitioned Options

Hi,
we have a large database containing many tables and lots of data. now
because of the number of records we decided to use partitions.
for this we have created partition function and partition scheme. the
problem is : How can we convert non-partitioned table into
partitioned one according to a partition scheme?
in the documentation it introduced 2 methods but I don't know are the
usable or not? if they are, how can I use them?
the clustered index is the primary key and there are some
foreign keys which are dependent on this one so it can't be droped.
and the base field which the table is going to be partitioned on that
is not primary key.
the other question is: when creating a table if we do not have primary
key , it
accepts the "ON PartitionScheme(FieldName)" but whenever we have
primary key then it says it is created on [primary] istead of the
partition scheme provided.
by the way, the field which the table is going to be partitioned
based
on it is a foreign key.
Thanks a lot,
Ali> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme.
Just because you have a lot of rows doesn't mean you need to partition.
Partitioning can help manageability of tables that are difficult to manage
due to size and can help performance of specialized operations. If your
tables are large and performance is slow, index and query tuning is the best
initial approach. Adding partitioning to the mix without tuning can slow
queries further.
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be dropped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
The definition of a partitioned table is either a partitioned heap or a
table with a partitioned clustered index. If you want to partition this
table on the FK column, you'll need to either add the partitioning column to
the primary key or create a partitioned clustered index that includes the
partitioning column and change the primary key to a non-partitioned
non-clustered index. Note that when you partition non-clustered indexes
differently than the base table, you lose some of the manageability
benefits.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
The clustered primary key specification takes precedence over the table
create ON clause. If the primary key is non-clustered, then you'll end up
with a partitioned table and a non-partitioned index.
In addition to the Books Online, see Kimberly Tripp's white paper for a
thorough partitioning discussion:
http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ali" <nikzad.a@.gmail.com> wrote in message
news:4bace43a-e047-4305-bf7a-de96542feb5b@.v4g2000hsf.googlegroups.com...
> Hi,
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme. the
> problem is : How can we convert non-partitioned table into
> partitioned one according to a partition scheme?
> in the documentation it introduced 2 methods but I don't know are the
> usable or not? if they are, how can I use them?
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be droped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
>
> by the way, the field which the table is going to be partitioned
> based
> on it is a foreign key.
>
>
> Thanks a lot,
> Ali
>sqlsql

convert a non-partitioned tables into partitioned Options

Hi,
we have a large database containing many tables and lots of data. now
because of the number of records we decided to use partitions.
for this we have created partition function and partition scheme. the
problem is : How can we convert non-partitioned table into
partitioned one according to a partition scheme?
in the documentation it introduced 2 methods but I don't know are the
usable or not? if they are, how can I use them?
the clustered index is the primary key and there are some
foreign keys which are dependent on this one so it can't be droped.
and the base field which the table is going to be partitioned on that
is not primary key.
the other question is: when creating a table if we do not have primary
key , it
accepts the "ON PartitionScheme(FieldName)" but whenever we have
primary key then it says it is created on [primary] istead of the
partition scheme provided.
by the way, the field which the table is going to be partitioned
based
on it is a foreign key.
Thanks a lot,
Ali> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme.
Just because you have a lot of rows doesn't mean you need to partition.
Partitioning can help manageability of tables that are difficult to manage
due to size and can help performance of specialized operations. If your
tables are large and performance is slow, index and query tuning is the best
initial approach. Adding partitioning to the mix without tuning can slow
queries further.

> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be dropped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
The definition of a partitioned table is either a partitioned heap or a
table with a partitioned clustered index. If you want to partition this
table on the FK column, you'll need to either add the partitioning column to
the primary key or create a partitioned clustered index that includes the
partitioning column and change the primary key to a non-partitioned
non-clustered index. Note that when you partition non-clustered indexes
differently than the base table, you lose some of the manageability
benefits.

> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
The clustered primary key specification takes precedence over the table
create ON clause. If the primary key is non-clustered, then you'll end up
with a partitioned table and a non-partitioned index.
In addition to the Books Online, see Kimberly Tripp's white paper for a
thorough partitioning discussion:
http://www.sqlskills.com/resources/...20Beta%20II.htm
Hope this helps.
Dan Guzman
SQL Server MVP
"Ali" <nikzad.a@.gmail.com> wrote in message
news:4bace43a-e047-4305-bf7a-de96542feb5b@.v4g2000hsf.googlegroups.com...
> Hi,
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme. the
> problem is : How can we convert non-partitioned table into
> partitioned one according to a partition scheme?
> in the documentation it introduced 2 methods but I don't know are the
> usable or not? if they are, how can I use them?
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be droped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
>
> by the way, the field which the table is going to be partitioned
> based
> on it is a foreign key.
>
>
> Thanks a lot,
> Ali
>

Conversion of data

Hi,
I have a database with lots of data in it. I now want to change the
database schema to reorganise the data. So tables are changing along with
data types.
Is there a tool/method I could use to transfer the data to a new dbase
with the new tables/types in.
I have found tools to convert Oracle to SQL server, but not to convert
data from one SQL server db to another of different schema.
Sue
If you are using SQL2000 then have a look at the DTS or Import/Export
wizard. If you are using SQL2005 then use SSIS.
Andrew J. Kelly SQL MVP
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E022212A-D86F-4CC5-932D-3383B421C814@.microsoft.com...
> Hi,
> I have a database with lots of data in it. I now want to change the
> database schema to reorganise the data. So tables are changing along with
> data types.
> Is there a tool/method I could use to transfer the data to a new dbase
> with the new tables/types in.
> I have found tools to convert Oracle to SQL server, but not to convert
> data from one SQL server db to another of different schema.
> --
> Sue
sqlsql

Conversion of data

Hi,
I have a database with lots of data in it. I now want to change the
database schema to reorganise the data. So tables are changing along with
data types.
Is there a tool/method I could use to transfer the data to a new dbase
with the new tables/types in.
I have found tools to convert Oracle to SQL server, but not to convert
data from one SQL server db to another of different schema.
--
SueIf you are using SQL2000 then have a look at the DTS or Import/Export
wizard. If you are using SQL2005 then use SSIS.
Andrew J. Kelly SQL MVP
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E022212A-D86F-4CC5-932D-3383B421C814@.microsoft.com...
> Hi,
> I have a database with lots of data in it. I now want to change the
> database schema to reorganise the data. So tables are changing along with
> data types.
> Is there a tool/method I could use to transfer the data to a new dbase
> with the new tables/types in.
> I have found tools to convert Oracle to SQL server, but not to convert
> data from one SQL server db to another of different schema.
> --
> Sue

Conversion of data

Hi,
I have a database with lots of data in it. I now want to change the
database schema to reorganise the data. So tables are changing along with
data types.
Is there a tool/method I could use to transfer the data to a new dbase
with the new tables/types in.
I have found tools to convert Oracle to SQL server, but not to convert
data from one SQL server db to another of different schema.
--
SueIf you are using SQL2000 then have a look at the DTS or Import/Export
wizard. If you are using SQL2005 then use SSIS.
--
Andrew J. Kelly SQL MVP
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E022212A-D86F-4CC5-932D-3383B421C814@.microsoft.com...
> Hi,
> I have a database with lots of data in it. I now want to change the
> database schema to reorganise the data. So tables are changing along with
> data types.
> Is there a tool/method I could use to transfer the data to a new dbase
> with the new tables/types in.
> I have found tools to convert Oracle to SQL server, but not to convert
> data from one SQL server db to another of different schema.
> --
> Sue

conversion char/nchar

Hi,
We are using sql server 2000..We intend to change the
char/varchar columns to nchar/nvarchat.Our application
contains lot's of dynamic tables.Hence there are no
standard no of tables and indexes in all sites..Can any
one help me out in writing scripts which queries the
dictionary objects and gives scripts which would work fine
in all sites...?
Thanks in advance
Sridhar
This script should get you started:
use tempdb
GO
CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar(10), thircol =
char(10))
go
SELECT 'ALTER TABLE ' + table_name +
' ALTER COLUMN ' + COLUMN_NAME +
CASE WHEN DATA_TYPE =3D 'char' THEN ' nchar ' ELSE ' nvarchar ' =
END +=20
' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
char(13) + char(10) + 'GO'
FROM information_schema.columns WHERE DATA_TYPE IN ('varchar', 'char')
--now execute the statements that are returned from the select statement
go
DROP TABLE foo=20
--=20
Keith
<anonymous@.discussions.microsoft.com> wrote in message =
news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
> Hi,
>=20
> We are using sql server 2000..We intend to change the=20
> char/varchar columns to nchar/nvarchat.Our application=20
> contains lot's of dynamic tables.Hence there are no=20
> standard no of tables and indexes in all sites..Can any=20
> one help me out in writing scripts which queries the=20
> dictionary objects and gives scripts which would work fine=20
> in all sites...?
>=20
>=20
> Thanks in advance
>=20
> Sridhar
>
|||Hi,
Thanks..do some where sql server stores the index and
constraints structure some where in dictionary ...other
wise how do i recreate the indexes and constraints after
converting to nchar
Sridhar
>--Original Message--
>This script should get you started:
>use tempdb
>GO
>CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar
(10), thircol char(10))
>go
>
>SELECT 'ALTER TABLE ' + table_name +
> ' ALTER COLUMN ' + COLUMN_NAME +
> CASE WHEN DATA_TYPE = 'char' THEN ' nchar ' ELSE '
nvarchar ' END +
> ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
> char(13) + char(10) + 'GO'
>FROM information_schema.columns WHERE DATA_TYPE IN
('varchar', 'char')
>--now execute the statements that are returned from the
select statement
>go
>DROP TABLE foo
>
>
>--
>Keith
>
><anonymous@.discussions.microsoft.com> wrote in message
news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...[vbcol=seagreen]
fine
>.
>
|||SQL Server stores this information in system tables, like sysindexes, syscomments etc. You can read off of
these and use that information to re-generate the statements needed to re-create your stuff. Or script the
stuff: http://www.karaszi.com/sqlserver/inf...te_script.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:898401c432a5$31453bb0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> Thanks..do some where sql server stores the index and
> constraints structure some where in dictionary ...other
> wise how do i recreate the indexes and constraints after
> converting to nchar
> Sridhar
> (10), thircol char(10))
> nvarchar ' END +
> ('varchar', 'char')
> select statement
> news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
> fine
sqlsql