Discover LocalDB for SQL Server Instances: Question and Answer

Q.1. What Does LocalDB Provide for a SQL Server Instance?

Ans: When you need to perform alterations to a view or create a stored procedure in a test SQL Server environment, having access to a readily available testing environment is highly beneficial. Since SQL2012DEV is the "live" server, it is not recommended to use it for testing. Instead, you require another SQL Server instance dedicated solely to offline testing.

LocalDB provides the ideal solution for this purpose. When you build your project, it automatically creates a new, lightweight, single-user instance of SQL Server on demand. This is particularly useful when you're working offline and don't have access to another development server. It's worth noting that LocalDB is different from the Express edition of SQL Server, although it is sometimes referred to as "SQL Express LocalDB." Its official name, however, is "LocalDB" to avoid confusion. To learn more about LocalDB, you may consider pursuing a SQL Certification.

Q.2. How to Deploy a Project to LocalDB?

Ans: Follow these steps to deploy a project to LocalDB:

• Press F5 to build the project. This action deploys the entire database structure specified in the project to LocalDB after validating it. However, keep in mind that this is the default behavior; if you require features not supported by LocalDB, you can modify the project properties to target another available server for testing, such as FILESTREAM.
• After the build, open SQL Server Object Explorer to check that SSDT (SQL Server Data Tools) has initiated a new LocalDB instance. The hostname of this instance should be "(localdb)\SampleDb," and it operates independently of the SQL2012DEV instance. Ensure that the new CustomerRanking table and the CustomerRankingId foreign key in the Customer table are correctly replaced.
• SimpleDB will now be deployed to LocalDB and expanded to display its tables.

Q.3. How is The Deployment of a Project to LocalDB Carried Out?

Ans: The deployment process involves running a schema comparison between the project and LocalDB on the target server. As mentioned earlier, models of the source project and the target database are created, and the schema comparison operates on these models. Since the database does not exist on the target server in your first build, the schema comparison generates a script that completely rebuilds the database. Subsequent builds, as you make changes to the project, will produce incremental change scripts, outlining the steps required to synchronize the target database with the project.

Q.4. How To Bring Reference Data To The LocalDB Table?

Ans: To import reference data into the LocalDB table, follow these steps:

• Open Excel and select the five rows of data (complete rows, not cells or columns).
• Copy the selected data using the right-click menu.
• In SQL Server Object Explorer, right-click the CustomerRanking table and choose "View Data."
• When the Editable Data Grid in SSDT loads, you'll see a template for adding a new row.
• Right-click the row selector in the left grey margin area (not a cell) and select "Paste" to paste the data from Excel into the new row template.
• SSDT will accurately import the data from Excel into the CustomerRanking table.

Q.5. How to Update a View in LocalDB?

Ans: To update a view in LocalDB, follow these steps:

• Double-click on vwCustomerOrderSummary.sql, located in the Views folder of the project within Solution Explorer (under the dbo schema folder).
• The view will open in a new code window, and you may notice warning indicators (squigglies) created by Visual Studio within the view's code.
• Hover the cursor over a warning indicator to view the warning text in a tooltip or find all warnings indexed as warning items in the Error List window.
• Address the warnings, such as correcting column names for case sensitivity issues.
• Make necessary changes to the view, like adding RankName to SELECT and GROUP BY column indexes and including another LEFT OUTER JOIN to connect the CustomerRanking table to the CustomerRankingId of the Customer table.
• Save the altered vwCustomerOrderSummary view definition to update the offline project.
• Press F5 to deploy the modified view to the test database on LocalDB.

--
Create a handy view summarizing customer orders
CREATE VIEW vwCustomerOrderSummary WITH SCHEMABINDING AS
SELECT
c. CustomerId, c. FirstName, c.LastName, r.RankName, ISNULL(SUM(oh.OrderTotal), 0) AS OrderTotal
FROM
dbo.Customer AS c
LEFT OUTER JOIN dbo.OrderHeader AS oh ON c. CustomerId = oh. CustomerId LEFT OUTER JOIN dbo.Customer Ranking AS r ON c.CustomerRankingId=
r.CustomerRankingId
GROUP BY
c. CustomerId, c.FirstName, c. LastName, r.RankName

Q.6. What Will Happen If You Try to Execute the Script Directly by Clicking Ctrl+Shift+E in The Code Window?

Ans: If you attempt to execute the script directly by clicking Ctrl+Shift+E in the code window, you'll encounter the following error message:

Msg 2714, Level 16, State 3, Procedure vwCustomerOrderSummary, Line 2 There is already an object named 'vwCustomerOrderSummary' in the database.

The reason for this error is that the query window is linked to the SampleDb instance via SSDT, and when you execute the script imperatively with Ctrl+Shift+E, it tries to run the script against the connected database. However, since this script is declarative and part of an offline project, presented as a CREATE VIEW statement, the view already exists in the database. The correct way to deploy the database update is through an incremental deployment script, which is done when pressing F5 during debugging.

Conclusion

We have learned about the benefits of LocalDB for SQL Server instances and how to deploy a project to LocalDB. We also explored how to bring reference data to the LocalDB table and update a view in LocalDB. Remember, if you're looking to acquire SQL skills for a career switch, you can consider joining an SQL server online training course, and JanBask Training can help you with that.