.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

Is the 'BETWEEN' function very expensive in SQL Server

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :

3 down vote favorite

I'm trying to join two relatively simple tables together, but my query is experiencing serious hangups. I'm not sure why, but I think it might have something to do with the 'between' function. My first table looks something like this (with a lot of other columns, but this would be the only column I'm pulling):


My second table "groups" my rows into "blocks", and has the following schema:

BlockID RowNumberStart RowNumberStop
1 1 3
2 4 7
3 8 8

The desired result I'm looking to get is to link the RowNumber with the BlockID like below, with the same number of rows with the first table. So the result would look like this:

RowNumber BlockID
1 1
2 1
3 1
4 2
5 2
6 2
7 2
8 3

In order to get that, I used the following query, writing the results into a temp table:

select A.RowNumber, B.BlockID
from TABLE_1 A left join TABLE_2 B
on A.RowNum

View Complete Post

More Related Resource Links

connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly.


I dont have the SQL EXPRESS installed instead I have SQL Standard Edition.

 I have two SQL Server instances installed.

 1- UserLT (this is sql 2000)
2- UserLT\SQL2005 (this is SQL 2005 named instance)

But when i try to add a database to my VS website project I get the following error:

Connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=4925

I went in Tools>Opetions>DataBase tools>Data Connection>Sql Server Instance Name (blank for default)

and changed the "SQLEXPRESS" to "USERLT\SQL2005".

But I still get the same error message. Any ideas how i can resolve this issue?

Creating a table-valued function on a user defined type in SQL Server CLR

We would like to be able to create a table-valued function on a user defined type.  We would like the syntax in SQL to look like it works for the XML nodes function:   DECLARE @myXml XML = '<a><b>1</b><b>2</b><b>3</b></a>' ; SELECT  node.query('text()') FROM    @myXml.nodes('a/b') nodes (node) ;   In other words, in the FROM clause, we can access the "nodes" method of the xml variable and it returns a rowset. Thanks!

Implementing Cumulative Beta Distribution function in Sql server 2005.

Hi,   I am trying to implement BETADIST function which is present in Excel through Sql Server Integration Services(SSIS). Can any body help me on this.   Thanks in advance, Prasad Palaparthy.

Performance perspective of Lookup function in SQL Server 2008 R2

Hi, Recently I had presented a session on SSRS 2008 R2 enhancements. As part of that I explained about LOOKUP function and its way of working. There was a question while presenting this feature. From performance perspective, what are the advantages of LOOKUP function over conventional implementation of JOINING the lookup tables in stored procedure itself. I tried explaining that, it enables to make best utilization of resources in scaleout environment because the logic of JOIN evaluation has been shifted to Report Server than a database server. Some how the participants were not convinced. I would request experts to comment on this and help me in understanding whether my evaluation is correct or is there anything I am missing over here during my explanation. Please help.Phani Note: Please mark the post as answered if it answers your question.

calculating age using sql server function

Hi ya, I have a table which will be having DOB and I want to calculate the current age of persons, i want to save it into currentage as default. I have searched over the interent and find some solutions but in my database the DOB is stored as = 'dd/mm/yyyy' and none of the solutions work on this. Any ideas?

Calling a Server side function from Javascript without using PageMethods

Good Day alli have used PageMethods to access the server side functions in javascript , but now my problem is that with pagemethods your controls will not go throught the full page lifecyle and these leads me to have "Object not set for an instance of an object" error. Now can someone come with a differnt approach without using pagemethods. What i want is to call a server side function , i dont want to pass any parameters to itthanks

Does SQL Server has the similar function just like Trace Event 10104 for hash join in Oracle?


My question is  how can SQL server display the  information about the buckets when doing the hash join just like the function of Trace Event 10104 in Oracle.By the way ,In Oracle ,the Event 10104 event dumps hash join statistics .
The buckets message what i needed is similar as the following,that is to say ,it must contains the information about buckets .
01 ### Hash table ### 
02 # NOTE: The calculated number of rows in non-empty buckets may be smaller 
03 # than the true number. 
04 Number of buckets with 0 rows: 16373 
05 Number of buckets with 1 rows: 0 
06 Number of buckets with 2 rows: 0 
07 Number of buckets with 3 rows: 1

Does Word Automation Services require Office 2010 install on server to function?


Subject is the question.


My requirements are to "maintain existing functionality with current version of a windows service running on Server 2003 and automating Word through COM."   I.E. All the stuff that breaks when you upgrade the OS to Server 2008.  

My customer has already rejected several third party alternatives as "not being accurate enough"  The rendered PDF doesn't match what comes out of Word when compared with an electron microscope.

So now I'm investigating using Word Automation Services and Sharepoint 2010.   Got my development server functional with Sharepoint 2010 and Visual Studio 2010.  But my first attempts at creating rendering jobs is failing with an "ArgumentNullException" in deep inside a method I'm 100.0% sure I'm NOT passing any NULL's to. 

My VM is low on space so I figure I'd save time by asking for a conclusive answer before I make room / install Office.

Server Side Code Not Firing After Javascript Function


I have a group of buttons and a text box which I have been using for paging. The user is able to type a number into the text box and either click the "Go" button or hit "Enter" for the paging to occur. To have the "Enter" button work I had to turn the "UseSubmitBehavior" property to false for all of the buttons except the "Go" button.

Now I am trying to add a javascript function that shows a confirm box when the user clicks on a paging button. If the function returns true, the client side code should run. If the function returns false then nothing else should run. I have been successful with getting this to work for the "Go" button but it won't work for any of the other paging buttons because I have "UseSubmitBehavior" set to false. Can anyone think of a way to get this to work with the other buttons?

Thanks in advance for the help.

<asp:Button ID="btnFirstPage" runat="server"  Text="<<"  OnClick="PagerClicked" OnClientClick="return unsavedChanges();" UseSubmitBehavior="false" />
<asp:Button ID="btnPreviousPage" runat="server"  Text="<"  OnClick="PagerClicked" OnClientClick="return unsavedChanges();" UseSubmitBehavior="

Problem with ISNumeric function in SQL Server 2005


Even though the system function IsNumeric('2,344') returns a true in SQL Server 2005, why does the following throw an error? Is there a special way to cast to numeric types in SQL Server 2005, so the statement below does not throw an error?

          WHEN 1 THEN CAST('2,344' AS INT)
          ELSE NULL

connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly



i've installed SQL server 2005 express and Visual web developper 2005 express.

when i whant to create a database in VWD the following error occures:

connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=49251

i searched the internet but can't find the solution, i already reinstalled my complete workstation but the problem stays.

please help!

Passing variable into function in SQL Server from Access



I am trying to call a function in SQL Server 2005 from Access 2003.  It works fine with the constant value, but fails with a variable of the same value.  This works:

strSQL = "SELECT * FROM functionName (constantValue)"
rs.Open strSQL, cn 

But when I use variable in the parameter, I get error.  I have tried creating parameter objects in Access, but couldn't execute it and don't know if this is a way to go anyway.

Would appreciate for any help or suggestion.


Thank you

Calling a javascript function from server side, using AJAX timer



I am developing a ASP.NET ajax application.
I am using a timer:

<asp:Timer ID="Timer1" runat="server" OnTick="Timer_Tick" Interval="5000" />      

The only thing I want to do is to call a javascript function on every tick of the timer:

 protected void Timer_Tick(object sender, EventArgs e)
            ClientScript.RegisterClientScriptBlock(this.GetType(), "blink", "<script>blinkit('2');</script>");

This doesn't work.
The javascript function is being called only if I put the 'RegisterClientScriptBlock' in the Page_Load.

How can I call the javascript every couple of seconds?

Thank you

how to use table valued function with join on table in server 2000


Hi please any oone tell me how to call a table valued function


select * from get_date_time_from_gmt ('+17:45','1879-01-02 00:00:00.000','17:30')  when i call it then it will work properly. but now i have to use columns from cricket_master  

as mention below



* from dbo.get_date_time_from_gmt

Call a SQL Server function(cleans html tags) on a derived column in SSIS


I have a task to move table having "text" data type for a column from SQL Server to CSV file so while transfering the data using SSIS I want to perform Cleanup on this column such as removing HTML tags, White spaces etc and then transfer it. Note: I dont have any option to store the table in the staging database as I will be running it on the production server directly.

Can any one help me accomplish this task???





Function In SQL Server to Convert from CST to GMT



Can any one tell me how to convert a date which is in this format "2000-11-27 11:24:00" ....the dates are in CST format..I need to convert them to GMT.

I hurd that in SQL we have a direct function which converts from CST to GMT ....

I need to implement this function in the derived column...while get the data from source to target

can any one help me out

Thanks in advance

Error "Could not find server 'System' in sys.servers" with non-context connection in CLR function


When trying to open a non-context connection in a CLR scalar valued function I am receiving the Exception:

Could not find server 'System' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers

I encounter this problem on two different SQL Server 2005 servers. The error seems strange to me since I am not using linked servers.

Here is an extract from my function:



class LimitSystemFunctions
  public static<

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