Moving Tables/File Groups

Let's say you want to move a table from file group A to file group B. What
is the easiest way to do that? Create a differently named table in file
group B and then DTS over the data and then do renames? Is there an ALTER
TABLE statement that will work and just move it over?
If I remember right, there's a trick if it has a clustered index but most
or at least many of these tables will not have a clustered index.
Any help would be appreciated.
Create a clustered index using the CREATE INDEX statement with the ON
FILEGROUP clause.
HTH
Jerry
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
> Let's say you want to move a table from file group A to file group B.
> What
> is the easiest way to do that? Create a differently named table in file
> group B and then DTS over the data and then do renames? Is there an ALTER
> TABLE statement that will work and just move it over?
> If I remember right, there's a trick if it has a clustered index but most
> or at least many of these tables will not have a clustered index.
> Any help would be appreciated.
|||If you don't have a clustered index then you will have to create a new table
on the new filegroup and move the data over.
David Portas
SQL Server MVP
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
> Let's say you want to move a table from file group A to file group B.
> What
> is the easiest way to do that? Create a differently named table in file
> group B and then DTS over the data and then do renames? Is there an ALTER
> TABLE statement that will work and just move it over?
> If I remember right, there's a trick if it has a clustered index but most
> or at least many of these tables will not have a clustered index.
> Any help would be appreciated.
|||David,
Is that correct? If he doesn't have a clustered index won't creating the
clustered index on the other filegroup move the data to the new filegroup?
HTH
Jerry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:r8CdnRQBjquCbd_enZ2dnUVZ8qCdnZ2d@.giganews.com ...
> If you don't have a clustered index then you will have to create a new
> table on the new filegroup and move the data over.
> --
> David Portas
> SQL Server MVP
> --
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
>
|||That's true. What I meant was that if you don't *want* to have a clustered
index you will have to create a new table. Of course it's questionable
whether it makes sense for many or most tables not to have a clustered
index. Sometimes you don't want a clustered index but more often it makes
sense to have one on every table.
David Portas
SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:udkaeiSyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> David,
> Is that correct? If he doesn't have a clustered index won't creating the
> clustered index on the other filegroup move the data to the new filegroup?
> HTH
> Jerry
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:r8CdnRQBjquCbd_enZ2dnUVZ8qCdnZ2d@.giganews.com ...
>

Moving Tables/File Groups

Let's say you want to move a table from file group A to file group B. What
is the easiest way to do that? Create a differently named table in file
group B and then DTS over the data and then do renames? Is there an ALTER
TABLE statement that will work and just move it over?
If I remember right, there's a trick if it has a clustered index but most
or at least many of these tables will not have a clustered index.
Any help would be appreciated.Create a clustered index using the CREATE INDEX statement with the ON
FILEGROUP clause.
HTH
Jerry
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
> Let's say you want to move a table from file group A to file group B.
> What
> is the easiest way to do that? Create a differently named table in file
> group B and then DTS over the data and then do renames? Is there an ALTER
> TABLE statement that will work and just move it over?
> If I remember right, there's a trick if it has a clustered index but most
> or at least many of these tables will not have a clustered index.
> Any help would be appreciated.|||If you don't have a clustered index then you will have to create a new table
on the new filegroup and move the data over.
David Portas
SQL Server MVP
--
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
> Let's say you want to move a table from file group A to file group B.
> What
> is the easiest way to do that? Create a differently named table in file
> group B and then DTS over the data and then do renames? Is there an ALTER
> TABLE statement that will work and just move it over?
> If I remember right, there's a trick if it has a clustered index but most
> or at least many of these tables will not have a clustered index.
> Any help would be appreciated.|||David,
Is that correct? If he doesn't have a clustered index won't creating the
clustered index on the other filegroup move the data to the new filegroup?
HTH
Jerry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:r8CdnRQBjquCbd_enZ2dnUVZ8qCdnZ2d@.gi
ganews.com...
> If you don't have a clustered index then you will have to create a new
> table on the new filegroup and move the data over.
> --
> David Portas
> SQL Server MVP
> --
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
>|||That's true. What I meant was that if you don't *want* to have a clustered
index you will have to create a new table. Of course it's questionable
whether it makes sense for many or most tables not to have a clustered
index. Sometimes you don't want a clustered index but more often it makes
sense to have one on every table.
David Portas
SQL Server MVP
--
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:udkaeiSyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> David,
> Is that correct? If he doesn't have a clustered index won't creating the
> clustered index on the other filegroup move the data to the new filegroup?
> HTH
> Jerry
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:r8CdnRQBjquCbd_enZ2dnUVZ8qCdnZ2d@.gi
ganews.com...
>

Moving Tables/File Groups

