| |
| |
| |
|
Page: 1 2 3 4 5 6
Comments:
<0> its called college :)
<0> well given that mssql and oracle dont really support bit type....
<0> :)
<1> As for hotspots: I once saw a DBA build a Clustered Index on a 20 million row table on a column that only had 20 unique values. That is what will create a hot spot. Every time #10 comes in and needs to be inserted, it has to swap all the index data around to get it in the Index Page with all the other 10's
<1> lol
<2> and 20 million rows isn't *that* much
<0> i think the way clustered index works is that it will just create a link to antoher page and then create teh data on that other page
<0> atleast in mssql
<1> The Clustered Index IS the data, physically ordered on whatever column you put the index on.
<0> this is one legitimate issue with clustered index
<0> right but you can get referecnes to it when you cant fit it on that page
<2> you say 'mssql and oracle dont really support' bit columns...you mean they are there but not really implimented?
<1> Well, but that's fragmentation
<0> Guest1: yes thats what i meant
<2> I always noticed they automatically forced table scans
<0> yes but hotspots create fragmentation
<0> Guest1: bit type columns force table scans?
<2> I seem to remeber that..it might have been in 7 though
<1> Right, but that's why you should place the Clustered on a column that is ordered anyway, like an Identity or a Date. Then all that happens is the Index page grows at the end...that's not hot-spot.
<1> well, we're beating a dead horse now
<2> someone asked here about bit columns and I told them every time I ran across them they caused performance problems
<1> If you're using a bit column, and in your query that is your only "where" criteria, yeah, that'll cause problems.
<0> i never actually used them so cant comment on them
<1> That'll cause a table scan for sure. :)
<2> I never realized they weren't fully implimented
<0> POWER2112: why?
<0> from what i understand the servers just use smallint for it?
<2> thus I never saw a point to them, i'd rather create char cols
<0> well internally smallint = char :)
<2> ...it was FASTER
<1> Let's say you have 10 million rows, with a bit column of 0 and 1. And you want to select everythign out where the bit column is 0... Well, to find all the 0's and 1's, SQL is going to scan the table to find out which one it wants
<2> why not the clustered index?
<1> I use Tinyints, if I want a 1/0 evaluation. You can index a Tinyint.
<2> hehehe
<1> lol
<0> well that doesnt have anything to do with the fact that you were using a BIT data type
<1> Sure, slap a Clustered on that Bit column
<0> that was just forced by teh where condition
<1> right
<1> That was my above point...if that's youronly "Where" criteria, you're going to be table scanning
<3> in SQL Server how can i check if there is an active connection to a specific database?
<0> asd22: use EM
<1> Otherwise, if you have multiple "Where" criteria and include the BIT column, that'll be fine.
<3> EM?
<0> enterprise manager
<3> but i need to this in my application
<2> EM gets all of it's data from the system tables, so if you can find the right one, you'll be golden
<3> so i going to execute a command to show me the connetions
<2> but there are a lot of binary encoded crap in those tables though
<4> whats more efficient, using nested queries such as select attr1 from table1 where attr2 IN (select attr2 from table2 where att3=123) or select t1.attr1 from table1 t1, table2 t2 where t1.attr2=t2.attr2 and t2.attr3=123
<3> there is any stored procedure to returns the active connections?
<2> quirr, totally depends on the size of the tables, but I'd think it'd be the first one
<4> any reason as to why?
<2> asd22, I'm sure there is, but I'd have to look at the BOL
<1> Quirr: I prefer doing an INNER JOIN. Otherwise, I'd go with your 1st query
<3> Guest1, could you help me to find it?
<2> the optimiser would love you if you wrote it the first way
<4> why is the 1st query more efficient than the second
<0> quirr: just run both and check which one performs better
<2> do a show plan and see for yourself
<0> lok at the execution plan in QA
<1> The 2nd query will actually attempt to pull back all the rows...and I've seen wierd things happen with that as well. It's not specific enough
<0> it all depends on ur data and index and many ohter things
<0> there no real rule
<1> SicLude: I totally agree
<2> and power, I don't think there's any diff between the INNER join syntax and the old school join in the where syntax, right?
<4> cause someone asked me that on an interview question
<1> The 2nd query was the old SQL 6.5 join, I believe. I've never been a fan of that
<4> and i wasnt sure how to answer it
<0> the optimizer will give differnt execution plans for the same sql depending on the data
<2> power, it's old school for most dbs
<0> heh i dont personally like using IN
<0> i like joins better
<1> Guest1: True...but in SQL 2000, I've seen it behave very strange, depending on the data.
<2> joins are the toughest thing an optimiser has to figure out
<2> ....especially outter joins
<0> heh if its properly indexed it shouldnt be that bad :)
<1> No, Stored Procs written by developers are the toughest thing the Optimizer has to figure out. :D
<1> I agree, Sic
<0> nah if hte developer knows to use stored proc then they are probably not that moronic and the code will be halfway decent
<1> lol
<0> but then again i have seen stored proc that basically does: exec 'select * from table'
<1> Then it's just up to us to go in and make the indexes work
<0> and that being the whole stored proc :)
<1> lol..yeah
<1> Me too
<4> and also whats the difference between foreign keys and referential integrity? foreign keys are used to ensure ref intregity right?
<1> Job security, I guess ;)
<1> Quirr: Yup
<0> heh ive been on an interview wher they asked me some sql question and they said my answer was bad when i know it was right
<0> :)
<4> it was like a true/false question, foreign keys is a more specific form of ref integrity
<1> You can enforce RI through the Procs and Transact, but in order to do that, you have to know EXACTLY what code is being written and account for it
<4> and the question didnt make sense to me
<0> like to figure out rows that are in table1 but not in table2.. i used a left join where is not null
<0> the guy said that doesnt work
<0> :)
<4> well i was applying for an entry level position and the guy interviewing me had a phd and like 10 years exp, so i didnt want to argue with him
<0> heh
<1> lol
<0> no there truly are morons out there that dont know enough but they are still interviewing
<0> like my last job.. my boss.. smart guy.. but he was interviewing me on sql questions.. i got them right and got hte job
<1> Well, and to be fair, Databases are SOOO vast... I freaking learn new concepts every day
<0> but afterwards.. it was like.. i knew more db then he did
<0> POWER2112: agreed
<0> but you cant just say something is wrong if you dont know if its wrong
<1> exactly
<5> how does one get mssql2005 to send emails?
<0> hmm i do remember reading something bout sending emails in 2k5
<0> where its built in
<5> do u have a url?
<1> catzai: You may want to read BOL...I think it goes into detail. Never done it myself
<1> I messed with SQL 2000 Mail...what a nightmare. I know they improved it in 2005.
<5> oh ok
<0> heh we used to just call dlls and do it :)
<0> in 2000
<6> SQLMail requires a MAPI account.
<6> Which means that the service needs to run under a user context with MAPI configured.
<5> a mapi account?
<5> in 2005 still?
<6> SQL2005 has DBMail which supports straight SMTP
<0> Halo_Four: cant you write the stored procs in c# and compile them? so you can just send the email from c# right?
<6> You could, sure.
<5> i dont see anything in dbmail
<5> in bol
<5> how does one write c# in sql2005
<0> catzai: i am sure theres hundreds of tutorials
<6> You write an ***embly using the appropriate attributes, compile to a DLL, use CREATE ***EMBLY to register the DLL in SQL, use CREATE PROCEDURE/FUNCTION/TRIGGER/AGGREGATE/TYPE and point it to the fully qualified member name.
<2> actually MOST MSSEs are pretty damn worthless
<2> oops, wrong window..heh
<2> laterz, I'm out to lunch'
<6> http://www.halofour.com/Code/SqlProcedure.cs.txt
<0> l8r
<5> thnx halo four
<7> SQL Server 2005: t-sql: If I have a select that returns one column of one row, then how do I use the returned value in an IF following the select? URL for tutorial is fine.
<6> DECLARE @variable DATATYPE; SELECT @variable = field FROM table; IF @variable = value ...
<0> damnit Halo_Four i was gonna answer it :)
<6> nyah nyah
<7> thanks Hao_Four!!!
Return to
#sql
or
Go to some related
logs:
Name of Female Assassin Killed by Mossad
#linuxhelp
#politics
#politics
#narcotics
#beginner
#freebsd
#firebird
#beginner
#nhl
|
|