Introduction | In the middle of 2011 the SQL server community was introduced an improved SQL Express “Denali” server named and known as “LocalDB” (more) (download). This is not a replacement for SQL Server Compact or SQL Server Express, it is an addition to SQL Server Express lineup: |
- 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.
- 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.
- 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.
- 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.
LocalDB ConnectivityAnyDAC requires SQL Server Native Client “Denali” (v 11) to work with LocalDB. Older versions of SQL Native Client will not work with LocalDB. V 11 may be installed by: - SQL Server Management Studio installer (download);
- Microsoft® SQL Server® 2012 Release Candidate 0 installer (download).
Note, an 32-bit application requires 32-bit SQL Native Client, and x64 application – 64-bit client. Since LocalDB runs as a separate process, your application can work with 32-bit or x64 LocalDB. AnyDAC and LocalDBAnyDAC 5.0.5 (more) and earlier versions won’t work with LocalDB right out of the box, and require to specify the SQL Native Client v 11 ODBC driver explicitly. Let’s add a virtual driver (more) to ADDrivers.ini: [MSSQL_2012]
BaseDriverID=MSSQL
ODBCDriver=SQL SERVER NATIVE CLIENT 11.0 As we use AnyDAC SQL Server driver (more), all driver parameters may be specified for a LocalDB connection definition with some notes: A connection definition to connect to LocalDB can be added into ADConnectionDefs.ini: [MSSQL_LocalDB]
DriverID=MSSQL_2012
Server=(localdb)\v11.0
Database=master
OSAuthent=Yes
MARS=Yes AnyDAC 5.0.6 version (beta will be released in January) will work with LocalDB right out of the box. MARS option and the driver configuration are not needed: [MSSQL_LocalDB]
DriverID=MSSQL
Server=(localdb)\v11.0
Database=master
OSAuthent=Yes To open a database file in a LocalDB session, use ODBCAdvanced parameter: [MSSQL_LocalDB]
Server=(localdb)\v11.0
DriverID=MSSQL
ODBCAdvanced=AttachDbFileName=C:\Users\Alex\ADDemo.mdf There can be security concerns, but the LocalDB will operate in the user's security context and that means that the server will access database files that the user could access directly in the OS anyway. LocalDB New FeaturesAmong new features (more) introducing by SQL Server 2012, we’d like to highlight two SQL enhancements: - Sequences: Sequences have been requested by the SQL Server community for years, and it's included in this release. Sequence is a user defined object that generates a sequence of a number.
/* Example */
CREATE SEQUENCE MySequence
START WITH 1
INCREMENT BY 1;
INSERT MyTable (ID, FullName)
VALUES (NEXT VALUE FOR MySequence, 'Jim Johnson'),
(NEXT VALUE FOR MySequence, 'Bob Thompson'),
(NEXT VALUE FOR MySequence, 'Tim Perdue'); AnyDAC will support LocalDB sequence objects in the near future. - Ad-Hoc Query Paging: Paging results in SQL Server has been discussed for years. The Order By option in the SQL SELECT statement has been enhanced in SQL Server 2012. Using a combination of OFFSET and FETCH along with ORDER BY gives you control of paging through a result set. In the sample query below, SQL Server would return 10 records beginning with record 11. The OFFSET command provides a starting point for the SELECT statement in terms of paging, and the FETCH command provides how many records to return at a time.
/* Example */
SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY; AnyDAC 5.0.6 version will use this feature for RecsSkip and RecsMax options (more). However, the new feature may produce inadequate results for queries with UNION (more). Running this query against Northwind database returns 10 rows: select o.OrderID from [Orders] o
union
select h.OrderID from [Order Details] h
order by 1
offset 0 rows fetch first 10 rows only While this query returns 830: select count(1) from (
select o.OrderID from [Orders] o
union
select h.OrderID from [Order Details] h
order by 1
offset 0 rows fetch first 10 rows only) a Apparently the ORDER BY clause is suddenly attached to the second part of the UNION only. More ReadingSummaryAs you can see starting AnyDAC 5.0.6 LocalDB databases can be connected right out of the box either via server connection or attaching a database directly. For AnyDAC 5.0.5 version and earlier just a small trick with the virtual driver required. SQL Server developers find no significant differences in working with LocalDB if they worked with SQL Server Express before. In addition they get opportunity to connect to a database file directly. | 