Let's say you want to move a table from file group A to file group B. What
is the easiest way to do that? Create a differently named table in file
group B and then DTS over the data and then do renames? Is there an ALTER
TABLE statement that will work and just move it over?
If I remember right, there's a trick if it has a clustered index but most
or at least many of these tables will not have a clustered index.
Any help would be appreciated.Create a clustered index using the CREATE INDEX statement with the ON
FILEGROUP clause.
HTH
Jerry
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
> Let's say you want to move a table from file group A to file group B.
> What
> is the easiest way to do that? Create a differently named table in file
> group B and then DTS over the data and then do renames? Is there an ALTER
> TABLE statement that will work and just move it over?
> If I remember right, there's a trick if it has a clustered index but most
> or at least many of these tables will not have a clustered index.
> Any help would be appreciated.|||If you don't have a clustered index then you will have to create a new table
on the new filegroup and move the data over.
--
David Portas
SQL Server MVP
--
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
> Let's say you want to move a table from file group A to file group B.
> What
> is the easiest way to do that? Create a differently named table in file
> group B and then DTS over the data and then do renames? Is there an ALTER
> TABLE statement that will work and just move it over?
> If I remember right, there's a trick if it has a clustered index but most
> or at least many of these tables will not have a clustered index.
> Any help would be appreciated.|||David,
Is that correct? If he doesn't have a clustered index won't creating the
clustered index on the other filegroup move the data to the new filegroup?
HTH
Jerry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:r8CdnRQBjquCbd_enZ2dnUVZ8qCdnZ2d@.giganews.com...
> If you don't have a clustered index then you will have to create a new
> table on the new filegroup and move the data over.
> --
> David Portas
> SQL Server MVP
> --
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
>> Let's say you want to move a table from file group A to file group B.
>> What
>> is the easiest way to do that? Create a differently named table in file
>> group B and then DTS over the data and then do renames? Is there an
>> ALTER
>> TABLE statement that will work and just move it over?
>> If I remember right, there's a trick if it has a clustered index but most
>> or at least many of these tables will not have a clustered index.
>> Any help would be appreciated.
>|||That's true. What I meant was that if you don't *want* to have a clustered
index you will have to create a new table. Of course it's questionable
whether it makes sense for many or most tables not to have a clustered
index. Sometimes you don't want a clustered index but more often it makes
sense to have one on every table.
--
David Portas
SQL Server MVP
--
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:udkaeiSyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> David,
> Is that correct? If he doesn't have a clustered index won't creating the
> clustered index on the other filegroup move the data to the new filegroup?
> HTH
> Jerry
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:r8CdnRQBjquCbd_enZ2dnUVZ8qCdnZ2d@.giganews.com...
>> If you don't have a clustered index then you will have to create a new
>> table on the new filegroup and move the data over.
>> --
>> David Portas
>> SQL Server MVP
>> --
>> "CLM" <CLM@.discussions.microsoft.com> wrote in message
>> news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
>> Let's say you want to move a table from file group A to file group B.
>> What
>> is the easiest way to do that? Create a differently named table in file
>> group B and then DTS over the data and then do renames? Is there an
>> ALTER
>> TABLE statement that will work and just move it over?
>> If I remember right, there's a trick if it has a clustered index but
>> most
>> or at least many of these tables will not have a clustered index.
>> Any help would be appreciated.
>>
>sql

moving tables

Hi,
ms-sqlserver 2000 sp4 - entrerprise edition
Can you help me on this issue. I would like to move some user
tables(about 100 MB) to new file group. Can you tell me what I need to
do?
Thanks in advance,
If you have clustered index on all the tables then just recreate the
clustered and non-clustered indexes and specify the new filegroup. Else
you can bcp out the data and then create the table in new file group and bcp
in the data.
"he_msql" wrote:

> Hi,
> ms-sqlserver 2000 sp4 - entrerprise edition
> Can you help me on this issue. I would like to move some user
> tables(about 100 MB) to new file group. Can you tell me what I need to
> do?
> Thanks in advance,
>
|||Hi,
Due to the details of moving tables (i.e. moving constraints, etc), it is
considered best practice to allow Enterprise Manager do the work for you.
Enterprise Manager knows what to move. I haven't found a stored procedure to
do the trick, so you will have deal with the GUI of Enterprise Manager. If
you happen to know one, please post.
This subject is located in the Books Online under 'filegroups:switching
filegroup for table'. Here is the procedure...
How to place an existing table on a different filegroup (Enterprise Manager)
To place an existing table on a different filegroup
1. Expand a server group, and then expand a server.
2. Expand Databases, expand the database in which the table belongs, and
then click Tables.
3. In the details pane, right-click the table, and then click Design Table.
4. Right-click any column, and then click Properties.
5. On the Tables tab, in the Table Filegroup list, select the filegroup on
which to place the table.
6. Optionally, in the Text Filegroup list, select a filegroup on which to
place any text, image, and ntext columns.
HTH,
Adam
"he_msql" wrote:

