Learning SQL on SQL Server 2005

khorner writes "I joined a local XP User Group in May of this year. As the IT Manager of Application Development for a 90+ year old agricultural cooperative, I'm introducing the concepts of agile development and need the support. Right off the bat, we've acquired some review copies of books and I volunteered for the O'Reilly book: Learning SQL on SQL Server 2005. I have been working with various versions of Microsoft SQL Server since 1999, so I figured I could give it a go." Read the rest of Kevin's review. Learning SQL on SQL Server 2005 author Sikha Saha Bagui & Richard Walsh Earp pages 325 publisher O'Reilly rating 4 reviewer Kevin Horner ISBN 0596102151 summary The organization and inconsistencies take away from the value of the book as a whole

Historically, I've found the O'Reilly books to be great references for professional programmers. I began with David Flanagan's Javascript: The Definitive Guide -- I think it was the 3rd edition. I enjoyed them for their reference value as well as business-oriented examples. Learning SQL on SQL Server 2005 does not, in my opinion, follow the mold I have become accustomed to from O'Reilly.

Learning SQL on SQL Server 2005 covers many of the topics necessary to introduce relational databases to the beginner. It is based on the authors' university course curriculum and it is evident with the review questions including with each chapter.

The authors cover important topics at an adequate depth for its target audience; however the organization needs some work. The first six chapters flip-flop across what I consider to be logical boundaries in a discussion on database development: schema versus data. Tools are a platform dependent subject necessary to discuss implementation.

The database provided could use some refactoring to get to a more cohesive and production level design. Not to be nitpicking, but as an example, equivalent domain level attributes for example, student number, are represented across tables as different column names. This is the attention to detail that drives me nuts on the professional level.

Chapter 1 sets the tone by touching multiple concepts and incorporates a smothering of screenshots. Over the first 25 pages (half being images and query result tables) we load the demo database, modify it, select from it, and cover to the Management Studio's syntax color coding and customization. Quite a lot to start off with for a novice, all with the assumption MS SQL 2005 is installed and ready to go.

Chapter 2 jumps into simple data selection of a single table and briefly hits the new MS SQL 2005 concept of synonyms.

Chapter 3 tries to focus on the schema oriented topic of table creation but falls short when jumping over to data topics like INSERT, UPDATE and DELETE. There is good coverage of data types, but we don't cover any design concepts of why we create tables and considerations for doing so. To the authors' defense, they state this is not a book on theory, but I think some level of theory is an important aspect to learn SQL.

Chapter 4 introduces the data selection concept of table joins and to do so, introduces the schema concept of keys.

Chapter 5 provides good coverage on internal functions for strings and dates and sets the foundation for more advanced queries.

Chapter 6 takes the reader through a logical process of developing a complex query. This is a good example process of taking a simple query and developing it further to satisfy a business need. Unfortunately, we experience some more inconsistency when we develop a join query using the WHERE clause - an inefficient and undesirable method the authors' discussed in chapter 4. Again, we jump from data concepts to schemas when we hit views and temp tables.

Chapter 7 through 10 present set operations, sub queries, and aggregate functions in a progressively logical manner. It would have been nice to have this progression prior to Chapter 6 and incorporate the concepts in the query development.

Chapter 11 throws in a thin coat of an introduction to table indexes and constraints: the final jump across topics.

Overall, the book provides an introduction to SQL topics. In my opinion, the organization and inconsistencies take away from the value of the book as a whole. If SQL is your profession (or you want it to be), with a list price of $44.99, Celko's SQL for Smarties is the better investment.

You can purchase Learning SQL on SQL Server 2005 from bn.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.

Comments

You poor soul.

[Anonymous Coward]

I joined a local XP User Group in May of this year.

Desperation leads some people to do strange things.

Re:You poor soul.

[P3NIS_CLEAVER]

This is the first time I didn't even bother to read the blurb much less the article.

Re:You poor soul.

[Pxtl]

Umm, he could mean Extreme Programming... we could only hope.

Re:You poor soul.

[Tekzel]

