Tuesday, July 4, 2017

How to create non-clustered indexes on a SQL Server 2008 database? Preferably without code?

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!

No comments:

Post a Comment

linux - How to SSH to ec2 instance in VPC private subnet via NAT server

I have created a VPC in aws with a public subnet and a private subnet. The private subnet does not have direct access to external network. S...