> Hi,
> ms-sqlserver 2000 sp4 - entrerprise edition
> Can you help me on this issue. I would like to move some user
> tables(about 100 MB) to new file group. Can you tell me what I need to
> do?
> Thanks in advance,
>
|||If the tables are defined relations (i.e. Foreign Key constraints), then
it is probably easiest to use Enterprise Manager to do the work and/or
create the script to do the work.
If there are no tables referencing the table you want to move, and if
this table has a clustered index, then the most efficient way to move
it, is to recreate the clustered index with the original name and
definition with "CREATE INDEX ... ON ...(...) WITH DROP_EXISTING ON
[your target filegroup]"
The same method can be used for nonclustered indexes.
If the table is a heap, then create a clustered index ON [your target
filegroup] and drop the clustered index afterwards.
HTH,
Gert-Jan
he_msql wrote:
> Hi,
> ms-sqlserver 2000 sp4 - entrerprise edition
> Can you help me on this issue. I would like to move some user
> tables(about 100 MB) to new file group. Can you tell me what I need to
> do?
> Thanks in advance,

moving tables

Hi,
ms-sqlserver 2000 sp4 - entrerprise edition
Can you help me on this issue. I would like to move some user
tables(about 100 MB) to new file group. Can you tell me what I need to
do?
Thanks in advance,If you have clustered index on all the tables then just recreate the
clustered and non-clustered indexes and specify the new filegroup. Else
you can bcp out the data and then create the table in new file group and bcp
in the data.
"he_msql" wrote:

> Hi,
> ms-sqlserver 2000 sp4 - entrerprise edition
> Can you help me on this issue. I would like to move some user
> tables(about 100 MB) to new file group. Can you tell me what I need to
> do?
> Thanks in advance,
>|||Hi,
Due to the details of moving tables (i.e. moving constraints, etc), it is
considered best practice to allow Enterprise Manager do the work for you.
Enterprise Manager knows what to move. I haven't found a stored procedure t
o
do the trick, so you will have deal with the GUI of Enterprise Manager. If
you happen to know one, please post.
This subject is located in the Books Online under 'filegroups:switching
filegroup for table'. Here is the procedure...
How to place an existing table on a different filegroup (Enterprise Manager)
To place an existing table on a different filegroup
1. Expand a server group, and then expand a server.
2. Expand Databases, expand the database in which the table belongs, and
then click Tables.
3. In the details pane, right-click the table, and then click Design Table.
4. Right-click any column, and then click Properties.
5. On the Tables tab, in the Table Filegroup list, select the filegroup on
which to place the table.
6. Optionally, in the Text Filegroup list, select a filegroup on which to
place any text, image, and ntext columns.
HTH,
Adam
"he_msql" wrote:

> Hi,
> ms-sqlserver 2000 sp4 - entrerprise edition
> Can you help me on this issue. I would like to move some user
> tables(about 100 MB) to new file group. Can you tell me what I need to
> do?
> Thanks in advance,
>|||If the tables are defined relations (i.e. Foreign Key constraints), then
it is probably easiest to use Enterprise Manager to do the work and/or
create the script to do the work.
If there are no tables referencing the table you want to move, and if
this table has a clustered index, then the most efficient way to move
it, is to recreate the clustered index with the original name and
definition with "CREATE INDEX ... ON ...(...) WITH DROP_EXISTING ON
[your target filegroup]"
The same method can be used for nonclustered indexes.
If the table is a heap, then create a clustered index ON [your target
filegroup] and drop the clustered index afterwards.
HTH,
Gert-Jan
he_msql wrote:
> Hi,
> ms-sqlserver 2000 sp4 - entrerprise edition
> Can you help me on this issue. I would like to move some user
> tables(about 100 MB) to new file group. Can you tell me what I need to
> do?
> Thanks in advance,

moving tables

Hi,

ms-sqlserver 2000 sp4 - entrerprise edition

Can you help me on this issue. I would like to move some user
tables(about 100 MB) to new file group. Can you tell me what I need to
do?

Thanks in advance1)backup db
2)generate the scripts for the tables
3)recreate the scripts in the other db
4)transfer data ,,by either DTS or export/import -depending on whether you
can connect to other filegroup

--
Jack Vamvas
__________________________________________________ ________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"helen_msql" <elena.kolovarsky@.gmail.com> wrote in message
news:1138694855.603631.161990@.g47g2000cwa.googlegr oups.com...
> Hi,
> ms-sqlserver 2000 sp4 - entrerprise edition
> Can you help me on this issue. I would like to move some user
> tables(about 100 MB) to new file group. Can you tell me what I need to
> do?
> Thanks in advance|||helen_msql (elena.kolovarsky@.gmail.com) writes:
> ms-sqlserver 2000 sp4 - entrerprise edition
> Can you help me on this issue. I would like to move some user
> tables(about 100 MB) to new file group. Can you tell me what I need to
> do?

