Features Of SQL Server 2012 | Hacker News Reader

Features Of SQL Server 2012

mcpmag.com

30 pointsKarenLopez27 comments

SQL Server has been one of my favorite software products of all time. As an admin it has been rock solid and not produced any headaches that weren't our own fault.

With that said, to really get the most out of SQL Server (and most likely most other SQL implementations) you really have to do your homework and put in the time to go through the features. Perhaps more than anything this is why NoSQL has taken off-- it's very simple to get going. At user groups I hear a lot of people saying they selected MongoDB because "They had millions of rows and SQL just couldn't keep up" and to me it just sounded like no one in their organization had any solid SQL experience.

It's too bad MS didn't build a 'SQL Admin' into their product that sent an email on occasion to say things like "You have a query that is called frequently and could be sped up if you simply included this column in this index. Here are some details!"

SQL Server has been one of my favorite software products of all time.

I feel the same way about PostgreSQL, along with the bit about learning all the features. I suspect that most decent RDBMS's are this way.

At user groups I hear a lot of people saying they selected MongoDB because "They had millions of rows and SQL just couldn't keep up" and to me it just sounded like no one in their organization had any solid SQL experience.

hahahaha. I have customers with db's with 10's of millions of rows (And I expect hundreds of millions or rows in the foreseeable future) and I can't imagine MongoDB keeping up in terms of reporting......

I feel the same about Oracle. There is just so much capability in the Oracle RDMBS if you take the time to learn it. If your organization is paying for SQL Server or Oracle and then just doing simple SELECT, INSERT, UPDATE, DELETE or throwing an ORM on it you are leaving a serious amount of value unutilized.

"At user groups I hear a lot of people saying they selected MongoDB because "They had millions of rows and SQL just couldn't keep up""

hehe :)

At work we do 500 million + in Mysql, billion(s?) if you count all the tables. But to be fair, you do loose a lot of flexibility and "features" when the tables become this big.

Purported new feature: "Columnstore Indexes -- This a cool new feature that is completely unique to SQL Server. They are special type of read-only index designed to be use with Data Warehouse queries. Basically, data is grouped and stored in a flat, compressed column index, greatly reducing I/O and memory utilization on large queries."

Many DB engines such as Vertica, SenSage, Sybase IQ, all use column-oriented storage. Perhaps the only novel-but-obvious thing here is that these "read-only" indexes are implied to live alongside the regular DB table data, though I'm not sure how these "read-only" indexes would mesh with the need to support deletion on regular tables.

http://en.wikipedia.org/wiki/Column_oriented_database

FWIW, there are ways to deal with the read-only limitation. Here is an example in an OLTP scenario:

http://blogs.technet.com/b/dataplatforminsider/archive/2011/...

"With native or custom partitioning, for example, you could use a sliding window scenario, having a static set of partitions in a table with a ColumnStore index, and load current data (say, for today only) into a separate table with no ColumnStore index. Once the current day rolls into the next, you can continue loading the new day’s data into yet another table, create the ColumnStore index on yesterday’s (now unchanging) daily table, and then switch it into the partitioned table. You could make this relatively seamless to end users by creating a view that unions the partitioned table with the “current” table. You can also consider loading daily data into a non-ColumnStore table and, at the end of each day, drop the ColumnStore index from the reporting table, insert all of yesterday’s data, and rebuild the ColumnStore index."

Perhaps the engine will update the "read only indexes" when the corresponding data in regular tables are deleted, but not in realtime, but only on demand or on a predetermined schedule?

The columnstore indexes are purely read-only; they will not be updated without requiring a rebuild.

However, you can create columnstore indexes on specific partitions, and thus, by partitioning your source data, you can continue to add new data in new partitions. As most DW/"big data" solutions use partitioning anyways, this is a usable solution, giving you the benefits of the readonly performance as well as the maintainability aspects, while still allowing you to feed in new data.

Columnstore indexes are not meant for OLTP type solutions, so there's no need for realtime regular data deletion; it simply doesn't happen on this kind of historical data. You might switch out a partition of your data, or you might switch in a partition of new data, but you won't change the actual data itself.

Comparing SQL Servers columnstore index implementation with other columnstore based databases doesn't make sense. I won't laud columnstore indexes as a revolutionary new feature, but the way it's implemented is new, AFAIK. Comparing it to "normal" columnstore based databases will make SQL Server look bad - but the thing is, it's not meant to compete with those. Use it where it makes sense.

Haven't read the wiki entry you linked but the description reminds me of Sybase IQs bitwise indexes... Super fast for queries, not so much for inserts. First encountered these in 2005.

A little OT, but this was very surprising:

"MS is making a push back to the command line for server products). Core is the GUI-less version of Windows that uses DOS and PowerShell for user interaction. It has a much lower footprint (50% less memory and disk space utilization), requires fewer patches, and is more secure than the full install. Starting with SQL 2012, it is supported for SQL Server."

