Over the last few years I have had a few friends ask about making a transition into working with data. I’ve spent some time asking them about what they are currently doing and then what they think they’d like to be doing more of so I can give them specific advice or direct them to follow specific SQL family¹. But consistently one question is “How do I get better at SQL?” The answer to that is simple: practice. But this can be tricky if one doesn’t already have access to data stored in SQL in their day to day job duties. For a while I’ve meant to put together a quick guide for gathering all the different bits to be able to practice at home. So here part 1 of my my guide for setting up an environment to practice writing SQL queries on your personal home pc.

Installing SQL Express

In order to practice writing SQL you’ll need to install an instance of SQL Server where you can access it. For 90% of us that means installing in on our home PC. You can download the installer from Microsoft here. Simply choose the SQL Express download. This is a limited version of SQL Server that is free but limits hosted databases to no more than 10 GB in size. Simply run the basic install without customization.

Open your start menu and type Services to find and start the Services app. Scroll through the list of services and you should see one called SQLExpress that is now running. You can stop and start the service here as needed. My recommendation would be to right click the service and select Properties and set the service to manual start. This ensures that it will only run and consume resources when you start it to practice².

Installing Azure Data Studio

Installing a local SQL Express instance doesn’t give us any tools with which to actually interact with data so we need to install either SQL Server Management Studio (SSMS–the current SQL Express installer gives this as an option after the SQL engine is installed) or Azure Data Studio (ADS). I would recommend Azure Data Studio as it the UI is superior and most of the functionality that is “missing” tends to be for database administrators or other folks that need to manage the SQL infrastructure. If this sounds like something you are interested in by all means install it and explore (we’ll assume that ADS has been installed and tailor instructions for that tool). The ADS user installer can be found here. Again simply run the installer and let it do it’s thing.

Connect to Your New Instance

Now we have a place to host data (SQL Server) and a tool to connect to and query that data (ADS/SSMS). Before we go too much farther we want to confirm the instance is running and change some settings to be sure we don’t crash our laptop while practicing. Open Azure Data Studio. Select the Servers Icon ( ) and add a new connection. For the Server you can enter localhost\SQLExpress. This is the pointer to the running SQL Service (if you have stopped the SQL service you will need to start it before this connection can be made). This connection will be saved to your servers list to be reused in the future. Right click the server and select New Query. This will open a query window in the main pane that we will do our work in. First we want to ensure that SQL won’t user all the RAM on our computer. This will impact how long SQL may take to run queries but practicing at home isn’t going to be about returning large data set quickly, it’s going to be returning correctly filtered data. First set the maximum memory for SQL to no more than half of the available memory for your PC (in MB). My laptop has 8 GB of RAM so I don’t want to give SQL more than 4 GB of that. If you don’t know how much RAM your machine has right click your task bar and select Task Manager. Select the Performance tab and then Memory. We’ll also want to limit SQL Server to a limited number of processors as well. This setting is called Max Degree of Parallelism. Again this number should be less than the number of logical processors on your machine. My laptop has 8 logical processors so I will tell SQL to use no more than 2 for any given query. Since no one else will running queries against the databases on my machine this will prevent SQL from keeping other processes (like the OS) on my laptop from getting the memory or processor time they need to function.

/* Set the maximum memory SQL can use: */
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 2000;
GO
EXEC sp_configure 'max degree of parallelism', 2;
GO  
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

Getting Practice Data

The last step is set up our data set to practice with. Microsoft maintains a couple of sample databases for this purpose. You can find options to download a version of the AdventureWorks database here. Select the version closest to your installed version of SQL Server (at the time of this writing you likely downloaded and installed SQL 2022 and will want the 2019 version of AdventureWorks). You could also choose to download the Stack Over sample database (more instructions are here).

Once we’ve downloaded a data set (or more than one) we’ll need to restore the database backup to our local SQL instance.

DECLARE @DataPath NVARCHAR(150)
    , @LogPath NVARCHAR(150);

/* Update the desired database name here and in the restore statement below to match */    
SELECT @DataPath = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR(128)) + N'Adventureworks.mdf'
SELECT @LogPath = CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS NVARCHAR(128)) + N'Adventureworks.ldf'
 
/*  Ensure the path to the backup on your local machine is correct 

    If you get errors that the logical names are not correct run

    RESTORE FILELISTONLY FROM DISK = 'path to .bak' 
    to get the list of logical names and update the MOVE statements accordingly. 
*/

RESTORE DATABASE AdventureWorks
    FROM DISK = 'C:\temp\AdventureworksDW2019.bak'
    WITH RECOVERY,
      MOVE 'AdventureWorksDW2017' TO @DataPath,
      MOVE 'AdventureWorksDW2017_Log' TO @LogPath;
GO

/* set the database compatibility mode to match SQL 2022 */
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 160;
GO

So long as no errors are raised you have restored your first database backup! Congratulations: if you stand too close to the SQL Server at your work you may now be voluntold/promoted to DBA. You’ll want to do some crash course training!