|
what diffrence betwwen in two below table
with cteStudent(ID,Name,Family)
AS
(
select ID,Name,Family from tblStudent
)
select * from cteStudent
SELECT ID,Name,Family into #tblTemp FROM tblStudent
select * from #tblTemp
assume that i remove temp table after run two query.
which of this two query is better than another (in performance aspect)
thanks for any help
t
|
|
|
|
|
mehdi.sabet wrote: what diffrence betwwen in two below table
The first will only work on Sql2005+, since that's when the Common Table Expression was introduced.
mehdi.sabet wrote: assume that i remove temp table after run two query.
which of this two query is better than another (in performance aspect)
What makes you think there's much difference in terms of speed? 
It's not like there's "duplicate" functionality and programmers having to look for the "most efficient" version of a routine. Your hunting for speed in the wrong place.
Now, to answer the question; the second version is preferred, it'd be unnoticeable faster. A good reason would be if you'd need to do multiple mutations on the same set. A good reason to use the CTE would be recursion.
Good luck.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
On SQLServer you can think of the CTE as an inline view, while the temporary table will be materialized and can be indexed.
mehdi.sabet wrote: which of this two query is better than another (in performance aspect)
In this specific case probably the CTE, because the select is straight forward and directly streamed to output.
While the temp table needs to be materialized, if even just in memory, and selected from memory to the output.
If the CTE/temptable would be used in more than one place and/or the select could benefit from indexing the temptable, the result could be quite different.
On Oracle it's a bit different though. There a CTE can be materialized.
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
As Jörgen Andersson said, cte is faster cause there's no IO on disk, it's just memory.
when you declare an #Temporary, you really create the table on your tempdb, that means that you have to write on disk. For a long range of information, you can say that is not that fast as use an cte.
If i'm not wrong, declare an variable ( Declare @Table Table (column) ) is faster as use an CTE , and less complicate, cause just create the table on Memory. BUT, you got to remember, that this will only use memory, for a long range of information, this mean that will use a lot of ram.
|
|
|
|
|
I need to export images from my SQL database into a .png format. Each image needs to have the client name (stored in db) as the name of the .png file.
I've tried google'ing the issue but there does not seem to be a clear path on how to do this. Is there a utility I can use to do this or do I need to code a process? 
Any help/direction would be greatly appreciated!!
Thanks
Michelle
|
|
|
|
|
I'd go for the PowerShell option mentioned here[^].
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I have data in input file given below -->
1,abc
2,xyz
------- End of data----
Notice i have an empty rows after second row.
I m doing bulk insert with FIELDTERMINATOR = ',',ROWTERMINATOR = '\n'. It works perfectly fine when bulk inserting after removing the empty records. But when there is empty record at end of file, it is throwing error given below and no records are inserted in db. I cannot ask the user to remove empty line at EOF.
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
|
|
|
|
|
searching for solution..... wrote: I cannot ask the user to remove empty line at EOF.
Then you'll have to write something that removes that line for the user. As the example, I'd expect it to be invalid, as an empty row would consist of field-separators and a row-separator. Like this:
1,abc
2,xyz
, An empty line does not equal an empty row. It equals a row without fields.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi
i need get data of checkbox which inside a gridview this grid inside another grid i have used concept called masterdetail row inside it i have placed a control called checkbox now i need to that checked value......... how it is possible please help me with code using asp.net and C#......
Thanks in Advance................
|
|
|
|
|
Please choose the proper forum and post your question once only. I suggest you delete the other two.
Use the best guess
|
|
|
|
|
//for each row of gridview
CheckBox chk;
for (int i = 0; i< GridView1.Rows.Count; i++)
{
chk = (CheckBox)GridView1.Rows[i].Cells[chechboxcolumnIndexInterger].FindControl("checkboxID");
}
|
|
|
|
|
Please help me......
how to upload a iamge file on web server in mvc4 asp.net c#
|
|
|
|
|
SQL Server will return a null date as 01/01/1900 if one castes a null string to a date.
Now the issue is that someone had the idea of populating dates with 01/01/1900 to signify a certain stage with a row(meaning that the date is not known but it is known that that stage has been reached).
I only realised yesterday that this could cause an issue if a query is written where a null string is compared to one of these dates.
Does anyone else have experience of this and any suggestions?
I am currently thinking of changing these 01/01/1900 dates to 01/01/1901 to remedy this potential problem.
Thanks
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Which version of SQL Server? I've just tried with SQL 2008, and casting a varchar containing Null to either datetime, smalldatetime, datetime2(0) or date returns Null in all cases.
http://www.sqlfiddle.com/#!3/d41d8/13023[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello, this is not SQL Server Issue.
This is related to the Connection Type you ar using (ADO.NET, OLE DB, ODBC...).
for example:
If you are using MS Access you will see:
[Null] dates returns/saves [01/01/1900]
[Bit] field returns [-1] or [1]
Just a moment ago a friend asked me about the same problem, he is developing on Visual Studio .NET 2008 SP1 (I really did not checked his code, but he resolved his problem).
Hope this help.
Best regards.
|
|
|
|
|
MS Access might do that but I seriously doubt SQL Server does.
If it is null in the database then a null is returned to .Net. No manner of casting/manipulation at that point in time is going to change that to something that isn't a null.
|
|
|
|
|
I'd suggest adding a bit-column called "HasDate".
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
GuyThiebaut wrote: SQL Server will return a null date as 01/01/1900 if one castes a null string to a date.
I seriously doubt that.
As a guess the data is actually an empty string.
|
|
|
|
|
jschell wrote: the data is actually an empty string
Yes, that makes sense:
http://www.sqlfiddle.com/#!3/d41d8/13027[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks.
I will have to check this with regards to the data I have been looking at - however I think you have hit the proverbial nail on the head.
I think I will go with changing the dates to something other than 01/01/1900 as I can see someone in the future doing an isnull comparison on an empty string and getting a match against these dates.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
GuyThiebaut wrote: as I can see someone in the future doing an isnull comparison on an
empty string and getting a match against these dates.
I don't understand that statement so just to be sure I will state that an empty string is not the same as a null value.
|
|
|
|
|
hi to all
i have a while loop like this
declare @Counter int
while(@Counter<1000)
begin
set @Counter = @Counter + 1
select * from mytable where [my where clause]
end
my where cluase change in each repeat.
i want to union all result to a table and return one table as query result
how can i do this ?
performance is very important for me
because while loop can be run for upper 15000
thanks for any help
|
|
|
|
|
mehdi.sabet wrote: my where cluase change in each repeat.
Can you post your where clause? It can probably be replaced with a single query, which will give you much better performance.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
thanks for reply
my main code is:
ALTER PROCEDURE [dbo].[lfd__GetFullPersonnelDetails]
(
@PersonnelList nvarchar(MAX),
@WorkPeriodID int,
@WorkPeriodYear int
) AS
Declare @startDate SMALLDATETIME
Declare @endDate SMALLDATETIME
Declare @PersonnelBaseID INT
WHILE(LEN(@PersonnelList) > 0)
BEGIN
SET @PersonnelBaseID = SUBSTRING(@PersonnelList, 0, PATINDEX('%,%',@PersonnelList))
IF(@PersonnelBaseID != 0)
SET @PersonnelList = SUBSTRING(@PersonnelList, LEN(CAST (@PersonnelBaseID AS NVARCHAR) + ',') + 1, LEN(@PersonnelList))
ELSE
BEGIN
SET @PersonnelBaseID=CAST(@PersonnelList AS INT)
SET @PersonnelList = SUBSTRING(@PersonnelList, LEN(@PersonnelList + ',') + 1, LEN(@PersonnelList))
END
set @startDate=(select top 1 [date] as startdate
from
lfd_DailyStatistics
where
PersonnelBaseID = @PersonnelBaseID and [Year] = @WorkPeriodYear and WorkingPeriodID = @WorkPeriodID
order by date asc)
set @endDate=(select top 1 [date] as startdate
from
lfd_DailyStatistics
where
PersonnelBaseID = @PersonnelBaseID and [Year] = @WorkPeriodYear and WorkingPeriodID = @WorkPeriodID
order by date desc)
SELECT
dailyAbsences.*,
CAST(CASE WHEN calculationQueue.[StartDate] IS NULL THEN 0 ELSE 1 END AS bit) AS [NeedsRecalculation]
FROM
lfd_dailyAbsence dailyAbsences LEFT JOIN
lfd_CalculationQueue calculationQueue ON dailyAbsences.[PersonnelBaseID] = calculationQueue.[PersonnelBaseID] AND calculationQueue.[StartDate] <= @endDate
WHERE
dailyAbsences.[PersonnelBaseID] = @PersonnelBaseID AND
dailyAbsences.[StartDate] >= @startDate AND
dailyAbsences.[EndDate] <= DATEADD(day, 1, @endDate)
ORDER BY
dailyAbsences.[StartDate]
END
start date and end date change in each repeat
|
|
|
|
|
Little tip - get rid of the order by clause - as it is taking up unnecessary processing time.
You can order the results once you have combined them.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
