ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,641 through 5,655 (of 10,143 total)

RE: UniqueIdentifier as a Primary Key

IgorMi (9/14/2012)
SQL Kiwi (9/14/2012)
ChrisM@Work (9/14/2012)
[font="Courier New"]
Table Ind_level External Fragm(%) Avg Frag Size Page_count Internal Fragm(%) Ind_size (KB)
identity ...
^{“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 14, 2012 at 9:03 am

#1537916
RE: how to select active inventory

Andy Hyslop (9/14/2012)
ChrisM@Work (9/14/2012)
Andy Hyslop (9/14/2012)
ChrisM@Work (9/14/2012)
SELECT i.*
FROM tbItems i
CROSS APPLY (
SELECT [active inventory] = CASE
WHEN Solddate >= CONVERT(DATETIME,'01/11/2011',103)
AND Solddate < CONVERT(DATETIME,'01/12/2011',103) THEN 1
WHEN Purchasedate >= CONVERT(DATETIME,'01/11/2011',103)
AND...
^{“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 14, 2012 at 8:57 am

#1537907
RE: how to select active inventory

Andy Hyslop (9/14/2012)
ChrisM@Work (9/14/2012)
SELECT i.*
FROM tbItems i
CROSS APPLY (
SELECT [active inventory] = CASE
WHEN Solddate >= CONVERT(DATETIME,'01/11/2011',103)
AND Solddate < CONVERT(DATETIME,'01/12/2011',103) THEN 1
WHEN Purchasedate >= CONVERT(DATETIME,'01/11/2011',103)
AND Purchasedate < CONVERT(DATETIME,'01/12/2011',103)...
^{“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 14, 2012 at 8:42 am

#1537891
RE: how to select active inventory

SELECT i.*
FROM tbItems i
CROSS APPLY (
SELECT [active inventory] = CASE
WHEN Solddate >= CONVERT(DATETIME,'01/11/2011',103)
AND Solddate < CONVERT(DATETIME,'01/12/2011',103) THEN 1
WHEN Purchasedate >= CONVERT(DATETIME,'01/11/2011',103)
AND Purchasedate < CONVERT(DATETIME,'01/12/2011',103)
AND Solddate 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 14, 2012 at 8:32 am

#1537878
RE: Need help with SQL Query

denis.gendera (8/31/2012)
I've got following query
select machine_id, MONTHS =
CASE
WHEN DATEDIFF("m", firstseen, getdate()) = 0 then 'AUG'
...
^{“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 14, 2012 at 8:14 am

#1537859
RE: UniqueIdentifier as a Primary Key

"Internal fragmentation" = "average page density", although the latter seems more sensible to 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 14, 2012 at 7:50 am

#1537831
RE: UniqueIdentifier as a Primary Key

SQL Kiwi (9/14/2012)
IgorMi (9/13/2012)
The above from BOL has proved true in a test i did some weeks ago. I wanted to see the fragmentation, page counts, index sizes for int,...
^{“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 14, 2012 at 7:40 am

#1537824
RE: DeadLOck on Primary Key

Two stored procedures are run at the same time by agent jobs.
SystemCheck2.dbo.sp_IMPORT_UTILITY_RUN_PROCESS_tb_Import_Data
- UPDATE [dbo].[tb_Import_Data]
SystemCheck2.dbo.sp_IMPORT_CLEAN_tb_Import_Data
- Copy rows FROM tb_Import_Data i to [dbo].[tb_ARCHIVE_Import_Data]
They should be run serially with 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 14, 2012 at 6:55 am

#1537799
RE: T-SQL Help Needed

aaron.reese (9/13/2012)
[
If I create a table to look up state codes ('AL', 'AK', etc.), I use state code as the key, not a meaningless identity value.
fine, but if you are...
^{“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 14, 2012 at 5:40 am

#1537746
RE: T-SQL Help Needed

Eugene Elutin (9/12/2012)
...Even DNA is not 100% unique ...
For us humans, and you too Eugene, it is - even for so-called "identical twins".
Eugene Elutin (9/12/2012)
...(that is why forensic DNA-result doesn'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

September 14, 2012 at 5:14 am

#1537736
RE: Stored procedure performance improvement

Luhar (9/14/2012)
we have 4 million records in our test db
How to delete them batch wise....Can you gimme a hint
Here's a useful script which Lynn Pettis prepared earlier: http://www.sqlservercentral.com/Forums/FindPost1357526.aspx
You have 4...
^{“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 14, 2012 at 4:13 am

#1537715
RE: subqueries in sql server2000

raghuldrag (9/13/2012)
Hai friends ,
i ve one doubt in subqueries in oracle
select col1,col2 from @tab1 where(col2,col3) in(select col2,min(col3) from @tab1 group by col2)
while i am executing these...
^{“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 14, 2012 at 3:51 am

#1537704
RE: Stored procedure performance improvement

Luhar (9/13/2012)
I am not running it in transaction.
No one will access the DB when this procedure runs as it runs nightly.
I just want to modify the procedure so 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 14, 2012 at 3:12 am

#1537691
RE: Archiving...INSERT & DELETE

oscarooko (9/13/2012)
...
Folks, thanks for the help. i have solved the issue, and more importantly, I learned a few new tricks from you. Mine was mostly a case of poor design....
^{“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 14, 2012 at 2:14 am

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

dwain.c (9/13/2012)
And here's one specially built for ChrisM@Work, who loves cascading CROSS APPLYs:
;WITH SourceNums AS (
SELECT num FROM (VALUES (1),(2),(5),(7),(8),(9)) a (num)
), MyNums AS (
SELECT num=a.num + 10*b.num + 100*c.num...
^{“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 14, 2012 at 2:08 am

#1537672

Viewing 15 posts - 5,641 through 5,655 (of 10,143 total)