Saved Bookmarks
| 1. |
How To Create Temporary Table? How Do We Apply Noncluster Index? What Is Nolock? When And Where Is Nolock Applied Normally? |
|
Answer» Two ways of creating temporary table with non clusterindex applied on it. Also example shows how to apply "nolock". nolock is NORMALLY applied while querying on production servers. This would MAKE the records being queried sharable on the table. ie, will not prevent other queries from querying the same record parallely on same table. The RISK will be nolock might return junk data some times because the select query might be querying the table while some other insertion or updation commands are performed on the table. CREATE TABLE #tmpTable ( OfficeName varchar(50) , officeid int , CustID int , AgentID int , mlsid varchar(4) , RequestMoreDetails int null , Emails int null ) CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #DW_AllListings(AgentID) select OfficeName , officeid , o.CustID , AgentID - , o.mlsid , PrintBrochure_Views = null , RequestMoreDetails = null , Emails = null into #ForOffices from #Offices LEFT JOIN dbo.planparts WITH (NOLOCK) ON bppa.officeid = o.RID CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #ForOffices(AgentID)Two ways of creating temporary table with non clusterindex applied on it. Also example shows how to apply "nolock". nolock is normally applied while querying on production servers. This would make the records being queried sharable on the table. ie, will not prevent other queries from querying the same record parallely on same table. The risk will be nolock might return junk data some times because the select query might be querying the table while some other insertion or updation commands are performed on the table. |
|