nonclustered

Which non-clustered index should I use?

Here I am studying nonclustered indexes on SQL Server Management Studio. I've created a table with more than 1 million records. This table has a primary key. CREATE TABLE [dbo].[Customers]( [CustomerId] [int] IDENTITY(1,1) NOT NULL, [CustomerName] [varchar](100) NOT NULL, [Deleted] [bit] NOT NULL, [Active] [bit] NOT N...

How to set index on the column which have datatype varchar(4096) in SQL Server 2005?

This is the query for creating index create index idx_ncl_2 on BFPRODATTRASSOCIATION (value,attributeid) include (productid) Table structure of BFPRODATTRASSOCIATION ProdAttrAssociationId bigint no 8 ProductId bigint no 8 AttributeId bigint no 8 Value varchar no 4096 I am getting this error: T...

Improve SQL Server 2005 Query Performance

I have a course search engine and when I try to do a search, it takes too long to show search results. You can try to do a search here http://76.12.87.164/cpd/testperformance.cfm At that page you can also see the database tables and indexes, if any. I'm not using Stored Procedures - the queries are inline using Coldfusion. I think I...

tsql query and index question

I have a table that stores photo information with id as Primary key: id(PK), title, album_id, posted_by, published, filename, tags, ratings, date_posted This table will hold infor of 100+ Million photos and I need to run this query like these frequently: 1) get all photos (just id,filename,title columns) of a given album s...

Where clause on non-clustered index Vs extra join and where clause on clustered index

Hi all I am trying to eak out a bit extra performance from some sql queries which have a where clause on a field which is a non-unique non-clustered index, it is also a foreign key in table A. That foreign key is the primary key on table B and is the clustered index. What I'm wondering is, will I get a performance gain if I add a join ...

Filtered index vs normal non clustered index.

Hi, I have 270 million records in a table. Currently I have a non clustered index in it on a date column. 99% of time I use rows with date > 1/1/2008..that means 140 million out of it. I am using SQL server 2008.In this situation will it be beneficial to use filtered index other than normal non-clustered index ? Also if I use "date" da...