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
A friend of mine over at 19Nates made a great post for beginners on how to connect flex 3 to ASP.NET. I would recommend it to anyone that wants to venture to .NET or anyone who just wants to learn some basics.
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 found this script while browsing the other day. I thought it might be useful for anyone needs to upload data into SQL Server from a csv file. The only catch is that you can't have NULL values in your CSV file.
<cffile action="read" file="\\stg-wb-20\ims-STG-WB-20\testSteve\ss.csv" variable="csvfile">
<!--- loop through the CSV-TXT file on line breaks and insert into database --->
<cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#">
<cfquery name="importcsv" datasource="dsnDB1">
INSERT INTO tblCycleCountData (ctCampus,ctDate,ctAISLE,ctLOCATION,ctITEM,ctLPID,ctQTY,_
ctZONE,ctLOCTYPE,ctLPIDPRESENT,ctCOUNTTOTAL)
VALUES
('#listgetAt('#index#',1, ',')#',
'#listgetAt('#index#',2, ',')#',
'#listgetAt('#index#',3, ',')#',
'#listgetAt('#index#',4, ',')#',
'#listgetAt('#index#',5, ',')#',
'#listgetAt('#index#',6, ',')#',
'#listgetAt('#index#',7, ',')#',
'#listgetAt('#index#',8, ',')#',
'#listgetAt('#index#',9, ',')#',
'#listgetAt('#index#',10, ',')#',
'#listgetAt('#index#',11)#')
</cfquery>
</cfloop>
<!--- use a simple database query to check the results of the import - dumping query to screen --->
<cfoutput>Done</cfoutput>
I was trying to format percentages and could not figure out why the number was rounding up to the nearest integer. I did a <cfquery> and did my percentage calculations within a SELECT statement. I then tried <cfoutput> and <cfdump> still showing only integers. I proceeded to try LSNumberFormat and made sure my variables in T-SQL were set to allow decimals. I still had no luck of removing the rounding problem.
I have come across some pages showing different techniques to help avoid looping through queries but none helped out until I came across a little built in function in SQLServer 2005 called PIVOT. I want to now explain to you how I put this new feature to use. I am sure there are other ways out there to accomplish this task but I found PIVOT to be the easiest and fastest available.
Recent Comments