For instance, my query is like the following using SQL Server 2005: SELECT * FROM Table WHERE FREETEXT(SearchField, 'c#') I have a full text index defined to use the column SearchField which returns results when using: SELECT * FROM Table WHERE SearchField LIKE '%c#%' I believe # is a special letter, so how do I allow FREETEXT to w...
Given that indexing is so important as your dataset increases in size, can someone explain how indexing works at a database agnostic level? For information on queries to index a field, check out http://stackoverflow.com/questions/1156/how-do-i-index-a-database-field...
Hopefully, I can get answers for each database server. For an outline of how indexing works check out: http://stackoverflow.com/questions/1108/how-does-database-indexing-work...
I have several tables whose only unique data is a uniqueidentifier (a Guid) column. Because guids are non-sequential (and they're client-side generated so I can't use newsequentialid()), I have made a non-primary, non-clustered index on this ID field rather than giving the tables a clustered primary key. I'm wondering what the performan...
I've just come across this in a WHERE clause: AND NOT (t.id = @id) How does this compare with: AND t.id != @id Or with: AND t.id <> @id I'd always write the latter myself, but clearly someone else thinks differently. Is one going to perform any better than the other? I know that using <> or != is going to bust any hopes for usin...
I have a query where I am searching against a string: SELECT county FROM city WHERE UPPER(name) = 'SAN FRANCISCO'; Now, this works fine, but it doesn't scale well, and I need to optimize it. I have found an option along the lines of creating a generated view, or something like that, but I was hoping for a simpler solution using an in...
Since both a Table Scan and a Clustered Index Scan essentially scan all records in the table, why is a Clustered Index Scan supposedly better? As an example - what's the performance difference between the following when there are many records?: declare @temp table( SomeColumn varchar(50) ) insert into @temp select 'SomeVal' selec...
From what information I could find, they both solve the same problems - more esoteric operations like array containment and intersection (&&, @>, <@, etc). However I would be interested in advice about when to use one or the other (or neither possibly). The PostgreSQL documentation has some information about this: GIN index lookups are...
I have a sproc that puts 750K records into a temp table through a query as one of its first actions. If I create indexes on the temp table before filling it, the item takes about twice as long to run compared to when I index after filling the table. (The index is an integer in a single column, the table being indexed is just two column...
I have a table with many millions of rows. I need to find all the rows with a specific column value. That column is not in an index, so a table scan results. But would it be quicker to add an index with the column at the head (prime key following), do the query, then drop the index? I can't add an index permanently as the user is nomin...
If I have a query like: Select EmployeeId From Employee Where EmployeeTypeId IN (1,2,3) and I have an index on the EmployeeTypeId field, does SQL server still use that index? ...
Was looking to get peoples thoughts on keeping a Lucene index up to date as changes are made to the domain model objects of an application. The application in question is a Java/J2EE based web app that uses Hibernate. The way I currently have things working is that the Hibernate mapped model objects all implement a common "Indexable"...
I have the following tables in my database that have a many-to-many relationship, which is expressed by a connecting table that has foreign keys to the primary keys of each of the main tables: Widget: WidgetID (PK), Title, Price User: UserID (PK), FirstName, LastName Assume that each User-Widget combination is unique. I can see two...
I'm writing a manual and some important keywords are repeated in several pages. In the project's index I defined the keywords like this: <LI> <OBJECT type="text/sitemap"> <param name="Name" value="Stackoverflow"> <param name="Name" value="Overview"> <param name="Local" value="overview.html#stackoverflow"> <param name="Name" value="C...
In SQL Server 2005, the query analyzer has told me many times to create a non-clustered index on a primary ID column of a table which already has a clustered index. After following this recommendation, the query execution plan reports that the query should be faster. Why would a Non-Clustered index on the same column (with the same sor...
I was going to Ask a Question earlier today when I was presented to a surprising functionality in Stackoverflow. When I wrote my question title stackoverflow suggested me several related questions and I found out that there was already two similar questions. That was stunning! Then I started thinking how I would implement such function...
I have a file (fasta file to be specific) that I would like to index, so that I can quickly locate any substring within the file and then find the location within the original fasta file. This would be easy to do in many cases, using a Trie or substring array, unfortunately the strings I need to index are 800+ MBs which means that doing...
If you're creating a temporary table within a stored procedure and want to add an index or two on it, to improve the performance of any additional statements made against it, what is the best approach? Sybase says this: "the table must contain data when the index is created. If you create the temporary table and create the index on an e...
If I create an index on columns (A, B, C), in that order, my understanding is that the database will be able to use it even if I search only on (A), or (A and B), or (A and B and C), but not if I search only on (B), or (C), or (B and C). Is this correct? ...
We have a whole bunch fo queries that "search" for clients, customers, etc. You can search by firstname, email, etc. We're using LIKE statements in the following manner: select * from customer where fname like '%someName%' Does full-text indexing help in the scenario? Edit: I failed to mention we're using MSSQL 2005. ...