Maximum size, connection limit and what version of SQL Server should I use in my desktop application

Limit every database has, SQL Server with less limitations is Enterprise (very expensive), and Standard solves most cases well. Of course, if you need Enterprise features you will probably already be a well experienced DBA and this information is irrelevant.

In some cases it may be good to use the web edition or even Azure (cloud subscription) and pay for what you use effectively.

When you need to resolve your questions, you can always refer to the product page that has everything.

There is a page with comparison between different editions of SQL Server .

When you are developing a solution (or an application), you should keep in mind some points:

In particular, I believe these points are critical to knowing which editions and / or versions of SQL Server are appropriate for your solution.

Tip: Always choose to use the latest versions. They support everything the previous ones support with the addition of new features.

For simpler solutions where data competition is relatively low (stores, site with few visits), SQL Server Express responds very well. It comes with 10GB, maximum consumption of 1GB of RAM per DB and supports up to 5 simultaneous connections to your server. It is true that today's applications use connections in which they open, perform the necessary querys, and close the connection again, all very fast.

Scenarios more complex than these, I recommend SQL Server Standard.