hi there,
i have the following joining table (many-to-many relationship)...
CREATE TABLE [dbo].[products_to_products_swatch] (
[products_to_products_swatch_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[product_id] [int] NOT NULL ,
[products_swatch_id] [int] NOT NULL
) ON [PRIMARY]
GO
question: do i need to include a primary key in this table - being that it is a joing table?
thanks
mike
Hi all,
This is a bit of a general question regarding SQL Server link tables that i hope someone can find the time to answer.
I am looking for the best practice. If I have 3 tables
1) tblCompetition
2) tblTeams
3) tblTeamsInCompetition (this is the link table)
I know the link table should have a related CompetitionID and TeamID,
but should the link table also have a primary key that is an identity
autoincrement by 1 just as you would with the other two tables. In
Access I have never done this, however in SQL Server I have read that
you should do this (but now I can't find the documentation again which
prompts me to ask the question here).
Thanks for taking the time to answer this question.
I find that joining to the same table twice is OK, but as soon as you do it 3 or more times you get a massive performance hit.
Does anyone know the reason for this? Whats special about 3?
What's the best approach to do this sort of thing?
(I've used the SQL Server 2005 Tuning Advisor to add indexes for the query).
Rather than:
Select ..., sum(a1.<column>), sum(a2.<column>), sum(a3.<column>) from master_table
left join table_1 a1 on ...
left join table_1 a2 on ...
left join table_1 a3 on ...
group by ...
I have to select all the table and filter it using case:
Select ...,
sum(case when table_1.<column> = '...') as a1,
sum(case when table_1.<column> = '...') as a2,
sum(case when table_1.<column> = '...') as a3,
from master_table
left join table_1
group by ...
I've searched the posts and library. Associates have different ideas on the topic. So what is your opinion?
For SQL 2005, what is the recommended data type for a primary key?
This is for SQL2k5. The database may be small or big, I don't know (it's going out to multiple customers). I'm wondering if in general it's considered "better" to create a single non-primary default filegroup and put all the objects there, or just leave everything in primary? In one training years back I got the impression that recovering the primary filegroup was important for certain restore operations, so it was always wise to separate them like this.
Thanks for your input,
Mike
MCDBA
Hi,
To all, How I can get SQL table to practice?
Regards,
Sherazi
I need a bit of help writting a select statment and joining 4 differant talbes. I have used inner joins between two different tables before but never more then two tables.
Table layout (listing only the columns I want to pull):
Abbrevations.Short
Abbrevations.Long
Avatars.Creator
Avatars.AvatarName
Avatars.FileName
Avatars.Game
Avatars.Section
Avatars.Width
Avatars.Height
FanInfo.Crator
FanInfo.ContactMethod
FanInfo.Address
FanRattings.Cration
FanRattings.Rating
How they relate:
Abbrevations.Short = Avatars.Game
Avatars.Creator = FanInfo.Creator
Avatar.AvatarName = Ratings.Cration
Any help would be really helpful. Thanks
--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia
hi,
i have a table(Measurement) with the following columns
SensorId,Maximumvalue,MinimumValue,GroupId,SystemId,DeviceId.
so in the above column SensorId is ForeignKey of the Table Sensor which havs two columns(SensorId,SensorName)
so in the above column GRoupId is ForeignKey of the Table Group which havs two columns(GroupId,GroupName)
SystemId is ForeignKey of the Table System which havs two columns(SystemId,SystemName)
DeviceId is ForeignKey of the Table Device which havs two columns(DeviceId,DeviceName)
so my result should be as like as follows:
SensorId,SensorName,Maximumvalue,MinimumValue,GroupName,SystemName,DeviceName.
instesd of displaysing GroupId, SystemId,DeviceId i need to diplay the SensorName,GroupName,SystemName,DeviceName. so please give me some example query to join multiple tables please
Hi All,Bit of a newbie question i'm afraid, so sorry if this is a reallystupid question,I am not sure if I am trying to do this in the right place as I am sureSQL Server has a far better way of doing thisWhat I am trying to do is join the contents of several columns andpresent the entry into a new column. the entries of all the columns Iam trying to join are different types such as dates and numbers. I wantto join all columns as a text string. To make it just a even moretricky I wish to insert a # between all fields.Just in case my poor description has got you thinking What the F*&Khere is an example of what I am trying to doC1 C2 C3 C4 C5Ian 1234 22/02/2006 123456789 Ian#1234#22/02/2006#1234567890I have been trying to do this in the Formula in the design veiw of thetable.Any Advice would be fantastic.Many ThankIan
View 1 Replies View RelatedI have two tables I need to join but there are 2 fields which theycould be joined on.Using the example Tablles, TableA and TableB below;TableAID1 ID2 Qty1 Null 42 A 5Null B 6TableBID1 ID2 QtyNull A 63 B 64 Null 7Null C 8I want to create TableC which will look like this;ID1 ID2 TableA.Qty Tableb>Qty1 Null 4 Null2 A 5 63 B 6 64 Null Null 7Null C Null 8Any ideas?Regards,Ciarán
View 2 Replies View RelatedWhat is the best way to setup relationships between one lookup tableand many other tables. The tables did not have any lookup tablerelationships which I am adding. One lookup table is used for same datain several different places.To use one lookup tables with several tables, I had to disable "CascadeUpdate" and only have "enforce relationships for updates and inserts"checked.Any pros/cons?Thanks in advance.P
View 1 Replies View RelatedHi all,
I am trying to join 2 tables in my database on an encrypted column. I am using a symmetric encryption, but because of the IV, a string encrypted at 2 different times produces 2 different encrypted strings. Therefore, I can not merely join the 2 tables on the encrypted columns. Has anyone any insight on techniques for solving this problem? The most naïve way, would be to decrypt both tables, join them, and re-encrypt them again. But, the tables are quite large, and this would be very processor (and time) intensive.
Any help would be greatly appreciated. Thanks in advance for any suggestions.
Hi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.
Thanks,
Jeyam
Hi All,
I am using SQL Server 2000 Enterprise Edition fully patched. Database is in Simple Recovery mode.
I need to change a column's data type from "int(4)" to "smallint(2)". I know for sure that there will be no data (precision) lost, because I know the possible values that this column could have.
My problem is that the table I am dealing with has 600,000,000 records in it. I dropped all indexes before I tried to alter the table. But still it is taking forever and filling up my 280GB disk with transaction log file.
I know that in Oracle, if I want I can turn off logging and do these kind of modifications relatively faster.
I was wondering if there is a way of disabling logging before running this alter command.
What is the best practice to handle a situation of this sort?
I appreciate your help.
Thanks in advance,
Sinan Topuz
I need to write a sql query which is a master-detail query. Here's the example structure of tables:
Master table:
ColID as longint, ColA as int, ColB as int, ColPartID as longint, ColPartName as longint
Child table -- Wheel:
ColID as longint, ColA as int, ColB as int
Child table -- Door:
ColID as longint, ColA as int, ColB as int
Child table -- Window:
ColID as longint, ColA as int, ColB as int
..... etc
From the master table, it needs to join with its child in order to get the detailed information. However, there're more than one child table for it to join. In other words, the query has to choose the correct child table to join for each row selectively. The use of correct child depends on one of the columns in its master table (ColPartName).
My question is: Does it worth of me keep finding a solution for this query or should I abandon this? I really need some advice, please.
Many thanks,
Leonard
Hi,
I am using a SQL back end to dynamically populate an asp.net report/page.
As the data I'm interrogating is created from a tree control, I'm having to use a recursive function to retrieve the data into a series of ID values. This all happens at the moment in a DataTable manipulated with c# code. So my ID values end up in this datatable.
My problem is that I am then performing a crosstab query in SQL Server 2000 and these ID are required as part of that query.
Should I create a temp table and join this into the query or should i feed in a series of ID values into a where clause?
Any help gratefully appreciated.
Thanks.
John
I need to add a child table that will tell us who the participants counselor is, what I did was I did a Make Table query based off the primary key of the Parent table and made that the link (foreign key) for the People_tbl and the Counselor_tbl, so if the counselor changes then the user adds the record to the counselor tbl and then puts in the Effective date. The problem is that when I run a report it doesn't show the present counselor always shows the old counselor?
Code:
SELECT Student_ind.StudentFirstName, Student_ind.StudentLastName, Student_ind.[Student ID], People_tbl.[Family ID], People_tbl.FirstName,
People_tbl.LastName, People_tbl.[Parent ID]
FROM People_tbl RIGHT OUTER JOIN
Student_ind ON People_tbl.[Family ID] = Student_ind.[Family ID]
WHERE (People_tbl.LastName = @Enter_LastName) AND (People_tbl.FirstName = @Enter_FirstName)
Previously same records exists in table having primary key and table having foreign key . we have faced 7 records were lost from primary key table but same record exists in foreign key table.
View 3 Replies View Related
Hi all,
my stored procedure have one table variable (@t_Replenishment_Rpt).I want to create an Index on this table variable.please advise any of them in this loop...
below is my table variable and I need to create 3 indexes on this...
DECLARE @t_Replenishment_Rpt TABLE
(
Item_Nbr varchar(25) NULL,
Item_Desc varchar(255) NULL,
Trx_Date datetime NULL,
Balance int NULL,
Trx_Type char(10) NULL,
Issue_Type char(10) NULL,
Location char(25) NULL,
Min_Stock int NULL,
Order_Qty int NULL,
Unit char(10) NULL,
Issue_Qty int NULL,
Vendor varchar(10) NULL,
WO_Nbr varchar(10) NULL,
Lead_Time int NULL,
PO_Nbr char(10) NULL,
PO_Status char(10) NULL,
Currency char(10) NULL,
Last_Cost money NULL,
Dept_No varchar(20) NULL,
MSDSNbr varchar(10) NULL,
VendorName varchar(50) NULL,
Reviewed varchar(20) NULL
)
I tryed all below senarios...it is giving error...
--Indexing the @t_Replenishment_Rpt table on the column Names Item Number, Vender , Department Number
--EXEC sp_executesql(CREATE UNIQUE CLUSTERED INDEX Replenishment_index ON @t_Replenishment_Rpt (Item_Nbr))
--CREATE UNIQUE CLUSTERED INDEX Idx1 ON @t_Replenishment_Rpt.Item_Nbr
INDEX_COL ( '@t_Replenishment_Rpt' , ind_Replenishment_id , Item_Nbr )
--EXEC sp_executesql('SELECT INDEXPROPERTY('+ '@t_Replenishment_Rpt' + ', ' + 'Item_Nbr' + ',' + 'IsPadIndex' + ')')
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Vendor','IsPadIndex'))
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Dept_No','IsPadIndex'))
Hi i have set up two very simple tables, I want a user to be able to create a basic account ( data stored in User_Profile table with Id set as the Primery Key as Identity) I
want the user to be able to be able to return to their account at a later date
and then post multiple reviews of different bands they have seen at a later date.
I kept the tables in my example very simple so I could get my head
around the concept, but generally, I want to connect the Id (PK) value in
User_Profile table to the User_Id filed in the User_Review table,
so every review that user writes, will be connected directly to their Id.
Any help you could give would be fantastic a i have no idea where to start!!!
User_Profile
Id int, ( as primary Identity Key)
Name
City
Country
I have a second table called User Reviews
User_Revews
Revew_Id int , ( as primary Identity Key)
User_Id int, ( I want this to contain the Id value in
the User profile Table)
Review_Details
Thanks
Odxsigma
mahesh writes "HI,
I am new to sql server.
can anybody help me
I have a table named tblqualificationmaster.
can i know the foreignkeys and the table related to this
tblqualificationmaster having foeign keys using stored procedure."
Hi,
I have a Users table that I use for membership. I am using username varchar(30) as the primary key for this table since username will always be unique.
The question I have is regarding how SQL Server actually stores data:
I see that when I add users, they are always stored alphabetically sorted on username.
I was expecting that all users will appear on the users table in the order they were added.
Example: I have 3 users (john, jonah, wilson). Now I added 4 user with username='bob'
If I execute select * from users, it returns me (bob, john, jonah, wilson). Look bob has become the first row of the table.
My question: Is Sql server moving 3 older rows to make room for 'bob' and it is also rebuilding part of the index due this new username 'bob'?
If this is the case, then it will have big impact if I have 100K users and I add one user that becomes firstrow. In that case 99,999 rows will have to move.
Bottom line, insert, delete will be very expensive.
I know sql server keeps data physically sorted on PK. But I am concerned here since rows are losing the order in which they were inserted.
Thanks
There is a restriction that we must have only one primary key per table. We can have composite primary key by defining primary key over 2 or more columns. Actually,, a primary key uniquely defines the table and if we have two or more columns which follow unique and not null features why can't we have two primary keys?? I want to know what is the main reason behind having only one primary key in a table??
View 3 Replies View RelatedI ran into a table that is used a lot. Well less than100,000 records. Maybe not a lot of records but i believethis table is used often. The table has 26 fields, 9 indexesbut no Primary Key at all!There are no table relationships defined in this database, noNatural keys, only Surrogate keys in the database.1- Maybe an odd question but is it normal to have 1/3 of thetable's fields as indexes? Is this a valid question or it reallydoesn't matter if you have 9 indexes if they are appropriate to beindexes?2- Below is the DDL of the indexes (Is DDL the appropriate termto describe the indexes?) Without going into too technical aboutwhat the table is, what relationships it has with other tables,would you be able to tell if the indexes are good, bad, too many,etc?3- If i open the table in DESIGN view in SQL EM, i don't seethe Primary key icon. Yet here i see the words "PRIMARY KEYNONCLUSTERED". Does this mean UNIQUENO is actually some typeof primary key? If it was CLUSTERED then SQL EM would showUNIQUEID with a key to the left it identifying it as a PK?If that is the case, then what is the difference betweenPRIMARY KEY NONCLUSTEREDandPRIMARY KEY CLUSTERED?CREATE UNIQUE CLUSTERED INDEX [TBLTEST_PK] ON[dbo].[TBLTEST]([UNIQUENO]) WITH FILLFACTOR = 80 ON [PRIMARY]GOCONSTRAINT [PK_TBLTEST] PRIMARY KEY NONCLUSTERED([UNIQUENO]) ON [PRIMARY]GOCREATE UNIQUE INDEX [ASSIGNUNIQUENAME] ON[dbo].[TBLTEST]([USERNO], [STARTDATE], [NAME]) WITHFILLFACTOR = 80 ON [PRIMARY]GOCREATE INDEX [ENDDATE] ON [dbo].[TBLTEST]([ENDDATE]) WITH FILLFACTOR = 80 ON [PRIMARY]GOCREATE UNIQUE INDEX [IUSERASSIGNACT] ON[dbo].[TBLTEST]([USERNO], [TASKNO], [PROCESSENTRYNO])WITH FILLFACTOR = 80 ON [PRIMARY]GOCREATE INDEX [STARTDATE] ON [dbo].[TBLTEST]([STARTDATE]) WITH FILLFACTOR = 80 ON [PRIMARY]GOCREATE INDEX [PROCESSENTRYNOTBLTEST] ON[dbo].[TBLTEST]([PROCESSENTRYNO]) WITHFILLFACTOR = 80 ON [PRIMARY]GO/****** The index created by the following statementis for internal use only. ******//****** It is not a real index but exists asstatistics only. ******/if (@@microsoftversion > 0x07000000 )EXEC ('CREATE STATISTICS [Statistic_NAME] ON[dbo].[TBLTEST] ([NAME]) ')GOCREATE INDEX [TASKNO_IDX] ON [dbo].[TBLTEST]([TASKNO]) WITH FILLFACTOR = 80 ON [PRIMARY]GOCREATE INDEX [TBLTEST_ORGANIZATIONNO_IDX] ON[dbo].[TBLTEST]([ORGANIZATIONNO]) WITHFILLFACTOR = 80 ON [PRIMARY]GOCREATE INDEX [TASKNOUSERNO] ON [dbo].[TBLTEST]([USERNO], [TASKNO]) WITH FILLFACTOR = 80 ON [PRIMARY]GOThank you
View 4 Replies View RelatedHi to all.
I have a table with about 8 columns as a primary key. I would like to delete one column but not destroy the other 7 primary keys and their relationships between the other tables. Is it possible to do this?
Thanks.
I'm trying to find the primary key on a given table in SQL Server 2000 using SQL. I'm querying the sysobjects table to find a given table, and then querying the sysindexes table. I've ALMOST found what I'm looking for. I see the indexes and columns etc. on the tables in the database, I just don't see the field that indicates that the index is the primary key.
Can anyone help?
Thanks, Alex
I'm trying to drop a table onto the design view in Web Matrix and the following message appears: "dropped table does not have a primary key and cannot be used".
I'm using a SQL 2000 database that was previously an MSDE 2000 database. Is there anyway that I can define a column as a primary key?
Thanks.
I want to get the Primary Key Columns in Arrays by sending a tablename. I am using SQL Server 2000 and I want to make a find utility in VB.net whichwill work for all the forms; I have tables with one Primary key and some tables with composite Primary keys. I used to do this in VB 6 by making a function which fills the Primary Keys inList Box (I require to fill in list box), now I need to get in array. Can some one tell me the migration of the following VB 6 Code? This was written for the MS Access, I need same for SQL Server, I cannot find Table Def and Index Object in VB.net 2003. Public Sub GetFieldsFromDatabase (ldbDatabase As Database, lsTableName AsString) Dim lttabDef As TableDef Dim liCounter As Integer Dim liLoop As Integer Dim idxLoop As Index Dim fldLoop As Field
With ldbDatabase For Each lttabDef In .TableDefs If lttabDef.Name = lsTableName Then liCounter = lttabDef.Fields.Count For liLoop = 0 To liCounter - 1 cboFieldLists.List(liLoop) = lttabDef.Fields(liLoop).Name Next liLoop For Each idxLoop In lttabDef.Indexes With idxLoop lblIndexName = .Name If .Primary Then liCounter = 0 For Each fldLoop In .Fields cboPrimaryKeys.List(liCounter) = fldLoop.Name liCounter = liCounter + 1 Next fldLoop End If End With Next cboFieldLists.ListIndex = 0 If cboPrimaryKeys.ListCount > 0 Then cboPrimaryKeys.ListIndex = 0 End If Exit For End If Next End WithEnd Sub
I need to find the primary key of a table, in MySQL i used SHOW COLUMNS and looped through them to find which one was primary if any. The MSSQL equivalent is SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'table_name' apparently. However the result doesnt give me any key information. How can i find out
1. if a primary key exists on a table
2. what column that primary key exists on
Hi Guys,
I have designed a simple table named "test" with ID as primary key and Name as a string data. When I delete a row from the table and insert a new row.. then the ID column increments itself by 1..
for eg : if i have 2 rows in my table
1 Karthik
2 you
if I delete the 2nd row and insert your name in the place of 2nd row..
actually my rows shows
1 Karthik
3 yourname
could anybody give me some advices..
Thanks,
Karthik Gopal
I have a table name Dispatch with the following column
ID,Date, Name, Pickup, Going
I have a next table called Move with the following column
ID, MoveName
I link Move table with Dispatch table via relationship.
ID_Pickup, ID_Going
upon running query no data is shown unless i remove one of the column form the query either pickup or going ...
Hi,
I need to create a new table in our database.
This table is not linked into the existing schema in anyway, so i'm not sure if I need a primary key or not.
either way, coudl anyone tell me how to create a primary key ni the CREATE TABLE statement.
I have tried searching but cannot find the answer.
many thanks,
Matt
i have 2 tables. Table a and Table b. i need to insert only the new records from Table a to Table B
how do you update table B where the primary key is 4 columns.
solved.!