Two basic questions - Record Number and Index

Question

Post reply

Two basic questions - Record Number and Index

munzer haque

Ten Centuries

Points: 1187
More actions
February 26, 2005 at 1:16 am

#102406

Two disjoint questions:
1) Does SQL server 2000 internally assign a unique record number to each row of a table regardless of the table has a PK or not? Probably, this value is used for indexing purpose?

2) In a very, very, simple system, the ONLY table has 3 columns:
UserId, Name, Good.
Data type of good is bit. We expect to query on Good.
Does it make any sense to index Good column to improve the performance?
Or we would be better off to store records in two seperate tables according to the value of Good.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 1

SQl Server 200 does not have a unique record number like Oracle (I guess&nbsp

But U can wirte quiries to get the unique record number like in this article

http://www.sqlservercentral.com/columnists/dasanka/findminmaxvaluesinaset.asp

My Blog: http://dineshasanka.spaces.live.com/

Charles Otten Default port Points: 1475 More actions · Answer 2

In your second question you asked about an index on a bit field helping. Actually, you can't index a bit field. SQL Server doesn't really need help with it, basically, and I would not suggest a separate table as you would be adding union requirements to get a list of all records.

If the phone doesn't ring...It's me.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Charles,

you *can* create indexes on BIT columns. At least with Query analyzer.

create table showme

(

c1 bit

)

create unique clustered index ci_prettyuseless on showme(c1)

select

objectproperty(object_id('showme'), 'TableHasClustIndex')

drop table showme

-----------

1

(1 row(s) affected)

,...but one might question the sense behind this, because selectivity isn't that high on such a column.

However, I think it might make sense to add a BIT column sometimes to get a covered index. But to judge on this, one needs to know what kind of queries will be run against this table.

As for the first question: No, SQL Server does not have such a number. However, internally SQL Server has a thing called RID, consisting of the file number : page number : slot number. Although you can't query it, you might want to read BOL about it.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

munzer haque Ten Centuries Points: 1187 More actions · Answer 4

Frank,

Your answers sound logical. But I need a little more clearification. Please see my original questions at the begining of this thread. I will appretiate your time.

1. I do not intend to query by sql server internal RID. I just wanted to know if sql server maintains a RID for internal use. How else can SQL server core engine can map the indexing? Is it not that in addition to indexing the pages sql server also index rows? [Note - analogy: A printed book index only the page numbers not the line number - it can't because it doesn't have any line number.]

2. As I mentioned in the second part of my question that the ONLY query will be coming in with WHERE clause is on the bit column. I suspect, I will be better off by keeping two seperate tables instead of one with bit column indexed.

Thanks.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

ad 1: SQL Server surely has internally a mechanism to access a row by its physical location. You have to distinguish between two possible cases. A table with only nonclustered indexex (a heap) on it. And a table with a clustered index on it. Basically both index structures are the same. Down to the leaf level. That's where they differ. A nonclustered index stores there among other, the RID, while a clustered index does store there the table data itself. This is a complex topic which is also explained in BOL. You might need to search a little bit, but under "indexes, architecture" you find some useful information about it. Also, see if these links help:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;311826

http://support.microsoft.com/default.aspx?scid=kb;EN-US;814324

http://support.microsoft.com/default.aspx?scid=kb;EN-US;325024

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/itwforsql.asp

ad 2) Might be better, since because of the low selectivity of such an index, SQL Server might not find that index useful anyway.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

munzer haque Ten Centuries Points: 1187 More actions · Answer 6

Frank,

I think you answered my questions. thanks a lot.

And yes, I was talking about non-cluster index.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

You should make up a new thread for these new questions. Most people will scan the subject and therefore might miss this one. I for myself don't know anything about these things

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]