Hi all,
I have been left to modify an old unique index, which no longer works,
as the two fields, which were used, are no longer unique. I’ve found two
others that are unique. But as I’m new to this I need some advice first.
What happens if one table inserts from another (the second containing
the index). However if there is a duplicate will the records which are
not duplicates be inserted? How could I insure this?
Thanks
Sam
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
I am using Full Text Index to index emails stored in BLOB column in a table. Index process parses stored emails, and, if there is one or more files attached to the email these documents get indexed too. In result when I'm querying the full text index for a word or phrase I am getting reference to the email containing the word of phrase if interest if the word was used in the email body OR if it was used in any document attached to the email.
How to distinguish in a Full Text query that the result came from an embedded document rather than from "main" document? Or if that's not possible how to disable indexing of embedded documents?
My goal is either to give a user an option if he or she wants to search emails (email bodies only) OR emails AND documents attached to them, or at least clearly indicate in the returned result the real source where the word or phrase has been found.
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.
The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
My Question are:-
Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?
Q3. I want to check what indexes has been used? on what search?
Q4. How can i check what table was populated when, or last date of update (DML)?
My Limitation is i Dont Create a Partioned table. I dont have permission to do it.
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
My SSIS package is running very slow taking so much time to execute, One task is taking 2hr for inserting 100k records, i have disabled unused index still it is taking time.I am rebuilding/Refreshing indexes and stats once in month if i try to execute on daily basis will it improve my SSIS Package performance?Â
hello friends i have table1 and 200 coulumn of table1 :) i have 647.600 records. i entered my records to table1 with for step to code lines in one day :) i select category1 category2 and category3 with select code but i have just one index.. it is productnumber and it is primarykey..So my select code lines is so slow.. it is 7-9 second.. how can i select in 0.1 second ? Should i create index for category1 and category2 and category3 ? But i dont know create index.. My select code lines is below.. Could you learn me and show me index for it ?? or Could you learn me and show me fast Select code lines and index or etc ??? Also my search code line have a dangerous releated to attaching table1 with hackers :) cheersi send 3 value of treview1 node and childnode and child.childnode to below page.aspx :) Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not Me.IsPostBack Then If Request("TextBox1") IsNot Nothing ThenTextBox1.Text = Request("TextBox1") End If If Request("TextBox2") IsNot Nothing ThenTextBox2.Text = Request("TextBox2") End If If Request("TextBox3") IsNot Nothing ThenTextBox3.Text = Request("TextBox3") End If End If Dim searchword As String If Request("TextBox3") = "" And Request("TextBox2") = "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "'" End If If Request("TextBox3") = "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "'" End If If Request("TextBox3") <> "" And Request("TextBox2") <> "" And Request("TextBox1") <> "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "' and kategori2= '" & Request("TextBox3") & "'" End If SqlDataSource1.SelectCommand = searchword End Sub
I'm running a merge replication on a sql2k machine to 6 sql2k subscribers. Since a few day's only one of the merge agents fail's with the following error:
The merge process could not retrieve generation information at the 'Subscriber'. The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'.
All DBCC CHECKDB command's return 0 errors :confused: I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . .
Hi everyone, When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ?? In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??
I have a clustered index that consists of 3 int columns in this order: DateKey, LocationKey, ItemKey (there are many other columns in this data warehouse table such as quantities, prices, etc.).
Now I want to add a non-clustered index on just one of the other columns, say LocationKey, like this: CREATE INDEX IX_test on TableName (LocationKey)
I understand that the clustered index keys will also be added as key columns to any NC indexes. So, in this case the NC index will also get the other two columns from the clustered index added as key columns. But, in what order will they be added?
Will the resulting index keys on this new NC index effectively be:
LocationKey, DateKey, ItemKey OR LocationKey, ItemKey, DateKey
Do the clustering keys get added to a NC index in the same order as they are defined in the clustered index?
Quick question about the primary purpose of Full Text Index vs. Clustered Index.
The Full Text Index has the purpose of being accessible outside of the database so users can query the tables and columns it needs while being linked to other databases and tables within the SQL Server instance. Is the Full Text Index similar to the global variable in programming where the scope lies outside of the tables and database itself?
I understand the clustered index is created for each table and most likely accessed within the user schema who have access to the database.
Is this correct?
I am kind of confused on why you would use full text index as opposed to clustered index.
One of the major syntax inside the SELECT statment is ..
WHERE FIELDA IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='A') AND WHERE FIELDB IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='B') AND WHERE FIELDC IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='C') AND WHERE FIELDD IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='D') AND WHERE FIELDE IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='E') AND WHERE FIELDF IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='F')
(It's to compare the field content with some user input parameter inside a parameter table... )
I think properly is that the SELECT ... IN is causing much slowness in the sql statement. I have indexed FIELDA , FIELDB, FILEDC etc and those PARAVALUE and PARATYPE in the PARATABLE table. But perfromance is still slow and execution takes >20 seconds for 200000 rows of records.
Do any one know if still any chance to improvide the performance like this?
I'm trying to find whether there is a dmv or system view that can help me see the last time an index was rebuilt or created. Assuming I rebuilt an index using tsql commands (not a job with a history), is there a way to find out the last time that index was rebuilt?
The following was not written by me but I need to modify it and I am definitely no expert in SQL. It returns a list of files that a user is allowed to access based on whether or not they belong to a specific user group and that group has been granted access to the file.
FROM AnswerFile AS a INNER JOIN User AS u ON a.UserID = u.ID
WHERE (a.UserID = 2) AND (a.ID NOT IN SELECT AnswerFileID FROM GroupAnswerFile))
I need to modify this code to add an additional check. I have added a file that allows specific users to be granted access to files. It is called AnswerFileUser and contains AnswerFileID (the file) and UserID (the user that has been given access). I need the query to return as part of the results those files where a user has been given access regardless of their group or group access. I hope this all makes sense. Thanks for your help!
How do you change the time out value for a specific connection? I'm having problems when I connect to SQL Server from my website and I want to test if this would help.
How do I modify data in a sqlDataSource object (like inserting records, editing, deleting) similiary to how I modify data in a GradView using a DataList or repeater? I want to be able to have my own EditTemplate and be able to edit say four fields while I'm only viewing like two of them in the datalist/repeater. <asp:DataList ID="DataList1" runat="server" DataKeyField="GameId" DataSourceID="SqlDataSource1" RepeatColumns="1"> <ItemTemplate> <%# Eval("field1") %><%# Eval("field2") %>.... </ItemTemplate> <EditTemplate>......</EditTemplate>
When I'm trying to add a edit-button (<asp:Button runat="Server" ID="EditButton" Text="Edit" CommandName="Edit" />) and click it nothing happens....What is missing?
I have a DTS was created by another programmer which is for move online Database to a offline Database and now I have to modify it the DTS package have 1 Source DB and 63 distinateDB between SourceDB and DistinateDB are "Black Line" and almost of DistinateDB have a green line between a Task ( excute SQL work) -- some of SQL work are Delete Table and some are Truncate Table and now I have to add some limits for the DTS 1. First, move all the Data From online Database to Offline Database 2. to Delete Data from online DB , Online Database will only keep Data for 6 months ( older than 6 months have to delete except table A and table B) can you help me? or tell me where have a reference information I can take a look... have no idea how to modify this DTS package..
I write a Sql Query for select UPdateTime between 2007/09/01~ 2007/09/30 select * from ZT_Master where TDateTime < DateAdd(Month,-1,GetDate()) the result return all the column.. why?
I have the following query that produces this: 1 0 Arts & Entertainment6 1 Arts & EntertainmentDance2 1 Arts & EntertainmentMovies9 0 Automotive10 0 Business11 0 Cancer12 0 Communications3 0 Computers13 3 ComputersE-Learning4 3 ComputersHardware14 3 ComputersJava16 3 ComputersLink Popularity17 3 ComputersMicrosoft.net15 3 ComputersRSS5 3 ComputersSoftware7 0 Real Estate8 7 Real EstateFinance Here is the query: SELECT tblArticleCategory.ACategoryID, tblArticleCategory.AParentID, ISNULL(Parent.ParentCategory + '', '') + ISNULL(tblArticleCategory.ACategoryname, '') AS ACategory FROM tblArticleCategory FULL OUTER JOIN(SELECT ACategoryName AS ParentCategory, ACategoryID FROM tblArticleCategory AS tblArticleCategory_1 WHERE (AParentID =0) AND (AActive = 1)) AS Parent ON Parent.ACategoryID = tblArticleCategory.AParentID WHERE (tblArticleCategory.AActive = 1) ORDER BY ACategory How do I modify this so that just the category name is returned and not the parent cetegory. In other words, everything shows up just once. Arts and Entertainment, once, then on the next line Dance, then Music then the next parent category which is automotive. Here is the table: ACategoryID int UncheckedACategoryName nvarchar(150) CheckedADescription nvarchar(300) CheckedAParentID int CheckedAActive bit Checked Unchecked
When you first come into the web page I want to exam the querystring and the modify the SQL Statement accordingly in a SQLDatasource.What is the right event of the SQLDataSource to modify the SQLDataSource.SelectCommand = MySQLString property?Thanks
I have a problem with a publication which is a transactional replication on SQL Server 7.0 . I want to add stored procedure to the existing publication along with the tables . But the Enterprise manager does not allow to select or check the box against the required stored procedure although it displays when i right click on the publication and select proeprties .
Can i have some help in this regard ? Does anybody knows to add a stored procedure by means of a query .
Is there any other way to modify publication in SQL Server 7.0 although it is possible in SQL Server 2000 .?
1) Mothly :- -payday is equal to @today -pay frequency is = 1 -payday between 1-31
2) Weekly -payday is between 1-5 ( 1 =Monday,2 =Tuesday,3=Weds,4=Thurs,5=Friday) -payfrequency is = 2
3) Fortnightly -after two weeks -payfrequency =3
How do l modify this procedure to be able to extract using the listed conditions ? Need help
CREATE Procedure Collections_Cats AS BEGIN
Declare @today int Set @today = (SELECT Day(GETDATE()))
DECLARE Collections_Cats_Cursor CURSOR FOR SELECT distinct n.loan_No AS Loan_No, n.customer_No AS Customer_No, c.first_name AS First_name, c.second_name AS Second_name, c.surname AS Surname, c.initials AS Initials, b.Bank_name AS Bank_name, br.branch_code AS Branch_code, d.bank_acc_type AS Bank_acc_type, pay_sheet.pay_frequency AS Pay_Frequency, n.monthly_Payment AS monthly_Payment, pay_sheet.payday AS payday FROM Transaction_Record tr
INNER JOIN Loan n ON tr.loan_No = n.loan_No INNER JOIN Customer c ON n.customer_No = c.customer_no INNER JOIN Bank_detail d ON c.customer_no = d.customer_no INNER JOIN Branch br ON d.Branch = br.Branch INNER JOIN Bank b ON br.Bank = b.Bank INNER JOIN pay_sheet ON c.customer_no = pay_sheet.customer_no WHERE Pay_sheet.Payday = @today AND pay_sheet.pay_frequency =1
Order by l.loan_No
OPEN Collections_Cats_Cursor
-- Perform the first fetch. FETCH NEXT FROM Collections_Cats_Cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM Collections_Cats_Cursor END
CLOSE Collections_Cats_Cursor DEALLOCATE Collections_Cats_Cursor END; GO
i have a replication job going on for one particular table from one server to another now i have added two new columns in source and replication works fine. once i add these two new columns in destination the replication is failing.
i went to properties of the publisher and select the filter columns where i can find all the columns checked except the two new columns when i try to select these two its not responding pls help me in this.
I am using Sql server 2005, i am trying to find out all the tables that modified recently. i cant find any last modified date!!.
My situation is; i have altered so many tables, usually when i do alter i save the sql file for doing the same alteration in the production database. but somehow i lost the scripts and i am not sure what are the tables that i have modified and what is new.
I have converted Access database to sql express. Access Database had AutoNumber FIelds for which trigger was generated by Upsizing wizard.
Now when I import data from client the autonumber field value changes because trigger is fired which distroys all links
I want to modify trigger so that it generates new number only when it is not supplied in a insert command.
Please help. Code is given below. Also suggest how to save because when I use save, it asks for a new .sql file name and a new file is generated instead of modifying the same trigger
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[T_AcControlLimit_ITrig] ON [dbo].[AcControlLimit] FOR INSERT AS SET NOCOUNT ON DECLARE @randc int, @newc int /* FOR AUTONUMBER-EMULATION CODE */ /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'IntAcControlCode' */ SELECT @randc = (SELECT convert(int, rand() * power(2, 30))) SELECT @newc = (SELECT IntAcControlCode FROM inserted) UPDATE AcControlLimit SET IntAcControlCode = @randc WHERE IntAcControlCode = @newc
I initially modified the column 'ID' from not allowing NULL to allow, and saved it. But now I will like to change it back to 'not allow'. But it gives me the following error message when I try to save my changes:
'Pages' table - Unable to modify table. Cannot insert the value NULL into column 'ID', table <databasename>.Tmp_Pages'; column does not allow nulls. INSERT fails. The statement has been terminated.
I am trying to create a report with unknown number of report items. (as I dont know how many columns the stored proc is going to return).Is there a way to create these type of reports using SSRS other than creating the rdl programatically using vb.net or c#?
