Using Microsoft SQL Server¶
Microsoft SQL Server is accessed using SQL Server Management Studio. We are using the so-called LocalDB version that hosts the server locally for development purposes, but you can also use the Express edition (any version).
Download links:
- LocalDB is installed with Visual Studio
- https://www.microsoft.com/en-us/sql-server/sql-server-editions-express
- https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
Video guide of the tools
How to use these tools: https://web.microsoftstream.com/video/98a6697d-daec-4a5f-82b6-8e96f06302e8
Using SQL Server Management Studio¶
In university computer laboratories, you can start the software from the start menu. Connection details are configured when the application starts. When using LocalDB, the Server name is (localdb)\mssqllocaldb; for Express Edition the name is .\sqlexpress (if installed with default settings). Either case use Windows Authentication.
When the connection is established, the databases are listed on the left in the Object Explorer window under Databases. A database can be expanded, and the tables (along with other schema elements) are listed here.
SQL code can be executed in a new Query window opened using 
on the toolbar. The commands in the Query window are executed on the currently selected database. This database is selected from a dropdown in the toolbar (see in the image below with yellow). We can open multiple Query windows.
The SQL command is executed using the 
button on the toolbar. Only the selection is executed if any text is selected; otherwise, the entire window content is sent to the server. The result and errors are printed under the script.
Creating a new database¶
If we have no database, we must create one first. In Object Explorer right-click Databases to open a dialog. We need to specify a name and leave all other options as-is. After creating a new database, we shall not forget to select the toolbar's current database for any Query window we have open!
Concurrent transactions¶
To simulate concurrent transactions, we need two Query windows; open two by pressing the New Query button twice. You can align these windows next to each other by right-clicking the Query tab title and selecting New Vertical Tab Group.
Listing and editing table content¶
To view any database table's content, open the database in Object Explorer, locate the table under Tables, then right-click and chose Select Top 1000 Rows or Edit Top 200 Rows.
Intellisense reload¶
Intellisense often does not work in SQL Management Studio query windows. Press Control+Shift+R-t to trigger a reload of Intellisense cache. We also need to use this after creating a new object (e.g., a new stored procedure).
Creating stored procedures and triggers¶
We can create new stored procedures or triggers by writing the T-SQL code to create them in a Query window. Once an item with the same name exists, we cannot create it but have to modify the existing one using the proper command.
Existing stored procedures are listed in Object Explorer under our database in the Programability/Stored Procedures folder. (Newly created items do not appear in the folder, but we have to refresh the folder content by right-clicking and choosing Refresh.)
Triggers are found in Object Explorer under the table on which they are defined in the Triggers folder (system-level triggers are in the Programability folder under the database itself).
The code of any existing stored procedure or trigger can be opened by locating them (see above), then right-clicking and choosing the Modify command. This will open a new Query window with an alter command and the current code of the program.