QOD 11 Sep 2003
-
What use is this question? I got the answer to this wrong (so you may say this is just sour grapes!) but I can't see how any developer would need to know this information.
If I've written the table I would know that auto_create_stats is on and I can't think of any reason why I would want to know that the data is in a heap.
Somebody enlighten me!!
-
As a general rule any good SQL developer or DBA needs to know their way around the sysindexes table and exactly how sql stores and accesses indexes. Questions like this will appear in relevant Microsoft exams (Module 6 and 7 in MCP 2073)
Knowing how SQL uses the sysindexes table is essential to creating good database design. Knowing what statistics are and how to use them is an essential part of both database design and maintenance.
If you know the answer to this question you would be able to make further design descisions based upon it, for instance you would know better which fields to index.
Also if you don't know the difference between a heap and a clustered index and how that effects non-clustered indexes on the table you really really shouldn't be designing or maintaining databases. You need to look up IAM, Heap, Clustered, non clustered, create|drop statistics and indexes in BOL
Keith Henry
Keith Henry
According to everyone I know I "do something with computers?" for a living, so there you go. -
I thought it was a good question, and very relevant to common DBA troubleshooting activities.
Cheers,
- Mark
Cheers,
- Mark -
Yes a good question and I got it wrong. I originally was going for 3 but changed my mind (doh!
). I went for 4 as in sysindexes indid >= 2 is a non-clustered index. After reading the question and answers a few times the words 'The data has' became prominent. I still think 4 is correct as the data has a nonclustered index whether it is for statistics or not, the statistics belong to the data, mute point probably and I'm sure someone will dispute this and shoot me down in flames. Far away is close at hand in the images of elsewhere.
Anon. -
quote:
I thought it was a good question, and very relevant to common DBA troubleshooting activities.Cheers,
- Mark
I agree, Mark, DBA relevant maybe, but not developer relevant. Especially on a table only containing 24 rows. any indexing differences are going to be so slight as to not be noticable.
-
quote:
4) The data has a non-clustered index called _WA_Sys_lastname_5031C87BThis can't be right because while the indid (between 2 and 250) suggests an non-clustered index there is a zero FirstIAM value.
If it were an index this field FirstIAM would contain the location of the start of the index
indid FirstIAM dpages rows rowmodctr name
------ -------------- ----------- ----------- ----------- -------------------------
0 0x8C0000000100 1 24 24 member
2 0x000000000000 0 0 -24 _WA_Sys_lastname_6FB49575create index members_nonclust_idx on member(lastname)
indid FirstIAM dpages rows name
------ -------------- ----------- ----------- --------------------
0 0x8C0000000100 1 24 member
3 0x8E0000000100 1 24 members_nonclust_idxI deliberately put question 4 in because I knew that it was a relly common mistake to confuse Statistics with Non-Clustered Indexes
Keith Henry
Keith Henry
According to everyone I know I "do something with computers?" for a living, so there you go. -
quote:
DBA relevant maybe, but not developer relevant. Especially on a table only containing 24 rows.The 24 rows are just to illustrate the example. It could be 24,000,000 rows but the example if from an MCP course on indexes I ran and I didn't want to get bogged down adding rows to my example.
I would say its not developer relevant to the pure ASP boys out there, but I think its a safe assumtion that anyone attempting this question has to develop on SQL server.
I wouldn't want anyone developing anything but the most basic and tiny databases if they didn't know a bit about indexes. Most databases are designed by developers and then handed over to DBAs who will be mighty pissed off if it has stupid indexes.
We had one once with 12 indexes on one table (48 million rows) that had a 4 field clustered index (fillfactor 10%) and they wondered why it was slow! I call that bad development
Two last points
1) The QOD is supposed to be challenging.
2) The MCP I quoted is SQL for Developers 2073, so I guess Microsoft agree with me
Keith Henry
Keith Henry
According to everyone I know I "do something with computers?" for a living, so there you go. -
It seems to become a habit to complain about the question if one had it wrong.
Be a sport.
This was one of the best questions in the QOD. There was no room for misinterpretation and it tested the knowledge of the internals of SQL Server.
-
quote:
It seems to become a habit to complain about the question if one had it wrong.Be a sport.
This was one of the best questions in the QOD. There was no room for misinterpretation and it tested the knowledge of the internals of SQL Server.
I don't have a problem with the question itself, some people may find it very useful. I wasn't really complaining either, just asking the question. I do think it should have been categorised differently though. Despite the points put forward by everybody here, I still don't think it's terribly relevant to a developer. So
to everyone. I'm right, you're wrong!! -
quote:
I still don't think it's terribly relevant to a developer. So to everyone. I'm right, you're wrong!! Amen!
How do you say: 'Another game, another luck' ?!?
Frank
Wenn Englisch zu schwierig ist?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
A developer who doesn't know the difference between a heap and a table with a clustered index or the difference between an index and statistics is bound to create applications and databases with bad performance.
But that's good for the 'real' DBA's. Then we can do 'miracles'. 😉
-
ErikBr,
quote:
It seems to become a habit to complain about the question if one had it wrong.I think that is a bit harsh. What is this forum for if not to query questions/answers etc. I am not complaining that I got it wrong I just think certain assumptions have been made.
quote:
There was no room for misinterpretation...I think there is. Keith is quite right in his explanation that the entry is statistical one. What I am saying is that in all the documentation I have found indid >= 2 is a non-clustered index not a statistic entry. IMHO a FirstIAM of 0x0 states that the non-clustered index is statistical.
Far away is close at hand in the images of elsewhere.
Anon. -
quote:
What I am saying is that in all the documentation I have found indid >= 2 is a non-clustered index not a statistic entry. IMHO a FirstIAM of 0x0 states that the non-clustered index is statistical.]Trust me. It's a statistic and nothing more. Try running the following code:
set nocount on
Print 'Create A sample table'
create table member (firstname varchar(100), lastname varchar(100), Address varchar(2000), phone varchar(11))
go
print 'Add some dummy data'
insert member (firstname , lastname , Address , phone)
select 'Test', 'name', 'a street', '1234123' union
--blah blah blah
select 'Mike', 'smith', 'someplace', '12341234'
go
print 'View The sysindexes Table'
print 'At the moment all we have is the heap'
select i.indid, i.FirstIAM, i.dpages, i.[rows], i.[name] from sysindexes i
join sysobjects o on i.[id] = o.[id] where o.[name] = N'member'
go
print ''
print 'Now go execute something that creates statistcs'
select lastname, firstname from member where lastname = 'Masters'
go
print 'see auto generated statistics.'
print 'some statistical information is stored but no FirstIAM'
select i.indid, i.FirstIAM, i.dpages, i.[rows], i.rowmodctr, i.[name] from sysindexes i
join sysobjects o on i.[id] = o.[id] where o.[name] = N'member'
go
print ''
print 'Now add a Nonclustered index'
create index members_nonclust_idx on member(firstname)
go
print 'View The sysindexes Table again'
print ' Now we have the Heap and an index'
select i.indid, i.FirstIAM, i.dpages, i.[rows], i.[name] from sysindexes i
join sysobjects o on i.[id] = o.[id] where o.[name] = N'member'
go
print 'remove sample table'
drop table member
go
set nocount off]As you can see the entry is there with no indexes having been created. An Index has statistics, but you can also have statistics without the index, in which case the query planner knows how many rows and pages it has to look up, but still does a table scan.
Keith Henry
Edited by - keithh on 09/11/2003 09:18:38 AM
Keith Henry
According to everyone I know I "do something with computers?" for a living, so there you go.
). I went for 4 as in sysindexes indid >= 2 is a non-clustered index. After reading the question and answers a few times the words 'The data has' became prominent. I still think 4 is correct as the data has a nonclustered index whether it is for statistics or not, the statistics belong to the data, mute point probably and I'm sure someone will dispute this and shoot me down in flames. 
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply