interview questions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

interview questions

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2012-07-23 : 18:37:12

What is the name of a table which has no non-clustered indexes?

It made me think of heap, primary key, and fact table at the centre in a data warehouse

Why ask the name of it?
_______________________________________
Three ways to count the rows in a table:
select * from mytable
or select count(*) from mytable might be equivalent and a valid second method

as for a third, I think the DBA has a subtle question, which might be obvious to you
such as not involving SELECT

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-23 : 18:51:55

A table with no clustered index is a heap. A table with no non-clustered indexes doesn't have a name as far as I know, it's just a table.

You can get the number of rows from the clustered index. It's also available in the index usage DMF.

I don't think select * is a good answer to the COUNT(*) question. I wouldn't accept it as an answer if I were the one doing the interview.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Brendthess
Starting Member

2 Posts

Posted - 2012-07-23 : 19:29:44

This is a valid, although bad, way to count the rows:

SELECT Sum(1)
FROM MyTable

One unusual way would be a self update followed by an @@rowcount check. If the table contained a field called DateAdded, for example, you could do:

UPDATE MyTable
SET DateAdded = DateAdded
SELECT @@rowcount

Why would you do it this way? No rational idea.

Also, you could examine the table's information for its assumed rowcount. Although this is too frequently inaccurate, it is one way to get a count of rows.

Also, stating that a table has no non-clustered index is insufficient to name the table type (assuming there is a valid name for a table with a clustered index). It does not state that there are any indexes on the table, which could mean it is still a heap.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-24 : 04:44:50

The 'quick' way to count the rows in a table is to look up the index stats. However, I imagine that's probably a 'extra credit' type answer :) It doesn't evaluate the rows in table so when you have multi million (or billion) row structures it's a life saver.
Something like this:


SELECT
	[Table_name] = OBJECT_NAME(so.[object_Id])
	, [Rows] = SUM ([row_count])
FROM
	sys.dm_db_partition_stats AS ss
	JOIN sys.objects AS so ON so.[object_id] = ss.[object_id]
WHERE
	so.[type] = 'U'
	AND so.[is_ms_shipped] = 0
	AND [index_id] IN (0, 1)
GROUP BY
	OBJECT_NAME(so.[object_id])

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-24 : 04:48:57

Out of interest...

What kind role was this question for?

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources