WayneS

Forum Replies Created

Viewing 15 posts - 4,726 through 4,740 (of 5,588 total)

RE: How to enhance query performance

Look at the columns in the WHERE / HAVING clauses, and the JOIN conditions. Usually, your more selective columns would be first in the index.
Also, check the master.sys.dm_db_missing_index_xxx views.
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

February 24, 2010 at 8:40 am

#1124307
RE: Procedure with cursor is much faster that without cursor(With memory table and a while loop).

Joy Smith San (2/24/2010)
Wel, the number of records vary from 5000 to 1.2 million........mostly above a million.
Thanks
If you can get us table definitions, test data and expected results (see 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

February 24, 2010 at 8:34 am

#1124300
RE: Problems with Date Searches

thomas.hill (2/23/2010)
I changed it to:
WHERE DateEnd >= '1/1/2009' and DateEnd <= '1/1/2010'
This means that you will return any DateEnd value in 2009, AND any DateEnd value of "1/1/2010 00:00:00.000". Nope,...
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

February 24, 2010 at 7:04 am

#1124253
RE: Problems with Date Searches

mike 84768 (2/24/2010)
You could also use the Datepart function...returns an integer representation of the year.
WHERE Datepart(yyyy,DateEnd) = 2009
The problem with this approach is that you won't be able to use...
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

February 24, 2010 at 7:00 am

#1124248
RE: Need Help with Query

You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put...
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

February 24, 2010 at 6:55 am

#1124245
RE: Are the posted questions getting worse?

Paul White (2/24/2010)
RBarryYoung (2/23/2010)
Give it a go, Paul. The solution that we (independently) came up with is pretty darn efficient, so I would be particularly interested to see 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

February 24, 2010 at 6:43 am

#1124239
RE: Are the posted questions getting worse?

RBarryYoung (2/23/2010)
Thats's what the Fair Use doctrine is all about, Wayne. Paid or not, copyright or not, we are still allowed to quote text from sources, within limits. ...
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

February 24, 2010 at 4:40 am

#1124194
RE: Problems with Date Searches

It has often been found to be easier to use a different way than using BETWEEN when working with date ranges:
WHERE DateEnd >= '1/1/2009' and DateEnd < '1/1/2010' will 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

February 23, 2010 at 8:32 pm

#1124045
RE: Are the posted questions getting worse?

Paul White (2/23/2010)
WayneS (2/23/2010)
Steve Jones - Editor (2/23/2010)
Barry, sourced, quoted, limited to < 1 para, fair use.
Steve, while I agree, my question is whether a quote from a paid subscription...
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

February 23, 2010 at 8:15 pm

#1124042
RE: How to import XML data into a table

johnnycash (2/23/2010)
Hello everyone!
I need some assistance and guidance. I have an XML file that I need to bring it into a SQL 2005 table. The problem is that...
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

February 23, 2010 at 5:13 pm

#1123990
RE: While Loop for Inserts from Read-only Function

Nugby,
I'm afraid that I gave you some bad advice.
From BOL:
TABLE
Specifies that the return value of the table-valued function is a table. Only constants and @local_variables can be passed 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

February 23, 2010 at 5:00 pm

#1123987
RE: Are the posted questions getting worse?

Steve Jones - Editor (2/23/2010)
Barry, sourced, quoted, limited to < 1 para, fair use.
Steve,
While I agree, my question is whether a quote from a paid subscription site (as where 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

February 23, 2010 at 4:36 pm

#1123975
RE: "Recent Posts" issue

Steve, this is the message I get:
Sorry your search results have expired within our database. Search results are held for around 30 minutes. You will need to perform your search...
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

February 23, 2010 at 11:17 am

#1123775
RE: Help- Very Complex SQL query

Paul White (2/23/2010)
WayneS (2/23/2010)
Paul White (2/22/2010)
Totally awesome job there Wayne.
Fixed that for you, Paul. 😀
... but seriously, Thanks!
:laugh: funny :laugh:
Payback... just couldn't resist. 😛
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

February 23, 2010 at 2:05 am

#1123478
RE: SQL LOOP - with variables for date inserts to a table

Loops, by their very nature, process things Row-By-Agonizing-Row. SQL is a set-based language, and thus performs best with set-based code.
If you provide the DDL scripts (CREATE TABLE, CREATE INDEX, etc.)...
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

February 23, 2010 at 2:03 am

#1123476

Viewing 15 posts - 4,726 through 4,740 (of 5,588 total)