WayneS

Forum Replies Created

Viewing 15 posts - 1,921 through 1,935 (of 5,588 total)

RE: Creating a comma-separated list (SQL Spackle)

Jeff Moden (1/12/2011)
Very cool! Wayne writes an excellent article, Mark provides an excellent performance enhancement in the discussion, and Wayne tests it for everyone. It doesn't get much...
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

January 12, 2011 at 6:48 pm

#1272757
RE: Are the posted questions getting worse?

Stefan Krzywicki (1/12/2011)
I'm just outside Boston, we're certainly snowed in here.
How much did you get?
Grant / Adam - how much did you'll get?
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

January 12, 2011 at 5:14 pm

#1272741
RE: PIVOT / CROSSTAB Question

Double-posted. Direct all responses to this thread where it is already answered
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

January 12, 2011 at 5:06 pm

#1272738
RE: T-SQL Question

SELECT ID,
[64] = MAX(CASE WHEN UnknownColumnName = 64 THEN AnotherUnknownColumnName ELSE NULL END),
[65] = MAX(CASE WHEN...
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

January 12, 2011 at 4:54 pm

#1272731
RE: Creating a comma-separated list (SQL Spackle)

Tom Bakerman (1/12/2011)
Wayne,
Can you provide more explanation on this part of the query:
TYPE).value('.','varchar(max)')
Tom,
Put an Ampersand (&), Less than (<) or Greater than (>) in your data, and see...
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

January 12, 2011 at 1:17 pm

#1272600
RE: How to determine first valid enrollment date

jwmott (1/12/2011)
Thanks. It looks like it does exactly what I need. I would like to learn more about how this works. Got any good reference sources to recommend?
I'm...
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

January 12, 2011 at 10:57 am

#1272501
RE: need to get 1 row/record

How's this?
-- See how this starts off with a table and data in it?
-- If you had provided us the data in this format,
-- it would have made...
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

January 12, 2011 at 10:34 am

#1272489
RE: Creating an orphaned user

If it's a sql login, script out the login from the secondary server, and apply it to the primary. Then build your user, and delete the login from the primary.
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

January 12, 2011 at 10:19 am

#1272478
RE: Are the posted questions getting worse?

Grant Fritchey (1/12/2011)
Anyone interested in reading a short blog post? I'm a little nervous about putting this one up. I'm talking about how heaps are stored and I've made mistakes...
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

January 12, 2011 at 10:15 am

#1272476
RE: Creating a comma-separated list (SQL Spackle)

Matt, Jason - thanks!
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

January 12, 2011 at 10:11 am

#1272470
RE: Creating a comma-separated list (SQL Spackle)

JJ B (1/12/2011)
I've been using something similar to your query for a while now, but I hadn't known about the option to convert to varchar(max) and get rid of the...
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

January 12, 2011 at 10:10 am

#1272469
RE: Help with using wildcards

itsjustme (1/12/2011)
either way works, I tried with move option and created a sql agent job to automate the backup\restore jobs.
Cool, thanks!
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

January 12, 2011 at 9:12 am

#1272439
RE: Help with using wildcards

itsjustme (1/12/2011)
Thank you very much! that really worked... just adding the text in single quotes and defining '.mdf' and '.ldf' to variables really worked...
Just so I can learn from this...
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

January 12, 2011 at 8:55 am

#1272423
RE: Are the posted questions getting worse?

For those building delimited strings with
FOR XML PATH(''),TYPE).value('.','varchar(max)')
See this post for a tweak that improves performance by a factor of 6 (on my test data... maybe more for you!)
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

January 12, 2011 at 8:31 am

#1272406
RE: Creating a comma-separated list (SQL Spackle)

Mark-101232 (1/12/2011)
Changing
value('.','varchar(max)'),1,1,'')
to
value('(./text())[1]','varchar(max)'),1,1,'')
appears to give a better query plan
Thanks for the information... I'll have to test this out.
Edit: WOW! What a difference! Identical...
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

January 12, 2011 at 8:15 am

#1272398

Viewing 15 posts - 1,921 through 1,935 (of 5,588 total)