Jeff Moden

Forum Replies Created

Viewing 15 posts - 40,651 through 40,665 (of 59,098 total)

RE: Convert String to a Table using CTE

I wondered about this... with the advent of CROSS APPLY and a well written While Loop like what Alexey Eliseev wrote on this thread, a While Loop solution looks 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)

December 15, 2009 at 8:45 pm

#1092791
RE: Convert String to a Table using CTE

schedde (12/15/2009)
Create Table #Id (n int)
declare @d datetime
set @d = getdate()
declare @s-2 varchar(max)
set @s-2 = '3903044,7569634,3947703,4397641,8304414,3889301,504543,5543592,2468890,644965,3343....
EXEC Util_ReadIdList @IdList=@s
select datediff(millisecond, @d, getdate())
Thanks, schedde. Do...
--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)

December 15, 2009 at 8:40 pm

#1092790
RE: Convert String to a Table using CTE

Roland Howard Boorman (12/14/2009)
I simply use XML.
Using XML whole complex structures can be passed to SQL and treated as Tables.
You can use Functions with the XML to render these...
--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)

December 15, 2009 at 7:09 pm

#1092783
RE: Convert String to a Table using CTE

schedde (12/15/2009)
Simple testing took 0.4 seconds to load 10,000 numbers.
What did the simple testing look like? In other words, was that all in a single "variable" or was that...
--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)

December 15, 2009 at 6:20 pm

#1092780
RE: Where's My Additional Free Space ?

homebrew01 (12/15/2009)
GilaMonster (12/15/2009)
That's a problem. The heap can't be rebuilt, except by putting a cluster on it, and if there are pages partially used in the heap they'll stay partially...
--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)

December 15, 2009 at 4:52 pm

#1092771
RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Thanks for the feedback, Calvin. Like you say, it's always good when someone comes to the same conclusion. 😀
--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)

December 15, 2009 at 4:28 pm

#1092767
RE: How to do this...?

krypto69 (12/15/2009)
Jeff Moden (12/14/2009)
krypto69 (12/14/2009)
I have a DB that gets updated throughout the day.
My users need the ability to go back in time and search on data say from 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)

December 15, 2009 at 10:52 am

#1092643
RE: Worst Practices - Objects Not Owned by DBO

I know this is an old article but I'm glad I found it. I agree 100% for homegrown stuff. Sure, if you have to follow a given naming...
--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)

December 15, 2009 at 9:09 am

#1092583
RE: Running T-SQL statement on all Databases

Yeah... that should do it. I just hate using it because it will, in fact, do it for all the DB's and that's not usually what you want.
--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)

December 15, 2009 at 6:33 am

#1092463
RE: Display cities in specified order

Bhavesh_Patel (12/15/2009)
No, there is no column that defines the order of the cities ...
Then, make a sister table (either permanent or temporary) and use 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)

December 15, 2009 at 6:27 am

#1092461
RE: sqlcmd numeric formatting numbers with thousands format i.e 1,234.56 I want 1234.56

Are you using the "-R" argument of SqlCmd?
--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)

December 15, 2009 at 6:24 am

#1092460
RE: Where's My Additional Free Space ?

GilaMonster (12/15/2009)
Partially. Pre-SQL 2008 a heap can't be rebuilt except by creating a cluster on it. A heap can still have the 10% avg usage of pages if the rows...
--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)

December 15, 2009 at 6:15 am

#1092456
RE: Extracting from a very wide table

If all the "fields" in this monster table are all in good order, then this shouldn't be a problem at all. I haven't had to look it up in...
--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)

December 15, 2009 at 6:12 am

#1092455
RE: Convert String to a Table using CTE

schedde (12/14/2009)
Here is solution using OPENXML:
Have you ever tested that for performance or resource usage?
--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)

December 14, 2009 at 10:52 pm

#1092362
RE: Indexing nvarchar or varchar larger than 900 bytes

Ummmm.... do you mean that you're going to do a join on a >900 character VARCHAR or that someone using a GUI is going to type more than 900 characters...
--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)

December 14, 2009 at 10:40 pm

#1092358

Viewing 15 posts - 40,651 through 40,665 (of 59,098 total)