SQL Server Performance Forum – Threads Archive

Concseptual questions…Plz explain

1) I am running query select * from <sometable>. Table has a clustered index and non clustered index. Will running above query result in table scanning or index scanning??? When I saw execution plan it was scanning of clustered index??
Why it must be table scan as i think?? 2) We have a one primary key in a table #%92A#%92 and table â€˜B#%92 with composite primary key consisting of three keys . Which one will be more efficient in terms of searching ??? Won#%92t composite primary key hit the performance?
When more data is inserted in table which leads to index rebuilding? 3) Table variable is said to perform good on low data while Temporary table is good for
Large dataset. If in my sproc, I have both large and small dataset, will using both
Table variable and temporary table enhance performance?? 4) Can I create non-clustered index on temporary table?? 5) When I design the DB, I can follow two concepts create primary key only in my table
Or can create identity and then clustered index on the column??
Which approach is better and why??
Rohit Kochar

Did the interview go well? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a> Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

hey that was so funny Kalis <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> ….. I did not went for any interview..I was tuning a big sproc where Temp tables were heavily used …That is why i want to clear the concept before applying in real time…. Hope to get some good replies now.. Rohit Kochar

1 Make use of sql server help file 2 Learn SQL http://www.sql-tutorial.net/</a> http://www.firstsql.com/tutor.htm</a> http://www.w3schools.com/sql/default.asp</a> 3 and 4 – Refer 1 and 2 [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Thanks Madhivanan for your reply and links .
But these are very conceptual qs…u wont get this on normal Sql tutorials as
I have gome through many of them…I just wanted to know if any one of
you came across such situation..If yes how did u solve it ??
just want to know ur technical views..
Rohit Kochar

Well. without giving more informations you asked five questions
Post the SP code Madhivanan Failing to plan is Planning to fail

It is not possible to post sproc code here…as it is very very long…
I just want the concept and i hope the questions are pretty clear.. Rohit Kochar
MCP .Net(Web and SqlServer)
Company : TCS ,Noida

quote:Originally posted by rohitkochar
1) I am running query select * from <sometable>. Table has a clustered index and non clustered index. Will running above query result in table scanning or index scanning??? When I saw execution plan it was scanning of clustered index??
Why it must be table scan as i think??

The clustered index IS the table. So, a clustered index scan is essentially a table scan with another name.

quote:
2) We have a one primary key in a table #%92A#%92 and table â€˜B#%92 with composite primary key consisting of three keys . Which one will be more efficient in terms of searching ??? Won#%92t composite primary key hit the performance?
When more data is inserted in table which leads to index rebuilding?

Which one will be more efficient depends on the queries run against these tables. Impossible to say without knowing your environment. And yes, a composite PRIMARY KEY will hit performance as it will blow up the size of every other table in which these data needs to be stored as FOREIGN KEY.

quote:
3) Table variable is said to perform good on low data while Temporary table is good for
Large dataset. If in my sproc, I have both large and small dataset, will using both
Table variable and temporary table enhance performance??

Broad question, broad answer: It depends…

quote:
4) Can I create non-clustered index on temporary table??

Have you tried?

quote:
5) When I design the DB, I can follow two concepts create primary key only in my table
Or can create identity and then clustered index on the column??
Which approach is better and why??

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=21767 —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de

Who locked this thread?
—
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de

Not me, unlocked it anyway and moving to appropriate section. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Thanks kallis…
I tried creating a NonClstrdIndex on Temp table and it dint throw any error….
But how can I be sure that this NonClstrdIndex is used in the temp table as
you mite be knowing that i cant check the performance of Temp table
through Execution plan…any way u suggest to check the performance of
Temp table..Also i dint get any article on inetrnet which uses Nonclstred index
On Temp table .
Rohit Kochar
MCP .Net(Web and SqlServer)
Company : TCS ,Noida

What?!? <pre id="code"> CREATE TABLE #t ( c1 INT ) GO CREATE INDEX IX_t ON #t(c1) GO INSERT INTO #t SELECT 1 SELECT * FROM #t WHERE c1 > 0 DROP TABLE #t </pre id="code"> Check the execution plan. I bet you will see the IX_t index is used. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] If you need to enforce the use of an index, for whatever reason there might be, change the SELECT to: <pre id="code"> FROM #t WITH (index (IX_t)) </pre id="code"> The reason why you haven’t found any article on nonclustered indices and temp tables might be, that it is questionable if such an index makes sense at all on a temp table. Typically with a PRIMARY KEY and, unless otherwise declared, the associated clustered index you benefit "more". – Frank Kalis Microsoft SQL Server MVP Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a> Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

3
http://support.microsoft.com/default.aspx?scid=kb;en-us;305977
Madhivanan Failing to plan is Planning to fail

On the point (4): I would suggest create index on the necessary columns only rather than using all the columns and all the data which will not make sense of using temporary tables. Use indexes on temporary tables and for large temporary tables consider using clustered and non-clustered indexes on temporary tables. Don’t foreget to delete them after the usage to free up the tempdb resources. Yes, I agree that temporary tables are deleted when connection is ended. but do not wait until such time. http://www.sql-server-performance.com/best_sql_server_performance_tips.asp fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

While I agree that you should explicitely clean up after usage, it is actually not strictly necessary as the temp table (and therefore also the associated index) will be removed by SQL Server when the session end, afaik. —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de

On a large scale of temp. tables usage and indexes my experience says drop as soon as the query finishes and not until the while connection is terminated by the process. They are better than using composite indexes. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Yes, surely it is better to control when what is done. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

]]>