SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 interview questions		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mikebird
Aged Yak Warrior

United Kingdom
518 Posts
Posted - 07/23/2012 :  18:37:12  Show Profile  Reply with Quote
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
Edited by - mikebird on 07/23/2012 18:45:29

tkizer
Almighty SQL Goddess

USA
35017 Posts
Posted - 07/23/2012 :  18:51:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

Brendthess
Starting Member

USA
2 Posts
Posted - 07/23/2012 :  19:29:44  Show Profile  Reply with Quote
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.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3448 Posts
Posted - 07/24/2012 :  04:44:50  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3448 Posts
Posted - 07/24/2012 :  04:48:57  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000