If the table has a clustered index rerun the CREATE INDEX statement
with WITH DROP_EXISTING ON NewFileGroup.

If the table does not have a clustered index, create one that uses
the ON clause, and then drop the index.

As the data is the at leaf level of the clustered index, this will move
the data as well as the index. If there are non-clustered indexes on the
table, then you can move these in the same way as well.

Disclaimer: I have never actually done myself, as I've never worked
with multiple filegroups.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

moving tables

Hi,
ms-sqlserver 2000 sp4 - entrerprise edition
Can you help me on this issue. I would like to move some user
tables(about 100 MB) to new file group. Can you tell me what I need to
do?
Thanks in advance,If you have clustered index on all the tables then just recreate the
clustered and non-clustered indexes and specify the new filegroup. Else
you can bcp out the data and then create the table in new file group and bcp
in the data.
"he_msql" wrote:
> Hi,
> ms-sqlserver 2000 sp4 - entrerprise edition
> Can you help me on this issue. I would like to move some user
> tables(about 100 MB) to new file group. Can you tell me what I need to
> do?
> Thanks in advance,
>|||Hi,
Due to the details of moving tables (i.e. moving constraints, etc), it is
considered best practice to allow Enterprise Manager do the work for you.
Enterprise Manager knows what to move. I haven't found a stored procedure to
do the trick, so you will have deal with the GUI of Enterprise Manager. If
you happen to know one, please post.
This subject is located in the Books Online under 'filegroups:switching
filegroup for table'. Here is the procedure...
How to place an existing table on a different filegroup (Enterprise Manager)
To place an existing table on a different filegroup
1. Expand a server group, and then expand a server.
2. Expand Databases, expand the database in which the table belongs, and
then click Tables.
3. In the details pane, right-click the table, and then click Design Table.
4. Right-click any column, and then click Properties.
5. On the Tables tab, in the Table Filegroup list, select the filegroup on
which to place the table.
6. Optionally, in the Text Filegroup list, select a filegroup on which to
place any text, image, and ntext columns.
HTH,
Adam
"he_msql" wrote:
> Hi,
> ms-sqlserver 2000 sp4 - entrerprise edition
> Can you help me on this issue. I would like to move some user
> tables(about 100 MB) to new file group. Can you tell me what I need to
> do?
> Thanks in advance,
>|||If the tables are defined relations (i.e. Foreign Key constraints), then
it is probably easiest to use Enterprise Manager to do the work and/or
create the script to do the work.
If there are no tables referencing the table you want to move, and if
this table has a clustered index, then the most efficient way to move
it, is to recreate the clustered index with the original name and
definition with "CREATE INDEX ... ON ...(...) WITH DROP_EXISTING ON
[your target filegroup]"
The same method can be used for nonclustered indexes.
If the table is a heap, then create a clustered index ON [your target
filegroup] and drop the clustered index afterwards.
HTH,
Gert-Jan
he_msql wrote:
> Hi,
> ms-sqlserver 2000 sp4 - entrerprise edition
> Can you help me on this issue. I would like to move some user
> tables(about 100 MB) to new file group. Can you tell me what I need to
> do?
> Thanks in advance,

MOVING SSIS PACKAGES FROM ONE PATH TO ANOTHER ONE

hi, does anyone know how can i move a group of ssis packages from the original path into another one in the same server? from ssis services? just like we do it with the windows explorer?

thanks for your help!!!!!!!!!!

May be it’s not the easiest way to do it, but it works for me:

1. Open MS Visual Studio, and create a new Integration Services project if you already don’t have one.

2. In the solution Explorer under SSIS packages - right click on the folder – Add existing packages (add original packages)

3. Right click on the project – Property.Specify the output path for your build.Click on Deployment utility and set “Create DepoloymentUtility” to true.Specify the output Path. Click OK.

4. Right click on the project – Build.The packages with a deployment utility should be under the directory specified in step 3.

5. Copy the entire deployment folder and paste it to the server you want to move the packages to.

6. Log in to the server and navigate to the directory made in step 5.Double click on the xxx.SSISDeploymentManifest file to initiate the deployment wizard.

a. Choose File System Deployment (Next)

b. Choose the new folder where you want your packages to be copied to (Next)

c. Finish the wizard, it should create packages in the new directory

Hope it helps…

|||

thanks it works fine, just for the record, there is another option i've found. using the 'dtutil' tool

there it is an example of it:

dtutil /MOVE SQL;destPackage /SQL srcPackage /SOURCEUSER srcUserName /SOURCEPASSWORD $Hj45jhd@.X /DESTUSER destUserName /DESTPASSWORD !38dsFH@.v||| Looks much cleaner..

Moving SQL DBs from machine to another with the same name

hello everyone. i am new to the group so i aplogize if this question has
been answered recently...

our sql db machine is too old. we bought a new server with which to replace
it. the trick is, we want to keep the name of the machine and db instance
the same.

