Solved

MS SQL Tables with no index

Posted on 2012-03-16
10
659 Views
Last Modified: 2012-03-30
I need to convert a cloud based database to an internal MS SQL 2008 database and one issue I have run into is that none of these tables have any indexes declared. Now this causes me concern for several reasons but the one that stands out immediately is that in new code I have written around one of tables requires a set of rows to appear in a certain order. So a simple query was select itemnmbr,itemdesc from my table where categoryid='XX' order by seq. Where what I thought what this would give me is all items that are in category XX sorted by the seq number. However, I quickly discovered that they do not use the seq number field. They are all zeroes. So much to my amazement the correct order is always achieved by simply select itemnmbr,itemdesc from my table where categoryid='XX'  with no order by clause. How is this possible? How can they be adding records to this table through their code and somehow always maintain the proper sort order without the use of an index? Is there some internal SQL database table structure element they are controlling?
0
Comment
Question by:rwheeler23
[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
10 Comments
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 125 total points
ID: 37731507
If they are inserted in order, they will usually be returned in that order until you start changing and deleting records.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 125 total points
ID: 37731655
If they are inserted in order, they will usually be returned in that order
I am afraid that is simply not true.  Unless you use an ORDER BY clause the order returned is not guaranteed. Period.
0
 

Author Comment

by:rwheeler23
ID: 37731702
There is no identity column either. The old website is a collection of very small data tables. The biggest table is about 3000 records and items are grouped into category. All items within any group share common properties and it is these properties that concern me. These properties appear across the top of each page and provide detailed information as to how the item is configured. There are about 60 categories. I have be checking and so far every time I ask for the list they come out in the order desired. I just do not know how that is happening. The good news is on my side there will be a column that puts items in the proper order so the real proof of the pudding will be after I insert all the items into my table, I do a cross comparison with the old table to see if they all came over in the correct order.
0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 125 total points
ID: 37731975
>so far every time I ask for the list they come out in the order desired.

We'll they will come out in some order, right?  And since there is no clustered index, the db is quite likely to just store them in the order inserted and return them in the order stored  So, coincidentally, they could be coming out in the order desired.  But, I do mean coincidentally and you cannot depend on it.  Even if you're cross-comparison checks out, you cannot rely on the order unless you stipulate one in an order by clause.  Agree with ACPerkins on that.

And, for that matter, if the old table does not have columns that convey the desired order, then I'm quite puzzled how you might derive or validate the seq column in the new table. And even if you do get the desired order 100 time in a row, without an order by clause you've proved nothing.

I'm glad you have the foresight to add the seq column in your internal DB.  You're fortunate to have the data somewhere to reconstruct the desired order.
0
 

Author Comment

by:rwheeler23
ID: 37732428
I agree completely. I was surprised when I saw nothing but zeroes in the seq column. When I first looked at the structure of the table I said, that is the field I need. Then I looked at the data and was surprised. This is a one time transfer. So what I will do is have a copy of the order on the old website and then manually compare the sort order to the new web site. If I find any differences I will update my table.
0
 
LVL 16

Assisted Solution

by:DcpKing
DcpKing earned 125 total points
ID: 37733764
What database system are they using on this cloud system? (obviously not SQL Server or Oracle, it seems). If you can find that out then maybe we can discover what the developers did ...
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37738060
>> you cannot rely on the order unless you stipulate one in an order by clause. <<

That could depend on the specific db being used.  

While the ANSI standard states that for relational dbs, and other relational dbs follow that rule, there is no guarantee that whatever db engine is used to store the original data does not have its own rule that is different.  We don't know if the original db is even relational.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 37738627
This is correct, Scott - even something like SQL Server will appear to behave like this in certain circumstances!

rwheeler23: Although knowing the environment for the original developers would be nice, you're constrained to working with your environment - SQL Server - to achieve your goals. I would suggest that you really do need to key and index your tables.

Which version will you be using, BTW? 2012 has a nice addition for the SELECT statement that help when you're supporting a paged display (on a web page, for example).
0
 

Author Comment

by:rwheeler23
ID: 37763679
Sorry for the trady reponse but I have been out of town and out of touch.

http://www.hostmysite.com/

This is were the current MS SQL database resides. I was able to connect to it using an instance of my instance of SQL Sever 2008. I have imported the entire database and will proceed with trying to analyze the table relationships.
0
 

Author Closing Comment

by:rwheeler23
ID: 37788826
All I know is that they did come out in the correct order with no order by clause. I dumped them all into my SQL table this time indexed with a sequence number. Thank you everyone for responding.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 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