That elitist attitude does not look good on you. I do have to be honest though, I did cringe a little inside when I read that :( Does that make me an elitist bastard too?

Re:You poor soul.

[bersl2]

It's a reaction similar to the one that happens when you think that one program/environment "stole" another's feature. Yes, it's a cheap shot to ridicule MS because some of its users reach the same conclusion (and even cast it in the same image) that Unix and original PC users reached decades ago. Oh well...

Re:You poor soul.

[pete6677]

Hmm, I don't know which is worse: Windows XP or the stupid buzzword-inspired fad known as Extreme Programming (with an X for the acronym to make it sound cooler and eXtremer!). Thank god I've never worked somewhere that forces people to use this abomination.

Re:You poor soul.

[tobiasly]

I joined a local XP User Group in May of this year.

Desperation leads some people to do strange things.

I read that more along the lines of a meth users group. The kind where you have to stand up at the first meeting and admit in front of everyone, "my name is Kevin, and I'm a Windows XP user."

Re:You poor soul.

[rp]

Wait a minute ... the quote as I know it:

Greed makes people do strange things ... - Lucy

Reinier (know thy classics)

Re:You poor soul.

[Anml4ixoye]

Indeed, he meant Extreme Programming. I know, because I'm the one who receives and distributes the books for the group (and if anyone is in the Columbia, MO area, the mailing list is here).

And for the poster who thinks XP is just a buzzword - I'm at Agile 2006 right now with over 1100 people from companies ranging from Microsoft, Mozilla and Progressive Insurance to Shure (the microphone people), Seagate and lots of overseas companies. XP is alive and well (if arguably poorly named) and in a lot more places than one probably thinks.

Re:You poor soul.

[rowett]

christ on a bike, he *clearly* means eXtreme programming, not windows.

worst slashdot thread ever.

Re:You poor soul.

[Monkelectric]

It depends on how seriously you take XP. At its core, XP is mostly good advice, and you'd be surprised how many companies need their relatively simple advice. However, there are a lot of small minds who make it into a religion in place of thinking for themselves.

I went to an interview a few months ago and the place told me, "We tried XP but we like scrumm better because XP's cycle times are too short." A statement I found incredible because they clearly had missed the entire point of a methodology in the first place.

How Qualified?

[Anonymous Coward]

How qualified are you to read a learner's guide to SQL using SQL server when you have been using both for around seven years now? You aren't exactly the target audience...

Re:How Qualified?

[Bogtha]

He might not be the target audience, but how would you expect a member of the target audience to pick up technical errors in a book such as this?

If it, say, taught you how to do something in a totally non-standard way that only worked on SQL Server when the standard way worked just as well, then I'd consider that to be a pretty big failing in a book like this. But somebody just beginning with SQL wouldn't have a chance of spotting something like that.

Re:How Qualified?

[LurkerXXX]

Someone new to the field might not know what parts were missing that really should have been included. It's always better to have someone who knows the area do the review, in conjunction, if possible, with a newbie so that they can see what information transfers well, as well as what areas are lacking.

Re:How Qualified?

[SirCyn]

Mod parent up! The target audience for a educational book is rarely a good reviewer of the book. The parent points out one good example. But also, what if the book contained syntatical errors? How would a 'target audience' reviewer ever know the difference.

The review does a good job of recognizing that organization is the key to a educational book. All this information is publicly available (in MS's KB, MSDN, etc). The reason you're buying this book is because the information is supposed to be presented in a easy-to-learn format.

Re:How Qualified?

[Amouth]

yes but to be honest to we want something out there that uses bad form to teach beginners? they will only learn bad things and then come work with you and make you mad in the long run..

if we can flog an author into writing things right and teching off of that we save our selves the time of fixing dumb shit..

but with plenty of better books out here that don't have the obvious dumb errors it is better just not to recommend this one in hopes that the author woln't write more.

Re:How Qualified?

[Bake]

And this is different from the numerous MySql books that get reviewed here, how exactly?

If anything SQL Server behaviour is a lot closer to that of Oracle and PostgreSQL (and possibly DB2, I don't know) than MySql can ever hope to achieve!

Re:How Qualified?

[Kjella]

Mod parent up! The target audience for a educational book is rarely a good reviewer of the book. The parent points out one good example. But also, what if the book contained syntatical errors? How would a 'target audience' reviewer ever know the difference.

An expert is also rarely a good reviewer of a book, at least alone. He's great at checking the educational quality of the book, in terms of teaching "best practise", technical correctness and scope. But I've also met many such a book which fail to be a good teacher, even though it's probably theoretically correct. Experts miss that because it comes natural to them, they automatically understand it the way it's meant to be understood and think others will see it their way too. In reality it is often ambigious, poorly structured and confusing for those who lack that basis. Basicly, there are two completely separate questions:

Does it teach correctly?
Does it teach well?

Let the experts answer the former, and let the target audience answer the latter.

Re:How Qualified?

[AndrewNeo]

Everyone's going to make fun of it because it's based on a Microsoft product.

Re:How Qualified?

[Bloke down the pub]

Let the experts answer the former, and let the target audience answer the latter.

I often say that the best book on a subject is usually two books, so what one does badly the other might do well & vice versa.

Probably true of reviewing too. At least for educational books, there are two roles - student & teacher - with different perspectives.

Re:How Qualified?

[Bogtha]

You're reading far more into my comment than is actually there. When I mentioned non-standard syntax, I wasn't implying that SQL Server was particularly non-standard, merely that the opportunity is there for a book like this to make a mess of teaching things.

It isn't different for MySQL books, and if somebody had posted the same comment in a MySQL book review, I'd have said the same thing.

Re:How Qualified?

[INeededALogin]

the describe works on MySql and Oracle, is to standard, but doesn't work on SQL Server(at least the last version) and is aliased with \d on Postgres. But.. sp_help works on SQL Sever for getting the same information which doesn't work on MySql, Postgres, and Oracle so... give some counter examples of how SQL Server is closer to Oracle and Postgres.

Or... are you talking of some other Database behavior?

Re:How Qualified?

[porl]

the better you teach someone the wrong way to do something, the better they will learn it...

Re:How Qualified?

[SirCyn]

Definately agree with that, both opinions would be the most useful.
But if you have to choose one over the other, which would you rather have (remember it will be published on /.)?

I would rather take the experienced user over the student in most cases. The OP seems to see things differently.

SQL apis suck.

[aersixb9]

Why does the API for the databases suck so much? I've been using ADO.net with C# .NET 2.0 on SQL 2005, and why must I use the arcane and shitty SQL language to save my web objects in a datafile? Isn't there an option somewhere between writing 25 lines of SQL code for each action for each object (either in code or in a SPROC), and managing the files myself on the local HD, which most webhosts won't allow? How about a nice simple DBObject that my other objects can inherit from, that provides a nice 'SaveAllInTable(String table)' method, that replaces the INSERT INTO VALUE for each )*(&%# variable in my object? (and other similar SQL wrapper methods...I guess I could write that, but it would be a PITA, and that's what APIs are for, anyways!) Unless there's a good C# SQL wrapper that I don't know about...the datatable and datagrid isn't useful for anything more than displaying a raw table, something that a webapp usually shouldn't do, anyways...Of course, streamreader and streamwriters aren't much better, perhaps we need a good shared file i/o api that's better than SQL?

Re:SQL apis suck.

[pixelpusher220]

have you looked at Strongly Typed Datasets?

personally I don't generally like prepacked stuff at that level, since most complex systems aren't just simple CRUD operations.

But if that's all you need it's pretty much out of the box.

Re:SQL apis suck.

[aersixb9]

I just want to save my customer's orders and messages, then load them. Why must this be so complicated??? In the old days, before we had these fancy 'shared webhosts', I could simply create an 'order' object, with fields such as price, shipping address, billing info, etc (floats and strings), then...uh...I guess in the old days I had to streamwriter(price);streamwriter(shipping);streamw riter(billing), etc, for each object, which kind of sucked when I made changes to the object's variables...the same is true of SQL 2k5, I have to make an SQL query for each primitive type in the object, I can't just SQLWrite(object, table, database);...for some reason...actually, I'll just stop complaining and go write that exact method. Does anyone want to purchase a function? (Strongly Typed Datasets may or may not solve that...I'm actually not familiar with them, although they did not look useful from the C# Station tutorial :( Plus, XML scares me, and everything that uses XML or any other standard is almost always useless) Oh, and on second thought, it's not exactly 100% clear to me how to parse through an arbitrary object and store the variables from it in C#...without tweaking the compiler?

Re:SQL apis suck.

[Anonymous Coward]

Have a look at Hibernate, or wait a few months for LINQ.

Hibernate is a big pill to swallow to start with, but it's dead powerful...

Re:SQL apis suck.

[Anonymous Coward]

Check out object relational mappers. nHibernate (.NET version of Java's Hibernate) comes to mind.

Re:SQL apis suck.

[CastrTroy]

It's called a database abstraction layer. You have to build your own objects that support saving themselves to the database. It's not as much work as it sounds like, and will actually save you a lot of trouble in the long run.

Re:SQL apis suck.

[Kunta Kinte]

...why must I use the arcane and shitty SQL language to save my web objects in a datafile?

Because you are retrieving the data from an SQL Database?

As others have pointed out, there are many ways to do this. Another method that may work for you, depending on your situation, is the server's XML capabilities, eg. "SELECT ... FOR XML ..." will convert your dataset to XML that you can easily serialize.

Re:SQL apis suck.

[killjoe]

Microsoft is philosophically opposed to ORM layers. That's why they have never provided one with ODBC, ADO, DMO, .NET or whatever TLA they ever came out with. MS believes that you should use stored procs for pretty much everything. For example when they re-did petshop they loaded the entire thing with stored procs because it provided faster performance.

Re:SQL apis suck.

[Hangtime]

Hmmmm...
Go check out OPENXML in the BOL. SQL Server allows you to send an XML string, shred it, store it in temp tables and in the end process it into the database without having to write multiple insert statements. You can move up and down the XML tree storing multiple transactions and additional datapoints as needed for instance an order and all its associated items as one XML string sent to SQL Server which then processes it. Since I do not come from the programming side of the tracks (DBA / SQL programmer really) I'm not quite sure why you would want to manage files on a database anyway. You can also check out the following newsgroup post I made discussing the issue. Hope this helps.

http://groups.google.com/group/microsoft.public.sq lserver.programming/browse_thread/thread/361f14c2b b73149b/8f0aa6a958cbaae2?lnk=st&q=&rnum=1&hl=en#8f 0aa6a958cbaae2

Re:SQL apis suck.

[bgraziano]

Actually MS is getting ready to introduce something very much like an ORM layer called LINQ/DLINQ. You can find out more here: http://msdn.microsoft.com/data/ref/linq/

Re:SQL apis suck.

[caseih]

I think the main problem for you is that SQL and relational databases were designed to store relational database data, not be a persistant store for objects. Indeed if you use a database as it was designed to be used, SQL is highly appropriate as a query language.

I think Microsoft is correct, as stated in another post, that ORM is inherently flawed. It may be the best thing we have, but shoe-horning database data into classes and vice versa is problematic. The time it takes to mess with Java's XML mapping files, for example, often exceeds the time it would take to write some decent stored procedures, use proper database views, and then do simple SQL queries directly. And I believe it is no less manageable or scaleable.

What you are looking for is really just a better persistant-object storage scheme. It doesn't matter what the backend is like, so long as your objects are accessible in an easy and logical fashion.

Re:SQL apis suck.

[ThatDamnMurphyGuy]

> Microsoft is philosophically opposed to ORM layers

They are? And what exactly are the strongly type dataset with TableAdapers in ADO.NET 2.0? Smells like ORM stuff to me.

Re:SQL apis suck.

[eggsovereasy]

How about you write a stored procedure to update your "Order" object (or whatever), then put a method in your Order class that calls said stored procedure then you just.. Order.X = X; Order.Y = Y; Order.Z = Z; Order.Update(); And its done. You write one stored procedure and the one method in Order and you can update everywhere easily.

Re:SQL apis suck.

[mortonda]

Isn't there an option somewhere between writing 25 lines of SQL code for each action for each object (either in code or in a SPROC), and managing the files myself on the local HD, which most webhosts won't allow? How about a nice simple DBObject that my other objects can inherit from, that provides a nice 'SaveAllInTable(String table)' method, that replaces the INSERT INTO VALUE for each )*(&%# variable in my object?

That what ORM classes do.... ActiveRecord works great in RoR.

Re:SQL apis suck.

[killjoe]

Maybe you are confusing "smells like" with "is". Is it an ORM or is it not? Why don't you compare it to nHibernate and get back to me.

Re:SQL apis suck.

[Sentry21]

The problem with your concept is that the application has no concept of the format of the table(s); if it did, it would have no idea what you want to do with it. Most SQL databases are not object-relational databases (Postgres is), and thus have no way to hold advanced concepts on their own. As such, you are required to break your relations down into normalised forms on your own, and write the appropriate INSERT or UPDATE statements for each.

The 'obvious' solution is to write this code once for your object and then implement it into a storeToDatabase() method for your object, along with a populateFromDatabase() method, populateFromDatabaseWithId(), populateFromDatabaseWithLatest(), and so on. This is made easier (though not necessarily easy) by moving work into separate functions, and having each function call the ones that it inherently requires.

The problem with the concept of abstracting ('wrapping') SQL in a programming interface is that it abstracts you from the features of the database which may not be standard, thus reducing you to the lowest common denominator (I believe that PEAR::DB in PHP has this problem). That said, a proper database interface (PDO is a good example) provides a standard set of functions along with the enhanced features of the database by extending the standard class.

That said, you still are forced to write your SQL by hand; either way, I don't see this as a huge problem necessarily, but then, I do more SQL work than I do programming, so I may be biased. For a programmer who doesn't want to learn SQL, who has a difficult time grasping the proper methodology, or who just doesn't have the time, it can definitely be a problem.

That said, your assertion that SQL is either arcane or 'shitty' isn't particularly educated. SQL is powerful, and the standard is more active than many other standards. SQL does what it does very well; the problem is that it does not do what you want it to do. Whether it should or not is a different argument.

It sounds like what you want is something more along the lines of OS X Tiger's Core Data API. Core Data allows the programmer to (semi-graphically) define objects and relate them together, and then bind those data times/formats/fields to your interface, your variables, and so on. Essentially, you define your object, and then the system takes care of loading/saving it. One of the save options is even an SQLite database, so obviously it wouldn't take much to extend this to other RDBMS implementations; whether that will ever happen, however, is a different question.

Unfortunately, even if I'm right and this is what you want, you can't have it. Sorry. It's only in Tiger, and it doesn't handle any other data formats except for XML, a proprietary binary format, and an SQLite database. You can't interface with any other SQL servers unless you're going to write an app to read/write the SQLite database and move that data over, and that would be even more work in the first place.

Hopefully people will realise the potential that a system like CoreData has and provide a way to duplicate it to other systems. Until then, we can only wish.

Re:SQL apis suck.

[Anonymous Coward]

You may want to check out the ADO.NET Entities that are coming.

http://www.google.com/search?q=ado.net+entities

Re:SQL apis suck.

[Jerf]

Why does the API for the databases suck so much?

SQL is now my canonical example of a "Good Enough" technology, which holds back any sort of improvement because there's no fast and easy way to make something immediately obviously better, even though it'd probably be really easy to make something that would be better over the medium or long term. Unfortunately, new technology needs that snappy I couldn't do that before! demo to really take off. (Most recent example: Ruby on Rails. And I say that as someone who isn't really a Ruby fan.)

The beginnings of SQL date back to the 1970s, and even in modern times it shows. People hadn't yet figured out how to deal with "NULL"s in any reasonable manner, and SQL has the dumbest NULL in any language I know of that is still in common use. People still thought empty lists were something exceptional, so "SomethingID IN ()" is an error, at least in the DBs I use, instead of a clause so obviously false the optimizer ought to positively jump for joy when it sees it. The syntax is fruity and unforgiving by modern standards, and the actual relational stuff that is supposed to underlie it all continues to be masked by the performance limitations in place in the 1970s; performance hacks now enshrined as the One Right Way to do things.

SQL sucks, because so much effort has been invested into it that no possible fix to it could ever get traction, and SQL is so wrong that trying to bend your new system to be backwards compatible with it will probably break your new system. The only hope we have is a brand new paradigm, and frankly those haven't been faring too well either, so far. But hope springs eternal.

Someday this mess will be resolved, but your guess is as good as mine as to which direction it's going to come from. SQL-the-language sucks, but it still manages to set the bar pretty high for any sort of technology to replace it, even just as a language-switchout on an existing DB server. (I mean something actually not SQL, not SQL adapted to Yet Another Procedure Language or SQL adapted with Yet Another Proprietary Extension.)

Re:SQL apis suck.

[blaster151]

That may be changing. Check out their recent webcast on the upcoming "ADO Entity Objects Framework," which looks like it's shaping up to be a formal ORM product.

Re:SQL apis suck.

[killjoe]

Wow that's amazing. Here it is the year 2006 and they are finally getting ready to introduce "something that's very much like an ORM layer". Where were they for the last decade?

From looking at the descriptions it seems like a typical MS project. Too complicated for it's own good, trying to produce a tool that will solve every problem known to mankind.

BTW if you know anybody on this team could you please urge them write real error messages? I work with SQL server every day and I curse the team every time I get a "string or binary data would be truncated" or "overflow" or "syntax error on line 387 near ')'" errors. First of all WHY DON'T YOU TELL ME WHAT FUCKING FIELD YOU ARE HAVING A PROBLEM WITH YOU FUCK! and WHY DON'T YOU GIVE ME THE ACTUAL LINE NUMBER INSTEAD OF THE LINE NUMBER OF THE PARSED STORED PROC YOU FUCK.

My entire day is filled with cursing the SQL server team and I know it's not going to get better when we move to 2005, if anything it's going to get worse because we will be piling on assemblies on top of a pile of shit.

Re:SQL apis suck.

[Alternate Interior]

In PHP, I've taken to using a database purely as a persistent store. A few hundred lines of reflective code to ascertain the data model and generate the SQL for it has now saved me weeks of work across many applications.

The trouble with it is generic code of that nature doesn't scale especially well. To make it scale, you lose the reflection, which pushed you right back to 25-lines of SQL to a model.

Personally, I'd rather save that time during initial development and then re-visit it later in the production cycle when obsticles are more clear. Many inevitably disagree.

I don't know enough about .net's implementation of reflection to provide code, but if you'd like to email me I can provide my PHP implementation.

Re:SQL apis suck.

[dividius]

"why must I use the arcane and shitty SQL language to save my web objects in a datafile"

- You musn't, get one of the half-dozen ORM's people have built for you. Some are even *gasp* open source. *Shhhhhh, don't tell anyone.

"the datatable and datagrid isn't useful for anything more than displaying a raw table, something that a webapp usually shouldn't do, anyways"

- Um... a brochure-ware web site doesn't usually, but a web app??!? I'm thinking... a table showing shopping cart items, a table showing employees, a table of properties in your neighboorhood, a ... surely you get the idea by now? Your misunderstanding stems from the word raw. (Not that I am standing on the mountaintop proclaiming the datatable the one true way.)

Re:SQL apis suck.

[computational super]

the server's XML capabilities, eg. "SELECT ... FOR XML ..." will convert your dataset to XML

Ah, good. I've been waiting for a long time for somebody to relieve me from the mind-boggling complexity of inner joins, subselects, triggers, referential integrity rules and stored procedures by adding DOM, XSLT, XPath, DTD and XmlSchema on top of inner joins, subselects, triggers, referential integrity rules and stored procedures.

Re:SQL apis suck.

[Anonymous Coward]

With SQL Server 2005, you can create a stored procedure and publish it as a web service using the native tools. This makes it almost point-and-click to build any type of functionality you want then then consume it without needing a lot of proxying services between code and database. Definitely better than the 2000 version of the product.

Re:SQL apis suck.

[utopianfiat]

This is what I hate about database programming. You have to look at STDs and operate with CRUD all day.

Re:SQL apis suck.

[recklez]

It's not the API. It's the programmer ...

Re:SQL apis suck.

[Anonymous Coward]

"In the old days"? You sound like a whiny ADD teenager who is ignorant of what is available and doesn't care to learn, instead preferring to whine "Why is it so haaard?". Try using paragraph breaks and editing instead of posting one long rambling stream of consciousness too, you'll sound twice as mature as you do right now.

Re:SQL apis suck.

[canozmen]

I'll second that. Take a look at the best practices/etc. examples that MS puts on the web. They usually have a database access and a business logic layer. If you have a single source of data and a number of application that depend on it (like a visualization, bookkeeping and a numerical numerical analysis app like we had), it is certainly the way to go. Just be careful when you are using vendor specific features or syntax in your stored procedures, or be prepared to code the differences into your DB layer later if you decide to switch DB vendors. That's why you spent the time coding the DB access layer in the first place!

Re:SQL apis suck.

[Anonymous Coward]

Bad error-messages? You do not want to use Oracle then. Boy they are bad (in a bad way).

Re:SQL apis suck.

[killjoe]

Never used oracle. Postgres gives beautiful error messages that tell you exactly what went wrong and where. It's so much better then SQL server in every way It's not even funny. Too bad nobody from postgres took my CIO out for golf and drinks though.

Re:SQL apis suck.

[GunFodder]

Interesting... Oracle does the same thing. I guess when you have gotten fat charging obscene prices for cookie-cutter software you tend to lose focus on the little things, like helpful error messages. This is one of the main reasons I hate PL/SQL and T-SQL - debugging them is painful.

Re:SQL apis suck.

[Javaman59]

Since the 1970's there have been 1000's of PhD's in "databases", and 1000's of academic careers, syposiums, journals editions, etc.. in "databases", and I'm sure that most of these haven't been to do with SQL, yet SQL is still the industry standard.

As you say...SQL is now my canonical example of a "Good Enough" technology, which holds back any sort of improvement because there's no fast and easy way to make something immediately obviously better,

Re:SQL apis suck.

[mtriley]

Look into Hibernate. It's an open source OR-mapping tool. I've never used it with .NET, but I've used it with Java and it does a great job of abstracting out all of those SQL queries and lets you just save an object to the database. I think the .NET version is called NHibernate.

Re:SQL apis suck.

[nuzak]

Also take a look at db4o, which gives you dead-simple object persistence without the rdbms underneath. Makes perfect sense when you're not actually using relational data, just storing objects (like 99% of rails-type apps do). As a bonus, it can replicate to Hibernate, which is perfect for reporting and data mining (personally I'd just cut the middleman and use Hibernate on both ends, but their replication is designed more for lots of small cache clients pushing changes into a bigger db).

Re:SQL apis suck.

[a.out]

Unless there's a good C# SQL wrapper that I don't know about

The Entity Framework in ADO.Net 3.0 combined with Language INtegrated query (Linq) C# language extensions is going to change how you write data based applications. Dispite my obvious bias this is one of the coolest things I've seen in quite a while.

LINQ and ADO.NET Entities: Video Podcast
http://channel9.msdn.com/Showpost.aspx?postid=2021 38
Whitepaper:
http://msdn.microsoft.com/vstudio/default.aspx?pul l=/library/en-us/dnvs05/html/nxtgenda.asp
Screencast: (see the code)
http://blogs.msdn.com/adonet/archive/2006/07/11/66 2454.aspx

Re:SQL apis suck.

[WED Fan]

Well, almighty you, you may have identified a need there. So, tell you what, instead of getting on your bitch-wagon, sit down, write a new access layer, and make it easier for folks.

I've noted that 2005 contains a number of solutions that 3rd party's developed to ease the way on 2000.

VS2005 contains a lot of ideas that 3rd party's came up with, and were paid handsomely for. An aquaintence made a bundle when MS bought him off. He's a self-employed consultant now.

...I'm sorry, did that sound good? Or, was it your intent to just complain to hear yourself be self-righteous?

Re:SQL apis suck.

[Profane MuthaFucka]

Oracle messages are great! Ok, maybe they are not. Could be worse though. If I recall correctly (I have sex with Postgresql every day. Haven't been screwed by Oracle for a few years now) the error messages are a letter and then a 4 or 5 digit number. You have to look in the right section for the letter, and then the messages are in numerical order in the book. I suppose they could have put the messages in alphabetical order by Roman numerals. Yes, that would be slightly worse than what they have now.

Re:SQL apis suck.

[Profane MuthaFucka]

"Lonestar, now you see that evil shall always triumph, because good is dumb"

Re:SQL apis suck.

[SpryGuy]

Trust me, strongly typed datasets sound exactly like what you want. It's mostly auto-generated stuff, so you don't have to write all that stuff yourself. Still, they're not perfect. My company wrote this great layer over top strongly typed datasets, and when I want to save or load an object, I basically don't have to write any additional code. Simple templates exist for creating new types... just point at the database table, push a button, and boom, all the properties are generated and written for me. Reading an object into memory is as simple as using array-indexer syntax in C#, passing in the primary key(s). Saving an object is as simple as calling Commit() on it.

Anyway, read up on Strongly Typed Datasets, and how you use them via the VS2005 IDE, and I think you'll be a lot happier than you are, even though you won't be perfectly happy.

Re:SQL apis suck.

[SpryGuy]

Amen brother. I just had another one of those today. It took hours for me to track down the problem, when it could have and should have not only told me what column (or even row) it was having problems with, and what constrataint it was having problems with. The error message reached a great new level of vagueness (paraphrasing here, but something like "a row or rows has a column that violates a uniqueness or not-null constrant or foreign key constraint"... I mean COME ON, you can't tell me which exactly constraint is complaining, let alone even what KIND of constraint, let alone the actual row or column or anything? UGH!)

Re:SQL apis suck.

[BlindRobin]

Bravo,Here Here, I wish I hd said it as succinctly as you, I never get past I Hate SQL simply because it is an abomination.

Re:SQL apis suck.

[mycall]

Maybe humans won't fix this mess but instead the machines will.

Re:SQL apis suck.

[Anonymous Coward]

or gentle.net

Re:SQL apis suck.

[Anonymous Coward]

i somewhat agree with the parent. but i also like processing XML to update database tables, via openxml. it saves writing tedious data abstraction layers. the client receives a table (as a ado.net datatable perhaps), updates the table, and sends the table as xml to the server. simulating the ado.net dataadapter concept, but a bit more flexible.

its actually easy. you just turn your xml into a table variable and then join to / insert from your table variable, rather than xpath syntax scattered throughout the proc.

on the other hand, this technique is not advisable for very large xml documents, as SQLServer only allocates 1/8th of the heap to XML processing i seem to recall reading somewhere.

Re:SQL apis suck.

[Anonymous Coward]

treat this with caution. SQLServer (2000) I believe only allocates 1/8th of its heap when processing XML documents (via OPENXML). thus don't use openxml for large sets of data, or with a large number of users.

Re:SQL apis suck.

[trezor]

How about a nice simple DBObject that my other objects can inherit from

In SQL Server 2005 you can use .NET classes as datatypes in the DB, and then just pump your objects directly into the DB. Not that I would ever recommend it though. But it can be done.

Or you could, as others have suggested, use strongly typed datasets. If you are writing lots of manual queries in C# when using ADO.NET, you are probably doing something wrong.

Re:SQL apis suck.

[trezor]

To be honest, I don't think using any of SQL Servers XML-features will make anyone's life any simpler, at least not in 99% of the cases.

T-SQL is a language designed for set-based operations, not fine detail-level data-shredding. Yes, it can be done. No, it doesn't look pretty.

Re:SQL apis suck.

[Waylander1970]

You should be looking at NetTiers, it does all of this and it fits in your chosen language (C#, SQL). Point it at a database and it will extract the relations, table structures and stored procedures and then create a very capable data abstraction layer so all of your data is accessible via objects and you even get enums built from tables you select (so you can link your table data with your code instead of maintaining your own list of enums which might get out of sync). I've saved months of work on projects by using it and thoroughly recommend it. Go to NetTiers.com and watch the demo, the code itself is open source although it depends on CodeSmith which is not. Microsoft are belatedly doing something similar with Visual Studio but it is heavily linked into Team Server and it is probably going to be expensive. The only downside is that you need to buy CodeSmith but then you could use a trial version for now.

Re:SQL apis suck.

[Krimszon]

Check out: Typed DataSets, LINQ and ADO.NET Entities. The last two technologies are under development. In the mean time, how about NHibernate, or ActiveRecord.

It wouldn't make sense for a relational database to do this, so you will always need an abstract layer in between. The ADO.NET API gives you direct access to the relational data, the aforementioned layers will abstract it for you, where LINQ uses new language constructs to try to bring queries in a natural syntax to your programming.

Re:SQL apis suck.

[Anonymous Coward]

you are dribbling

Re:SQL apis suck.

[adolfojp]

What you want is an Object Relational Mapping Layer and generator.
For the .NET framework or MONO try dOOdads with MyGeneration. It is free. Don't let the names fool you. That is some enterprise quality software.
You get support for 12 databases and get to write code like this without having to write config files or xml definitions.

// Load and Save
Employees emps = new Employees();
if(emps.LoadByPrimaryKey(42))
{
emps.LastName = "Just Got Married";
emps.Save();
}

// Add a new record
Employees emps = new Employees();
emps.AddNew();
emps.FirstName = "Mr.";
emps.LastName = "dOOdad";
emps.Save();

http://mygenerationsoftware.com/

Disclaimer: I don't work for the company, but I believe that their free solution answers the parent's question.

Re:SQL apis suck.

[port119]

check out NHibernate
www.nhibernate.org

Re:SQL apis suck.

[TheLoneGundam]

Awww - you actually have to do some coding, instead of all the nice objects being built for you? I'm sorry for you, you have to actually work for a living.

Re:SQL apis suck.

[Anonymous Coward]

Use Java and Hibernate instead and just load objects and save objects directly

Re:SQL apis suck.

[markh100]

How about Microsoft ObjectSpaces? Lets have a detailed look into the Microsoft ObjectSpaces, an upcoming OR mapping tool from Microsoft.

SQL WAS okay, until..

[Asshat_Nazi_v2.0]

I was still in High School, I had a big cock and was horny all the time, jerked off at least 3 times a day. My body is small and slim with very little hair, 5"4",125lbs. My fat cut 7" cock looked huge on me. I had been jerking off thinking about gay sex lately, I was very turned on by the fantasy of having sex with an older man, and having a cock in my ass.

I got a job working after school and weekends at a antique shop, it was ran by 2 older gay gentleman, very nice gentleman who were always flirting and teasing me. An older very distinguished looking handsome customer came in the store, he was a silver haired fox who looked like he had money.

The owners knew him well, he bought a small end table and asked the owners if I could help him unload it at his house, I thought this was kind of suspicous since it didn't weigh much but my horniness and curiousity made me jump at the chance. We rode in his SUV to a big house in a ritzy neighborhood and I carried the end table into his house. He gave me a tour, it was huge and very nice, there was an indoor hot tub and he asked me if I wanted to soak for a while, I told him I didn't have a swim suit and he laughed and told me I could go without, he always did.

I was getting turned on so I started to undress, my tank top came off first and my back was turned to him and I pulled down my cutoffs, no underwear and bent over to finish removing my cutoffs, it was a turn on to expose my ass to him, he watched me climb into the hot tub, my cock was rock hard. I watched him take off his shirt, he had a sexy chest covered with silver hair, he pulled down his pants and underwear in one motion exposing a beautiful 8" cut cock, very fat. We sat in the tub for five minutes talking, he asked me if I wanted a massage, I moved over close to him with my back to him and sort of sat on his lap, I could feel that big cock, I started moving my ass around until it was between my cheeks, I moved up and down, it felt so hot, made my asshole spasm. He was rubbing my shoulders and back, he reached around and started massaging my inner thighs making my cock twitch, finally he started stroking my cock, I was so turned on it was all I could do not to cum. He had me stand up and started tonguing my ass while stroking my cock, I was in pleasure overload and exploded cum after about two minutes of this.

We went into his bedroom, still naked and dried off, he put his hands on my shoulders and gently pushed me to my knees, grabbed the back of my head and guided me to his cock. I sucked on it hungrily feeling it get harder in my mouth, when he was rock hard he guided me to the bed and had me lay on my stomach. He ate my ass again this time harder, getting his tongue up inside me, this made my cock hard again, I relaxed and felt my boypussie open up. Next he slowly inserted one of his fingers , it kind of hurt at first but then I started to love the feeling. Two fingers was next with some lube, he two finger fucked me for along time, I loved how it felt, like I was getting stretched. I was moaning and moving my ass up and down.

He stopped and put his big cock back in my mouth, I sucked him for maybe a minute and he pulled out and rolled on a condom, had me get down doggie style got behind me and pushed that big cock head against my tight hole. He slowly pushed, I thought it was to big and would never fit, all of a sudden it popped in, the sensation took my breath away, it felt so huge and it hurt a little, but I was starting to relax and it was feeling better by the second.

He slowly pushed in until he was deep inside me and moved in and out very slowly to start with, it still burned but the thought of getting fucked, having a big cock inside me was such a turn on.

He fucked me for a long time, after I got used to it and fully relaxed the feeling was pure pleasure. My cock was rock hard.

The pace got faster and harder, finally I came again, without even touching my cock, such intense pleasure. He came and stayed inside me, I layed flat on my stomch with him still inside me, he slowly went limp, slipped out of me and rolled off me.

Re:SQL WAS okay, until..

[Anonymous Coward]

Thanks, I enjoyed that.

Slashdot wants to know

[Anonymous Coward]

"I joined a local XP User Group in May of this year."

On purpose?

Re:Slashdot wants to know

[__aaclcg7560]

There probably wasn't a Linux or Mac User Group around. It's either the XP User Group or WoW Anonymous.

What vendor?

[GigsVT]

Many different vendors make DBMS services that speak SQL.... What product is this talking about?

Re:What vendor?

[TrappedByMyself]

Many different vendors make DBMS services that speak SQL.... What product is this talking about?

I'm not sure, but the few dozen or so references to "SQL Server 2005" makes me believe it's talking about SQL Server 2005.
I could be wrong though.

Re:What vendor?

[GigsVT]

Which SQL Server? There are literally hundreds of servers that do SQL.. I just don't get it.

SQL...? agile...? YES!

[abscondment]

I think he's in desperate need of this:

SQL on Rails

Re:SQL...? agile...? YES!

[trezor]

Nice of you promoting something requiring MySQL in a Microsoft SQL Server 2005 related article.

Do you see me going around MySQL & PHP related threads promoting ASP.NET & MSSQL? No? There is a reason for it, you know. Its because it would be unwanted and unuseful zealotry.

So... Why isn't parent mod'ed off-topic yet?

Re:SQL...? agile...? YES!

[00lmz]

Because it's funny?

  For your database, MySQL is the only stable option for SQL on Rails at the moment--the development team decided to tackle the gold standard first. However, work is underway to add support for a few more databases: PostgreSQL, SQLite, Oracle(TM), SQL Server(TM), DB2(TM), Firebird(TM), Fox Pro(TM), FileMaker Pro(TM), Lotus Approach(TM), XML(TM), Ingres, Sybase(TM), Informix(TM), GNU(TM) gdbm, Oracle Berkeley DB(TM), and Microsoft Excel(TM). Just about any operating system will do, but we recommend one with Minesweeper.

Re:SQL...? agile...? YES!

[abscondment]

Wait...

Did you think this was a *real* product? Yes? There's obviously a reason for that, too -- ignorance.

This site is a spoof, making light-hearted fun of Ruby on Rails, Web 2.0, et cetera. The part of the joke is that you shouldn't need any specific database in a real agile environment. Then again, jokes that are over-explained wind up being less funny.

I'm more of a Postgres guy, anyway, and I think PHP is the spawn of the devil. If you're interested in understanding, do a quick comparison of the following sites, and come back with a worldview that goes beyond the purview of your toolset.

• Ruby on Rails
• SQL on Rails

When the article's second sentence indicates the author's desire for help with agile development, my post is considered FUNNY .

Re:SQL...? agile...? YES!

[khorner]

I like it. Send me a copy of "Fragile Web Development with SQL on Rails" and I'll get another review up. I'll share it with the Windows XP user group when I'm done. Removing the VC from MVC. Fantastic. A totally new and eXtreme Pattern you need to get the GoF to look at.

Learning SQL on MS SQL is like...

[TheSimkin]

Learning to drive a car by riding a bicycle. Sure it's totally different and the bicycle doesn't require you to follow any of the standards but it still gets you somewhere...

Re:Learning SQL on MS SQL is like...

[vmcto]

I couldn't possibly disagree more...

I'm no MSFT fanboy, but SQL Server (I don't count 4.2 which was basically a direct port) makes it very easy to focus on learning basic DB design, query development, and even a little performance tuning.

With few "knobs to turn and buttons to push", the beginning DBA wannabe can do stuff without needing to know a lot of gory details.

I am by no means advocating that top-notch DBAs don't need to eventually know all the gory details, along with a bunch of theory, just that for someone new trying to understand DBs better the tool makes it fairly easy to do so.

I knock MSFT all day long for lots of reasons, but SQL Server ain't one of them...

Re:Learning SQL on MS SQL is like...

[beavis88]

You must have some karma to burn :)

I agree, though - I'd recommend SQL Server as a teaching tool over MySQL, Postgres, or Oracle any day, assuming price/licensing wasn't an issue.

Re:Learning SQL on MS SQL is like...

[jkroll]

I agree, though - I'd recommend SQL Server as a teaching tool over MySQL, Postgres, or Oracle any day, assuming price/licensing wasn't an issue.

I'll disagree with that statement as far as Postgres or Oracle are concerned. While I can't speak for SQL Server 2005 (haven't used it yet), but previous versions of SQL Server required special settings for correct handling of null values and arithmetic exceptions. Because most tutorials don't mention these settings when creating a database (nor are they accessible as part of the wizards), most people don't realize they haven't set up a standards conformant database.

I feel it is more important for a teaching database to conform to the standards than just have easy to use GUI tools.

Re:Learning SQL on MS SQL is like...

[cruachan]

Parent has been modded insightful? Why?

We all like to knock Microsoft periodically for it's well know shortcomings, but SQL Server is not one of them. In fact it's one of the best products Microsoft produce. I've worked with Oracle, DB2, SQL Server, MySQL, Postgres on various products over the past decade or two, often as DBA, and SQL Server is a gem.

It's a highly complient database and the SQL conforms to every standard going. In fact of all the databases I've worked on it's possibly my favourite as it can be installed on clients servers and left to it's own devices and it will continue to chug away with minimal human maintenance. Put that in your pipe and smoke it Oracle (or Postgres :-)

I'm not a MS fanboy by any means, but SQL Server is an excellent product and to declaim otherwise just shows up your lack of experience. My guess is you've probably confusing MySQL with a RDBMS and extrapolating from there :-)

Re:Learning SQL on MS SQL is like...

[Anonymous Coward]

I knock MSFT all day long for lots of reasons, but SQL Server ain't one of them...

How about for SQL Slammer? :P

Re:Learning SQL on MS SQL is like...

[Shados]

I agree with you, but pricing/licensing IS not an issue. SQL Server 2005 Express is free. Limited to 1 CPU and 4 gigs databases, mind you, but if you need more than that as a teaching tool, I wanna know what the hell you're learning :)

Re:Learning SQL on MS SQL is like...

[DelawareBoy]

That's Wrong... NUlls and Arthimetic items can be changed by going into the Enterprise Manager and altering the properties. No SQL needed. Maybe not a wizard, but you can do it in a GUI.

Re:Learning SQL on MS SQL is like...

[Anonymous Coward]

Yeah, maybe SQL Server is pretty good but it runs on a total pile of crap OS. It's like being served some fine creme brulee on a great big pile of human feces. Would you eat that ?

Re:Learning SQL on MS SQL is like...

[cruachan]

Honestly, there's justified MS bashing, and clueless knee-jerk wannabe cool MS bashing, and this is the later. Any production MS SQL installation will be running on Windows Server 2003, most likely as the only major application on the system, and under those circumstances it's really pretty solid.

Nobody pretends that Windows Server is as solid or efficient as Linux, but realistically it's not the pile of crap you posture, it simply doesn't score quite as highly. For practical purposes both are quite good enough to run a service on, and overall Windows Server 2003 + MS SQL is a pretty good choice.

Re:Learning SQL on MS SQL is like...

[jkroll]

The issue isn't allowing columns to be nulls and the like. It is to correct concatenation results using nulls, null comparisons, exceptions on arithmetic underflow/overflow, and similiar issues.

I can't check right now to see if the Enterprise Manager allows changing these settings via the GUI, but the only way I have found to do it is by directly issuing alter database commands.

The fact that these settings aren't the default is another issue.

Save $16.65 by buying the book here!

[Anonymous Coward]

Save yourself $16.65 by buying the book here: Learning SQL on SQL Server 2005. And if you use the "secret" A9.com discount, you can save an extra 1.57%! That's a total savings of $17.09, or 38.58%!

Wow

[FrostedWheat]

There are local XP user groups?

Re:Wow

[pigwin32]

When they use the word "groups" they really mean small gatherings of one person or less. This avoids any embarrassment.

that's what I thought

[garyrich]

when he said this:

" have been working with various versions of Microsoft SQL Server since 1999"

Man... talk about Microsoft winning the marketing war even when the tech is 3rd rate.

Also also question the need for a book on leaning sql that is specific to that bastard version of Sybase that Microsoft sells (the real answer is that PHBs typically sign off on book buys). Sql is sql, stuff that you have to code specific to any particuloar RDBMS is not sql, or at least not standard sql. Why not teach people standard sql before giving them the nonstandard exceptions for "Microsoft SQL Server" or Oracle for that matter? Or just title the book "How to write queries for MS SQL Server 2005"?

The answer probably is that you can't fill a book with "Learning SQL", there just isn't that much to it.

Re:that's what I thought

[TheFlyingGoat]

Your comment is way off. All of the database varieties I've worked with have differences in their SQL syntax. I actually have an O'Reilly book that details commands for Oracle, MS SQL, and mySql. Pretty much every command has differences depending on which database version you're using. It's kind of like CSS... in a perfect world every browser would follow the standard exactly, but not a single one actually does in real life.

The thing is, if someone learns one variety of SQL, they're going to be able to pick up on another pretty quickly since they are basically the same thing. I had never used MS SQL, but I was able to work with it after working with mySql for a long time without reading any sort of manual or book.

Quit Microsoft hating and realize that it's not a MS issue... it's industry wide, and it's really not a bad thing. ODBC, ADO.Net, Perl's DBI, etc all make it easy to work with a certain version of database server without having to worry about the flavor of SQL.

Re:that's what I thought

[garyrich]

They all have "enhancements" to standard sql. Some good, some bad like all things. IF you write basic ansi89 sql it will work on all 3 of the databases you mention - admittedly not optimally and once in a great while not at all. When I did this for a living I typically used the Perl DBI, wrote simple sql queries and used perl to do things that are a pain in the ass to do in in proprietary sql variants - or worse - Oracle PL/SQL (if I say PL/SQL sucks that make me not a pure MS hater?).

If you learn one dialect of sql first and nothing else most people will write tons of code that has RDBMS specific gunk in it. This makes it a nightmare to move the codebase to a new platform. Usually this means that PHBs will totally scrap a functional system, spend a couple million $ on some new VAR's closed vertical application that is less suited to the business than the original one was - just because there is a corporate mandate to move everything to Oracle - or "MS SQL Server" - or "JD Edwards" (and no, they can't understand that that is not an RDBMS).

If you learn basic "baby talk" sql and use it whenever you can, you will produce code that is more portable and much easier to maintain. Start simple and get fancy if - and only if -you really need to. Don't write T-SQL procedures before you can write a basic ANSI89 query.

First in a series

[DysenteryInTheRanks]

Also coming soon from O'Reilly:

• Learning HTML from Microsoft Word,
  
• Learning CSS from IE
  
• Learning Anger Management from Steve Ballmer
  
• Fact Checking 101 from the Operators of Slashdot.org
  
• Data Integrity for Dummies With MySQL 1.0
  
• XBox Manager on How to Make a Profi ...
  

... aghhhhh, forget it, the sport has gone completely out of it ... wake me up when these completely counterintuitive books/articles stop appearing ...

(lapses into never-ending coma)

Re:First in a series

[kfg]

wake me up when these completely counterintuitive books/articles stop appearing

Ahhhhhhh, you seem a perfect candidate for our Mission to Alpha Centauri Project.

Allow me to show you the technical details of our MTSOTBD (Monkeys Throwing Shit Out The Back Drive).

KFG

Re:First in a series

[cruachan]

Your CSS from IE and HTML from Word certainly raise a smile and is worth a mod point or two, but there is nothing wrong with the MS implementation of SQL, and SQL Server generally is a rock solid, highly complient, database.

MS may be fairly knocked for much, nae most, of their offerings, but SQL Server is a gem. I know it must have been tempting to do the standard MS knocking, but frankly in this case all you do is show yourself up as an utter eejit.

Re:First in a series

[dvice_null]

The first two I thought were real books ( I think I have seen those in local bookstore), but the Anger Management was too much to bulieve.

Re:First in a series

[Anonymous Coward]

Don't forget "Hardware Compatibility - the Linux way"

Re:First in a series

[tb3]

SQL Server is a gem?

Maybe you should ask the russians about that.

Re:First in a series

[Anonymous Coward]

Learning Anger Management from Steve Ballmer

I think I'd rather buy "Steve Ballmer's Feng Shui: Arranging Your Furniture for Fun and Profit".

Re:First in a series

[value_added]

.. aghhhhh, forget it, the sport has gone completely out of it ... wake me up when these completely counterintuitive books/articles stop appearing ...

You're too humble. And this is too funny.

My contributions:

• Shell Programming using CMD.EXE
• Systems Administration with vbscript
• Hacking The Start Menu
• Vista Performance Tuning
• The Registry is Your Friend

Re:First in a series

[Anonymous Coward]

MS may be fairly knocked for much, nae most, of their offerings, but SQL Server is a gem.

The point I think, like most of the other whiners around here, is that SQL Server isn't to-the-letter-of-the-standard SQL, it's T-SQL: yet another instance where Microsoft added advanced features beyond the standard and made up its own syntax for them, the rest of the world standardised on some other syntax and then slashdot bitch at Microsoft for it.

SQL 2005 does have very good support for the latest SQL standard but it'll also let you do stuff its own old way.

Re:First in a series

[cruachan]

But there is no agreed standard for procedural SQL as such, so you can't really complain about T-SQL not being compliant. All RDBMs have different implementations of procedural extensions so it's really rather unfair to pull Microsoft out for excessive criticism. Oracle's PL/SQL for example is equally as proprietary. The real point is over the ANSI SQL standards where MS SQL does offer very good support indeed.

In practise I don't think procedural SQL standards are too much of an issue as if you are designing a system that makes extensive use of such features you're probably pretty tied into a specific platform/database anyway.

Try NHibernate

[/dev/zero]

It is a full-featured and low-overhead ORM. Go to http://www.hibernate.org and check out the NHibernate link.

Plus, there are several books avilable from, e.g., O'Reilly, Manning.

Learning the relational model?

[Anonymous Coward]

Learning SQL on SQL Server 2005 covers many of the topics necessary to introduce relational databases to the beginner.

Something tells me, a book called "Learning SQL" is not going to have much to say about relational databases.

Do yourself a favor, get yourself a copy of Date's "Database in Depth", and learn the fundamentals that apply to all data management.

LINQ to the rescue

[melted]

If there's a single example of Microsoft innovation to point out it'd be LINQ.

Check it out: http://msdn.microsoft.com/data/ref/linq/

The cool thing is that the query language can be adapted to ANYTHING, even Reflection.

MS SQL is actually pretty good standards-wise

[blorg]

...it supported ANSI standard JOIN syntax long before Oracle, for example. (And don't get me started on MySQL which is only _very_ recently following standards.)

I've worked with both, (learnt on Oracle, then worked with MS SQL, and then Oracle, and then back to MS SQL) and honestly, you can learn SQL on whatever you like and your core SQL skills will be completely transferrable between different DBMSes.

SQL server 2005 tools much improved

[hguorbray]

While still behind Oracle in many other ways, SQL server 2005 does boast a nicely remade user tools in Management Studio.

I took MS-SQL classes back to back earlier this year with the first one using SQL server 2003 and the 2nd one using 2005 express and the difference was night and day in ease of use.

In 2003 you had to have 2 or 3 different applications up in order to create a table, populate it with data and then view the table data. I was constantly trying to do things in the window which didn't allow that action....Plus transact SQL was like an (even more) retarded version of SQL+

With Management Express it can all be done from the main window -I just wonder why it took them 10 years to figure this out.

there are still some funky aspects of MS-SQL language itself (the GO directive for instance), but this is a great new tool.

Of course now that TOAD works on MS-SQL this may not matter much to database diehards, but I do see signs of Microsoft improving GUIs, simplifying designs and improving usability both here and in VisualStudio.

Too bad they won't be able to do the same for Vista.....

What's the speed of dark?

Re:SQL server 2005 tools much improved

[Anonymous Coward]

"Of course now that TOAD works on MS-SQL this may not matter much to database diehards, but I do see signs of Microsoft improving GUIs, simplifying designs and improving usability both here and in VisualStudio"

I've tried TOAD, and honestly it is not worth nearly the amount they charge ($470 per seat or $3445 for their server suite).

Re:SQL server 2005 tools much improved

[spec8472]

First of all - there was no SQL Server 2003.
Perhaps you're talking about Visual Studio.NET 2003?

"In 2003 you had to have 2 or 3 different applications up in order to create a table, populate it with data and then view the table data."

With the MS SQL Server Enterprise Manager (comes with MS SQL Server 2000 - it's part of the client tools on the install CD), you can perform almost any action on the server - from creating databases, tables, views, users, through to all the scheduling, full-text index setup, etc etc.

If you need to perform particularly complex tasks (executing a pre-prepared, or complex query) that can't be done in the GUI - the Query Analyser is two clicks away from the main Enterprise Manager window (Tools -> Query Analyser)

"Plus transact SQL was like an (even more) retarded version of SQL+"

That's pretty much a matter of opinion - I find T-SQL very easy to use and learn. Help and reference material is one F1 key away, and if that doesn't do it - Googling on MSDN is usually the best source of examples and references.

My experiences with using PL/SQL under Oracle have been less than ideal - on several occasions I've had to stop using Oracle and switch to an MS SQL / MySQL environment, simply because I was spending far too much time trying to get the DB up and running. I suppose if I had several months and a good reference on hand -- that might all be different, but there's not an easy learning curve in PL/SQL (even for someone who has a decent amount of experience with other DBMSs). YMMV.

"Of course now that TOAD works on MS-SQL this may not matter much to database diehards,"
I suppose if you're getting paid at the Oracle consultant level, then the licence cost for TOAD isn't going to be of any matter to you. For those of us who are either freelancing - or working for a company that isn't keen on buying more expensive bits of software, then this isn't going to help.

The biggest advantage for SQL Server is that they ship Enterprise Manager in the box.
It makes help and maintaining a db (to a point) fairly painless, and helps to flatten the learning curve.

Re:SQL server 2005 tools much improved

[unity]

"The biggest advantage for SQL Server is that they ship Enterprise Manager in the box.
It makes help and maintaining a db (to a point) fairly painless, and helps to flatten the learning curve"

Couldn't agree more. Any non-moron programmer or IT person can do even "complex" tasks with almost no learning curve thanks to enterprise manager. It is the one thing that that makes sql server better than another of it's competitors. They got enterprise manager "right" from the start and is probably a BIG reason for its quick acceptance into the market.

I also happen to enjoy programming in transact sql, so to each his own.

Re:SQL server 2005 tools much improved

[spongman]

FYI: the "GO" command isn't part of the T-SQL language. it's a special pseudo-command used by the tools & APIs to separate batches of T-SQL statements.

Re:SQL server 2005 tools much improved

[dcam]

I took MS-SQL classes back to back earlier this year with the first one using SQL server 2003...

There is no SQL Server 2003. There is SQL Server 2000.

In 2003 you had to have 2 or 3 different applications up in order to create a table, populate it with data and then view the table data.

Learn SQL and use Query Analyzer (QA). Seriously, most SQL Server admin and management should be done in QA.

I was constantly trying to do things in the window which didn't allow that action....Plus transact SQL was like an (even more) retarded version of SQL+

s/retarded/standards compliant/

Re:SQL server 2005 tools much improved

[osoese]

I will just interject that SQL Server is one of the products that restored some of my confidence in Microsoft. It is easy and Robust. Granted, Oracle is always one step ahead with funtionality, but as a DBA coming to SQLServer form Oracle on Linux, I will have to say it impressed me. I did not find any of the things you mentioned to be a problem. Let's face it...coding is coding and every language seems to have its differences. T-SQL and PL-SQL aren't that far off from what I found. The ease of management from a DBA perspective to me (as a self-taught, very inexperienced DBA...so save the wise cracks...I managed an Oracle install on Redhat 8 so I cant be that bad off) was by far in the SQLServer2000 corner. I also (as someone else mentioned) did not remember an SQL Server 2003. SQL Server is probably NOT the product to bash Microsoft about when it comes to functionality. Now for the other stuff, go ahead and sling some mud...

Re:SQL server 2005 tools much improved

[Anonymous Coward]

It used to be worth every penny, but now that you can download SQLDeveloper for free, TOAD has a lot less going for it. It still has pretty decent DBA tools, but any DBA is probably going to need better tools for things like performance graphs anyway.

not a review

[glwtta]

Reviews provide information about the content of the book beyond the sequence of chapters. The above is called a Table of Contents.

Critique more intelligently, please.

[emil]

• You may be unaware that Sybase and M$ SQL Server were the same program until release 4.8. If I use the Sybase dialect of Transact SQL, am I still riding a bicycle?
• M$ has higher TPC scores than Sybase, but is Sybase still a car?
• Sybase has always been strong in the financial sector because of its rich types (MONEY, for example). Oracle is somewhat anemic in this regard, without even so much as a BOOLEAN. Is richness of data types akin to riding a bicycle? Is it amateurish of me to remember them fondly?

There are some great critiques for most database vendors. Where are the UNIX clients for M$ SQL server? Why hasn't Sybase maintained DB & CTlib compatibility with MS no matter the cost? Why hasn't Sybase benchmarked on the POWER5 systems that have clobbered Oracle to prove themselves more scalable than M$ once and for all?

If you are really interested in intelligent Sybase criticism, you might read some Tom Kyte. He started out as a Sybase guy, but is now an Oracle VP.

Re:Critique more intelligently, please.

[Anonymous Coward]

> Where are the UNIX clients for M$ SQL server?

How about ODBC, which works terrifically on Unix. How about JDBC? How about FreeTDS?

And no one takes you seriously when you spell MS with a dollar sign. Grow up.

Comment removed

[account_deleted]

Comment removed based on user account deletion

You _CANNOT_ learn SQL with M$ SQL SERVER

[IBitOBear]

Given the bizarre additions; arbitrary constructs; and the non-ANSI (e.g. "illegal") prefetch semantics, well, whatever you end up learning it won't do you a wet-slapp of good if you eventually run up against an actual SQL environment.

[And, yes, this is a little bit of a troll. But having been given the task of trying to "port" a database designed in SQL Server and having discovered just how non-portable it was, I am a tad jaded... 8-)]

Comment removed

[account_deleted]

Comment removed based on user account deletion

MS-SQL Uhhhhhg

[FlyingGuy]

Why not just hit yourself in the face with a shovel, it would be less painfull. As to all those other comments. And you would replace SQL with what?

MyQUery = New.Query ;
MyQuery.ConvertToXML;
MyQuery.Parse40KofXML;
MQuery.GetTheChildren.Of.TheChildren.Of.TheChildre n.of.the.root.objects.children;
etc.

I mean get a clue. SQL is consise, it works. OBJECTS are not the end all be all, and you are all fools if you think they are!
And you would replace a statement like:

Select * from Invoice_Line_Items

With? You could make it shorter and more concise with an object how? Ohhhhh whate, I know, lets write a 5.8 meg XML parsers to give you what that single line statement gives you so you can output in your freekin piss ass web page?

If you cant buy a clue, then at least borrow one. SQL is a tight little language with a very small amount of overhaed. You get the column names,tyes and sizes then you get the data. But of course with all the code weenies out there that only want pretty pictures, well I guess we have to give them pretty pictures to fumble their way through doing actual database work.

MySQL although not even close to the level of Oracle in terms of scaleability, performance, flexibility and reliability, is a great little SQL engine. Oracle is still the undisputed king of the mountain when it comes to SQL based databases. If M$ hadn't stolen the code from Sybase they would still be wondering around in the dark with their thumbs up their asses.

M$'s security model for their SQL server is an absolute joke. They want us to trust credentials floating around on individual workstations, I mean just how fucking stupid is that?! I dont trust any credentials that come from any desktop system, they are just to week and especialy in the case of M$ just so easy to crack.

Think about it. All I need is an install disk of win2k,winxp,win2k3, physical access and I can clobber any SQL server that relies of the credentials from a local workstation, its just horribly designed and its once again M$'s utterly stupid attempt at global credentials. Its like roaming profiles, a really bad idea that should have been aborted long before it was born.

Rarely does M$ do much of anything correctly because their development is driven by the insanity of people like Balmer who's single objective is "beating THEM", and them is the rest of anyone. Can I run MS-SQL on anything besides a Windows Box? Nope. Can I talk to an SQL server, effectively and efficiantly from any kind of box, nope, not even a windows box.

So now they are screwing it up even more by wrapping it in C#?! What the hell are they thinking? I mean Anders was once my hero, now he is a zero. C# could have been something really cool, but a decent enough idea was totaly shit upon when he decided to go to the dark side and work for M$ and developed it the "Microsoft Way", which as we have all discovered, over the years, its just flat out the worst way ever devised

Re:MS-SQL Uhhhhhg

[Anonymous Coward]

Dude, at least try and get some facts in the post, the Sybase rant is such BS, see here for more details; http://blogs.msdn.com/euanga/archive/2006/01/19/51 4479.aspx to see pictures; http://blogs.msdn.com/euanga/archive/2006/05/23/59 7677.aspx

Re:MS-SQL Uhhhhhg

[FlyingGuy]

And this proves what? The only thing it proves is the M$ likes to write their own history. The seed code was all Sybase. Yeah they went out and hired the best they could find, but it all started with Sybase, they had the Sybase code to read through, to look at and takes the best bits from it and then move on from their.

Your argument is a straw man. M$ has never yet built anything from _scratch_, well except maybe for BOB, and well we all know where that went.

A few short comments

[trezor]

A few short comments.

1. Calling Microsoft "M$" doesn't make you look clever nor smart.
2. Raving about how Oracle is great in a thread related to Microsoft SQL Server is just silly. In a thread about Microsoft SQL Server, the topic, is in fact SQL Server.
3. OR mapping can be useful. That doesn't mean its always the best approach, but trying to say its never useful is just being zealous.
4. Yes. Microsoft products are designed for the Microsoft platform. Does this surprise you at all?
   And what do you mean with "effectively and efficiantly from any kind of box, nope, not even a windows box". A few examples would be nice.
5. No. SQL Server's security model does not rely on some random credentials from some random workstattion. It relies on authenticated, encrypted Kerberos tickets, authenticated by a central server. Is this any less secure than usernames and passwords sent across the wire?
   Please show me how you intend to break into Microsoft SQL Server with a random Windows installer CD. I'm truly intruiged by this, so please provide a few examples.
6. You say roaming profiles is a bad idea? I guess you have never worked with a large network, or administered networks where users may work on different workstations from time to time. Like for instance, at a university where students hardly use the same workstation twice. If you had the slightest idea how stupid this single remark made you look, you might have had a chance to excuse your way out of your ignorance.
7. Yes. There will be abstraction layers for the database in C#. Not only for SQL Server, but for any database supported by ADO.NET. Even your wonderful Oracle! Are you saying abstraction layers is a bad thing?

You seem rather ignorant and zealous, and while I value the opinion of others, I do prefer informed opinions rather than random, uninformed drivel by someone who evidently knows one product only, and bashes everything else for not being that one product.

Re:A few short comments

[FlyingGuy]

1. M$: A somewhat derogitory, but rather well know abbreviation for MicroSoft.

2. I also mentioned MySQL, or did you miss that part?

3. OR Data mapping really has no place in tighly coupled, hi performance systems. This concept imparts vast amounts of additional overhead.

4. And are therefor pretty much useless, unless you buy into the notion that M$ is the end all be all.

5. The credentials can be forged, it imparts more un-needed overhead, if you want a secure login use SSL.

6. Think about it for two minutes, its not that hard, physical access _is_ required.

7. ADO.Net. Yeah now there is a bit of code, makes BDE look positively brilliant! An abstraction layer, should be as small as reasonably possible. It should abstract the most inconvienient bits of getting the information back from the database, but nothing more! Loading more and more into the abstraction layer simply adds to already bloated code. Abstraction should be done in layers, individual parts that are only loaded by the developer of the application in question, instead of just throwing in the kitchen sink as it were.

MS-SQL has been slow, cumbersome and insecure from the very beginning. Now on M$'s behalf, they are beginning to get to place in its development life where its becomming usefull, but only just beginning. Sadly this point of enlightenment is being overshadowed by their really bad ideas. An SQL server is NOT an integrel part of windows, its a database machine. If you want an example of something that was built along those lines, look back to Pick. In Pick the Database was the O/S and vice versa and we all know where that landed, even though it was blazingly fast and very very efficient.

Re:A few short comments

[trezor]

1. Yes. And it still doesn't mean using it makes you look any more clever. In fact it does the opposite.
2. No. But my main point was that in a thread discussing MSSQL and getting the most out of it, giving long rants about how all other DBs are better, still seems a bit off-topic.
3. Not all systems are performance critical systems on 100% load. Some systems have in fact very low load, and using OR mapping to cut development time to a tenth, will please your boss.
4. I can't say I agree. See point 3.
5. I asked for examples of just how you would do that, since I'm evidently just some noob who wouldn't know how. In effect, I am asking for proof of these statements.
6. With physical access to a workstation, you will still not gain access to SQL Server even though you are on the same LAN. See, you do not have the needed Kerberos tickets, since you are not authenticated. This is my stance and I'll remain just here, unless ofcourse, you provide some proof that I'm wrong.
   With physical access to any server, the server is by definition compromised. So just where are you going with this argument?
7. Yes. I hate how I can use ADO.NET to directly bind a database table/view/query to user-interface elements without having to write that same generic "glueing-it all together" code everytime.
   Yes. There is overhead. However, not everything in this world is 100% performance critical, and this can allow for this overhead, if it makes development faster, and the code more maintainable. Sometimes hardware is cheaper than developer-time.

Slow, cumbersome and insecure? I doubt you have used SQL Server, at least since SQL Server 2000. Granted, version 6.5 was a true POS, but the current versions work well, are secure and a breeze to use.

Simply because it ain't the tools you know well, doesn't make it crap. In fact, I think MySQL is the worst excuse for a database ever. But this is a MSSQL thread, so I won't go into that. I can save that for later.