Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need help creating a simple index on MS SQL Server

Posted on 2014-11-22
8
Medium Priority
?
377 Views
Last Modified: 2014-11-23
Our website is hosted by web.com.  We have an plan that provides an MS-SQL server.
Since it is a hosted server, I can not index it with SMSS as I would my local server.

I have .asp code that runs on the server which queries the LOGSEARCH table of the InternetData database.

I need four indexes:

strSearchNumber,  SResult, OrderNumber, , and SDate.  

Can anyone help?
0
Comment
Question by:pcalabria
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 40459344
Here's the MSDN page that shows the SQL for creating indexes: http://msdn.microsoft.com/en-us/library/ms188783.aspx
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 40459358
Here's a simple ASP page that works with my SQLEXPRESS 2005 database.  You'll have to change the names of everything.
<%@ LANGUAGE = VBScript %>
<%  Option Explicit		%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<title>Old Website List - ASP/MSSQL</title>
</head>
<body>
<!-- Display Header -->
<h2>Old Website List Index - ASP/MSSQL</h2>
<p>Creating Index Sortdex on websitelist</p>
<%
Dim connectstr, oConn, ndxrslt

connectstr = "Provider=SQLNCLI;Server=YOURSERVER\SQLEXPRESS;Database=YourDB;Uid=YourID; Pwd=YourPWD;"

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open connectstr

' --------- create index ---------------
Set ndxrslt = Server.CreateObject("ADODB.Recordset")
ndxrslt.Open "CREATE INDEX Sortdex ON websitelist (Sortname)", oConn

Set oConn = nothing
%>
</body>
</html>

Open in new window

0
 
LVL 84

Accepted Solution

by:
Dave Baldwin earned 1000 total points
ID: 40459360
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 

Author Comment

by:pcalabria
ID: 40459370
Thanks Dave, but I'm very confused.

First, my ASP code connects to the database, and works fine.  No problems here, in fact I prefer not to change the connection strings.  They took forever to figure out!

I may just be too green at MS SQL to understand the W3schools reference.  I guess the first question is to confirm that this should work with a shared hosting plan.  In other words, I do not have Admin privileges.

Secondly, if I can create an index in code where does the code go?  I've run transact SQL queries before, but that's it.

Can I open a query and use the code from W3?
0
 
LVL 7

Assisted Solution

by:Ray
Ray earned 1000 total points
ID: 40459415
With most shared hosting service, you should have access to create database objects (tables, view, stored procedures).  Then you should be able to create indexes.

If you want to creat the index with code, just replace the transact sql queries (SELECT * from table1) with the following.

CREATE INDEX IX_strSearchNumber ON LOGSEARCH (strSearchNumber)
CREATE INDEX IX_SResult ON LOGSEARCH (SResult)
CREATE INDEX IX_OrderNumber ON LOGSEARCH (OrderNumber)
CREATE INDEX IX_SDate ON LOGSEARCH (SDate)

These will create 4 indexes on the LOGSEARCH table with the specific fields.
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 40459916
After changing the login info, my script works fine on my Godaddy hosting with SQLEXPRESS 2005.  I used the same login as I do for doing a SELECT or any other query.
0
 

Author Closing Comment

by:pcalabria
ID: 40460398
Thanks guys!
Dave, your links taught me so much about MS SQL that I did not know,
and Ray, you sure made things easy.  I copied and pasted your links into the query area, typed Go and my problem was solved!

Appreciate the help!
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 40460402
You're welcome, glad to help.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question