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=’
![Wink ;) ;)](styles/default/xenforo/smilies/wink.png)
hey that was so funny Kalis <img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
1 Make use of sql server help file<br />2 Learn SQL<br /<a target="_blank" href=http://www.sql-tutorial.net/>http://www.sql-tutorial.net/</a> <br /<a target="_blank" href=http://www.firstsql.com/tutor.htm>http://www.firstsql.com/tutor.htm</a> <br /<a target="_blank" href=http://www.w3schools.com/sql/default.asp>http://www.w3schools.com/sql/default.asp</a> <br /><br />3 and 4 – Refer 1 and 2 [<img src=’/community/emoticons/emotion-5.gif’ alt=’
![Wink ;) ;)](styles/default/xenforo/smilies/wink.png)
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.
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??
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.
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?
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…
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??
quote:
4) Can I create non-clustered index on temporary table??
Have you tried?
4) Can I create non-clustered index on temporary table??
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
—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??
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?!?<br /><pre id="code"><font face="courier" size="2" id="code"><br />CREATE TABLE #t<br />(<br /> c1 INT<br />)<br />GO<br />CREATE INDEX IX_t ON #t(c1)<br />GO<br /><br />INSERT INTO #t SELECT 1<br />SELECT *<br /> FROM #t<br /> WHERE c1 > 0<br /><br />DROP TABLE #t<br /></font id="code"></pre id="code"><br />Check the execution plan. I bet you will see the IX_t index is used. [<img src=’/community/emoticons/emotion-5.gif’ alt=’
![Wink ;) ;)](styles/default/xenforo/smilies/wink.png)
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=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
]]>