obviously, we don't plan on having two machine with the same name on the
network at the same time. how do we handle this?

my first thought is to name the new machine with a new name (mach2),
transfer over all the db's, take the old one (mach1) off of the network.
rename the machine/instance to the old one (mach1).

if i do this, can i drop the original instance on the new machine
(mach2)-->register the new (mach1)? will my data be there?

Thanks so much for any relplies!!!Go Browns! (go@.browns.com) writes:
> hello everyone. i am new to the group so i aplogize if this question has
> been answered recently...
> our sql db machine is too old. we bought a new server with which to
> replace it. the trick is, we want to keep the name of the machine and
> db instance the same.
> obviously, we don't plan on having two machine with the same name on the
> network at the same time. how do we handle this?
> my first thought is to name the new machine with a new name (mach2),
> transfer over all the db's, take the old one (mach1) off of the network.
> rename the machine/instance to the old one (mach1).
> if i do this, can i drop the original instance on the new machine
> (mach2)-->register the new (mach1)? will my data be there?

This KB article gives some good advice
http://support.microsoft.com/defaul...1&Product=sql2k

You have have to use sp_dropserver and sp_addserver to get the name
of the server to be correct. Check SELECT @.@.servername, to see that it
has the right name.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||i will give this a shot tomorrow.

i really appreciate your feedback!

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94175A62E6CCYazorman@.127.0.0.1...
> Go Browns! (go@.browns.com) writes:
> > hello everyone. i am new to the group so i aplogize if this question
has
> > been answered recently...
> > our sql db machine is too old. we bought a new server with which to
> > replace it. the trick is, we want to keep the name of the machine and
> > db instance the same.
> > obviously, we don't plan on having two machine with the same name on the
> > network at the same time. how do we handle this?
> > my first thought is to name the new machine with a new name (mach2),
> > transfer over all the db's, take the old one (mach1) off of the network.
> > rename the machine/instance to the old one (mach1).
> > if i do this, can i drop the original instance on the new machine
> > (mach2)-->register the new (mach1)? will my data be there?
> This KB article gives some good advice
http://support.microsoft.com/defaul...1&Product=sql2k
> You have have to use sp_dropserver and sp_addserver to get the name
> of the server to be correct. Check SELECT @.@.servername, to see that it
> has the right name.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Moving rows to columns

I've seen this answered a number of times in the group, but now that I
actually need the answer I don't know where to look or what to put in
the search.

The question is this:

I have a query that produces these reults:

Col1 Col2
A B
A C
A D
B A
B C
B D

I would like take the above reults, manipulate them and get these:

Col1 Col2 COl3 Col4
A B C D
B A C D

Right now I've got a loop through a cursor, which is ok. It works,
but I'm worried about it because if I end up with a large table in the
future, the longer it will take to run the query with the curors.

Can someone help me please - point me to previous answers to this
question?

Thanks,
JenniferHi

I would search for crosstab!

Maybe a starter?

http://tinyurl.com/i9mt

John

"Jennifer" <jennifer1970@.hotmail.com> wrote in message
news:3358f49d.0310060601.1ca0ebf3@.posting.google.c om...
> I've seen this answered a number of times in the group, but now that I
> actually need the answer I don't know where to look or what to put in
> the search.
> The question is this:
> I have a query that produces these reults:
>
> Col1 Col2
> A B
> A C
> A D
> B A
> B C
> B D
>
> I would like take the above reults, manipulate them and get these:
> Col1 Col2 COl3 Col4
> A B C D
> B A C D
>
> Right now I've got a loop through a cursor, which is ok. It works,
> but I'm worried about it because if I end up with a large table in the
> future, the longer it will take to run the query with the curors.
> Can someone help me please - point me to previous answers to this
> question?
> Thanks,
> Jennifer

Moving objects from Primary File Group using T-SQL

