ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,566 through 5,580 (of 10,143 total)

RE: casting uniqueidentifier to number and back

Eugene Elutin (9/25/2012)
farax_x (7/24/2012)
...On the other hand sending UNIQUEIDENTIFIER to customer is not acceptable so I need to convert it to int ...
ChrisM@Work (9/25/2012)
How does CHAR(16) suit you?
...
I don't think...
^{“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 25, 2012 at 9:01 am

#1541924
RE: sum every two number combination

dwain.c (9/24/2012)
Sean Lange (9/24/2012)
And of course if the exact rows matter this can get a LOT more challenging because there may be more than 1 pair where the sum 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 25, 2012 at 8:58 am

#1541921
RE: casting uniqueidentifier to number and back

How does CHAR(16) suit you?
SELECT
[Start GUID] = d.aguid,
z.[To Character],
[back to GUID] =
CAST(
STUFF(STUFF(STUFF(STUFF(
STUFF(CONVERT(CHAR(34),CONVERT(BINARY(16),z.[To Character],0),1),1,2,'')
,9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')
AS uniqueidentifier)
FROM (SELECT aguid = newid()) d
CROSS APPLY (SELECT vID = CAST(REPLACE(aguid,'-','') 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 25, 2012 at 8:10 am

#1541886
RE: Consolidating records - TSQL problem

Abu Dina (9/25/2012)
Thanks Chris,
I'm looking at recursive CTEs but I don't think it will work. Well I've been trying for the last couple of hours and it's still not giving...
^{“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 25, 2012 at 7:46 am

#1541867
RE: Consolidating records - TSQL problem

Abu Dina (9/25/2012)
Lynne's solution works if we assume that the retained ID is always the maximum.
But tthe solution doesn't work with the following record set:
drop table dbo.testing
create table dbo.testing...
^{“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 25, 2012 at 6:48 am

#1541833
RE: How Do You Keep The "Pieces" Of A Complex Statement "Organized"

EdA ROC (9/24/2012)
...Paul - Can you give me an example of what you mean by 'break such queries up'? Maybe my example, or something simpler.
I think I mentioned before that...
^{“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 25, 2012 at 4:35 am

#1541762
RE: Creating dynamic Stored Procedures(changing dates)

ChrisM@Work (9/25/2012)
t.brown 89142 (9/25/2012)
Yes dynamic SQL would seem appropriate.
Basically you create your entire SQL Statement in a string then call EXEC on it.
e.g.
DECLARE @sql Varchar(MAX);
DECLARE @TODAY Varchar(10);
SELECT @TODAY = CONVERT(...
^{“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 25, 2012 at 3:34 am

#1541727
RE: read multiple fields with common delimited data into either a temp table or table variable without using cursors

mick burden (9/24/2012)
if I have a table with fields which contain common delimited data in them, is there a way I can read them into some kind of temporary table,...
^{“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 25, 2012 at 3:21 am

#1541718
RE: Creating dynamic Stored Procedures(changing dates)

t.brown 89142 (9/25/2012)
Yes dynamic SQL would seem appropriate.
Basically you create your entire SQL Statement in a string then call EXEC on it.
e.g.
DECLARE @sql Varchar(MAX);
DECLARE @TODAY Varchar(10);
SELECT @TODAY = CONVERT( VARCHAR(10),GETDATE(),...
^{“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 25, 2012 at 3:07 am

#1541713
RE: How To Master a Database in SQL Server 2008

Familiarisation? Probably the easiest place to start is the Activity Monitor.
Watch for Recent Expensive Queries and examine them in detail as they come up. Experiment by sorting the columns...
^{“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 25, 2012 at 3:04 am

#1541712
RE: Consolidating records - TSQL problem

Hi Abu, thank you for the kind mention - I take it that phase of the project is now concluded.
Have a try with this. It works by first selecting retained...
^{“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 25, 2012 at 2:33 am

#1541700
RE: Performace Improvement in Table Valued Function

Inline tvf's would get around the cardinality issues Gail mentions. Something like this:
DECLARE @strID int
SELECT ID
FROM Table1
WHERE ID = @strID AND REP = 1
UNION ALL
SELECT ID
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 25, 2012 at 2:18 am

#1541696
RE: Creating dynamic SP(date changes)

prasan.dash83 (9/25/2012)
I have are a set of create table statements having Select queries with multiple conditions. Some of the conditions include filters for the current date like:
'Create table emp_hrs 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 25, 2012 at 2:13 am

#1541695
RE: Creating dynamic Stored Procedures(changing dates)

Duplicate post - please reply to
http://www.sqlservercentral.com/Forums/FindPost1363825.aspx
^{“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 25, 2012 at 1:55 am

#1541690
RE: Creating dynamic SP(date changes)

prasan.dash83 (9/25/2012)
Hi Guys,
I have an MIS system where I have to run 15 queries and update 15 sheets which takes a lot of time as I need to make changes...
^{“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 25, 2012 at 1:53 am

#1541689

Viewing 15 posts - 5,566 through 5,580 (of 10,143 total)