How Can A Foregin Key Reference Multiple Coulmns Of Clustered Key
Sep 25, 2007
Hi there...
Sounds like a veryy silly question but I somehow got stuck with this & cant think anymore
Basically, I have got a table 'Order_Product' with Clustered Primary Key of columns 'Order_Number' & 'Product_Id'.
I am creating a new table Order_Discussion' with column 'Product', which is a Foreign Key & should reference 'Order_Number' & 'Product_Id' of table 'Order_Product'.
I want to partition this table on 5 different coulumns.
year, month. coulumn3, coulumn4 and coulumn5.
How do I do this best?
What is the best way of doing this; is there any best practice on ths?
The obvious thing is to make a partition funktion based on the year and month coulmns, but I also want to partition on coulumn3, coulumn4 and coulumn5.
I need to write a query that requires respective fields referencing from multiple tables. For example, here are the tables: Main Table: InfoID Team1 Player1 Team1 Table: Player_ref Player Team_Player_ref Player1 John doh Table: Team_ref Team Team_Player_ref Team1 My Team
Ideal result Table from query: InfoID Count John Doh 1 My Team 2
Any suggestion to creat the Ideal Results table from query? Normally, I could do it if it only referenced from 1 table, I would do an inner join, however, since there are 2 referenece table, doing inner join wouldn't work. A proposed suggestion would certainly be nice. Thanks in advance. --daydreamstuck at the current problem
Not sure if the title describes my situation or not.
Simplified example is: I have an [Employee] table with EmpCode, EmpName
I have a second table [NewHires] that has: HireDate, EmpCode, Addedby
Both EmpCode and Addedby contain EmpCode referring to the Employee table.
I wish an output similar to:
New Employee (from EmpCode in NewHire), Hired on (From HireDate), Hired By (from Addedby)
My problem is with an Employee.EmpCode=NewHires.Empcode or Employee.EmpCode=NewHires.Addedby in the Where clause or Join part of the SQL I don't know how to get EmpName from the Employee table twice but using two different EmpCode as the reference.
I have a clustered environment which has many instances in it. I have modified the xml file to allow several instances to use ssis. Everything works great when the instances are on the same node as the clustered ssis. However if they are on different nodes there seems to be an aunthentication problem and it loses my credentials. For some reason it tries to use Anonymous Logon instead. Here is the full error:
The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in Server 2005 Books Online.
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. (MsDtsSrvr)
We are going to use SQL Sever change tracking. The problem is that some of our tables, which are to be tracked, have no primary keys. There are only unique clustered indexes. The question is what is the best way to turn on change tracking for these tables in our circumstances.
I desire to have a clustered index on a column other than the Primary Key. I have a few junction tables that I may want to alter, create table, or ...
I have practiced with an example table that is not really a junction table. It is just a table I decided to use for practice. When I execute the script, it seems to do everything I expect. For instance, there are not any constraints but there are indexes. The PK is the correct column.
CREATE TABLE [dbo].[tblNotificationMgr]( [NotificationMgrKey] [int] IDENTITY(1,1) NOT NULL, [ContactKey] [int] NOT NULL, [EventTypeEnum] [tinyint] NOT NULL,
I have created two tables. table one has the following fields,
Id -> unique clustered index. table two has the following fields, Tid -> unique clustered index Id -> foreign key of table one(id).
Now I have created primary key for the table one column 'id'. It's created as "nonclustered, unique, primary key located on PRIMARY". Primary key create clustered index default. since unique clustered index existed in table one, it has created "Nonclustered primary key".
My Question is, What is the difference between "clustered, unique, primary key" and "nonclustered, unique, primary key"? Is there any performance impact between these?
I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:
Error system.NullReferenceException: Object refrence not set to an instance reference.
In PreExecute section TextReader = new system.io.streamreader(" file name") In the CreateNewOutputRows: dim nextLine as string nextLine = textReader.ReadLine
[code]...
is there something which i did not install or what can be the error?
Hi there, I have a table that has an IDENTITY column and it is the PK of this table. By default SQL Server creates a unique clustered index on the PK, but this isn't what I wanted. I want to make a regular unique index on the column so I can make a clustered index on a different column.
If I try to uncheck the Clustered index option in EM I get a dialog that says "Cannot convert a clustered index to a nonclustered index using the DROP_EXISTING option.". If I simply try to delete the index I get the following "An explicit DROP INDEX is not allowed on index 'index name'. It is being used for PRIMARY KEY constraint enforcement.
So do I have to drop the PK constraint now? How does that affect all the tables that have FK relationships to this table?
I have a really super slow stored proc that does something simple. it updates a table if certain values are received.
In looking at this the matching is done on the Primary Key, which is set as a Clustered index, looking further I have another constraint, that sets the same column to a Unique, Non-Clustered.
I am not sure why this was done, but it seems to be counter productive. I have read only references to Which one is better on a primary key, but not can their be both and if it is "Smart".
I've a table with primary key defined as non-clusterd, now without dropping it can I modify the existing index to clustered through tsql as I had to write some migration script and in that script I wanna do this.
Hello,I have a query that I need help with.there are two tables...Product- ProductId- Property1- Property2- Property3PropertyType- PropertyTypeId- PropertyTypeThere many columns in (Product) that reverence 1 lookup table (PropertyType)In the table Product, the columns Property1, Property2, Property3 all contain a numerical value that references PropertyType.PropertyTypeIdHow do I select a Product so I get all rows from Product and also the PropertyType that corresponds to the Product.Property1, Product.Property2, and Product.Property3ProductId | Property1 | Property2 | Property3 | PropertyType1 | PropertyType2 | PropertyType3 PropertyType(1) = PropertyType for Property1PropertyType(2) = PropertyType for Property2PropertyType(3) = PropertyType for Property3I hope this makes sence.Thanks in advance.
I would like to find information on Clustered and Non-clustered indexes and how B-trees are used. I know a clustered index is placed into a b-tree which makes sense for fast ordered searching. What data structure does a non-clustered index use and how? I tried to find info. on the web but couldn't get much detail...
I have a table<table1> with 804668 records primary on table1(col1,col2,col3,col4)
Have created non-clustered index on <table1>(col2,col3,col4),to solve a performance issue.(which is a join involving another table with 1.2 million records).Seems to be working great.
I want to know whether this will slow down,insert and update on the <table1>?
SELECT a.AssetGuid, a.Name, a.LocationGuid FROM Asset a WHERE a.AssociationGuid IN ( SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
takes 30-60 seconds to run on my machine, due to a clustered index scan on our an index on asset [about half a million rows]. For this particular association less than 50 rows are returned.
expanding the inner select into a list of guids the query runs instantly:
SELECT a.AssetGuid, a.Name, a.LocationGuid FROM Asset a WHERE a.AssociationGuid IN ( '0F9C1654-9FAC-45FC-9997-5EBDAD21A4B4', '52C616C0-C4C5-45F4-B691-7FA83462CA34', 'C95A6669-D6D1-460A-BC2F-C0F6756A234D')
It runs instantly because of doing a clustered index seek [on the same index as the previous query] instead of a scan. The index in question IX_Asset_AssociationGuid is a nonclustered index on Asset.AssociationGuid.
The tables involved:
Asset, represents an asset. Primary key is AssetGuid, there is an index/FK on Asset.AssociationGuid. The asset table has 28 columns or so... Association, kind of like a place, associations exist in a tree where one association can contain any number of child associations. Each association has a ParentAssociationGuid pointing to its parent. Only leaf associations contain assets. AssociationDataAssociation, a table consisting of two columns, AssociationGuid, DataAssociationGuid. This is a table used to quickly find leaf associations [DataAssociationGuid] beneath a particular association [AssociationGuid]. In the above case the inner select () returns 3 rows.
I'd include .sqlplan files or screenshots, but I don't see a way to attach them.
I understand I can specify to use the index manually [and this also runs instantly], but for such a simple query it is peculiar it is necesscary. This is the query with the index specified manually:
SELECT a.AssetGuid, a.Name, a.LocationGuid FROM Asset a WITH (INDEX (IX_Asset_AssociationGuid)) WHERE a.AssociationGuid IN ( SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
To repeat/clarify my question, why might this not be doing a clustered index seek with the first query?
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this: When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004 Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing? If so does this "pseudocode" example also cause this to occur: sqlstring="select * from item where itemid=12345" rs.open sqlstring, etc, etc, etc rs.Fields("ItemName")="My New Item Name" rs.Fields("ItemPrice")=1.00 rs.Update Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?
We have a table, which has one clustered index and one non clustered index(primary key). I want to drop the existing clustered index and make the primary key as clustered. Is there any easy way to do that. Will Drop_Existing support on this matter?
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.
Dear All, i've observed one particular table, one column is having clustered and non clustered index. is it ok? or i need to drop the non clustered column?
the table has 16 columns and at present 8 million records are there. per day approxmately 60000 rows will be getting into the table. it has another 3 non clustered indexes.
please suggest me.
Arnav Even you learn 1%, Learn it with 100% confidence.
I just ran the Database Engine Tuning Advisor on a relative complex query to find out if a new index might help, and in fact it found a combination that should give a performance gain of 94%. Fair enough to try that.
What I wonder about: The index I should create contains 4 columns, the last of them being the Primary Key column of the table, which is also my clustered index for the table. It is an identity integer btw.
I think I remember that ANY index does include the clustered one as lookup into the data, so having it listed to the list of columns will not help. It might at worst add another duplicate 4 bytes to each index entry.
Right? Wrong? Keep the column in the index, or remove it since it is included implicit anyway?
After applying SP3 to to cluster, cluster still displays that it has SP2, is there a way to check for sure which version it has. And what's the appropriate way to install SP3 on cluster.
Many times we see tables with customerID as the PK and clustered but with customerName as null.
So, I try to inforce it by alter table add constraint of AK1_customerName unique for it. However, though the uniqueness is enforced, the display sequence is also 'altered' to be sorted by customerName?
I've been doing a bit of reading and have read in quite a few placesthat an identity column is a good clustered index and that all or atleast most tables should have a clustered index. The tool I used togenerate tables made them all with non clustered indexes so I wouldlike to drop all of them and generate clustered indexes. So myquestions is a) good idea? and b) how? There are foreign key referencesto most of them so those would need to be dropped first and thenre-created after the clustered one was created and that could cascade(I think?)Any existing scripts out there that might do this? I found somethingsimilar and modified it, the sql is included below. This gives me thelist of all the columns I need, I just need to get the foreign keys foreach from here before each one and generate all the create/dropscripts.All the columns I am looking to do this for are called "Id" making thissomewhat simpler. I'm just looking to incrementally make the SQL sidebetter and don't want to rewrite a bunch of application level code tomake the column names ISO compliant, etc./*-- Returns whether the column is ASC or DESCCREATE FUNCTION dbo.GetIndexColumnOrder(@object_id INT,@index_id TINYINT,@column_id TINYINT)RETURNS NVARCHAR(5)ASBEGINDECLARE @r NVARCHAR(5)SELECT @r = CASE INDEXKEY_PROPERTY(@object_id,@index_id,@column_id,'IsDescending')WHEN 1 THEN N' DESC'ELSE N''ENDRETURN @rEND-- Returns the list of columns in the indexCREATE FUNCTION dbo.GetIndexColumns(@table_name SYSNAME,@object_id INT,@index_id TINYINT)RETURNS NVARCHAR(4000)ASBEGINDECLARE@colnames NVARCHAR(4000),@thisColID INT,@thisColName SYSNAMESET @colnames = INDEX_COL(@table_name, @index_id, 1)+ dbo.GetIndexColumnOrder(@object_id, @index_id, 1)SET @thisColID = 2SET @thisColName = INDEX_COL(@table_name, @index_id, @thisColID)+ dbo.GetIndexColumnOrder(@object_id, @index_id, @thisColID)WHILE (@thisColName IS NOT NULL)BEGINSET @thisColID = @thisColID + 1SET @colnames = @colnames + ', ' + @thisColNameSET @thisColName = INDEX_COL(@table_name, @index_id,@thisColID)+ dbo.GetIndexColumnOrder(@object_id, @index_id,@thisColID)ENDRETURN @colNamesENDCREATE VIEW dbo.vAllIndexesASbeginSELECTTABLE_NAME = OBJECT_NAME(i.id),INDEX_NAME = i.name,COLUMN_LIST = dbo.GetIndexColumns(OBJECT_NAME(i.id), i.id,i.indid),IS_CLUSTERED = INDEXPROPERTY(i.id, i.name, 'IsClustered'),IS_UNIQUE = INDEXPROPERTY(i.id, i.name, 'IsUnique'),FILE_GROUP = g.GroupNameFROMsysindexes iINNER JOINsysfilegroups gONi.groupid = g.groupidWHERE(i.indid BETWEEN 1 AND 254)-- leave out AUTO_STATISTICS:AND (i.Status & 64)=0-- leave out system tables:AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0end*/SELECTv.*FROMdbo.vAllIndexes vINNER JOININFORMATION_SCHEMA.TABLE_CONSTRAINTS TONT.CONSTRAINT_NAME = v.INDEX_NAMEAND T.TABLE_NAME = v.TABLE_NAMEAND T.CONSTRAINT_TYPE = 'PRIMARY KEY'AND v.COLUMN_LIST = 'Id'AND v.IS_CLUSTERED = 0ORDER BY v.TABLE_NAME
