Jeff Moden

Forum Replies Created

Viewing 15 posts - 35,326 through 35,340 (of 59,099 total)

RE: Filter a table to get unique rows in a certain logic

It's an interesting problem because the supposed "set-based" methods that immediately come to mind are all going to be Triangular Joins which will be much slower than any kind of...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

January 19, 2011 at 5:30 am

#1275223
RE: Filter a table to get unique rows in a certain logic

moadh.bs (1/19/2011)
Hi,
I'm trying to select specific rows from this table:
CREATE TABLE MVTS (idMvt1 int,
idMvt2 int,
idMvt3 int);
INSERT INTO MVTS
SELECT 271, 204, 136
UNION ALL
SELECT 271, 202, 136
UNION ALL
SELECT 268, 253, 204
UNION...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

January 19, 2011 at 4:44 am

#1275201
RE: Hierarchical Data with joins

ezimmerman 33749 (1/18/2011)
Let's say I have the following tables:
Task Table
-----------
Id [Guid]
Name [nvarchar(64)]
ParentId [Guid]
Time Entry Table
-----------------
Id [Guid]
EntryDate [DateTime]
HoursCharged [float]
TaskId [Guid]
1. I would like to load the entire hierarchy of a parent...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

January 18, 2011 at 9:29 pm

#1275090
RE: SSIS task causes tempdb to grow uncontrolled

romanoplescia (1/17/2011)
I have a very strange problem where a execute SQL task in a SSIS package completely fills up my tempdb database (15GB) by executing a sproc. The weird thing...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

January 18, 2011 at 9:24 pm

#1275089
RE: 7934149330 reads Vs 20 Writes - 20 executions?

sqldba_icon (1/18/2011)
Seems like its always been the same..i will further investigate..
With that many reads, it is possible that the query has an "accidental" Cross-Join in it in the form of...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

January 18, 2011 at 9:21 pm

#1275088
RE: DATETIME/DATETIME2 versus DATE and TIME Datatypes

I absolutely agree with Gail and Elliott. Having Date and Time in separate columns is usually going to cause more problems than it fixes. To repeat what Gail...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

January 18, 2011 at 9:17 pm

#1275087
RE: Using Case for an Update

tacy.highland (1/18/2011)
Thanks for the replies.
I'm a little nervous on the MERGE code since I'm not familiar with it. Can i assume that, while not best practice, the UPDATE FROM...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

January 18, 2011 at 9:08 pm

#1275084
RE: Calculating Delta values for each day

drew.allen (1/18/2011)
Carl Federl (1/18/2011)
First one needs to get the date only from the datetime column using this method:
1. subtract one-half of a day and subtract 2 milliseconds, which is 43200002...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

January 18, 2011 at 8:59 pm

#1275082
RE: Help with TSQL and Regular Expressions

The following should help. Be advised that a revision to make it faster for higher byte counts is in the works must not much will touch it for strings...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

January 18, 2011 at 8:55 pm

#1275081
RE: .mdf / .bak / .ldf

Heh... possibly even more fun... just swap the extensions on the data and log file names. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

January 18, 2011 at 7:41 pm

#1275077
RE: Hierarchies in SQL

From the article...
The speed difference and IO difference can be significant on the two. For example, I have a hierarchy in one of my databases with 2,700 nodes in it,...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

January 18, 2011 at 6:56 pm

#1275073
RE: Getting RowNumbers from a Table

Heh... and what form of age discrimination will this be used for? 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

January 18, 2011 at 12:47 pm

#1274945
RE: SQL Dilemma

chaudharyabhijit (1/17/2011)
Hello Jeff,
Thank you for the suggestion. I would love to do what you asked me but unfortunately these tables are from a really obsolete software and there is no...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

January 18, 2011 at 7:31 am

#1274715
RE: How to fill in nulls in a set with zeroes?

keymoo (1/18/2011)
Jeff Moden
Just to follow up on that... a good ol' fashioned cross-tab can make life real easy and "prettier".
SELECT Symbol,
SUM(CASE...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

January 18, 2011 at 7:29 am

#1274713
RE: Normalise Data

Bryan McGuire (1/17/2011)
Hi,
I have been given a task to create a pivot table from a table of data supplied via a 3rd party.
The table is structured like this...
CREATE TABLE [dbo].[EntityRiskGroupKeyItem](
[Name]...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

January 17, 2011 at 11:21 pm

#1274494

Viewing 15 posts - 35,326 through 35,340 (of 59,099 total)