How To Find Filegroups For A Given Table And Table's Indexes
Oct 6, 2006
Hi
I am using SQL Server 2005 Developer Edition.
I want a list of the following things from the database: -
Table Name , FileGroup Table resides on
Table Name, Index Name, FileGroup index resides on
To put it simply, consider the following example:-
Lets say I have a table XYZ in my database created on Filegroup F1. It has a PK PK1 nonclustered index on Filegroup F2.
List1
-------
XYZ F1
List2
---------
XYZ PK1 F2
Please do not tell me of sp_help <table> option
Regards
Imtiaz
View 1 Replies
ADVERTISEMENT
May 19, 2004
I'm fishing for some advice here.
I'm porting an old Access 97 application to SQL Server 2000. The Access app uses 9 separate files for a series of linked tables (one table in each file to get around the 1Gb Access file size limit). The tables vary in size form 2Mb to 800Mb so the whole data set weighs in at around 6Gb.
I've prepared tables for the data in SQL Server and assigned each table into its own filegroup each of whihc has its own file so I can separate the data out and also keep an eye on the amount of data.
My next problem is the indexes. The Access tables don't have primary keys. My SQL tables do (Large Integers) but each table is mainly indexed on an account number which is an 11 char alphanumeric. This is non unique so I can't use it as a primary key.
At present I have all the table indexes in the PRIMARY filegroup (which in tunr just has the default MDF file in it)
I've built a small version of the DB for testing various triggers and new views and the DTS import packages and the indexing for the 300Mb of data I have now is obviously fairly quick. I am wondering if I should split the indexes out into each of the separate MDF files that I am storing the table data or should I split the indexes into their own files?
I want this thing to be fast. The VBA app that will be plugging into the DB has a huge amount of code and currently struggles especially when several dozen people are all connected to the same tables.
So any advice? Indexes in separate files? Or in with the data? or all together in one index file? Any performance impacts I should be aware of?
The DB is running on its own dedicated box. Its not huge 1Gb Ram, 30Gb drive and a 3Ghz P4. But given that it isn't running anything else it should be up to the job. It should certainly be faster than the current shared drive that the access app runs from.
many thanks
Steve
View 1 Replies
View Related
Jul 20, 2005
Hi,I'm a newbie in this SQL Server development. I have a database with 7tables that are linked to each other and have created clustered indexfor the primary keys and non-clustered index for the secondary keys.All my indexes are on the same filegroup, what is the effect of thisbecuase the application that I'm using runs very slow duringprocessing.PS: My tables have a grown rate of about 10 000 records a day.
View 1 Replies
View Related
Jun 18, 2002
I want to change the filegroup of a table. While we can do this in enterprise mgr, I would like to accomplish this in transact sql. We have a new configuration where the filegroups are located on different disks and to take advantage of that, I want to move the heavily accessed tables onto different groups. Example: table emp needs to be moved from filegroup Primary to file group Secondary.
please help...
View 1 Replies
View Related
Apr 2, 2008
CREATE DATABASE Dummy
ON
PRIMARY (
NAME = PrimaryLog,
FILENAME = 'D:primary.mdf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
(
NAME = Data,
FILENAME = 'D:Data.ndf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
LOG ON
-- Stores The Log Information used To Recover The Database
(
NAME = Log,
FILENAME = 'D:Log.ldf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB )
Go
After this I want to create table on Data .
CREATE TABLE Sample (
No INT ,Name VARCHAR(30) , Department VARCHAR(4000) NULL
)
ON Data
GO
it shows invalid filegroup DATA specified
what may be wrong
View 1 Replies
View Related
Apr 2, 2008
CREATE DATABASE Dummy
ON
-- Primary file contains Startup information of the database
PRIMARY (
NAME = PrimaryLog,
FILENAME = 'D:primary.mdf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
-- Holds The Data of LookUPTables,TPProfile,CRM.
(
NAME = Data,
FILENAME = 'D:Data.ndf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
LOG ON
-- Stores The Log Information used To Recover The Database
(
NAME = Log,
FILENAME = 'D:Log.ldf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB )
Go
After this I want to create table on Data .
CREATE TABLE Sample (
No INT ,Name VARCHAR(30) , Department VARCHAR(4000) NULL
)
ON Data
GO
it shows invalid filegroup DATA specified
what may be wrong
View 1 Replies
View Related
Apr 2, 2008
CREATE DATABASE Dummy
ON
-- Primary file contains Startup information of the database
PRIMARY (
NAME = PrimaryLog,
FILENAME = 'D:primary.mdf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
-- Holds The Data of LookUPTables,TPProfile,CRM.
(
NAME = Data,
FILENAME = 'D:Data.ndf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
LOG ON
-- Stores The Log Information used To Recover The Database
(
NAME = Log,
FILENAME = 'D:Log.ldf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB )
Go
After this I want to create table on Data .
CREATE TABLE Sample (
No INT ,Name VARCHAR(30) , Department VARCHAR(4000) NULL
)
ON Data
GO
it shows invalid filegroup DATA specified
what may be wrong
View 1 Replies
View Related
Sep 28, 2000
Recently, we had some tables inadvertantly dropped from a 20+ GB data warehouse in the middle of one of our load cycles. In order to avoid restarting the load, we had to make restore a copy of the database from a full database backup and then manually move in the dropped tables using DTS.
This ended up being a painful process, and I am looking to avoid it by restructuring the database into filegroups so I can mimic the table restore function from 6.5.
I am looking for pros, cons, gotchas, and best practices regarding splitting a MSSQL 7 database into multiple filegroups. We would be including 1 or more tables and all of thier indexes in each filegroup. Specifically, I have the following questions:
1. All the literature I read mentions the need for doing transaction log backups and restores if I utilize filegroup backups. Is this really necessary? We have turned off transaction logging for performance reasons, and since we completely control the addition of new data into the db.
2. Are there implications for disk read performance involved with filegroups? Our data resides on an EMC disk array. I feel that we'll most likely have between 20 and 30 filegroups.
3. If we decide to move a table (and its indexes) between filegroups, is the process as simple a dropping the clustered index and recreating it on the new filegroup (then dropping and recreating the n-c indexes)?
Any information y'all can post would be greatly appreciated.
View 1 Replies
View Related
Aug 13, 2007
I have approximately 400 - 600 tables that need to have their filegroups changed on a new SQL 2005 installation. In 2000, I could go the table properties in Enterprise Manager and change the filegroup, but on 2005 Management Studio, I can view (but not change) the table properties.
I realize that I can create the table on the other filegroup using a new name, move my constraints and indexes to this new table, copy the data over, drop the original table, then rename the new table to the name my application is expecting. But that could take me weeks (especially with such a time-consuming process)! I don't mind the process being so slow, but I do mind it being labor intensive.
Does anyone have an idea how I can automate this process (or at least make it as easy as it was with Enterprise Manager?)
Paul Teal
View 3 Replies
View Related
Jul 20, 2005
Hi,I want to find out in my SP the filegroup associated with a particulartable? I was not able to establish the relationship between sysobjectsand sysfilegroups table. Is there any other way to find filegroup of atable in T_SQL?Also lets say there are 3 dtatfiles in a filegroup 'FG1' and table'xyz' is created on filegroup 'FG1' - Is there any way to find out thedatafile's names on which table xyz's data is scattered?Thanks in Advance,Subodh
View 1 Replies
View Related
Mar 7, 2014
Why the Indexes on table slow down the DML operation on table, what is the exact reason?
View 5 Replies
View Related
Jan 6, 2004
Hi there,
Can someone tell me if it is possible to add an index to a Table variable that is declare as part of a table valued function ? I've tried the following but I can't get it to work.
ALTER FUNCTION dbo.fnSearch_GetJobsByOccurrence
(
@param1 int,
@param2 int
)
RETURNS @Result TABLE (resultcol1 int, resultcol2 int)
AS
BEGIN
CREATE INDEX resultcol2_ind ON @Result
-- do some other stuff
RETURN
END
View 2 Replies
View Related
Apr 29, 2014
I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.
What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:
'allstores' 'Stores'
storeid doc article storeid doc article
ALL 0010 001 101 0010 001
ALL 0010 002 101 0010 002
ALL 0011 001 102 0011 002
ALL 0011 002
So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".
View 7 Replies
View Related
Feb 4, 2008
Hi,
How can I copy a database table with all its data, indexes and constraints to a new table in the same database in sql server 2005
View 7 Replies
View Related
Jun 25, 2015
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),
[Code] ....
View 5 Replies
View Related
Jun 12, 2008
I have 3 database say
db1
db2
db3
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
View 5 Replies
View Related
Feb 18, 2007
I'm looking for information on how to add indexes to a table in a SQL Server 2000 Database, why add them etc? Any source of good information on the web regarding this?
View 6 Replies
View Related
Sep 20, 2005
is there a way to drop all the indexes on a table other than listing them out separately in a drop index statement?
View 2 Replies
View Related
Jun 8, 2004
Hello again,
Two Short questions this time,
I have a table with several indexes, currently most of them are very narrow (one column), and the question is, when I modify the table by updating a record, does all the indexes are calculated again?? Even if the modified field isn't indexed??? Or the server is smart and knows what indexes to calculate if any.
Second question, can I give to a query a low priority(In dynamic SQL), for example when I don't want my query to exploit too many system resources so it won't interfere the main system ?
Inon.
View 12 Replies
View Related
Sep 1, 2006
How can I list indexes with a (SQL) query ?
Thanks
View 1 Replies
View Related
Feb 8, 2007
Hi ALL,
Somehow one of tables in my database with clustered index got tempered and the pageid are not in proper order. I tried the checkdb command with repair_rebuild option but it is giving the following error.
Table error: Object ID 1984530649, index ID 1. Page (1:254682) is missing a reference from previous page (1:254681). Possible chain linkage problem.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1984530649, index ID 1. B-tree chain linkage mismatch. (1:254680)->next = (1:256198), but (1:256198)->Prev = (1:256197).
Not able to reindex the index of table. I can not even export the data to any other table with same structure.
Please help me...........
Thanks,
Pracheer
View 2 Replies
View Related
Sep 9, 2005
Hi,I simply want to create a duplicate of an existing table with adifferent name. I've tried the "select * into newtable fromorgtable", which works great, except that it doesn't mark the primarykey field from orgtable in the newtable. I tried creating thenewtable prior to using this select, but then sql server cries aboutthe table already existing. I tried creating the index after theselect "CREATE UNIQUE INDEX FieldAIndex ON newtable (FieldA)" - thatdidn't give a error, but also failed to mark the field as primary.Suggestions?
View 2 Replies
View Related
Jul 27, 2006
Hi,
Regarding to my previous post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=553652&SiteID=1, just wondering if there's a way to detect any changes which is made on the existing indexes in a published table so that the device can determine whether reinitialization of subscrption is needed before every synchronization.
Cheers,
Justin
View 3 Replies
View Related
May 26, 2006
I used the option "Script Table" --> "Create to" in SQL Server Management Studio Express for a table with secondary index (like IX_IndexName), but in the .sql script file there is only the instruction for the primary key and not for the secondary (I expected something like CREATE INDEX). What can I do?
View 3 Replies
View Related
Jun 27, 2007
In our environment we have a very high insert intensive OLTP table, this has 60 million rows. From some of our applications we are very rarely getting timeouts when inserting data into this table. We are expecting that this table would soon run into billion rows and continue to grow. below is the table description and indexes. What indexes should we keep/remove on this table?
Column Name Datatype computed Length Precision Scale Nullable
Col1 int no 4 10 0 no
Col2 smallint no 2 5 0 no
Col3 smallint no 2 5 0 yes
Col4 int no 4 10 0 yes
Col5 int no 4 10 0 yes
Col6 int no 4 10 0 yes
Col7 char no 10 yes
Col8 int no 4 10 0 yes
Col9 uniqueidentifier no 16 yes
Col10 datetime no 8 yes
Col11 smallint no 2 5 0 yes
Col12 varchar no 15 yes
Col13 varchar no 30 yes
Col14 varchar no 50 yes
Col15 int no 4 10 0 yes
Col16 int no 4 10 0 yes
Col17 varchar no 12 yes
Col18 smallint no 2 5 0 yes
Col19 smallint no 2 5 0 yes
Col20 int no 4 10 0 yes
Col21 bit no 1 no
Col22 int no 4 10 0 yes
Col23 bit no 1 no
Col24 bit no 1 no
Col25 datetime no 8 no
Col26 char no 6 yes
Col27 char no 6 yes
Col28 smallint no 2 5 0 yes
Col29 char no 2 yes
Col30 int no 4 10 0 yes
Col31 smallint no 2 5 0 yes
Col32 smallint no 2 5 0 yes
Col33 bit no 1 yes
Index1 nonclustered, stats no recompute located on FileGroup2 Col9, Col10, Col12
Index2 nonclustered, stats no recompute located on FileGroup2 Col9, Col12, Col11, Col10, Col4
Index3 nonclustered, stats no recompute located on FileGroup2 Col6
Index4 nonclustered, stats no recompute located on FileGroup2 Col6, Col4
Index5 nonclustered, stats no recompute located on FileGroup2 Col5, Col9
Index6 nonclustered, stats no recompute located on FileGroup2 Col4, Col9
Index7 nonclustered, stats no recompute located on FileGroup2 Col29
Index8 nonclustered, stats no recompute located on FileGroup2 Col9
Index9 nonclustered, stats no recompute located on FileGroup2 Col6, Col9
Index10 nonclustered, stats no recompute located on FileGroup2 Col7
Index11 nonclustered, stats no recompute located on FileGroup2 Col12
Index12 clustered, unique, primary key, stats no recompute located on FileGroup3 Col1
Any links to MS best practises on indexes for sqlserver 2000 appreciated.
thanks a lot in advance.
View 6 Replies
View Related
Jun 10, 2014
It is possible to find table size and in that table each row size.
View 4 Replies
View Related
Jan 15, 2015
How to find if there is a query that can be written on DMV's which will be able to retrieve the indexes that are not being used in a table.
View 2 Replies
View Related
May 26, 2015
I use Indexes Fundamentals of Microsoft SQL Server - Lesson 30: Indexes in the website of URL... to learn the basic things of Indexes. In my SQL Server 2012 Management Studio (SSMS2012), I executed the following code..
-- scFTX_CreateTableEmployees.sql
-- saved in C:/Documents/SQLServerIndexes_downloadCode
-- 26 May 2015 10:52 AM
USE ScottChangDB;
GO
CREATE TABLE Employees
[code]....
Where the SCHEMA and the index 'IX_Employees are located in the Object Explorer of the database "ScottChangDB" of my SSMS2012.Â
View 3 Replies
View Related
Aug 9, 2014
how to take backup of indexes for a particular table.
View 1 Replies
View Related
Aug 9, 2014
How to take backup of indexes for a particular table.
View 5 Replies
View Related
Sep 29, 2015
IS there a way to remove all indexes from a table with one single command ( without having to know what the index name is ) ;
View 2 Replies
View Related
Mar 5, 2008
Dear all,
I'm using SQL Server 2005 Standard Edetion.
I have the following stored procedure that is executed against two tables (RecrodedCalls) and (RecordedCallsTags)
The table RecordedCalls has more than 10000000 Records and RecordedCallsTags is about 7500000 Records
Now the lines marked in baby blue are dynamic (Dynamic where statement) that varies every time this stored procedure is executed, may it contains 7 columns in condetion statement or may it contains 10 columns, or 2 coulmns.....etc
Now I want to create non-clustered indexes on the columns used in the where statement, THE DTA suggests different indexing whenever the where statement changes.
So what is the right way to created indexes, to create one index on all the columns once, or to create separate indexes on each columns, sometimes the DTA suggests 5 columns together at one if I€™m using 5 conditions, I can€™t accumulate all the possible indexes hence the where statement always vary from situation to situation, below the SP:
CREATE TABLE #tempLookups (ID int identity(0,1),Code NVARCHAR(100),NameE NVARCHAR(500),NameA NVARCHAR(500))
CREATE TABLE #tempTable (ID int identity(0,1),TypesCount INT,CallsType NVARCHAR(50))
INSERT INTO #tempLookups SELECT Code, NameE, NameA FROM lookups WHERE [Type] = 'CALLTYPES' ORDER BY Ordering ASC
INSERT INTO #tempTable SELECT COUNT(DISTINCT(RecordedCalls.ID)) As TypesCount,RecordedCalls.CallType as CallsType
FROM RecordedCalls LEFT OUTER JOIN RecordedCallsTags ON RecordedCalls.ID = RecordedCallsTags.CallID
WHERE RecordedCalls.ID <= '9369907'
AND (RecordedCalls.CallDate BETWEEN cast ('01 Jan 1910 00:00:00:000' as datetime ) AND cast ( '01 Jan 2210 00:00:00:000' as datetime ))
AND (RecordedCalls.Duration BETWEEN 0 AND 1000000)
AND RecordedCalls.ChannelID NOT IN('62061','62062','62063','62064','64110','64111','64112','64113','64114','69860','69861','69862','69863','69866','69867','69868')
AND RecordedCalls.ServerID NOT IN('2')
AND RecordedCalls.AgentID NOT IN('1000010000')
AND (RecordedCallsTags.TagID is null OR RecordedCallsTags.TagID NOT IN('100','200'))
AND RecordedCalls.IsDeleted='false'
GROUP BY RecordedCalls.CallType
SELECT IsNull(#tempTable.TypesCount, 0) AS TypesCount, CASE('English')
WHEN 'Arabic' THEN #tempLookups.NameA
ELSE #tempLookups.NameE
END AS CallsType FROM
#tempTable RIGHT OUTER JOIN #tempLookups ON #tempTable.CallsType = #tempLookups.Code
DROP TABLE #tempLookups
DROP TABLE #tempTable
Thanks all,
Tayseer
Any suggestions how to create efficient indexes??!!
View 2 Replies
View Related
Sep 13, 2006
I have a table that is in a one way transactional publication. I need to create a full-text catalog on this table and have that catalog exist on the subscriber as well. I understand simply creating a FTC for an object in a publication will not cause that FTC to be replicated to the subscribers. I have scripted out the command and tried to use sp_addscriptexec to push it to the subscriber. When I do this, I get the following error:
Last 183 characters in 'sqlcmd' output buffer: Changed database context to 'database'.
Msg 574, Level 16, State 1, Server SQLSERVER, Line 2
CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.
Also, when this script is run directly from the subscriber, it works fine (but since the subscriber is our production machine, I'd prefer to not have to do it that way). I believe I can reinitialize the publication and have the FTC pushed to the subscriber, but this is not ideal either, since some of our databases can be very large and take a long time to initialize.
Is there a better way to accomplishing this other then connecting directly to the subscriber and running the script or by reinitializing the publication? Thanks for your help!
-mike
View 12 Replies
View Related
