.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

UDF Table and Table Scans in SQL 2008

Posted By:      Posted Date: September 27, 2010    Points: 0   Category :Sql Server
I have a UDF that returns a table. Whenever the UDF is used it always creates a table scan. Is there away to eliminate the table scan for the table returned by a multistatement UDF?
DJ Baby Anne's Biggest Fan................

View Complete Post

More Related Resource Links

Under the Table: Programming with FileStreams in SQL Server 2008


There's always been disagreement about whether large blobs, such as document and multimedia items, should be stored in the database or file system. In SQL Server 2008 you don't have to choose; filestream storage provides the best of both approaches.

Bob Beauchemin

MSDN Magazine May 2009

Under The Table: Spatial Data Support In SQL Server 2008


New spatial data support in SQL Server 2008 opens the door to mapping and querying geometric and geographic data, allowing you to build exciting new applications.

Bob Beauchemin

MSDN Magazine February 2009

PIvot Table in SSRS 2008

Hi All, Is there any data Region (Pivot) in SSRS 2008 or higher version? Our requirement is to show the report like excel pivot gererated from a OLAP Cube where we can select or unselect any dimesion attribute or measures. Thanks, IndiaSQl  

Excel 2007 Pivot table OLAP- SSAS 2008 Error

Hi, I do have a strange issue with Excel 2007 pivot table report; the data source is SSAS 2008 cube. When refresh the excel 2007 report data using the 'Refresh All' or 'Refresh' button available under the Data menu, the below error is thrown. "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples." After some analysis, the report works well on the below workaround, 1. Remove all the calculated measures from report (3 measures in this scenario) 2. Remove one row label (dimension attribute) from report But the measures, attributes are valid one from cube and it is required fields in the report. So, understand that removing few items is not at all a solution. The below workaround also works. 1. Check the 'Defer Layout Update' option from the Pivot Table Field List window. 2. Click on the Update button After that Unchecked the 'Defer Layout Update' option and apply the filter, etc in the report. The report works very well. But every time, we need to use the above work around after open the excel report. I have NO clue on this issue. Please help to resolve this issue. Thanks, Jey


hi I Have deleted my sql table data (delete from employee) but this was a fault.i was suppose to delete data from other table what should i do to get my data back i have used (ROLLBACK select * from employee) to get my data back but it didnt help me.my DataBase recovery mode is also set to FUll ,still i am not able to get back my data Plaese Help me what query should i run?

sql server 2008 Table Partition

HI i want to implement partitioning in my sql server 2008 database table http://blogs.msdn.com/b/manisblog/archive/2009/01/18/easy-table-partitions-with-sql-server-2008.aspx   i have used this link. but when i try this link then i am able to create only primary file group no secondary file group..so help me also by fault i have created partition in wrong table .so i want to detete tha partition from that table but of i delete the PARTITION SCHEME and PARTITION FUNCTION THEN it throws error..so what shoul i do to remove partition from my current table

sql server 2008 Table Partition

HI http://blogs.msdn.com/b/manisblog/archive/2009/01/18/easy-table-partitions-with-sql-server-2008.aspx  i have used  above link to create partition,but my problem is i want to do partition on daily basic, one column in my table is date(by default has getdate()),on the base of this i want to do partition, eg.in this link i need to give time duration between which one can do partition, and also either daily or monthly or quarterly or yearly need to be specify ..if i give date between 01/08/2010 to 01/08/2012 and then select daily wise partition which i am in need then table create more than 700 partion but problem comes when i need to assign group to them how cud i select Primary file group more than 700 times to assign it to each partiotion.it becomes very lengthy process  Please help me

Partitioning a table in SQL Server 2008

Our application has got few tables which has grown to a larger size. Hence one suggestion put forward is to consider table partitioning. Since this does not involve any code changes in the application, we are looking into this option. 1. Is there any disadvantage if we use table partitioning feature 2. Will table partitioning affect the current indexes that exist on the table? 3. Database mirroring is enabled in our schema. Will partitioning have any effect on this? 4. Any suggestion on how we should try this out?  

How to Add "Modified On" to my Table and View Listings (SQL 2008)

There is a "Create Date" - but I want to know when the last "Modify" date is in both Tables and Views.  is this even doable?  I think it was in SQL 2005RICHARDG

Relate table using SSMS 2008 Express

Hello,How to add relationship between tables using SSMS 2008 Express?Thank You,-imperialx

Partitoning Table in SQL 2008 - Index

1. How is the index mainitained for a partitioned table?  2. Whether SQL Server has an overhead in maintaining INDEX in a partitioned table compared to a non-partitioned one? 3. If i insert few rows, does it affect the INDEX statistics in all partitions or only the affected partition. I would like to find out more details on how SQL Server manages INDEX on a partitioned table.

SSRS 2008 - Fixed headers while scrolling - works fine for Matrix - but not for Table??!!


I have this feature working fine for matrix, but it does not work as expected for Table (Tablix).

