ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,251 through 8,265 (of 10,143 total)

RE: Identity column limitation

Dugi (1/12/2010)
Did you reach a 2 billion records in your table ... usually the tables cannot have 2 billion records, at least I don't know any case, all data in...
^{“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

January 12, 2010 at 6:49 am

#1102366
RE: Using Subqueries and Joins - Retrieving columns from multiple tables without using table variables/temp tables

Ronan
Run this:
SELECT RowMark = ROW_NUMBER OVER (PARTITION BY machname ORDER BY eventtime),
eventtime, [description], machname
FROM scriptlog s
INNER JOIN vmachine v ON v.Agentguid = s.agentguid
INNER JOIN itf_companyID c ON...
^{“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

January 12, 2010 at 5:13 am

#1102316
RE: 10 digit random unique alpha-numeric number

Abhijeet Dighe (1/11/2010)
Hi
How to generate 10 digit random unique alpha-numeric string in sql server 2000?
Thanks in advance.
Save yourself from a lifetime of misery and pain, do it properly and use...
^{“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

January 12, 2010 at 4:46 am

#1102305
RE: How to return rows with same value in one column and not same value in another column

No problem:
SELECT u.*, dupes.MainLastname
FROM #Users u
INNER JOIN (
SELECT UserID, MIN(LastName) AS MainLastname
FROM (
SELECT UserID, LastName
FROM #Users
GROUP BY UserID, LastName
) d
GROUP BY UserID
HAVING COUNT(*) > 1
) dupes ON dupes.UserID =...
^{“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

January 12, 2010 at 3:36 am

#1102274
RE: parse comma-delimited list into seperate columns

Has anyone played with this yet?
SELECT s.ID, x.*
FROM #Sample s
CROSS APPLY (
SELECT
MAX(CASE d.ColNo WHEN 1 THEN SUBSTRING(d.DodgyStringData, d.b, d.e-d.b) END) AS [Account],
MAX(CASE d.ColNo WHEN 2 THEN SUBSTRING(d.DodgyStringData,...
^{“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

January 12, 2010 at 3:01 am

#1102260
RE: Select statement with logical operator

Try this:
-- parameters
DECLARE @Year CHAR(4), @Month VARCHAR(10)
SELECT @Year = '2008', @Month = 'February'
-- variable
DECLARE @RowsToReturn INT
-- test rowcount using user-supplied values; ignore parameter if NULL
SELECT @RowsToReturn = COUNT(*)
FROM #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

January 11, 2010 at 8:47 am

#1101858
RE: Select statement with logical operator

ahmadjk (1/11/2010)
This Report is for only Internal use only. Specially for Technical team.
Secondly, the data itself stored in sql table is for present day only , after 24 hours time...
^{“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

January 11, 2010 at 8:22 am

#1101828
RE: How to return rows with same value in one column and not same value in another column

Fantastic! Three different methods in as many minutes!
^{“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

January 11, 2010 at 7:28 am

#1101776
RE: How to return rows with same value in one column and not same value in another column

DROP TABLE #Users
CREATE TABLE #Users (UserID INT, LastName VARCHAR(20), FirstName VARCHAR(20))
INSERT INTO #Users (UserID, LastName, FirstName)
SELECT 1, 'Smith', 'John' UNION ALL
SELECT 2, 'Jones', 'James Earl' UNION ALL
SELECT 3, 'Sahathevarajan', 'Rajkumar'...
^{“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

January 11, 2010 at 7:28 am

#1101775
RE: Select statement with logical operator

Ahmad
I can see where you're coming from with this and I'm pretty sure that in some circumstances, your suggestion might make sense: you're indicating to the user "No, there isn't...
^{“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

January 11, 2010 at 6:24 am

#1101736
RE: Select statement with logical operator

If there is no matching data for the supplied year/month, then data should be returned for which years / months instead?
I think we've tied this down for months -...
^{“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

January 11, 2010 at 5:30 am

#1101717
RE: Joining three tables without success

mullins.tammy (1/11/2010)
There is no relationship between Table 1 and Table 3.
Currently, Table 2 is completely empty
If you are joining table 1 to table 3 via table 2,...
^{“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

January 11, 2010 at 5:13 am

#1101711
RE: Select statement with logical operator

Please post the exact code which you ran, and describe any result set (or error messages). Thanks.
^{“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

January 11, 2010 at 4:40 am

#1101702
RE: Query difference: HOw to Avoiding UNION ALL with correlated Query

Try this:
select
...
^{“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

January 11, 2010 at 4:10 am

#1101692
RE: Query difference: HOw to Avoiding UNION ALL with correlated Query

Bhuvnesh (1/11/2010)
sample data is not possible , as it includes amny tables
yes row count is correct and time taken = 18 secs
but my concerns is UNION ALL ...
^{“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

January 11, 2010 at 3:29 am

#1101686

Viewing 15 posts - 8,251 through 8,265 (of 10,143 total)