What is surprising about it? Server core is for specific situations.

I'd be happy with simple syntax for things like OFFSET/LIMIT and GROUP_CONCAT at this point.

You'll be happy then. I think they added OFFSET in SQL 2012.

http://sqlwithmanoj.wordpress.com/2011/07/28/tsql-denali-new...

OFFSET/LIMIT is coming: http://msdn.microsoft.com/en-us/library/ms188385%28v=sql.110...

GROUP_CONCAT still needs to be done with the FOR XML PATH hack

if they gave you that, it'd be too easy to port a lot of basic stuff away from sql server.

I worked someplace that was primarily a SQL Server shop, although was still largely Java and some PHP at the time. I was tasked with building something to paginate through records. There were several hundred thousand to paginate through, and I had to resort to getting the DBA to build some weird sproc with cursors and junk in it.

"I just need some of the rows"

"Why would you need that?" (honest to goodness question from the DBA)

"Well, because I only need to show 30 items on the screen, not 400,000. Showing 400,000 might take a while."

"Just use TOP. SQL Server lets you do SELECT TOP(30) already!"

"Umm... but I might need to see the second 30, or the 99th 30."

"Why? No one does that!"

Insane. They experimented with some monstrosity of nested TOP() queries. They refused to allow MySQL to be used ("it's just a toy"), when it was perfectly capable for the requirement, and had developer-useful stuff like LIMIT in it. I got some overly engineered sproc that had to be updated whenever I needed a change, and I ended up leaving a few months later. Not specifically because of that, but that culture was one I couldn't fit in to. I've heard it's gotten better, but I don't really believe it's better so much as the people who've stayed there have adapted and worked around the cognitive dissonance it triggered.

ACK - I can't edit. FWIW, this was in the year 2004, not 2012.

And... to the extent that people will say "it's got it now"... this the same situation people have with mysql - spouting off about stuff that it didn't do 10 years ago, and don't use it (or postgres from X year ago, etc). Once you hit some rather stupid/useless limitation, it tends to stick with you and influence your choices in the future.

I've used SQL Server on projects since then, but it's never been my choice to start a project with it. Clients have used it, and I've used it because that's what they had opted for, but it wasn't my choice or suggestion.

As someone else noted, connecting to it from outside the world of Windows/.Net is a bit of a pain. The PHP connector situation is weird - it's changed in 5.3, and you seem to have to download a separate connector from MS directly. It'd be super swell if they'd just open source that connector so it could be distributed around (or provide a binary and license terms so at least WAMP-stacks could distribute it more easily).

I might be wrong, but I'm pretty sure I've implemented pagination in SQL Server and it wasn't as complicated as that. Maybe I produced the world's worst pagination technique but I don't think I ever resorted to TOP. The ROW_NUMBER() function takes care of this.

Yes.

http://stackoverflow.com/questions/548475/efficient-way-to-i...

I am also angry that stuff like pagination isn't done in some standard way (pg, oracle, mysql sql server are doing it differently, sybase doesn't even have it). BUT! Dude, saying that sql server can't do something when a simple search gives you the right solution?

The SQL standard is missing couple more features I think, like identity/sequences (I even had to use different code in .NET to get the newly inserted id), upserts, ...

Writing cross-database software isn't even worth it. And I say that as a developer of a medium sized project supporting 4 databases. It would have been much better if we just decided to use one and use all the features properly

You can do pagination by using the standard ROW_NUMBER() function and using CTEs, check my blog post for the same: http://sqlwithmanoj.wordpress.com/2011/12/30/creating-stored...

Also in SQL 2012 now you can use the offset-fetch clause to implement paging, check this: http://sqlwithmanoj.wordpress.com/2011/12/30/creating-stored...

This problem was solved in SQL 2005. The solution in SQL 2000 was pretty terrible. You had to select from the start of the table to the end of the current page, reverse it, take a top of the page size, then reverse again.

Yes, that was one of the proposed solutions. Again, as I said in one of my replies, this was in mid 2004 - we were using SQL Server 2000, not anything higher, because it wasn't available to us yet.

It's 2012. There's almost no need to ever use cursors. I'm pretty sure pagination can easily be achieved with ROW_NUMBER()

Now if only MS would sit down and create a useable NodeJS client for MS-SQL.

The lack of a good clients outside the Windows world is what makes me move away from this otherwise great product.

There is some great features there but Reporting Services is a turd that should have been flushed, not polished.

I disagree. Reporting Services in 2008 (especially with R2 and Report Builder 3.0) is great. It serves as a great alternative to Crystal and it comes included with the SQL Server license.

IMHO SSRS in SQL Server 2012 has matured more than I expected and offers a lot of the same functionality that comes with others in the Enterprise Reporting space. It's worth giving it another shot.

Microsoft is so last year

Reply on news.ycombinator.com