I tried all over, and setting both check boxes in the 'Tablix Properties' dialog page 'Repeat header rows on each page' and 'Header should remain visible while scrolling' for both row header and column header, but none of it is working. Am I missing something?

In the properties table (F4) for text box of tablix, under 'Other' there is 'RepeatWith' - none / tablix: what is this used for? I tried this property also but I cannot get the headers fixed / visible while scrolling. I cannot get the 'column headers' repeat on each page of the report. Very frustrating...

Does anyone face similar issue? Can anyone help please? Do I need any update / download any fix? For now I have the following version installed for Visual Studio 2k8:
Microsoft SQL Server Reporting Services Designers Version 10.0.1600.22


SSAS 2008 R2 Cube being queried by Excel 2010 Pivot Table - 60x slower than with Excel 2003!


SQLServer 2008 R2

Excel 2010 x64

Hi All,

I have a fairly complex Excel report where a raw data sheet is updated from 10 or so PivotTables pulling from an Analysis Services Cube, (2008 R2), with a VBA script that refreshes all pivottables, and then does some post-processing, formatting etc.

This was originally developed in Excel 2003, and the updating of all the pivot tables ran in under 10 seconds

I've now recreated in Excel 2010 and *each table* is taking up to a minute to refresh!

I've also observed that I can get the refresh time of a single table back down to 1sec, if I delete all other pivottables from the worksheet/book.

Its deeply frustrating, as I'm touting 2010 as being the way for our business to go...it won't help the cause if I deliver something with a x60 increase in runtime!

I've run traces of both the 2003 and 2010 versions executing against the SQL Server, the only thing I can see is that the 2003 version passes 1 single MDX query, whilst the 2010 version appears to be firing it in sections.

I've posted this in the Excel forum too.

Please can anyone help?

Many thanks,





Find '' in a table in MS SQL SERVER 2008



I need to find white spaces in my table. the table contains 35 columns, therefore, I cannot check with AND or OR operator something like this

SELECT * FROM MS_Exchange_IS WHERE (TMZDIFF != '' AND Timestamp != ''..........)

Also I don't want to use cursors as it will greatly hamper the performance of my application.

Please suggest any other option.


Any link or pointers would be helpful


Thanks in Advance,


Questions on partitioned table keys in SQL 2008


I've been experimenting with the partitioned table feature in SQL 2008 (evaluation version).  Nice work, I can see how the feature would be really useful.  I am investigating it for an application that involves a scrolling window based on a datetime column, so that the table holds one year's worth of data. The datetime is used as an event timestamp, recording it down to the millisecond.  New data is loaded every hour. I would like to keep the impact of the new data load minimal, so I want to take advantage of partition switching when a new hour's worth of data is ready.  Trimming old data would only happen once a day.  My questions/assertions based on experimenting a little with this and reading what I could find on the subject:

1) Both the partition function and the left-most column of the clustered index should be based on the datetime column value. This takes maximum advantage of the datetime range terms that are in every query generated by the UI  (show me events of this type in the last week) and also allows partition switching for adding new records and trimming old ones.

2)  It doesn't make sense to keep an identity column on the partitioned table, despite their convenience as a surrogate key for many editing tools.  You would have to add the datetime column to an identity column in a primary key or unique c

Cannot Export SQL 2008 Table to Excel 2010


I'm trying to use SSIS in Visual Studio 2008 to export a table to Excel 2010 and get the following error:


The table cannot be created. (Microsoft Visual Studio)


Syntax error in field definition. (Microsoft Office Access Database Engine)

Program Location:

   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
   at Microsoft.DataTransformationServices.Design.PipelineUtils.ExecuteSqlStatement(ConnectionManager connectionManager, String sqlStatement, IServiceProvider serviceProvider, Boolean getReader)
   at Microsoft.DataTransformationServices.Design.DtsTableCreator.okButton_Click(Object sender, EventArgs e)

Trouble Using More Than One LIKE Operator in a Table Filter in SSRS 2008


Hello All,

I'm having what I'm hoping will be a simple issue -

My co-worker has created a large report which contains several tables in SSRS 2008.  Each table is tied to one dataset, yet only certain data can be returned to each respective table.  Right now he's applying filters at the table-level behind each table, however as you can imagine this is not efficient because there is a LOT of data returning for this report. 

I'll try and give a simplified example -

There are two tables in the report body - one for containing products relating to both Cats and Dogs, and one for containing products relating to Birds.   Each product starts with the name of the specific animal - for example "Cats-Mouse Toy", "Dogs-Frisbee", "Birds-Rubber_Leaf".  

Now I need to somehow filter out each product so that they go to their proper tables.  As mentioned above, my co-worker was creating a specific filter for each product, but this is proving to be inefficient.  For example, there are 52 different products for Birds alone.  That would mean 52 different filter entries for each product.  What I would like to do is use the LIKE operator but i'm not getting the results I want on the first table.

So, on the first table I need something like:  =(Fields!Products.value LIKE &

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend