WayneS

Forum Replies Created

Viewing 15 posts - 4,471 through 4,485 (of 5,588 total)

RE: Comparing Table Variables with Temporary Tables

matt32 (4/9/2010)
Thx Wayne for the link to Paul Randall.
@paul-2
I had this issue some month ago with a customers 2005 db.
heavy data load using a SP with select .. into #temp....
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

April 9, 2010 at 9:01 am

#1149198
RE: Comparing Table Variables with Temporary Tables

Matthias, I've never seen this before. Very interesting.
If you're interested, I just found a way to work around this.
select top 1
CONVERT(int, 1) as nr,
name as colname into #tmptable
from sys.all_columns
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

April 9, 2010 at 8:55 am

#1149183
RE: Comparing Table Variables with Temporary Tables

matt32 (4/9/2010)
2. Creating a #temptable as select expression,.. into #temptable will generate a not null constraint on the expression column.
Has anyone ever heard of this before?
I tried to duplicate it...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

April 9, 2010 at 8:28 am

#1149154
RE: Comparing Table Variables with Temporary Tables

Paul White NZ (4/9/2010)
BTW Wayne, did this article change much between publications? It seems more comprehensive than previously - or is my memory playing tricks again?
Paul
No changes at all....
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

April 9, 2010 at 8:12 am

#1149132
RE: Are the posted questions getting worse?

Luke L (4/9/2010)
jcrawf02 (4/9/2010)
I need some inspiration, what do you guys do when you start to lose focus and don't have the time to go get the crap kicked out...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

April 9, 2010 at 7:46 am

#1149100
RE: Comparing Table Variables with Temporary Tables

matt32 (4/9/2010)
Hi Wayne ,
great article and here some problems i encountered while working with
#temp for further articles with the same focus.
1. Creating a #temptable within a SP as select...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

April 9, 2010 at 7:28 am

#1149080
RE: Comparing Table Variables with Temporary Tables

Knight (4/9/2010)
Great article
Thanks
I shall ensure part of this article goes into our best practices just to hammer the point home 🙂
:blush: Thanks again!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

April 9, 2010 at 4:33 am

#1148940
RE: Comparing Table Variables with Temporary Tables

Gianluca Sartori (4/9/2010)
Thanks for clarifying Wayne. So, it works the same as permanent tables: I was fearing that a different behaviour could apply.
I wrote that because I use indexes on...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

April 9, 2010 at 4:31 am

#1148939
RE: How to calculate ranks or position?

COldCoffee (4/8/2010)
I think nguyennd has got the code you wanted...
Cheers!!
I agree. Good job.
Keep in mind the difference between RANK() and DENSE_RANK() if it is possible to have a tie.
Nuts, do...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

April 9, 2010 at 4:19 am

#1148932
RE: How is this done ... or how would you do this?

sku370870 (4/9/2010)
In the first situation my Where clause would be ...
Where Postcode1ID = @Postcode1ID and Postcode2ID = @Postcode2ID
In the second situation my Where clause would be ..
Where (Postcode1ID = @Postcode1ID...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

April 9, 2010 at 4:01 am

#1148915
RE: Comparing Table Variables with Temporary Tables

Gianluca Sartori (4/9/2010)
Outstanding article, Wayne. I must have missed it when it was first published. Nice job.
Your article raised a doubt in my mind. You write
Temporary tables can have named...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

April 9, 2010 at 3:44 am

#1148903
RE: Update a column but only part of the text

TRACEY-320982 (4/8/2010)
Worked a treat and replaced it anywhere it found it in the string.
Thank you so much got a hugh update to do.
What does the 1 mean ?
(CHARINDEX('00001', column, 1)
http://www.lmgtfy.com/?q=charindex+sql+server+2005
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

April 8, 2010 at 7:38 pm

#1148746
RE: How to calculate ranks or position?

I think I have a good idea of what you want, and I think that the RANK() function will do what you want, but there are several questions remaining. If...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

April 8, 2010 at 7:34 pm

#1148742
RE: MCJ - The Missing Link

Okay, to try to get some talk going about the review board again. Earlier, I had said
Well, to start off, there are several distinct areas to cover:
1. Who qualifies to...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

April 8, 2010 at 7:23 pm

#1148741
RE: MCJ - The Missing Link

CirquedeSQLeil (4/5/2010)
One could be any of the following or combination of the following.
MCJ
MCJ with an emphasis in Replication
MCJ with an emphasis in CLR
MCJ BI with an emphasis in SSAS
Due to...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

April 8, 2010 at 7:00 pm

#1148738

Viewing 15 posts - 4,471 through 4,485 (of 5,588 total)