Get Free Tips
MSSQLTips.com
Home » Learning SQL Server
Professional Development Career

Learning SQL Server

written by Jeremy Kadlec 0 comments

Problem

Every industry has it – jargon. As a professional new to SQL Server, I am interested in learning about SQL Server terminology, what it all means and how I can learn more.

Solution

Let’s start from the top and dive into the common SQL Server terminology to help you build your skill set.

Microsoft SQL Server

SQL Server is relational database management system software that runs on Windows or Linux on a physical or virtualize hardware platform to support applications and users. The SQL Server would be considered the highest level in the overall hierarchy to support databases, data, security, high availability, reporting and business intelligence.

Some people refer to SQL Server as the hardware platform, others as software or an application. And still others refer to SQL Server as the database engine.

Current versions include SQL Server 2019, SQL Server 2017, SQL Server 2016, 2014, etc. for Enterprise Edition, Standard Edition, Developer Edition, Express Edition and Web Edition.

Additional Information:

SQL Server Instance

A SQL Server instance is an installation of the SQL Server relational database platform that can be considered the highest level in the software hierarchy.  The SQL Server instance encapsulates all of the database concepts (database, tables, data, stored procedures, security, etc.) covered in this article.

A single server (i.e. hardware platform) can have numerous SQL Server instances installed, each of which can be completely autonomous, but share the underlying CPU, memory, network and storage resources.  This configuration can be used for Production, Quality Assurance \ Testing \ User Acceptance Testing and\or Development.  This is a separate configuration from virtualization.

Additional Information:

SQL Server Service

The SQL Server Service is the main Windows service that controls whether the SQL Server instance is started or stopped.  Each SQL Server instance has a corresponding Windows service.  This Windows service can be configured to run with a domain account to access resources on the network or a local system account.

Additional Information:

SQL Server Agent

SQL Server Agent is responsible for running Jobs on a predefined schedule. Jobs can include T-SQL, PowerShell, SSIS, Python, R, DOS, MDX, etc. commands. Operators can be configured and notified via email or text when a Job completes, fails or succeeds. SQL Server Agent also includes Alerts that can be configured for when performance or operational thresholds are met.

Additional Information:

 

Virtual Machine

A Virtual Machine is an independent operating system and SQL Server installation with management similar to a SQL Server.  The difference is that there is a virtualization layer between the physical hardware and the virtual machine.  A single Host will support numerous Guests (virtual machines).

Virtualization is used to consolidate numerous independent machines onto a single hardware platform to reduce power and resource costs.  Each server remains a separate entity that still requires management and maintenance.  Windows and SQL Server licensing is still required for each virtual machine.

Additional Information:

Host

A Host is the hardware platform (i.e. server) with the virtualization software to support one or many Guest virtual machines.

Guest

A Guest is an independent virtual machine with an operating system and SQL Server installed.

VMWare

At the time of writing, VMWare is the most used virtualization platform to support virtualized environments.

Relational Database Management System

SQL Server is one of many Relational Database Management Systems on the market such as Oracle, PostgreSQL, Azure, Azure SQL Database, MySQL, etc. The Relational Database Management System manages all of the databases, data, transactional integrity, security, user access, etc. This could be for data warehouse projects, business intelligence and more.

SQL Server Database

A database is a logical container for data, structures and code with a specific name.  A single database can support one or more applications and users.  Permissions can be granted at the database level, but permissions are generally better suited to be granted to an application that a user needs at the object level such as a stored procedure, table or view.

In SQL Server there are both system and user defined databases.  At the most basic level, a database consists of two physical files.  First is for the database objects and data.  The second file is for the transaction log which is used to maintain transaction integrity as numerous users and applications are adding, removing and modifying data within the database.

Additional Information:

SQL Server System Database

The SQL Server system databases are responsible for particular functions of the underlying relational database management system which includes:

  • Master – All system objects to run the active relational database management system
  • Model – Template database for new user defined databases and Tempdb when SQL Server starts
  • TempDB – Stores all temporary objects such as #temp tables, ##temp tables, hash and sort records, etc.
  • MSDB – Stores all SQL Server Agent related tables and stored procedures
  • ResourceDB – Hidden and read-only database that includes all system objects

Additional Information:

SQL Server User Database

A SQL Server user defined database is created to support a specific application such as CRM, eCommerce, Inventory, Reporting, etc.  Each user defined database on a SQL Server instance has a unique name and has a database owner which is generally the login who created the database.  The User Defined database has one or more schemas and each schema has tables, indexes, stored procedures, views, functions and more.

MDF File in SQL Server