Hello All;
I am trying to spread database objects by moving certain tables and indexes
to file groups that I have created just for this purpose. Right now,
everything is in the PRIMARY file group, and I have created a secondary Data
filegroup and a third file group for indexes.
I see in BOL the syntax for moving an index (actaully dropping and
re-creating) but don't see how to move just a single table. I can't seem to
get the syntax correct.
ALTER TABLE <My Table>
DROP CONSTRAINT <My Constraint>
WITH (ONLINE = ON, MOVE TO <My File Group> )
That works, but only if I have something to drop. I don't want to drop the
PK Constraints, I want to move them to one file group and the table itself t
o
another.
Any suggestions would be appreciated.
--
Todd C(Re)Create a clustered index on the table of concern and specify the
filegroup then.
TheSQLGuru
President
Indicium Resources, Inc.
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
> Hello All;
> I am trying to spread database objects by moving certain tables and
> indexes
> to file groups that I have created just for this purpose. Right now,
> everything is in the PRIMARY file group, and I have created a secondary
> Data
> filegroup and a third file group for indexes.
> I see in BOL the syntax for moving an index (actaully dropping and
> re-creating) but don't see how to move just a single table. I can't seem
> to
> get the syntax correct.
> ALTER TABLE <My Table>
> DROP CONSTRAINT <My Constraint>
> WITH (ONLINE = ON, MOVE TO <My File Group> )
> That works, but only if I have something to drop. I don't want to drop the
> PK Constraints, I want to move them to one file group and the table itself
> to
> another.
> Any suggestions would be appreciated.
> --
> Todd C|||Todd, note that this moves the data, the clustered index and all
non-clustered indexes to the new filegroup. By definition, you cannot put
the clustered index on one filegroup, the data on another, and non-clustered
indexes on yet others...
Aaron Bertrand
SQL Server MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
> (Re)Create a clustered index on the table of concern and specify the
> filegroup then.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
>|||Hello Aaron;
So a table's data and indexes must all be in the same filegroup?
Leaving Table Partitioning out of the discussuion, what is the best way to
increase performance on tables that are heavily used and heavily indexed?
Our databases have been relatively small up til now and I foresee that in
the near future I am going to need to look at tuning and performance issues.
What is the best approach?
Thanks in advance.
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:

> Todd, note that this moves the data, the clustered index and all
> non-clustered indexes to the new filegroup. By definition, you cannot put
> the clustered index on one filegroup, the data on another, and non-cluster
ed
> indexes on yet others...
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
>
>|||> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
Are the performance problems on inserting, querying, something else? The
"best" approach is pretty subjective, and depends on a lot of factors,
including budget.
Aaron Bertrand
SQL Server MVP|||Inserting/querrying/updating...not sure where the load is coming from at thi
s
point. Currently running a tuning profile to analyze later.I just know that
this one database is the heaviest used (but not the biggest) on this one
server, and the one data drive is experiencing some heavy load.
Two new drives have been added (RAID, dedicated channels, etc) for me to
start spreading out the load. OK, So it looks like I'll be studying the blac
k
art of database performance tuning for a while...
Any good books or resources out there?
--
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:

> Are the performance problems on inserting, querying, something else? The
> "best" approach is pretty subjective, and depends on a lot of factors,
> including budget.
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||> So a table's data and indexes must all be in the same filegroup?
No. I don't think that was what Aaron's meant.
A table and it's *clustered index* need to be on the same file group because
the clustered index
*is* the table. But you can have non-clustered indexes on other filegroup(s)
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:A83833FE-CCDE-47C4-AC4F-D0404CD12B8F@.microsoft.com...[vbcol=seagreen]
> Hello Aaron;
> So a table's data and indexes must all be in the same filegroup?
> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
> Our databases have been relatively small up til now and I foresee that in
> the near future I am going to need to look at tuning and performance issue
s.
> What is the best approach?
> Thanks in advance.
> --
> Todd C
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Hello Tibor:
OK, I think I got it now. That makes more sense
Thanks
Todd C

Moving objects from Primary File Group using T-SQL

Hello All;
I am trying to spread database objects by moving certain tables and indexes
to file groups that I have created just for this purpose. Right now,
everything is in the PRIMARY file group, and I have created a secondary Data
filegroup and a third file group for indexes.
I see in BOL the syntax for moving an index (actaully dropping and
re-creating) but don't see how to move just a single table. I can't seem to
get the syntax correct.
ALTER TABLE <My Table>
DROP CONSTRAINT <My Constraint>
WITH (ONLINE = ON, MOVE TO <My File Group>)
That works, but only if I have something to drop. I don't want to drop the
PK Constraints, I want to move them to one file group and the table itself to
another.
Any suggestions would be appreciated.
Todd C
(Re)Create a clustered index on the table of concern and specify the
filegroup then.
TheSQLGuru
President
Indicium Resources, Inc.
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
> Hello All;
> I am trying to spread database objects by moving certain tables and
> indexes
> to file groups that I have created just for this purpose. Right now,
> everything is in the PRIMARY file group, and I have created a secondary
> Data
> filegroup and a third file group for indexes.
> I see in BOL the syntax for moving an index (actaully dropping and
> re-creating) but don't see how to move just a single table. I can't seem
> to
> get the syntax correct.
> ALTER TABLE <My Table>
> DROP CONSTRAINT <My Constraint>
> WITH (ONLINE = ON, MOVE TO <My File Group>)
> That works, but only if I have something to drop. I don't want to drop the
> PK Constraints, I want to move them to one file group and the table itself
> to
> another.
> Any suggestions would be appreciated.
> --
> Todd C
|||Todd, note that this moves the data, the clustered index and all
non-clustered indexes to the new filegroup. By definition, you cannot put
the clustered index on one filegroup, the data on another, and non-clustered
indexes on yet others...
Aaron Bertrand
SQL Server MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
> (Re)Create a clustered index on the table of concern and specify the
> filegroup then.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
>
|||Hello Aaron;
So a table's data and indexes must all be in the same filegroup?
Leaving Table Partitioning out of the discussuion, what is the best way to
increase performance on tables that are heavily used and heavily indexed?
Our databases have been relatively small up til now and I foresee that in
the near future I am going to need to look at tuning and performance issues.
What is the best approach?
Thanks in advance.
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:

