ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,161 through 2,175 (of 10,143 total)

RE: Problem in sorting by alphanumeric table column of NVARCHAR datatype.

WayneS (9/24/2015)
SELECT activityName
FROM #Activities
CROSS APPLY (SELECT PATINDEX('[A-Z,a-z][0-9]%', activityName),
...
^{“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 24, 2015 at 7:39 am

#1829144
RE: Problem in sorting by alphanumeric table column of NVARCHAR datatype.

SELECT activityName, SortOrder = x4.LeftChars + x6.NewRightNums + ' ' + x3.RightPart
FROM #Activities
CROSS APPLY (SELECT SpacePos = CHARINDEX(' ',activityName+' ')) x1
CROSS APPLY (SELECT LeftPart = LEFT(activityName,x1.SpacePos-1)) x2
CROSS APPLY (SELECT RightPart...
^{“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 24, 2015 at 7:13 am

#1829129
RE: DUPLICATE Records

jkramprakash (9/24/2015)
i have one table having three columns.This table contains lot of repeated records.
i want to delete this records .
In this below example i want to delete all the records
which...
^{“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 24, 2015 at 6:44 am

#1829106
RE: A Pattern for Email Address storage and retrieval

Is there any particular reason for storing the email address as NVARCHAR?
^{“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 24, 2015 at 6:07 am

#1829095
RE: Need help in SQL Query

vipin_jha123 (9/24/2015)
Hi Used below query to get the desired output.
;with cte AS (SELECT [ProgramId]
,[ProgramVersionId]
,[WRINPrefix]
...
^{“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 24, 2015 at 5:30 am

#1829086
RE: Need help in SQL Query

Here's how you do it:
SELECT
s.*,
[ForecastDate] = DATEADD(DAY, x.n, s.ForecastStartDate)
FROM #STG s
CROSS APPLY (
SELECT TOP(1 + DATEDIFF(DAY, s.ForecastStartDate, s.ForecastEndDate))
n = -1 + ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
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

September 24, 2015 at 2:29 am

#1829052
RE: ISDATE function not working as expected

squvi.87 (9/24/2015)
Declare @var varchar(100) set @var = '2000'
select case when ISDATE(@var ) =1 then 'Valid Date' when ISDATE(@var ) =0 then 'Invalid Date' END
In the above code I...
^{“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 24, 2015 at 1:52 am

#1829040
RE: Using T-SQL Cursors to find first, highest and last values

steve.tarry (9/22/2015)
drop table ##ss
select 3 as nr, 'a' as kar, 1 as groupingcode into ##SS
insert into ##SS values
(9, 'C',1),
(15,'h',1),
(7, 'e',1),
(6, 'd',1),
(12,'z',1),
(4, 'b',1),
(5, 'q',1)
select * from ##SS
--
-- Get the first...
^{“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 22, 2015 at 8:08 am

#1828414
RE: How to control Deadlock?

Plenty of scope for optimisation here - and the faster a batch completes, the less likely it is to cause a deadlock. I'd start with the nested cursor. Resolve 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 22, 2015 at 7:11 am

#1828382
RE: Memory-optimized queries using table scan instead of seek

There's a couple of references to the 64/65 split in this article by Paul White. I'm sure I've seen something more comprehensive somewhere but the Google - force eludes me...
^{“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 22, 2015 at 4:11 am

#1828310
RE: Are the posted questions getting worse?

Brandie Tarvin (9/21/2015)
Eirikur Eiriksson (9/20/2015)
Is anyone memory optimized?
😎
Not me. But now that I've seen that thread, I am curious.
IIRC Paul White has one or two articles which exploit...
^{“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 21, 2015 at 4:44 am

#1828040
RE: How do I copy several records from one server to another?

Rod at work (9/11/2015)
Ed Wagner (9/11/2015)
If this is a one-time activity, you can query the rows in SSMS and save them as a CSV file by right-clicking the results pane...Save...
^{“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, 2015 at 9:56 am

#1826056
RE: Delete statement taking too much time..?

Walton (9/11/2015)
I will try to replace NOT IN with LEFT JOIN and filtering out with left column of left table IS NOT NULL. It had helped me in many cases.
Have...
^{“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, 2015 at 8:50 am

#1826012
RE: Trigger fire on each inserted row to insert same record into remote Table

mohelsheikh (9/9/2015)
I have two different SQL 2008 servers, I don't have permission to create a linked server in any of them. i created a trigger on server1.table1 to insert 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, 2015 at 7:19 am

#1825972
RE: Are the posted questions getting worse?

BL0B_EATER (9/11/2015)
I had to google JET, never heard of it.
Somewhere in Oxfordshire innit?
^{“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, 2015 at 1:46 am

#1825898

Viewing 15 posts - 2,161 through 2,175 (of 10,143 total)