The SQL Server MDF file is one of two core files to support a physical SQL Server database.  The MDF file is responsible for storing the system objects, user defined object definitions (i.e. tables, views, stored procedures, etc.), data and indexes.  Each SQL Server database must have an MDF file and only one MDF file can exist per database.  The MDF file generally is the largest file supporting the database since the size of the data should be much larger than the space needed to support the transaction log.

NDF File in SQL Server

A SQL Server NDF file is a secondary data file to support a physical SQL Server database.  Although it is not mandatory for a SQL Server database, an NDF file can support tables and indexes.  NDF files are created for SQL Server databases to store a database across numerous disk drives to improve performance or support the database growth.

LDF File in SQL Server

The SQL Server LDF file is the second of two core files to support a physical SQL Server database.  The LDF file supports the database transaction log which stores before and after versions of records to enable a transaction to rollback.  The LDF file is generally a fraction of the database file size, perhaps five to twenty five percent of the database.  There are circumstances with large transactions where the transaction log file may need to be sized larger.

Additional Information:

SQL Server Database Transaction Log File

The SQL Server Transaction Log File corresponds to the physical LDF file supporting the database.  The SQL Server Transaction Log File is responsible for storing before and after records of data involved in UPDATE or DELETE transactions in order to rollback the transactions to maintain transactional integrity with numerous users in the database.

Additional Information:

SQL Server Schema

A schema is a logical container within a database to grant permissions to particular objects.  By default, each SQL Server database has a dbo and sys schema.  Although user defined objects can be created in the dbo schema, it is generally recommended to create a separate schema for each logical application such as sales, inventory, accounting, etc.

SQL Server Table

A table is a logical structure to store data related to a single entity such as customer, product, order, etc.  Each table has one or more columns.  Each column should be configured with the appropriate data type and null setting.

Row in a SQL Server Table

A single record in a table that consists of multiple columns.

SQL Server Data Types

Data types are defined for columns in tables and parameters for stored procedures, functions and queries.  The data type determines the acceptable data for the column or parameters.  It is prudent to select the appropriate data type for your columns and parameters to minimize the amount of storage that is needed at a table level and avoid implicit or explicit conversion issues which could be a performance problem in the long term.  Common data types are INT, DATETIME, VARCHAR(), CHAR(), etc.

Additional Information

NULL in SQL Server

Null is an unknown value.  A column in a table can be configured to allow a null value or not.

Additional Information:

Referential Integrity in SQL Server

Referential integrity maintains consistent data from a primary key on one table to a foreign key on a related table.  For example, with an Orders table the primary key could be OrderID.  A related table could be OrderDetails, which should include the OrderID column from the Orders table.  On the OrderDetails table, the OrderID column would be the foreign key related back to the OrderID column from the Orders table.

SQL Server Primary Key

A primary key is generally a single column, but could be two or more columns, that uniquely identifies a row in a table.  A table should have a primary key to uniquely identify each row.

SQL Server Foreign Key

A foreign key is generally a single column, but could be two or more columns, that relates to another table to ensure data is consistent.

SQL Server Indexes

SQL Server indexes are physical structures that are built to improve data access performance.  Without an index, a table scan will occur, which means the SQL Server Query Processor is scanning the table from the first to last row to complete the query.  This is an expensive operation with a significant amount of data and a significant user base.

SQL Server indexes should be chosen wisely based on the data access patterns.  Having too many or duplicate indexes is counterproductive and will impact the performance of INSERT, UPDATE and DELETE operations as well as excessive storage.  Indexes also need to be maintained (rebuilt or reorganized) on a regular basis, either weekly, monthly or quarterly.

SQL Server ships with numerous types of SQL Server indexes that all provide specific value based on the need.  The SQL Server indexes include:

  • Clustered Index
  • Non Clustered Index
  • Covering Index
  • Included Columns
  • Filtered Index
  • ColumnStore Index
  • Full Text Index
  • XML Index

Additional Information:

SQL Server Clustered Index

A SQL Server Clustered Index brings physical and logical order to a table.  There is a single SQL Server clustered index created per table.  Typical columns are a unique ID such as the Primary Key for the table or date column such as OrderDate.  It is a best practice to create a clustered index for most tables to improve data access performance when querying the table.

Additional Information:

SQL Server Non Clustered Index

One or more SQL Server Non Clustered Indexes can be created per table.  Non Clustered Indexes are generally used for range queries, Foreign Keys or on columns used in WHERE, ORDER BY and GROUP BY clauses to fulfill specific queries.  The more selective the data is in the column, the greater benefit the index will provide to the query performance.