> Todd, note that this moves the data, the clustered index and all
> non-clustered indexes to the new filegroup. By definition, you cannot put
> the clustered index on one filegroup, the data on another, and non-clustered
> indexes on yet others...
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
>
>
|||> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
Are the performance problems on inserting, querying, something else? The
"best" approach is pretty subjective, and depends on a lot of factors,
including budget.
Aaron Bertrand
SQL Server MVP
|||Inserting/querrying/updating...not sure where the load is coming from at this
point. Currently running a tuning profile to analyze later.I just know that
this one database is the heaviest used (but not the biggest) on this one
server, and the one data drive is experiencing some heavy load.
Two new drives have been added (RAID, dedicated channels, etc) for me to
start spreading out the load. OK, So it looks like I'll be studying the black
art of database performance tuning for a while...
Any good books or resources out there?
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:

> Are the performance problems on inserting, querying, something else? The
> "best" approach is pretty subjective, and depends on a lot of factors,
> including budget.
> --
> Aaron Bertrand
> SQL Server MVP
>
>
|||> So a table's data and indexes must all be in the same filegroup?
No. I don't think that was what Aaron's meant.
A table and it's *clustered index* need to be on the same file group because the clustered index
*is* the table. But you can have non-clustered indexes on other filegroup(s).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:A83833FE-CCDE-47C4-AC4F-D0404CD12B8F@.microsoft.com...[vbcol=seagreen]
> Hello Aaron;
> So a table's data and indexes must all be in the same filegroup?
> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
> Our databases have been relatively small up til now and I foresee that in
> the near future I am going to need to look at tuning and performance issues.
> What is the best approach?
> Thanks in advance.
> --
> Todd C
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||Hello Tibor:
OK, I think I got it now. That makes more sense
Thanks
Todd C

Moving objects from Primary File Group using T-SQL

