Viewing 15 posts - 14,686 through 14,700 (of 14,953 total)
Realized I'd left a permutation out of my tests. I ran a set of ten selects against the table with no calculated columns, based on "where datepart(day, date) =...
- 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
February 12, 2008 at 10:58 am
In my usual, "Gotta test everything", obsession, I tried some stuff with computed columns.
I created three tables, 1 with just the date, 1 with the date and some computed columns,...
- 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
February 12, 2008 at 10:45 am
The only way I can think of to do this in one proc is a lot of IF ELSE statements. "IF @table1 = 'People' ... ELSE IF @table1 =...
- 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
February 12, 2008 at 7:42 am
I wasn't aware of this behavior. Good write-up.
(I think I'll still stick with as narrow a table as I can manage in most cases, but it is good to...
- 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
February 12, 2008 at 7:33 am
In that case, take the CTE example I provided earlier in this thread, or the one in Books Online, and modify it for your tables, and you'll have what you...
- 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
February 12, 2008 at 7:23 am
The best thing I can suggest for getting this script moving in the right direction is add a Try Catch function, and in the Catch include "select @sql as [Script]"....
- 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
February 11, 2008 at 3:19 pm
"The big bucks" is about $400. If you're in the usual range of pay for a database developer, that comes out to less than a week's pay. Tell...
- 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
February 11, 2008 at 3:16 pm
You have the additional option (beyond temp tables), of converting the table variable to XML and passing that to the dynamic 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
February 11, 2008 at 2:53 pm
Rather than trying to come up with a query for this, which may take you a huge amount of time, why not use one of the products available on the...
- 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
February 11, 2008 at 2:48 pm
The third option will have to use an index scan instead of an idex seek, which will be slower than the other options.
The "if ... " version can have 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
February 11, 2008 at 2:34 pm
On the specific point of "if exists", the reason to use "*" in that case is so SQL server can pick which index it wants to use, instead of putting...
- 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
February 11, 2008 at 2:28 pm
The advantage of "Select Col1, Col2...." (all columns explicitly named), vs "Select *", is more than just performance.
First, if you just need a few columns, not all of them, it...
- 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
February 11, 2008 at 2:24 pm
Nisha (2/11/2008)
With DB set to compatibility level 90, which uid_design am I ordering by here..... because this works:
SELECT DISTINCT uid_design AS U1, uid_design AS U2
FROM Ad_Master_Import
ORDER BY uid_design
Neither/both. Without...
- 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
February 11, 2008 at 2:11 pm
There are also many examples of resolving hierarchical data right in these forums. Search "hierarchy" in the forums and you'll find examples and discussions of them.
- 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
February 11, 2008 at 2:07 pm
Beyond Jeff's correction to the trigger (very, very necessary), I have to question using "Print" in a trigger?
Is this schoolwork of some sort? The only way a Print command...
- 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
February 11, 2008 at 2:05 pm
Viewing 15 posts - 14,686 through 14,700 (of 14,953 total)