What is the difference between creating database inside localDb in visualstudio and creating database in sqlserver and joining it in VS .
Answered by:- neena
1. LocalDB uses the same sqlservr.exe as the regular SQL Express and other editions of SQL Server. The application is using the same client-side providers (ADO.NET, ODBC, PDO and others) to connect to it and operates on data using the same T-SQL language as provided by SQL Express.
2. LocalDB is installed once on a machine (per major SQL Server version). Multiple applications can start multiple LocalDB processes, but they are all started from the same sqlservr.exe executable file from the same disk location.
3. LocalDB doesn't create any database services; LocalDB processes are started and stopped automatically when needed. The application is just connecting to "Data Source=(localdb)\v11.0" and LocalDB process is started as a child process of the application. A few minutes after the last connection to this process is closed the process shuts down.
4. LocalDB connections support AttachDbFileName property, which allows developers to specify a database file location. LocalDB will attach the specified database file and the connection will be made to it.
If you set up a database with SSMS and connect to it via SQL Express then you don't have a local database that's part of your project, you have a database for which the server happens to be local to your workstation.
|
Deployment/ Installation Features |
SQL Server Compact 3.5 SP2 |
SQL Server Compact 4.0 |
SQLite, incl. ADO.NET Provider |
SQL Server |
SQL Server 2014 LocalDB |
|
Installation size |
2.5 MB download size |
2.5 MB download size |
10 MB download, 14 MB expanded on disk |
120 MB download size |
32 MB download size |
|
ClickOnce deployment |
Yes |
Yes |
Yes |
Yes |
Yes |
|
Privately installed, embedded, with the application |
Yes |
Yes |
Yes |
No |
No |
|
Non-admin installation option |
Yes |
Yes |
Yes |
No |
No |
|
Runs under ASP.NET |
No |
Yes |
Yes |
Yes |
Yes |
|
Runs on Windows Mobile / Windows Phone platform |
Yes |
No |
Yes |
No |
No |
| Runs on WinRT (Phone/Store Apps) | No | No | Yes | No | No |
| Runs on non-Microsoft platforms | No | No | Yes | No | No |
|
Installed centrally with an MSI |
Yes |
Yes |
Yes |
Yes |
Yes |
|
Runs in-process with application |
Yes |
Yes |
Yes |
No |
No (as process started by app) |
|
64-bit support |
Yes |
Yes |
Yes |
Yes |
Yes |
|
Runs as a service |
No – In process with application |
No - In process with application |
No - In process with application |
Yes |
No – as launched process |
|
Data file features |
SQL Server Compact 3.5 SP2 |
SQL Server Compact 4.0 |
SQLite, incl. ADO.NET Provider |
SQL Server |
SQL Server 2014 LocalDB |
|
File format |
Single file |
Single file |
Single file |
Multiple files |
Multiple files |
|
Data file storage on a network share |
No |
No |
No |
No |
No |
|
Support for different file extensions |
Yes |
Yes |
Yes |
No |
No |
|
Database size support |
4 GB |
4 GB |
140 TB |
10 GB |
10 GB |
|
XML storage |
Yes – stored as ntext |
Yes - stored as ntext |
Yes, stored as text |
Yes, native |
Yes, native |
|
Binary (BLOB) storage |
Yes – stored as image |
Yes - stored as image |
Yes |
Yes |
Yes |
|
FILESTREAM support |
No |
No |
No |
Yes |
No |
|
Code free, document safe, file format |
Yes |
Yes |
Yes |
No |
No |
|
Programmability |
SQL Server Compact 3.5 SP2 |
SQL Server Compact 4.0 |
SQLite, incl. ADO.NET Provider |
SQL Server |
SQL Server 2014 LocalDB |
|
Transact-SQL - Common Query Features |
Yes |
Yes |
No |
Yes |
Yes |
|
Procedural T-SQL - Select Case, If, features |
No |
No |
Limited |
Yes |
Yes |
|
Remote Data Access (RDA) |
Yes |
No (not supported) |
No |
No |
No |
|
ADO.NET Sync Framework |
Yes |
No |
Yes |
Yes |
|
|
LINQ to SQL |
Yes |
No (not supported) |
No |
Yes |
Yes |
|
ADO.NET Entity Framework 4.1 |
Yes (no Code First) |
Yes |
Yes |
Yes |
Yes |
| ADO.NET Entity Framework 6 | Yes (fully) | Yes (fully) | Yes (limited) | Yes | Yes |
|
Subscriber for merge replication |
Yes |
No |
No |
Yes |
No |
|
Simple transactions |
Yes |
Yes |
Yes |
Yes |
Yes |
|
Distributed transactions |
No |
No |
No |
Yes |
Yes |
|
Native XML, XQuery/XPath |
No |
No |
No |
Yes |
Yes |
|
Stored procedures, views, triggers |
No |
No |
Views and triggers |
Yes |
Yes |
|
Role-based security |
No |
No |
No |
Yes |
Yes |
|
Number of concurrent connections |
256 (100) |
256 |
Unlimited |
Unlimited |
Unlimited (but only local) |
Reference:
https://blogs.msdn.microsoft.com/jerrynixon/2012/02/26/sql-express-v-localdb-v-sql-compact-edition/
Answered by:- vikas_jk
Subscribe to our weekly Newsletter & Keep getting latest article/questions in your inbox weekly
Buy us a coffee
Become a Patron