Viewing 15 posts - 9,946 through 9,960 (of 14,953 total)
Joe Celko (4/15/2009)
One of the Newsgroups has a thread with a dozen plus ways that...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2009 at 1:22 pm
Thomas (4/15/2009)
I stand corrected. Cross joining on syscolumns does appear to be a bit faster. Perhaps this is one loop based solution that now has a reasonable set-based solution.
Thomas...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2009 at 1:18 pm
Thomas (4/15/2009)
Couple of very simple solutions.
First one is SQL 2005/2008 only, won't work in 2000:
create table dbo.Numbers (Number int primary key);
go
insert into dbo.Numbers (Number)
select top 10000 row_number() over (order by...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2009 at 12:57 pm
Your dynamic concat would look more like this:
create table #Tables (
TName varchar(100) primary key);
insert into #Tables (TName)
select 'GL20000' union all
select 'GL30000';
declare @sql varchar(max), @Q char(4);
select @Q = '''';
select @sql =...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2009 at 12:36 pm
You don't create the table with dynamic strings. You build the string with data from a table.
I just created the temp table and put some data in it for...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2009 at 12:26 pm
I had that same point in my original post, but it got lost when my browser crashed mid-post. I think it's just a game to not use Reverse. ...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2009 at 12:12 pm
ApexSQL and Redgate both sell products that will do that for you. It's not a simple process.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2009 at 12:11 pm
One thing you could try is building a single dynamic string, like this:
create table #T (
ID int identity primary key,
Name varchar(100));
insert into #T (Name)
select 'Ink Inc' union all
select 'ACME Co';
declare...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2009 at 12:10 pm
Here's the sampe I came up with.
declare @Str varchar(100), @StrRev varchar(100);
select @Str = 'able was I ere I saw elba';
select @StrRev = coalesce(@StrRev + substring(@Str, number, 1), substring(@Str, number, 1))
from...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2009 at 12:05 pm
One thing you might try is changing the cursor to either "Static Forward_Only", or "Fast_Forward". You're not using it to update the base table, so that will often speed...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2009 at 11:24 am
To make sure I'm understanding this, it looks like you have a tables in a number of different databases that you're querying, and inserting some data into a single master...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2009 at 11:07 am
The version I posted has as its only virtue that it is simple and easy to understand. There are lots of ways to speed it up.
For example, you could...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2009 at 7:57 am
Your boss came up with pretty much the same solution we did. Thus, most lists arrived already formatted.
I must be looking at a different function than you are. ...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2009 at 7:45 am
Agreed. Here's another example. You want to build a numbers table that simply contains a series of sequential integers. There are many uses for such a table in solving problems...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2009 at 7:37 am
I've seen a SAN slow down a database horribly, because the whole thing was configured as a single RAID 5 array, and the "separate drives" for data and logs were...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2009 at 7:20 am
Viewing 15 posts - 9,946 through 9,960 (of 14,953 total)