Re-arrange Of Tables (putting Them In Seperate Filegroups)
Jan 28, 2008
Hi!
I have a big database which need some separation
in multiple filegroups.
Therefore I plan to move tables to new filegroups.
As I can understand this must be done by
dropping the clustered index and
recreating it on new filegroup, then data will move.
1. best way to get a list of all clustered indexes (sys.indexes)?
2. how to get index scripts? (using SSMS, right click is manual task, but I need scripts for than 100 tables)
3. when having the scripts it should be easy to replace filegroup part
4. after movement: just shrinking old files?
Thanks for help ;-)
View 1 Replies
ADVERTISEMENT
May 22, 2008
My boss has asked me to look into this and I haven't been able to find any information on the web. I hope someone can answer this for me. We currently have a single database that is storing all the user information and transactions. Within the same database we are also logging different types of user activity. If both these tables are heavily used, would it make sense to separate it into different database, one for data and one for logging? Is there any pro or cons of having more than one database? Any opinion or suggestion would be greatly appreciated. I'm the closest thing they have to DBA and I'm really new to this. Thanks.
View 5 Replies
View Related
Jul 5, 2007
Does anyone know how to move tables between filegroups in 2005?
Cogito ergo spam -- I'm pink therefore I'm Spam
View 3 Replies
View Related
Dec 6, 2005
Hey all
For performance reasons i want to move the Merge Replication tables in a DB of mine (mainly MSMerge_Tomestone and MSMerge_Contents) to a seperate filegroup ... it's a heavy transactional DB and the index fragamentation rate is quite phenominal!
View 6 Replies
View Related
Jan 29, 2008
Hi!
When you have many tables with data
- is there an easy way with SSIS to migrate/move to another filegroup?
Greetins
View 5 Replies
View Related
May 10, 2008
I have 4 tables with the respective amount of records
1) 6755
2) 2021
3) 2021
4) 355
They all have the same columns. However, they need to be seperate, or at least when I query them. I'll be accessing this database via the web. i was first afraid that a large database would cause major slow down when accessing the db. So I broke it up into 4 tables. If I combined all 4 tables into one large table and just had a column that differentiated the 4, how significant would be the change in speed when accessing the table? It's not a big deal to keep them seperate, its just that when I have to add or remove a column from one table I have to remove it from all the tables. Furthermore, I'm using a module from DEVEXPRESS, don't know if anyone has heard of it, but when you use a gridview, it loads up the entire table even though your paging (which I think is retarded), so for that reason I was afraid it would slow up my access to the db. Any thoughts?
View 2 Replies
View Related
Jul 20, 2005
Does anyone have any idea how to syncronise two SQL identical tables acrossa network, between servers?We want to run our website from a SQL tables on the same server, but set upso that if the main SQL tables across the network change, or the tables onthe web server change, the two seperate servers willsyncronise/update/trigger updates to the tables on each other?We are running SQL server 2000.AHAJohn
View 2 Replies
View Related
Apr 12, 2006
Hi folks, I've got a fairly easy one here me thinx. I'm looking for the best way to compare two entire rows from two seperate tables which have the same primary key.
Here's the basic lowdown:
I get some data every night from an external system (cache') via DTS. This is more or less my "master" data which drives my application. I have just been informed of an interesting constraint. If any of the data changes in the external system, those changes do not become effective until the first day of the ensuing fiscal quarter.
I'm solving this by running the DTS as normal, but populating a "duplicate" table which I will evaluate once per quarter for any changes. This is also the preferred solution because they would like to see a snapshot between current cache' data and the data my application is currently working with.
So, I end up with two identically structured tables. both tables have the same primary key and can be linked by an id field with relative ease. What I would like to do is a full row comparision once this join is established.
Right now I explicitly check the value of each column. ie:
WHERE t1.field1 <> t2.field1 OR t1.field2 <> t2.field2 OR t1.field3 <> t2.field3 ... etc
I'm hoping there is something buried in TSQL that I just don't know about that can handle comparing entire rows and tell me if they're different. Or perhaps there's another approach all-together.
Any thoughts?
View 5 Replies
View Related
Mar 20, 2008
Hello,
I have two databases in sql server. I'll call them DB1 and DB2. I have a table in DB2 that needs to form a relationship with a table in DB1. When I attempt to add a relationship I only see tables in DB2. Can this be done?
Thanks,
Mark
View 10 Replies
View Related
Apr 13, 2007
Hello,
I don't know if this could be done, but I will present this question...
I have an employee_table with empid, firstName, lastName, phExt columns.
I have another table called location_table that contains locationID, locationName, locPhext.
Here is the dataset from the 2 tables...
From Employee Table....
empid firstName lastName phExt
1 Ann Smith 1234
2 Barb Jones 4567
3 Jeff Teeves 8901
From Location Table
locationID locationName locPhext
1 Computer Room 3245
2 Board Room 1 8745
3 Conference Room 1 4564
Here is the data that I would like to pull in this format...
Ann Smith 1234
Barb Jones 4567
Board Room 1 8745
Computer Room 3245
Conference Room 1 4564
Jeff Teeves 8901
What SQL script could I use to produce the above results?
Thanks in advance
View 3 Replies
View Related
Mar 24, 2007
Hello.
I just created separate tables for each of my categories and wanted to know how to return them all to be viewed on one page using the SQL Datasource (or whatever) This is for user accounts. I just need to know that part.
Sincerely,
Computergirl
View 1 Replies
View Related
Dec 11, 2004
Hi all,
I want to produce some output for Mainframe application. For that I want to insert values from multiple table as source to a single column (huge in size)of a different table (Destination table). There may be same related records in all of the source tables with the primary key. When I export values from the source tables , each related records should be insterted to the destination table's field (multiple entries for each table). Please advise.
Thanks
View 5 Replies
View Related
Oct 1, 2013
I am workong on this Pivot select. At it is last part, I want to find a way to re-arrange the column order. Here is the test data:
if OBJECT_ID ('tempdb..#temp') is not null drop table #temp
go
create table #temp(b_date varchar(15), Gender varchar(5), Admin_date varchar(15), S_ID int, TEST_NAME varchar(5),SCHOOL_YEARvarchar(5), PART_DESCRIPTIONvarchar(15),Test_Score int)
[code]....
But the output requries the last couple columns to be SCHOOL_YEAR, TEST_NAME, i.e. they come after the scores.I thought the easiest way will be to select into #temp2, then extract from there. Even that, I couldn't get that to work with the select pivot.
View 4 Replies
View Related
Jun 8, 2008
Last query, mr. visakh help to produce a result as follow
Date_Taken | Main_ID | Daily_Rainfall
-------------------------------------------
5/1/2005 | 194 | 142
6/1/2005 | 194 | 155
7/1/2005 | 194 | 132
8/1/2005 | 194 | 199
5/1/2005 | 196 | 333
6/1/2005 | 196 | 544
7/1/2005 | 196 | 722
8/1/2005 | 196 | 845
...
...
*Combination of Date_Taken and Main_ID generate unique row
*Above data consist of 1 May 2005 to 1 AUG 2005 data (4 month data for each Main_ID), which is Date_Taken start from 1st day every month.
*Date_Taken | Main_ID | Daily_Rainfall
-------------------------------------------
5/1/2005 | 194 | 142
---> Mean, In May 2005 Main_ID=194 having 142 Daily_Rainfall
6/1/2005 | 196 | 544
---> Mean, In Jun 2005 Main_ID=196 having 544 Daily_Rainfall
Let's say, current month is 8
I stuck to do the query to display result like as follow
Date_Taken | Main_ID | CurrentMonth_DR | LastMonth_DR | Last2Month_DR| Last3Month_DR
--------------------------------------------------------------------------------------
8/1/2005 | 194 | 199 | 132 | 155 | 142
8/1/2005 | 196 | 845 | 722 | 544 | 333
...
...
*CurrentMonth_DR is 8/1/2005, LastMonth_DR is 7/1/2005, Last2Month_DR is 6/1/2005 and so on ( this can be harcoded in sql)
Plz help me
View 1 Replies
View Related
Feb 25, 2008
Hi,I have following data,India 91USA 01UK 44Like this, I have 100 Records (Rows) with 2 Column DataNow I want to have report like belowIndia 91 USA 01 UK 44Pakistan 92 .....How can I do this?Nilesh
View 4 Replies
View Related
Sep 20, 2007
Can the items, i.e. reports within the SSRS Report Designer Solution Explorer be arranged in a custom manner instead of being sorted alphabetically only?
View 2 Replies
View Related
Apr 10, 2008
hi,
i have a sales database as follows
InvNo Date Customer ProductID Qty Rate Amount
1 01/04/2008 XYZ Pro1 100 10.15 1015.00
2 01/04/2008 ABC Pro1 100 10.15 1015.00
3 03/04/2008 XYZ Pro2 100 10.15 1015.00
4 04/04/2008 XYZ Pro2 100 10.15 1015.00
5 05/04/2008 ABC Pro2 100 10.15 1015.00
I Want to generate a daywise report for each product as foolows
ProductID 01/04/2008 02/04/2008 03/04/2008 04/04/2008 upto 30/04/2008 ( may be upto n date)
Pro1 200 - - -
Pro2 - - 100 100
Is it possible using the report viewer (*.rdlc) reports in VB.Net 2005
View 5 Replies
View Related
Jul 31, 2006
i am trying to build a database that contains a coloumn of phone number but i reallly dont know where and how i will be able to give a specific format for phone numbers.. or anything else any common type of information that may have a format..
Will i use Visual Web Developer or SQL Server Management or whatever else to arrange my phone number coloumn?
View 5 Replies
View Related
Apr 19, 2013
i have below table in DB
DB Table
ID
Column
Row data
1
Supplier CODE
1001
2
Supplier Name
ACB
3
Product
7K7
4
Price
1000
now I create one report parameter order1 IF I will give order1.value=1,2,3 then Report will come like this :--
Suppliercode
Supplier Name
Product
1001
ACB
7K7
IF I will give order1.value=3,2,1 then Report will come like this :--
Product
Supplier Name
Suppliercode
7K7
ACB
1001
IF I will give order1.value=1,3 then Report will come like this :--
Suppliercode
Product
1001
7K7
View 35 Replies
View Related
May 25, 2000
I was told to move tables to new filegroups by placing the clustered index on the filegroup and the table would
follow. There are times when I see tables listed on the new filegroup but still listed also on Primary. My goal is
to have only system tables on Primary. How can I get a table to totally leave the PRIMARY group?
There seems to be a system index on the table that was not created by me.
View 1 Replies
View Related
Jan 23, 2001
I have databse that was created when I got here and the database was created with 3 file groups. The tables in the database are spread out over the 3 file groups.
How can I find out which table belongs to which file group.
Thank You,
John
View 1 Replies
View Related
Oct 8, 1999
If I'm running RAID 5, is it still good practice to split system and user data onto separate filegroups ? .....
On separate disks ?
Thanks in advance..
Any comments welcome ! Peter
View 1 Replies
View Related
Oct 8, 1999
If I'm running RAID 5, is it still good practice to split system and user data onto separate filegroups ? .....On separate disks ? Thanks in advance..
Any comments welcome ! Peter
View 1 Replies
View Related
Feb 8, 2006
Hi,
I was wondering if there is a downside of placing individual tables into their own filegroup (i.e., 1:1). This would seem to allow me to continue to backup entire databases but give me the flexibility to restore at a table level if necessary. I ask this because the number of filegroups allowed per database has increased from 256 in SS2000 to 32K in SS2005. What kind of headaches could I get into if I follow this type of design ?
Thanks,
Mario
View 4 Replies
View Related
Sep 22, 2005
i want to obtain information about all the filegroups for every database. is there a special command to obtain this information like the dbcc sqlper or something similar ??
View 1 Replies
View Related
Oct 8, 2007
hi all,
What is the purpose of files?(primary secondary,transaction log ). what is the use of these files?
View 1 Replies
View Related
Jul 21, 2000
Is there a way to list all the tables in each of the filegroup
in a database. I have a database with 7 filegroups and would like
to have a list of tables in each filegroup
Sp_helpfilegroup gives you list of filegroups and the names of files
View 1 Replies
View Related
Jan 23, 2001
Is there any way in which I can move an objects/ data to another
filegroup using transact sql?
I have created a table such that the data is on a secondary filegroup.
But the table has image and text data and this data is going to
the PRIMARY filegroup.
I tried chnging the filegroup for the text data but this property but does not change(I did it through EM).
As a result, the disk which contains the primary filegroup is getting filled up.
I have configured my database such that the primary file group is on one disk and the secondary on another and the log on the same disk as the primary filegroup.
I have given lesser space to the primary file group and more space to the secondary filegroup.
help on this is greatly appreciated because my system is rapidly growing with respect to data.
thanks and best regards
Sush.
View 1 Replies
View Related
Sep 10, 1999
I was under the impression that if you are using multiple files in your filegroup to store your data that the data should be written evenly across the files.
I have 5 files and the data is being written to only one file which is growing rather large, while the other files are still at 5 - 7 MB. They are all marked for Automatic Growth at this point, but only the 1 file is growing.
Is there something else that needs to be done to enable this? BOL left me with the impression that this is automatic.
View 1 Replies
View Related
Sep 14, 2006
We are going to start using fielgroups to move some indexes to a different RAID. Simple ? is, when you do a full database backup, that backup alss contains the new filegroup?
View 2 Replies
View Related
Feb 9, 2006
hi all,
i have a problem with creating filegroups in sql server...i just want to copy the names of the columns from one table into my filegroups...
this is my problem : i m writting the following code in tsql:
SELECT * INTO Sales FROM Sales1 WHERE 0=1 ON [MyFG1]
Can anyone please help meeee???? i'd like it to work this way, to copy only first column names from Sales1 to Sales but in this filegroup.
if this is not the write way...then can anyone please tell me the right syntax and the way to copy just the names into a filegroup?????
thanx in advance.
Regards.
View 3 Replies
View Related
Jul 18, 2007
Hello everyone -
Currently the setup is a single filegroup sql machine
with each company as a new database in a single system.
Would breaking the companies into thier own filegroups
be benefitial?
or perhaps creating a instance of SQL for each company be a better approach?
thoughts / ideas would be appreciated
thanks
tony
View 2 Replies
View Related
Oct 12, 2007
Hey guys I have a VLDB and I would like to point or move several tables to another filegroup.
1). Add a filegroup to the database
ALTER DATABASE dbname
ADD FILEGROUP filename_table
go
2). Add a file to the file group
ALTER DATABASE dbname
ADD FILE
.....
TO FILEGROUP filename_table
go
If a table is already part of the primary filegroup:
Can I change it to another filegroup?
When changed to the another filegroup will it move the whole table to the other filegroup or just start to write data to the newly created filegroup?
View 2 Replies
View Related