Additional Information:

SQL Server Stored Procedure

A stored procedure is an object created within a database and schema which includes business logic to perform a discrete operation such as retrieve, add, modify or delete data.  Stored procedures are written with T-SQL code and permissions are granted to execute the code.  Stored procedures are given a logical name.  Zero, one or many parameters can be passed into a stored procedure to make them modular and one or more result sets can be returned by a single stored procedure.  Once a stored procedure is created, it can be modified with an ALTER command or deleted with a DROP command.

Additional Information:

SQL Server Query

A query is written with T-SQL code and is not stored within a database, but rather in an external file.  The user executing the query needs to have permissions to the tables and views that are accessed with the query.  The query can be used to retrieve, add, modify or delete data.  A query can have one or more parameters as well as one or more result sets.

Additional Information:

SQL Server Function

There are two types of SQL Server Functions – System and User Defined.  System functions are created by Microsoft and ship with SQL Server.  System Functions perform string manipulations, date calculations, statistical calculations and more.  These functions are fairly generic and used across numerous applications.  User Defined Functions are created by Developers and DBAs at specific organizations to meet a particular business need.  The User Defined Functions can be scalar valued (return a single value), inline table valued (returns multiple columns) or multi statement table valued (returns multiple columns and rows).

Additional Information:

SQL Server View

A SQL Server View is a virtual table.  The code that comprises the View is a SELECT statement that accesses one or more tables via a JOIN statement.  A View can be used to simplify a complex query or to simplify reporting for power users.  Permissions for users or groups should be granted to Views.

Additional Information:

SQL Server Management Studio

SQL Server Management Studio (SSMS) has been the long-standing tool available from Microsoft to administer and develop database objects with T-SQL code. SQL Server Management Studio enables you to connect to instances of the database engine, Integration Services, Reporting Services and Analysis Services that are on-premises, at a co-location facility and in some circumstances, in the cloud. SSMS originally shipped with the database engine, but has subsequently become a separate download directly from Microsoft.

From an administration perspective, SSMS enables DBAs to write T-SQL code to perform tasks such as backups, restores, configure databases, tune performance, schedule jobs and more. SSMS also includes user interfaces and wizards to perform many of these same tasks. On many of these interfaces, you can also get a copy of the T-SQL code that is generated to automate the process across numerous instances or to include in your source control system as the change progresses from development to test and production systems.

From a development perspective, SSMS enables Developers and Business Intelligence Professionals to build tables, stored procedures, views, functions, etc. There are also user interfaces to visually build database diagrams and tables to better understand relationships among objects. SSMS also includes visual query plan analysis to understand the interworking’s of your T-SQL code in order to optimize it.

Additional Information:

SQL Server Profiler and Server Side Trace

SQL Server Profiler has been a long-standing client side tool run by DBAs or Developers to capture each and every line of code, in the form of events, being executed against a SQL Server instance to tune performance. Profiler can generate a tremendous amount of data and potentially impact the performance of the monitored system if not configured correctly on an already taxed SQL Server instance. Profiler can be fine-tuned to only capture event data for specific users, databases, time periods, etc. Profiler data can be written to a table for further analysis or to a text file.

A related process is the Server Side Trace which is a set of T-SQL code which can be executed against a SQL Server instance either via SQL Server Management Studio or a SQL Server Agent Job to capture the same events and write them to a file. The file is generally loaded to a table to perform analysis. The Server Side Trace generally has less of a performance impact on the monitored SQL Server instance as compared to Profiler.

In general, Profiler has been replaced by Extended Events due to the performance implications, but some SQL Server Professionals still prefer Profiler and Server Side Trace for ad-hoc or specific performance tuning cases.

Additional Information:

SQL Server Extended Events

In many respects, SQL Server Extended Events have grown out of the original SQL Server Profiler and Server Side Trace functionality. Many of the same events and configurations are available in Extended Events in the SQL Server Management Studio Wizard or T-SQL code. Extended Events can be run to perform data collection for performance tuning or monitor for operational items such as a database that goes offline. SQL Server also ships with some default Extended Event sessions and templates which can be used as a starting point to collect data. Keep in mind this data can become large very quickly, so it is not a configure it and forget it situation. It is necessary to only capture the needed events and associated data then monitor this process as a portion of your daily tasks.

Additional Information:

Performance Monitor

