0

I would appreciate help on how to create non-clustered indexes on a SQL Server 2008 database without using code--or rather, 'statically' once and for all prior to running any SQL queries (that probably does not make sense, but my point being I don't want to run the SQL command to create indexes everytime I run my SQL queries that are part of my business application).

That is, ideally there's a tool within Microsoft SQL Server built into Visual Studio 2010 Professional (NOTE: I DO NOT HAVE ENTERPRISE OR ULTIMATE EDITIONS--THIS MAKES A BIG DIFFERENCE AS TO WHAT I CAN DO WITH THE BUILT-IN SQL MANAGER IN PROFESSIONAL VERSION) to do this--since I don't have any other tool (I just looked, and found that Microsoft SQL Server 2008 does not have what I need--at least on my system--it is apparently a crippled freeware version). So perhaps a simple SQL command to index the below table is warranted.

I have read the references below, but I cannot figure out how to do this.

Here is my table:

Table CUSTOMER
Columns:

CustomerID = GUID - this is a unique primary key

CustomerDecimal1 = decimal- this is not unique, but 99% of the time it is unique   
compared to the rest of decimal fields.  I wish to index this field 1 of 2

CustomerDecimal2 = decimal- this is not unique, but 99% of the time it is unique
 compared to the rest of decimal fields. I wish to index this field 2 of 2

CustomerTextComments = vChar(50)

The decimal fields are frequently used in WHERE clauses, so they are ideal candidates for a non-clustered index, which apparently is a new feature supported in Microsoft SQL Server 2008.

Further about my platform: I do already have a table with existing data in it, but only a few records, mostly blank. I am working from the Server Explorer from inside of Visual Studio 2010, which has a lot of functionality including the ability to generate SQL queries. Ideally I'd like to write any indexing method in Linq-to-entities (only because I don't really know SQL that well) but if somebody can give me a complete listing on how to index CustomerDecimal1, CustomerDecimal2 fields in this table I would be grateful.

References:

http://blog.sqlauthority.com/2008/09/01/sql-server-2008-introduction-to-filtered-index-improve-performance-with-filtered-index/ (SQL Server 2008 new 'filtered' index property for WHERE clause searches)

http://en.wikipedia.org/wiki/Index_%28database%29#Non-clustered

-----Updated

@mrdenny -- I thank you for your time and I see you have a stellar reputation, but I cannot believe what you are saying--yes I am stubborn and call it denial! :-) I will leave this thread open a bit more in the hopes somebody else sees it. Also as I do not run SQL natively, only Linq-to-entities from inside the Entity Framework (EF 4.0) I would not even know where to put the code you helpfully provided ("T/SQL to create a non-clustered index on the two decimal columns"). I am using both decimal columns at all times in my WHERE search--so your first SQL command is appropriate for me.

Can anybody translate Mr. Denny's first SQL code into Linq-to-Entities? Failing that,I will throw up my hands and say i don't believe it (goes against what I read about indexing being like a balanced tree of sorts, which should be automatically built into the system), or, in the alternative, I've read between the lines that indexing will save you at most about 20% better performance--good but nothing to really get too worked up over. Yes it's sour grapes!

7
  • @mrdenny -- I thank you for your time and I see you have a stellar reputation, but I cannot believe what you are saying--yes I am stubborn and call it denial! :-) I will leave this thread open a bit more in the hopes somebody else sees it. Also as I do not run SQL natively, only Linq-to-entities from inside the Entity Framework (EF 4.0) I would not even know where to put the code you helpfully provided ("T/SQL to create a non-clustered index on the two decimal columns"). I am using both decimal columns at all times in my WHERE search--so your first SQL command is appropriate for me. Can anybod Nov 24, 2011 at 2:24
  • Where did you read the stuff about indexing being built in. I've never heard a more ridiculous statement about database design.
    – Jim B
    Nov 24, 2011 at 3:49
  • 1
    Still his answer is correct. Deal with it.
    – TomTom
    Nov 24, 2011 at 4:15
  • As far as I know there is no way to create indexes via Linq to SQL. SQL Server indexes are B-Trees and the indexes are built into the system. Indexes are not created automatically. When you add the correct indexes into the database the performance can go up tens, hundreds or thousands of times. The bigger the table is the better the performance boost that you will get from adding the correct indexes.
    – mrdenny
    Nov 24, 2011 at 4:59
  • I've moved your answer to a comment, and updated the question with the information and questions as it won't all fit into a single comment.
    – mrdenny
    Nov 24, 2011 at 5:00

1 Answer 1

1

First non-clustered indexes are not new in SQL Server 2008. They have been around for ever. Filtered indexes are new in SQL Server 2008.

There is no way to have the system figure out what indexes need to be created before any queries are run against the SQL Server.

There are two ways to create indexes in SQL Server. One is with T/SQL, and the other is using SQL Server Management Studio which has a graphical editor. The graphical editor will simply generate T/SQL and run the T/SQL against the database.

The T/SQL to create a non-clustered index on the two decimal columns would look something like it looks below.

CREATE INDEX IX_CustomerDecimal1_CustomerDecimal2 on dbo.CUSTOMER
(CustomerDecimal1, CustomerDecimal2)
INCLUDE (CustomerTextComments)
WITH (FILLFACTOR=70)

Not if you plan on using the two CustomerDecimal fields independently when searching the database table then you'll need a separate index for each.

CREATE INDEX IX_CustomerDecimal1 on dbo.CUSTOMER
(CustomerDecimal1)
INCLUDE (CustomerTextComments)
WITH (FILLFACTOR=70)

CREATE INDEX IX_CustomerDecimal2 on dbo.CUSTOMER
(CustomerDecimal2)
INCLUDE (CustomerTextComments)
WITH (FILLFACTOR=70)

In either case you'll need to add what ever columns you are returning to the list of included columns so that you minimize the IO being generated.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .