Looking for a fast and easy tip to speed up your SQL queries then look no further. Using a conditional AND in your join will improve performance rather than putting it in the traditional WHERE clause. Let me explain by using a simple book order example.
So I ran into a little problem with some of my SQL between statements this last week. The new year was causing no data to be shown in some data grids and graphs but only if the between statement included data from 2008. Here is my old between statement and after I will show the fix. It is pretty simple but maybe it will be helpful to someone else.
DOES NOT WORK!!!
| WHERE CONVERT(VARCHAR(10), date, 101) BETWEEN CONVERT(VARCHAR(10), #DateField1#, 101) AND CONVERT(VARCHAR(10), #DateField2#, 101) |
NEW BETWEEN STATEMENT THAT WORKS
| WHERE date BETWEEN CONVERT(VARCHAR(10), #DateField1#, 101) AND CONVERT(VARCHAR(10), #DateField2#, 101) |
For some reason the first CONVERT statement did not like having the range between two different years. Hope this saves someone a couple minutes ;)
MySQL has a really helpful LIMIT feature that allows you to return a specific amount of rows from a query.
SQL Server 2005 doesn't have the LIMIT clause, but there are a few other functions that can be used instead.
First there's the SET ROW COUNT which allows you to specify the number of rows to return. The code below will return 7 Records:
"font-size: 10pt;">SET ROWCOUNT 7
SELECT *
FROM tblMyTable
ORDER BY MyDate
One of my colleagues and I have been trying to find out exactly what the difference between using ISNULL (or NVL for you Oracle people) and (var) = NULL in our WHERE clause.
For example:
SELECT a
FROM tblB
WHERE a = NULL
Just when you feel you have learned all the basics you come across something so simple. Copy a table and its contents from one database to another is as simple as the code below:
| SELECT * INTO database2.dbo.tblTest FROM database1.dbo.tblTest |
Note that this is done on the same sql server and that it does not copy indexes. Anything else I should know?
I needed to print a large group of customer Orders by date in .pdf format. Instead of having an employee individually click and sort 2000 .pdf's each day, (Yes they were doing that) I set up a little web application to do all the sorting for them. I first needed to get an OCR software that could read characters off of large .pdf file, split the .pdf into individual .pdfs by customer order number and store specific data into a database.
I was posed with the task of making little dashboards for various managers lately. One of the things that every dashboard needed was a date range that was dynamically driven through date pickers.
I first setup my coldfusion component .cfc to return a query. I then set up 2 date arguments to be used in my between statement to narrow my query results. An example function can be found below:
<cffunction name="dateRange" access="remote" returntype="query">
<cfargument name="DateField1" required="true" type="date">
<cfargument name="DateField2" required="true" type="date">
<cfquery name="revReport" datasource="dsnScoreCard">
SELECT column1, column2
FROM test
WHERE date1 BETWEEN CONVERT(VARCHAR(10), <cfqueryparam cfsqltype="cf_sql_date" value="#DateField1#">, 101) AND CONVERT(VARCHAR(10), <cfqueryparam cfsqltype="cf_sql_date" value="#DateField2#">, 101)
</cfquery>
<cfreturn revReport>
</cffunction>
I made a view on SQL SERVER 2005 that queries 2 databases. I then set up a datasource to the database that contains the view. The set up a coldfusion login specific to that database containing the view. When I try to RemoteObject the view in flex 3 I get an error stating that the cfc could not be invoked. Is this because the coldfusion login only has permission to one of the databases? Does anyone have any experience with this problem? Thanks in advance
PS I set up a view that queries only one database with the same datasource and it works no problem..
Recent Comments