| |
| |
| |
|
Page: 1 2 3 4 5 6
Comments:
<0> no, I think its configurable, I've seen tables on a legacy system that literally had over 250 fields, usually varchar(255) and I know there were amny rows over that
<0> many thousands actually
<1> noise cancelling headphones for the office rawks
<2> guest2, i don't believe thats right. if you had 250 cols, you could still store data in each provided the sum total per row doesn't exceed 8k
<2> afaik
<3> if you need varchar for data over 8kb there's something wrong
<0> you know, there are many legacy systems that came from flatfile cobol dbs that are indeed over that limit... they don't redesign a system that 1000s of people use everyday just because you guys don't like the way it's designed. Reality is often very very ugly
<2> um, okaaay. i was referring to the way sqlserver works, which was the focus of the question.
<2> but you go get 'em don quixote
<0> some peoples kids, I tell ya
<2> you referring to me?
<1> guest2: i dont think many people here understand your common sense
<2> is that what it is? was hard to spot amidst all that dogma and dis***ociative commentary
<4> hmm looks like i missed an interesting debate :)
<5> looks like you missed any debate
<6> back now....
<6> df: thanks for the info
<6> unlikely anyone will be inserting long strings and all the columns will have fairly low sizes individually so maybe i'll be ok, but I think i'll loook into splitting into several tables soon just to be safe and make room to be ab;e to grow
<6> adios and gracias :)
<7> df you're right, there is a haed 8k limit, I did read where someone got around it somehow using blobs
<7> hard 8k limit
<7> I guess M$ doesn't care to be a media db
<8> 2,147,483,647 bytes
<8> that's 2gb
<8> or am I misreading?
<9> 1.99999999GB
<9> close enough though who is counting
<8> so who's claiming they only support 8k?
<9> no one eye
<9> guest1 is just a bit slow
<8> ah. my bad for the late comment.
<7> I thought the 8k rowsize limit on SQLserver was configuraeable, df said it wasn't
<7> turns out he's right
<10> sqlserver2000 select query help requested... need help returning results randomly
<10> http://sql-servers.com/nopaste/?show=517
<3> why random?
<7> well you could use case/when statement in the ORDER BY statement somehow
<10> because thats the requirement :)
<7> ...but that is an unusual request
<7> usually app logic does that sort of stuff, not the db
<10> that query is a subset / much simplified version of what i am actually doign
<3> if you could explain the reason for the requirement i'm sure you could get better help
<10> I am displaying top selling products and their categories within an ecommerce system... we dont want the same category / product always showing up first
<3> you don't want your system to behave predictably?
<10> nope
<3> i understand the need for variation, but "random" doesnt usually fit in to that paradigm :P
<3> when a customer asks "how the hell does it sort those things; i can never tell what's going to come up first" you don't want the answer to be "they're completely random!"
<11> Kaine[] : surely "top selling" != random?
<10> its top selling behind the scense
<10> scenes
<10> on the frontend it looks like "check out these other recommended products" or somesuch nonsense
<11> and therefore not random - either something is a best-seller or it isn't
<10> regardless that is the client requirement
<3> ah for a recommendation engine
<3> that makes a little more sense i guess
<12> text, ntext, and image past a certain size aren't stored in the row, just the pointer is. That's how you "get around it".
<11> ah - so it's actually "buy this crap, 'cos some cretin ordered 12,000 units"
<10> exactly
<12> There's a big technical article on msdn about it if you care for the details.
<11> Drk`Angel : for any size, not just past a certain limit
<10> the page shows when a user does a search and gets 0 results
<11> Kaine[] : unless you're going to be honest about the requirement, we can't help you
<10> so its like "sorry ur search ****ed... so buy this ****" and then a random slathering of top selling products
<12> Gunther: Oh, guess I'm misremebering. I thought if it was small enough it stored it in the row to optimize retrival.
<10> what does that meean?
<12> Probably getting confused with something else.
<11> Drk`Angel : nah, 'cos then stuff wuld get badly fragged when you increased the size of the data
<12> True nuff.
<10> the clients requirment has nothing to do with my sql question
<11> Kaine[] : but you were not honest about your question
<10> my question is... is it possible to do with sql and if so... how :)
<11> you asked how to select random stuff, we asked why, you said for 'top seller' list, we said 'top seller' != random
<3> kaine: http://www.mssqlserver.com/faq/development-randomrows.asp
<11> Kaine[] : which rdbms?
<3> GuntherX it makes sense to me
<10> sqlserver2000
<11> rand()
<12> I'm thinking he just ****s at asking the question, and he wants the top sellers in a random order.
<11> tenfour : i didn't say it didn't
<10> top sellers are always top sellers... but within their category
<11> http://weblogs.sqlteam.com/jeffs/archive/0001/01/01/5248.aspx is worth a read
<10> yes i may not have asked it the best... its awkard thing to word
<7> rand() is wierd, it only seeds once a session
<11> Guest1 : you can seed rand() as many times as you like
<10> rand() doesnt exactly work... because categories / products need to stay relatively together if that makes sense
<10> (see the paste for example http://sql-servers.com/nopaste/?show=517 )
<7> select cast(RAND(1)*1000 as int),cast(RAND(10)*100 as int),cast(RAND(1)*10000 as int),cast(RAND(1)*10 as int)
<7> returns the same seed for all of them
<7> I wouldn't expect that
<7> when I call rand() I want a different number each call
<7> so careful there
<7> so my case/when in the order by idea wouldn't work
<7> good luck
<11> Guest1 : Repetitive calls of RAND() with the same seed value return the same results.
<10> so back to my orginal question.... is there any way to sort "blocks" of rows based on some shared value randomly?
<7> so does this: select cast(RAND(1)*1000 as int),cast(RAND(10)*100 as int)
<11> so, of course, rand(1) will always return the same value
<10> sorry im trying to think of a better way to word that
<7> dood
<11> Guest1 : the secret is not to put a fixed value in for the seed
<7> I like the idea of using @@IDLE or @@CPU_BUSY as random numbers
<11> for example, i usually use milliseconds after midnight
<8> kaine: have you been helped yet? ORDER BY NEWID()
<11> bbl
<7> hahah brialliant, that works
<10> EyePulp.... my problem goes another layer deep... in that i need to sort blocks of rows which share a common value randomly (hopefully that makes sense)
<10> so that will sorch each row... but i need products / categories to remain together =/
<8> kaine: that makes it a touch more difficult.
<7> no, he just gave you your answers now go work out the code yourself
<10> just a bit :P
<3> make the rand() seed with the category ID
<3> plus another seed
<8> you need randomness within sorted sections.
<10> right
<10> i think
<3> rand(CategoryID + @myseed)
<10> ooo
<10> 10-4 on that let me try that out
<3> and set myseed to some *actual* random number before the select
<7> he could put it ia subquery or is a group by or in a case/when in the order by
<13> hello people q: how can be the 'delite .. from .. where' query be optimized because it take 15min to delete 1.5 milion rows?
<8> SELECT catID, f1,f2 FROM tbl ORDER BY (catID + CONVERT(VARCHAR(40),NEWID())
<7> haha, sure, why not
<3> that wouldn't work
<8> that would do it. major grouping by category, minor grouping would be random.
<10> EyePulp that will order the products within the category
<3> that would order categories normally, and it would randomize sub-items
<8> but the categories wouldn't be randomly postioned.
<3> you need to create 1 random number per 1 catid
<10> Yeah i need the reverse
<3> my example should be fine
<7> sash, whenever you have a where clause you should be looking at indexing
<8> you don't want random item position within the category?
<10> Nope
<10> i want them to stay the same
<14> For MSSQL, does anyone here recommend any additional tool sets for design and maintenance other than the tools that come with?
<8> oh. well shoot.
<10> I need the categories randomg KEEPING the relative position of the products
<7> slade, dbartisan
<7> works with multiple platforms too, like oracle and sybase
<14> Guest1: yea thats one i've been looking at..
<8> are you showing all items or only TOP x within a cat?
<10> ORDER BY rand(categoryID + @someSeed) would work i think
<7> in fact thats why I got it, I had to migrate all sorts of stuff to different platforms
<10> all
<13> anyway the delition is to long how can it be optimised?
<10> however in actuality categoryID is a uniqueidentifier not an int
Return to
#sql
or
Go to some related
logs:
#stocks
euchix
#photoshop
#windows
#stocks
#winxp
#worldcup
pdf995.msi
8mm Mauser Iraq
acidity activia
|
|