ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,481 through 9,495 (of 10,143 total)

RE: Problems with dynamic string in Openquery

Hi Joey
It's good practice to print the string you're going to execute. Try this, there are a couple of minor changes:
[font="Courier New"]DECLARE @LinkedNameServer VARCHAR(500)
DECLARE @Schema VARCHAR(100)
SET @Schema = 'Schema'
DECLARE @TableName...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 9, 2008 at 4:15 am

#882783
RE: Parent Child Link

Hi David
With six node levels and a remote possibility that it could increase, your best bet is almost certainly a recursive CTE as described in the following article:
http://www.sqlservercentral.com/articles/T-SQL/2926/
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 9, 2008 at 3:08 am

#882755
RE: Parent Child Link

Is there a logical maximum?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 9, 2008 at 2:29 am

#882742
RE: Parent Child Link

Hi David, many thanks for posting this. The next question would be...how many levels deep can this hierarchy go? If it's fixed at say 2 or 3, then the solution...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 9, 2008 at 2:23 am

#882740
RE: Parent Child Link

Hi David, there are established methods for achieving this. Some sample data would help tremendously. Here's a start though I'm guessing somewhat, please can you correct the data to fit...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 9, 2008 at 12:57 am

#882711
RE: Need Help--Derived Tables???

Hello
This is also untested but should be pretty close to what you're looking for - if it isn't, please post DDL of your table and some sample data.
[font="Courier New"]SELECT d1.RIV_RCN_ID,...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 9, 2008 at 12:36 am

#882707
RE: 'N' number of usage - Tally Table

rbarryyoung (10/7/2008)
Jeff Moden (10/7/2008)
BWAA-HAA! Did you leave any redmarks on your cheeks from the slingshot kickback? :hehe:
Is that the tough chewy part?
Nah that's the target 😛
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 8, 2008 at 8:08 am

#882297
RE: 'N' number of usage - Tally Table

Interesting point. I'd guess that most of us use a tally table with a million numbers in it. Having found that reducing the rowcount (or maximum number, in other words)...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 8, 2008 at 8:07 am

#882295
RE: select within select

dr_csharp (10/8/2008)
whats wrong with this :
Select * from Callrecorder where RecId between (select RecId from Table2 ) tempTable
Firstly, as mentioned earlier, a table alias ('tempTable') is only required if the...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 8, 2008 at 5:13 am

#882179
RE: Finding Table is empty or not without using count() function

Hi Garadin, use this:
http://extras.sqlservercentral.com/prettifier/prettifier.aspx
Cheers
ChrisM
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 7, 2008 at 8:12 am

#881684
RE: Ignore "dupes" when importing using SELECT

Hi Mr Baseball
If I'm teaching you to sucks eggs then I humbly apologise now...best practice in this scenario is to use a "staging table" as the target for your import,...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 6, 2008 at 10:30 am

#881218
RE: int to date and time.

Here's another way - I'm sure it won't be the last.
DROP table #test
create table #test (
[Date] int,
[Time] int)
INSERT INTO #test
SELECT 20081003,70004
UNION
SELECT 20080326,210053
UNION
SELECT 20081003,20003
UNION
SELECT 20081002,200002
SELECT CONVERT(DATETIME, STUFF(STUFF(CAST([Date] AS...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 3, 2008 at 10:32 am

#880587
RE: Creating a ROT13 Function

Yeah I see your point now. It's easy enough to create a short numbers table on the fly
SELECT CAST(CAST(d1.Number AS VARCHAR(2)) + CAST(d2.Number AS VARCHAR(2)) AS INT) AS number
FROM...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 3, 2008 at 9:41 am

#880550
RE: Creating a ROT13 Function

Had to try it, guys...
[font="Courier New"]DECLARE @StringIn VARCHAR(200), @StringOut VARCHAR(200)
SET @StringIn = 'The Quick Brown Fox Jumps Over The Lazy Dog.'
SET @StringOut = ''
SELECT @StringOut = @StringOut + CHAR(CASE WHEN...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 3, 2008 at 7:54 am

#880472
RE: Group data and count by interval data

Hi Augusto
This is usually straightforward - what does your source table look like? Can you post DDL with some sample data please?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 3, 2008 at 5:14 am

#880385

Viewing 15 posts - 9,481 through 9,495 (of 10,143 total)