Hi All, when i am trying to create a table using NDB storage engine i am getting error "Got error 904 'Out of fragment records (increase MaxNoOfOrderedIndexes)' from NDB" how to change the size of this variable..i am not able to find where this variable is stored. thanks in advance. ...
I am using Fluent-NHibernate (with automapping) to generate my tables but would like to choose a different clustered index than the ID field which is used by default. How can you create clustered indexes with Fluent NHibernate on a field other than the default Primary Key field? The primary reasoning behind this is simple. I am using Gu...
I have a table in SQL Server that needs its normal pk index to be replaced by a clustered index on two fields. These other fields are not part of the primary key. I need to do something similar in Oracle. As far as I know, this can be done using index-ordered tables, but my guess is these indexes are only constructed on the primary key....
Hi, I've got a Stored Procedure in SQL Server 2005 and when I run it and I look at its Execution Plan I notice it's doing a Clustered Index Scan, and this is costing it the 84%. I've read that I've got to modify some things to get a Clustered Index Seek there, but I don't know what to modify. I'll appreciate any help with this. Thanks...
I have a series of questions about Keys, Indexes and Constraints in SQL, SQL 2005 in particular. I have been working with SQL for about 4 years but I have never been able to get definitive answers on this topic and there is always contradictory info on blog posts, etc. Most of the time tables I create and use just have an Identity colu...
I have an application where the instruction says that it doesn't work with clustered index with more than one field. Couldn't resist testing and it is true. The app gets stuck in some endless lopp if i add a clustered index with 2 fields. I thought the index was something internal to the database that doesn't affect the application. Ho...
Consider the following composite clustered index: CREATE UNIQUE CLUSTERED INDEX ix_mytable ON mytable(a, b) Obviously, a separate index on b will make searching for a particular value of b faster. However, if a separate index on b is not employed, it seems to me that the composite index can still be used to find tuples with a particu...
The goal of using a sequential guid is so you can use clustered indexes without the high levels of fragmentation that would normally exist in a clustered index if it was a regular guid, correct? ...
Suppose I need to update myTab from luTab as follows update myTab set LookupVale = (select LookupValue from luTab B where B.idLookup = myTab.idLookup) luTab consists of 2 columns (idLookup(unique), LookupValue) Which is preferable : a unique clustered index on idLookup, or one on idLookup and...
If I'm am trying to squeeze every last drop of performance out of a query what affect does having these types of index's being used by my joins. clustered index. non-clustered index. clustered or non-clustered index with extra columns that may not be involved in the join. Will I gain any performance if I go through and create cluster...
begin transaction; create table person_id(person_id integer primary key); insert into person_id values(1); ... snip ... insert into person_id values(50000); commit; This code takes about 0.9 seconds on my machine and creates a db file taking up 392K. These numbers become 1.4 seconds and 864K if I change the second line to create table ...
I have a very large database with hundreds of tables, and after many, many product upgrades, I'm sure half of them aren't being used anymore. How can I tell if a table is is actively being selected from? I can't just use Profiler - not only do I want to watch for more than a few days, but there are thousands of stored procedures as well,...
After I have created a clustered index on a table, is there any point in updating the statistics for that table? Cheers. ...
How are clustered indexes stored on a hard disk? What is the logical order? How do non-clustered indexes work? ...
Usually the clustered index is created in SQL Server Management Studio by setting the primary key, however my recent question about PK <-> clustered index (http://stackoverflow.com/questions/2262998/meaning-of-primary-key-to-microsoft-sql-server-2008) has shown that it is not necessary to set PK and clustered index to be equal. So how s...
I've made mistake creating clustered primary key on GUID column. There are many tables that reference that table with defined foreign keys. Table size is not significant. I would like to convert it from clustered to non-clustered without manually dropping and recreating any foreign keys or even primary key constraint. Is it possible to...
Was scanning through a SQL2005 database and saw the following two indexes for a table: **PK_CLUSTERED_INDEX** USER_ID COMPANY_ID DEPARTMENT_ID **NON-unique_NON-clustered_INDEX** USER_ID COMPANY_ID My initial thought is, drop the last index since the PK_CLUSTERED_INDEX already contains those columns, correct order and sort. Do...
Hey guys, I am trying to work on the upgrade component in our program. This requires changing an index's name EXEC sp_rename N'Sig_Summary1Index.IX_Sig_Summary1Index_StartTime', N'Sig_Summary3Index.IX_Sig_Summary1Index1_StartTime', N'INDEX'; (we need to remain compatible with SQL SERVER 2005) and then check if it exists in the same...
I have the following query: SELECT COUNT(*) FROM FirstTable ft INNER JOIN SecondTable st ON ft.STID = st.STID As you can guess, "STID" is the primary key on "SecondTable"... and "FirstTable" will have a pointer to that second table. Here are the indexes that I have: FirstTable: NONCLUSTERED INDEX on column "STID" Sec...
Let's say we have a Product table, and Order table and a (junction table) ProductOrder. ProductOrder will have an ProductID and an OrderID. In most of our systems these tables also have an autonumber column called ID. What is the best practice for placing the primary key (and therefor clustered key)? Should I keep the primary key o...