Finding non-contiguous data

Question

Finding non-contiguous data

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

CELKO (12/6/2010)
A little change in the DDL makes life more relational. Never sue BIT flags in SQL; that is assembley language.

Since SQL Server doesn't actually qualify as a relational database according to the Rules of Codd, why bother trying to make it more relational? It's just a tool... not a religion. :hehe: And what's wrong with a little Assembly Language? Done correctly, nothing can touch it for performance. 😉

--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)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

CELKO (12/6/2010)
You cannot do much math with the IT data type -- it is a seriously restricted integer (i.e NULL-able and all the math operators) in T-SQL now, but it used to be a real bit flag. I don't care that much about storage these days.
The thing in MySQL is not a function; it is the use of zeroes to extend the DATE format -- "yyyy-mm-00" for months and "yyyy-00-00" for years. It is really neat notation.

I'm assuming IT data type was a typo for BIT datatype. It's beyond seriously restricted, it's a straight up flag, I agree with you there. It can be nulled in newer versions, but usually isn't. And you still didn't answer my question how a 4 byte flag vs. a 1 byte multiflag changes speed, optimization, or anything else. All the math operators, afaik, that you can do against them are basically implicit converts to tinyint by the optimizer. But then, why would I want to do math against a bitflag other than to turn it into a bitmask (fld1*1+fld2*2+fld3*4...etc). It's like anything else, if it's overused and overloaded, it becomes worthless. Used properly it's just fine.

For storage, no, I agree, storage itself is not the problem. Optimization and the optimizer are the issue, as is data transfer. Storing, and using, a Terabyte are two different issues. So, I'll half agree with you on that comment. I don't care about archived storage. Active storage is a finer issue. Not every server maxes out a 64bit OS's RAM, nor can every company afford a SAN.

Regarding the MySQL, I worked in it briefly, mostly for some repairs to a backend that was just kept around because there was little value in upgrading it. I'll have to look more into the notation and methodology on that. Seems interesting, if nothing else. Thanks.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA