ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,671 through 5,685 (of 10,143 total)

RE: Puzzle : Generating two unique numbers from an array of numbers (repetation not allowed)

dwain.c (9/12/2012)
...
BTW Chris - Thanks for the credit!
No worries mate - so long as you keep up the repayments! 😛
^{“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

September 13, 2012 at 1:44 am

#1537040
RE: Puzzle : Generating two unique numbers from an array of numbers (repetation not allowed)

A completely different method, courtesy of Dwain C:
;WITH SourceNums AS (
SELECT strcol FROM (VALUES ('1'),('2'),('5'),('7'),('8'),('9')) a (strcol)
),
nTuples (n, Tuples) AS (
SELECT 1, CAST(strcol 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

September 12, 2012 at 9:59 am

#1536807
RE: Puzzle : Generating two unique numbers from an array of numbers (repetation not allowed)

Eugene Elutin (9/12/2012)
I've tried all three methods, INNER JOINs, CROSS APPLY and CROSS JOIN with WHERE: all produce very similar query plans with use of Nested Loops (Inner Join).
Cannot...
^{“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

September 12, 2012 at 9:15 am

#1536770
RE: Puzzle : Generating two unique numbers from an array of numbers (repetation not allowed)

GSquared (9/12/2012)
Here's what I came up with. Does the same thing the same way, just another way to write it:
WITH Numbers
...
^{“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

September 12, 2012 at 8:54 am

#1536760
RE: SQL Query Help - Join + Max

There's something screwy about this - you're aggregating on pr_id but expecting the max date per id. Try isolating the part which generates data for TBL_VAL and working with it...
^{“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

September 12, 2012 at 8:22 am

#1536738
RE: Puzzle

Anju Renjith (9/12/2012)
Step:1
CREATE TABLE tbl_Group_Test
(
_ID INT IDENTITY,
_Place VARCHAR(50),
_Name VARCHAR(100)
)
Step:2
INSERT INTO tbl_Group_Test VALUES('Tamil Nadu','Abdul Kalam')
INSERT INTO tbl_Group_Test VALUES('Tamil Nadu','Karunanidhi')
INSERT INTO tbl_Group_Test VALUES('Tamil Nadu','Jayalalitha')
INSERT INTO tbl_Group_Test VALUES('Tamil Nadu','Robin Singh')
INSERT INTO tbl_Group_Test...
^{“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

September 12, 2012 at 5:26 am

#1536652
RE: Archiving...INSERT & DELETE

ChrisM@home (9/11/2012)
...
If there's no index on it, then that's your problem. Each delete in SPTLog will cause a table scan of the column in SPTLogArchive to ensure that the key...
^{“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

September 12, 2012 at 4:50 am

#1536639
RE: Need the Best Solution for splitting into batches

venus.pvr (9/11/2012)
ChrisM,
No two columns should have same value within a row. With the query you provided, below is the result set:
111
222
123
124
567
8910
3NULLNULL
First two rows have same values for all the columns.
Thank...
^{“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

September 11, 2012 at 10:08 am

#1536372
RE: Archiving...INSERT & DELETE

Lynn Pettis (9/11/2012)
ChrisM@Work (9/11/2012)
oscarooko (9/11/2012)
It actually took me 47 minute to delete 13 684 records in batches of 100. I am there's got to be a faster way, and one...
^{“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

September 11, 2012 at 9:49 am

#1536352
RE: Archiving...INSERT & DELETE

oscarooko (9/11/2012)
It actually took me 47 minute to delete 13 684 records in batches of 100. I am there's got to be a faster way, and one of you good...
^{“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

September 11, 2012 at 9:40 am

#1536344
RE: Need the Best Solution for splitting into batches

SELECT
col1 = MAX(CASE WHEN seq = 1 THEN a END),
col2 = MAX(CASE WHEN seq = 2 THEN a END),
col3 = MAX(CASE WHEN seq = 3 THEN...
^{“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

September 11, 2012 at 9:36 am

#1536341
RE: Archiving...INSERT & DELETE

oscarooko (9/11/2012)
Lynn Pettis (9/11/2012)
ChrisM@Work (9/11/2012)
Lynn Pettis (9/11/2012)
ChrisM@Work (9/11/2012)
oscarooko (9/11/2012)
I am trying to delete old records from a table, and insert them into another table. Problem is that it is...
^{“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

September 11, 2012 at 9:18 am

#1536327
RE: Archiving...INSERT & DELETE

Lynn Pettis (9/11/2012)
ChrisM@Work (9/11/2012)
oscarooko (9/11/2012)
I am trying to delete old records from a table, and insert them into another table. Problem is that it is taking too long. 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

September 11, 2012 at 9:00 am

#1536312
RE: Archiving...INSERT & DELETE

oscarooko (9/11/2012)
I am trying to delete old records from a table, and insert them into another table. Problem is that it is taking too long. The table has about...
^{“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

September 11, 2012 at 8:51 am

#1536303
RE: Reformat Phone Number

Brandie Tarvin (9/11/2012)
Eugene Elutin (9/11/2012)
The best and easiest would be creating CLR function which will remove all non-digits from string using RegEx.Replace.
Forgive me a moment, Eugene, while I poo-poo your...
^{“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

September 11, 2012 at 8:45 am

#1536297

Viewing 15 posts - 5,671 through 5,685 (of 10,143 total)