Jeff Moden

Forum Replies Created

Viewing 15 posts - 30,181 through 30,195 (of 59,101 total)

RE: insert 100 rows without using loop or union etc.,

michael vessey (5/4/2012)
capn.hector (5/4/2012)
Eugene Elutin (5/4/2012)
Jeff Moden (5/3/2012)
michael vessey (5/2/2012)
does the following count as a loop?
INSERT INTO dbo.yourTable default values
go 100
??:-P??
BWAAAA-HAAAA!!!!! Ask that question on the receiving end 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)

May 4, 2012 at 1:19 pm

#1483746
RE: Remove successive rows with same column value

Ummmm... I've not run any of the code but, just by looking, shouldn't any ordering be done in descending order so that you get the "latest" row like what the...
--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)

May 4, 2012 at 8:07 am

#1483576
RE: Selecting from hierarchies like Managers and Employees

W. Kevin Hazzard (5/4/2012)
Great points, Miika. In my experience with terabytes of hierarchical data in my system, recursive CTEs are faster, especially when going down the tree. Going up creates...
--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)

May 4, 2012 at 7:55 am

#1483565
RE: Working with an ID column and SQL. ("newid").

You can use ROW_NUMBER() OVER for this.
--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)

May 3, 2012 at 8:20 pm

#1483391
RE: Write all the records of a table to a text file on the local disk with in T-SQL (SP)

opc.three (5/3/2012)
Lowell (5/3/2012)
is adding CLR assemblies an option?
I made a suite of CLR export functions as a proof of concept, and i can export to any text data type or...
--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)

May 3, 2012 at 7:55 pm

#1483389
RE: insert 100 rows without using loop or union etc.,

michael vessey (5/2/2012)
does the following count as a loop?
INSERT INTO dbo.yourTable default values
go 100
??:-P??
BWAAAA-HAAAA!!!!! Ask that question on the receiving end of an interview and see what happens. 😉
--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)

May 3, 2012 at 7:19 pm

#1483384
RE: insert 100 rows without using loop or union etc.,

NLV (5/2/2012)
Hi,
I want to insert 100 rows like (1,2,3,4,..100) without using loops into a table using CTE. can you please help.
Ok... getting back on track for this thread... I've got...
--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)

May 3, 2012 at 7:14 pm

#1483382
RE: RS with two date columns from a single date column

Using the data you provided, this will produce the correct output. I'll leave it up to you to format the dates.
WITH
cteEnumerate AS
( --=== Assign a numeric sequence to each...
--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)

May 3, 2012 at 6:45 pm

#1483380
RE: Selecting from hierarchies like Managers and Employees

imarran (5/3/2012)
I also have a solution in the form of a view that you can join to that will display upline and down line structure depending on how it is...
--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)

May 3, 2012 at 6:04 pm

#1483375
RE: Selecting from hierarchies like Managers and Employees

W. Kevin Hazzard (5/3/2012)
The recursive CTE of these procedures is much faster on large data sets. Easier to grok, too, IMO:
CREATE PROCEDURE [dbo].[usp_Employee_Select_Down_Param_EmpID]
@selectEmpID INT = NULL, -- NULL = all
@maxSupLevel...
--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)

May 3, 2012 at 5:53 pm

#1483374
RE: retrieve only month and year (last two digits) part of datetime field

polkadot (5/3/2012)
There's a lot to think about here.
Do we have to convert datetime to varchar (Jeff, ColdCoffee)?
If we don't is it *better not to* (you guys)?
What is internationally sensitive...
--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)

May 3, 2012 at 5:38 pm

#1483373
RE: insert 100 rows without using loop or union etc.,

drew.allen (5/2/2012)
Furthermore, it's just clearer. If you specify CROSS JOIN, it's absolutely clear that you are expecting the Cartesian product of the two tables.
I simply see no advantage and...
--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)

May 3, 2012 at 11:02 am

#1483213
RE: Grouping

There is indeed. But I'd like to see what you think you'd like the output to look like.
--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)

May 3, 2012 at 7:22 am

#1483004
RE: Selecting from hierarchies like Managers and Employees

hayden-508426 (5/3/2012)
I use rCTE for account control and access on our websites. Best way to go, haven't found one that's faster, including hierarchyid. I'm thinking hierarchyid loses on...
--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)

May 3, 2012 at 6:57 am

#1482977
RE: Selecting from hierarchies like Managers and Employees

imarran (5/3/2012)
yes, I have done this often with rCTE's which is much faster 🙂
Proof!!! Let's see the proof for your claim!!!
--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)

May 3, 2012 at 6:51 am

#1482971

Viewing 15 posts - 30,181 through 30,195 (of 59,101 total)