
Things Developers Should Know About SQL Server (2013) - neiesc
https://www.brentozar.com/archive/2013/02/7-things-developers-should-know-about-sql-server/
======
BrentOzar
Daaaang, on the HN front page. Glad you liked it.

I don't wanna take money from HN folks if I can avoid it though - if you wanna
get the course listed on the post, use coupon code hnfrontpage to get it free
this week. Enjoy.

(And if you're a bootstrapped startup using MSSQL, holler - I have a soft spot
in my heart for that, and I'll do what I can to help.)

~~~
lancefisher
Cool! Thanks. I'll definitely check out the video. We use MSSQL at our
startup. Initially, because that's what the founders were familiar with, but
it really is a nice database to work with.

------
whatnotests
> Avoid ORDER BY; sort in the app instead.

If you have unlimited memory, network bandwidth isn't a problem and the
additional garbage-collection won't kill your performance.

Otherwise, yeah, totally.

~~~
millstone
I don't understand this. Isn't sorting an in-place procedure that does not
need to allocate on the heap?

~~~
twic
Depends on the sort. A naively implemented mergesort needs a temporary array
of the same size as the the array being sorted. But whilst that takes up a
chunk of memory for a while, it shouldn't be the sort of thing that makes GC
choke.

And anyway, not many people will be using a naively implemented mergesort in
this situation!

------
alecco
The avoid ORDER BY is the main reason not to fall into per-cpu licensing (most
commercial data engines). That's the licensing trap they make.

Also it's the same for other things requiring memory/cpu like JOIN and all
that. You don't want to end up walking on eggshells.

~~~
BrentOzar
> The avoid ORDER BY is the main reason not to fall into per-cpu licensing
> (most commercial data engines). That's the licensing trap they make.

I used to agree with you, but in the cloud, all databases are essentially per-
CPU licensing. It's just that instead of paying once up front, you pay by the
hour.

That's also why I love database administration as a long term career bet, too.
Now, I can go performance tune someone's database queries, and they see an
instant reduction in their monthly bill, thereby justifying the DBA cost. If I
tune someone's on-premises licensed server, they don't get an instant rebate -
the licensing and hardware is a sunk cost.

~~~
alecco
Sorry but it's not the same. When you use your own hardware you have to scale
it to handle the peak usage times. This is often a small fraction of the day.

I don't like putting databases in the cloud, but there you pay closer to
actual usage instead of 24x7 at max capacity.

BTW I was thinking in open-source options. Or commercial ones with a less
draconian licensing (if there are any left).

~~~
BrentOzar
> I don't like putting databases in the cloud, but there you pay closer to
> actual usage instead of 24x7 at max capacity.

Bad news again - with Amazon RDS and Azure SQL DB, you pay a fee based on
instance size, and you can't scale up/down instantly with demand. (You can
scale automatically, but it takes quite a bit of work - you don't "pay actual
usage."

------
kbaker
Also, [2013]. Lots of things have changed in the DB market over the past 4
years!

~~~
BrentOzar
> Also, [2013]. Lots of things have changed in the DB market over the past 4
> years!

Nothing about the article has changed in current versions of SQL Server,
actually.

~~~
kbaker
Yes, the article still gives good advice for SQL Server.

I was meaning in the more general sense - Oracle hadn't released 12c yet,
Postgres was at 9.2, MariaDB just released 10.0. Lots of changes in the other
DBs to make them more competitive.

One thing that has seemingly changed for SQL server 2016 is the licensing
cost... $14,256 per core now??? Or is that only in a 2-pack?
[https://www.microsoft.com/en-us/sql-server/sql-
server-2016-p...](https://www.microsoft.com/en-us/sql-server/sql-
server-2016-pricing) (Open Level)

~~~
BrentOzar
> One thing that has seemingly changed for SQL server 2016 is the licensing
> cost... $14,256 per core now??? Or is that only in a 2-pack?

That's for a 2-core pack with Software Assurance (maintenance), so it's
roughly half that. It's the same cost it's been since SQL Server 2012 when
Microsoft switched to per-core licensing.

Prior to that, it was per-socket, but still roughly the same cost for quad-
core servers.

------
__s
Function tip should give special mention to inline table valued functions

------
scarface74
If you are doing an order by on a single table on an indexed column, is that
expensive? If you are doing an order by on a clustered index, wouldn't the
order by be essentially free?

But I never thought about it, but it does make perfect sense to sort on the
app server and filter on the database. When hosted locally, it's practically
nothing in terms of money, time to spin up another API server and add it to
the load balancer compared to another SQL Server instance.

~~~
BrentOzar
> If you are doing an order by on a single table on an indexed column, is that
> expensive?

No, but it sure is unusual. I don't know a lot of folks who only reference a
single table in their queries. (Folks who do are generally using key/value
stores, not RDBMS's.)

> If you are doing an order by on a clustered index, wouldn't the order by be
> essentially free?

Assuming you're using an identity (sequential) field, that's also incredibly
unusual. The clustering key rarely has value to the select query.

------
cyberferret
Nice article, and well done to the OP, but this post has actually reinforced
why Microsoft's SQL Server is the LEAST favourite SQL database I have ever
worked with in over 30 years.

Routines tasks like setting granular permissions for users is a major exercise
in confusion and "Lets see if this actually works like we expect it to..."

That we have to use a completely separate database collections in MS-SQL for
temporary tables, rather than have built in memory or non volatile tables as
part of the core system is mystifying indeed.

~~~
aussie123
you can have local temp tables, global temp tables as well as indexed views.
always depends on what you want to accomplish.

------
jarulraj
Any war stories on the SQL Server Database Engine Tuning Advisor? How
significantly has it affected the performance of the system?

------
brightball
I learned a lot from this, so thank you.

------
guard-of-terra
Which one?

------
LoSboccacc
"This is the postgres installer"

------
exclusiv
> 7\. SQL functions rarely perform well.

So SQL Server is not good at SQL.

How does it compare performance wise between alternative SQL DBs?

~~~
ZenoArrow
>"So SQL Server is not good at SQL."

That's not what was implied. SQL functions are snippets of SQL you wrap in a
function to use in a larger query. The issue being suggested is that SQL
Server will go into single-threaded execution when SQL functions are used.

However, you can use stored procedures for the same purpose as functions
without incurring the same performance penalty, so the advice was a warning
about a specific SQL Server feature rather than the platform as a whole.

~~~
exclusiv
My mistake. I realize now the author is talking about user defined SQL
functions and not app layer SQL functions for composing SQL. Thanks for the
clarification.