Performance Monitor (or sometimes referred to as Perfmon or System Monitor) is a free tool that ships with Windows to capture real time (or via a predefined schedule) macro level performance statistics related to the Windows operating system and SQL Server. The Performance Monitor counters provide either a usage percentage or a count of a particular event for a specific resource or application. Performance Monitor counters do not drill into a specific line of code. At a high level, the Windows counters are related to memory, CPU, disks, etc. and from a SQL Server perspective are related to scan methods, recompiles, buffer cache hit ratio, memory grants, etc.

Using both Performance Monitor and Profiler was a long time performance monitoring trick to understand the macro level statistics such as a CPU spike with Performance Monitor and then see the corresponding code executed during that time frame with Profiler. Here is one tip covering that technique – Correlating Performance Monitor and SQL Server Profiler Data.

Additional Information:

SQLCMD

SQLCMD is a command line tool that is run at the Windows command prompt to connect to the SQL Server relational engine and execute T-SQL code. SQLCMD can be used to automate execution of particular script files or for troubleshooting purposes. Everything that can be performed in SQL Server Management Studio can also be run with SQLCMD with the corresponding T-SQL commands.

Additional Information:

BCP

BCP is a command line tool to import or export data between a text file and a SQL Server table or view. BCP can accept a number of parameters to format the data as needed. BCP in many respects has been replaced by SQL Server Integration Services and Azure Data Factory due to the graphical nature of the newer tools and additional functionality. However, BCP remains a consistent tool that is easy to integrate for scripted deployments.

Additional Information:

DBCC

In SQL Server, DBCC is the acronym for Database Console Command. These commands are intended to retrieve low-level status data for databases as well as correct specific settings or data within a database.  They may not be commands you use on a daily basis, but are very useful when needed.

  • DBCC CHECKALLOC
  • DBCC CHECKCATALOG
  • DBCC CHECKDB
  • DBCC CHECKPRIMARYFILE
  • DBCC CHECKTABLE
  • DBCC CLONEDATABASE
  • DBCC DROPCLEANBUFFERS
  • DBCC FREEPROCCACHE
  • DBCC INPUTBUFFER
  • DBCC LOGINFO
  • DBCC OPENTRAN
  • DBCC OPTIMIZER_WHATIF
  • DBCC PAGE
  • DBCC SHOW_STATISTICS
  • DBCC SHOWCONTIG
  • DBCC SHRINKFILE
  • DBCC SQLPERF(logspace)
  • DBCC UPDATEUSAGE
  • DBCC WRITEPAGE

Additional Information:

DBCC CHECKDB

DBCC CHECKDB checks the consistency of a SQL Server database to ensure there is no corruption at the data or index page level. There are parameters that can be passed to this command to correct corruption including correcting the corruption at the price of losing data.

Additional Information:

DBCC CHECKTABLE

DBCC CHECKTABLE checks the consistency of a specific SQL Server user defined table in a user defined database for corruption. DBCC CHECKTABLE can be run on larger tables, if there is not sufficient time to run DBCC CHECKDB or after correcting corruption on a specific table. However, it is prudent to run DBCC CHECKDB to ensure there is not corruption on every object after corrective measures have been taken to correct database corruption.

DBCC CHECKALLOC

DBCC CHECKALLOC checks the internal allocation structures for a user defined SQL Server database. DBCC CHECKALLOC is included as a check when DBCC CHECKDB is run.

Additional Information:

DBCC CHECKCATALOG

DBCC CHECKCATALOG checks the consistency of the system catalog (i.e. objects). DBCC CATALOG is included as a check when DBCC CHECKDB is run.

Additional Information:

DBCC PAGE

DBCC PAGE is used to review the contents of SQL Server data and index pages. This is generally used when troubleshooting corruption.

Additional Information:

DBCC WRITEPAGE

DBCC WRITEPAGE is a command to test and correct SQL Server database corruption. This command can cause corruption and should only be used in development or test environments.

Additional Information:

DBCC CHECKPRIMARYFILE

DBCC CHECKPRIMARYFILE reads the meta data from the primary database data file on disk such as the database name, logical file name, physical file name, etc. One reason to do so is to use the output from this command to attach databases.

Additional Information:

DBCC SHRINKFILE

DBCC SHRINKFILE is used on a SQL Server database to shrink an existing database or transaction log file with the ability to free up disk space on the drive.

Additional Information:

DBCC CLONEDATABASE

DBCC CLONEDATABASE creates a copy of an existing SQL Server database with only the objects and statistics. No data is included so the cloned database is generally a fraction of the size of the original version. DBCC CLONEDATABASE is helpful to use when trying to perform query tuning or move the database structure to a development or test environment without the data.

Additional Information:

DBCC DROPCLEANBUFFERS

DBCC DROPCLEANBUFFERS is a means to clear cache for testing purposes in a SQL Server development or test environment. This should not be run in a production environment.