Hello All;
I am trying to spread database objects by moving certain tables and indexes
to file groups that I have created just for this purpose. Right now,
everything is in the PRIMARY file group, and I have created a secondary Data
filegroup and a third file group for indexes.
I see in BOL the syntax for moving an index (actaully dropping and
re-creating) but don't see how to move just a single table. I can't seem to
get the syntax correct.
ALTER TABLE <My Table>
DROP CONSTRAINT <My Constraint>
WITH (ONLINE = ON, MOVE TO <My File Group>)
That works, but only if I have something to drop. I don't want to drop the
PK Constraints, I want to move them to one file group and the table itself to
another.
Any suggestions would be appreciated.
--
Todd C(Re)Create a clustered index on the table of concern and specify the
filegroup then.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
> Hello All;
> I am trying to spread database objects by moving certain tables and
> indexes
> to file groups that I have created just for this purpose. Right now,
> everything is in the PRIMARY file group, and I have created a secondary
> Data
> filegroup and a third file group for indexes.
> I see in BOL the syntax for moving an index (actaully dropping and
> re-creating) but don't see how to move just a single table. I can't seem
> to
> get the syntax correct.
> ALTER TABLE <My Table>
> DROP CONSTRAINT <My Constraint>
> WITH (ONLINE = ON, MOVE TO <My File Group>)
> That works, but only if I have something to drop. I don't want to drop the
> PK Constraints, I want to move them to one file group and the table itself
> to
> another.
> Any suggestions would be appreciated.
> --
> Todd C|||Todd, note that this moves the data, the clustered index and all
non-clustered indexes to the new filegroup. By definition, you cannot put
the clustered index on one filegroup, the data on another, and non-clustered
indexes on yet others...
--
Aaron Bertrand
SQL Server MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
> (Re)Create a clustered index on the table of concern and specify the
> filegroup then.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
>> Hello All;
>> I am trying to spread database objects by moving certain tables and
>> indexes
>> to file groups that I have created just for this purpose. Right now,
>> everything is in the PRIMARY file group, and I have created a secondary
>> Data
>> filegroup and a third file group for indexes.
>> I see in BOL the syntax for moving an index (actaully dropping and
>> re-creating) but don't see how to move just a single table. I can't seem
>> to
>> get the syntax correct.
>> ALTER TABLE <My Table>
>> DROP CONSTRAINT <My Constraint>
>> WITH (ONLINE = ON, MOVE TO <My File Group>)
>> That works, but only if I have something to drop. I don't want to drop
>> the
>> PK Constraints, I want to move them to one file group and the table
>> itself to
>> another.
>> Any suggestions would be appreciated.
>> --
>> Todd C
>|||Hello Aaron;
So a table's data and indexes must all be in the same filegroup?
Leaving Table Partitioning out of the discussuion, what is the best way to
increase performance on tables that are heavily used and heavily indexed?
Our databases have been relatively small up til now and I foresee that in
the near future I am going to need to look at tuning and performance issues.
What is the best approach?
Thanks in advance.
--
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:
> Todd, note that this moves the data, the clustered index and all
> non-clustered indexes to the new filegroup. By definition, you cannot put
> the clustered index on one filegroup, the data on another, and non-clustered
> indexes on yet others...
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
> > (Re)Create a clustered index on the table of concern and specify the
> > filegroup then.
> >
> > --
> > TheSQLGuru
> > President
> > Indicium Resources, Inc.
> >
> > "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> > news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
> >> Hello All;
> >> I am trying to spread database objects by moving certain tables and
> >> indexes
> >> to file groups that I have created just for this purpose. Right now,
> >> everything is in the PRIMARY file group, and I have created a secondary
> >> Data
> >> filegroup and a third file group for indexes.
> >>
> >> I see in BOL the syntax for moving an index (actaully dropping and
> >> re-creating) but don't see how to move just a single table. I can't seem
> >> to
> >> get the syntax correct.
> >>
> >> ALTER TABLE <My Table>
> >> DROP CONSTRAINT <My Constraint>
> >> WITH (ONLINE = ON, MOVE TO <My File Group>)
> >>
> >> That works, but only if I have something to drop. I don't want to drop
> >> the
> >> PK Constraints, I want to move them to one file group and the table
> >> itself to
> >> another.
> >>
> >> Any suggestions would be appreciated.
> >> --
> >> Todd C
> >
> >
>
>|||> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
Are the performance problems on inserting, querying, something else? The
"best" approach is pretty subjective, and depends on a lot of factors,
including budget.
--
Aaron Bertrand
SQL Server MVP|||Inserting/querrying/updating...not sure where the load is coming from at this
point. Currently running a tuning profile to analyze later.I just know that
this one database is the heaviest used (but not the biggest) on this one
server, and the one data drive is experiencing some heavy load.
Two new drives have been added (RAID, dedicated channels, etc) for me to
start spreading out the load. OK, So it looks like I'll be studying the black
art of database performance tuning for a while...
Any good books or resources out there?
--
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Leaving Table Partitioning out of the discussuion, what is the best way to
> > increase performance on tables that are heavily used and heavily indexed?
> Are the performance problems on inserting, querying, something else? The
> "best" approach is pretty subjective, and depends on a lot of factors,
> including budget.
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||> So a table's data and indexes must all be in the same filegroup?
No. I don't think that was what Aaron's meant.
A table and it's *clustered index* need to be on the same file group because the clustered index
*is* the table. But you can have non-clustered indexes on other filegroup(s).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:A83833FE-CCDE-47C4-AC4F-D0404CD12B8F@.microsoft.com...
> Hello Aaron;
> So a table's data and indexes must all be in the same filegroup?
> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
> Our databases have been relatively small up til now and I foresee that in
> the near future I am going to need to look at tuning and performance issues.
> What is the best approach?
> Thanks in advance.
> --
> Todd C
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> Todd, note that this moves the data, the clustered index and all
>> non-clustered indexes to the new filegroup. By definition, you cannot put
>> the clustered index on one filegroup, the data on another, and non-clustered
>> indexes on yet others...
>> --
>> Aaron Bertrand
>> SQL Server MVP
>>
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
>> > (Re)Create a clustered index on the table of concern and specify the
>> > filegroup then.
>> >
>> > --
>> > TheSQLGuru
>> > President
>> > Indicium Resources, Inc.
>> >
>> > "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
>> > news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
>> >> Hello All;
>> >> I am trying to spread database objects by moving certain tables and
>> >> indexes
>> >> to file groups that I have created just for this purpose. Right now,
>> >> everything is in the PRIMARY file group, and I have created a secondary
>> >> Data
>> >> filegroup and a third file group for indexes.
>> >>
>> >> I see in BOL the syntax for moving an index (actaully dropping and
>> >> re-creating) but don't see how to move just a single table. I can't seem
>> >> to
>> >> get the syntax correct.
>> >>
>> >> ALTER TABLE <My Table>
>> >> DROP CONSTRAINT <My Constraint>
>> >> WITH (ONLINE = ON, MOVE TO <My File Group>)
>> >>
>> >> That works, but only if I have something to drop. I don't want to drop
>> >> the
>> >> PK Constraints, I want to move them to one file group and the table
>> >> itself to
>> >> another.
>> >>
>> >> Any suggestions would be appreciated.
>> >> --
>> >> Todd C
>> >
>> >
>>|||Hello Tibor:
OK, I think I got it now. That makes more sense
Thanks
Todd C