Additional Information:

DBCC FREEPROCCACHE

DBCC FREEPROCCACHE is a means to clear the procedure cache for testing purposes in a SQL Server development or test environment. This should not be run in a production environment.

Additional Information:

DBCC INPUTBUFFER

DBCC INPUTBUFFER with the System Process ID passed to it displays the last run command for that specific System Process ID in SQL Server. Depending on the size of the code issued, this command may not return all code run.

Additional Information:

DBCC OPTIMIZER_WHATIF

DBCC OPTIMIZER_WHATIF in SQL Server provides the ability to perform query plan simulation for differing hardware configurations between production and development as an example.

Additional Information:

DBCC UPDATEUSAGE

DBCC UPDATEUSAGE in SQL Server updates the internal page counts that sometimes become inaccurate with a large data load or deletion. DBCC UPDATEUSAGE can help the SQL Server optimizer obtain accurate data for query plans.

Additional Information:

DBCC SHOW_STATISTICS

DBCC SHOW_STATISTICS provides statistics for a particular SQL Server table including the most recent UPDATE STATISTICS, row count, histogram steps, etc.

Additional Information:

DBCC SQLPERF(logspace)

DBCC SQLPERF(logspace) reports the amount of the SQL Server transaction log is in use.

Additional Information:

DBCC LOGINFO

DBCC LOGINFO reports on the virtual logs in the SQL Server transaction log.

Additional Information:

DBCC OPENTRAN

DBCC OPENTRAN reports the open transactions in a SQL Server database.

Additional Information:

DBCC SHOWCONTIG

DBCC SHOWCONTIG reports on SQL Server table fragmentation.

Additional Information:

SQL Server Maintenance Plan

SQL Server Maintenance Plans are included in SQL Server Management Studio as a point and click option to perform SQL Server database backups, rebuild indexes in SQL Server, reorganize indexes in SQL Server, SQL Server DBCC CHECKDB and more.  The SQL Server Maintenance Plans are actually SQL Server Integration Services Packages that are created and saved to the instance.  The maintenance plans can be configured for all databases and scheduled to run on a regular basis.  Depending on the workload for the SQL Server environment, maintenance can be scheduled at night, over the weekends, on a monthly basis, etc.  The benefit of performing the SQL Server database maintenance is to ensure the databases are free of corruption and able to achieve high performance.

Additional Information:

SQL Server Maintenance

Beyond the SQL Server Maintenance Plans included with SQL Server Management Studio, SQL Server database maintenance can be performed with DBCC Commands, index rebuilds, index reorganization, database backups, database restores, UPDATE STATISTICS, etc.  These are T-SQL commands that can be run in SQL Server Management Studio or can be scheduled via SQL Server Agent.  The benefit of using the T-SQL commands is that additional parameters can be included and code can be built dynamically to include or exclude particular databases without manual intervention.

Additional Information:

SQL Server Backup

SQL Server Backups are a critical process which generate point in time snapshots of a database or transaction log.  SQL Server Backups include: Full, Differential, File, Transaction Log, Copy_Only and Snapshot.  SQL Server backups can be created in SQL Server Management Studio or via Transact-SQL code. SQL Server Backups are generally scheduled on a regular basis to provide point in time recovery in case an issue occurs.  Depending on the organizational or application requirements, Full backups are executed daily and transaction log backups are issued throughout the business day, perhaps every one to fifteen minutes. Keep in mind that SQL Server Backups are a point in time version of your online database, so these files need to be protected and secured to ensure your data is not compromised.  Often times, SQL Server Backups are the last resort to get a database operational and continue business operations.  It is prudent to verify backups run accurate on a predefined basis, to issue backups prior to any major changes to a database and to retain a backup prior to retirement for long term retention.

Additional Information:

Restore Database in SQL Server

A SQL Server Restore is the process of using a previously generated SQL Server Backup to create a new database or overwrite the existing database.  The SQL Server Restore options include Full, Differential, File, Transaction Log and Snapshot.  A SQL Server database may need to be restored if there is an issue with the online database, data needs to be recovered, testing needs to be performed or a new development environment needs to be setup.  Keep in mind all of the production data will be restored, so it is imperative to properly secure the database once restored and either obfuscate or delete sensitive data, if it is not appropriate for the new environment.  Also, it is prudent to test backups by restoring them on a regular basis to ensure the restore process is understood and can be executed under pressure. 

Additional Information:

Next Steps

Copyright (c) 2006-2025 Edgewood Solutions, LLC All rights reserved
sql server 2025 guide