All Posts – Page 2 – Database Math

Learning Series: MSSQL Server | Introduction

Lesson 01 : Introduction

Too often, technical documentation makes too many assumptions about prior knowledge of terminology and standards for beginners to comfortably get started. So, to kick off this learning series, let’s go over some of the terminology and concepts we will be using when learning about SQL Server.

NOTE: Even if you are an experienced database professional or programmer, I encourage you to review this article anyway because identical terminology is often used across different RDBMS with not identical meaning.

Microsoft SQL Server

Relational Database Management System (RDBMS)

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. Other data storage and processing models have become increasingly popular over the years but RDBMS are still widely used for data warehouse projects, business intelligence and more.

SQL Server Business Intelligence (BI)

SQL Server Integration Services (SSIS)

Integration Services at its core is used to perform ETL for a data warehouse. ETL is an industry term that stands for Enhance, Transform, Load. Basically moving data between one database and another. The databases don’t need to be on the same platform either, they can be SQL Server databases, flat files or other database platforms such as Oracle, DB2, Access, Sybase, PostgreSQL, cloud, etc.

SQL Server Reporting Services (SSRS)

Reporting Services provides a set of on-premises tools and services that create, deploy, and manage mobile and paginated reports from Microsoft SQL Server. It is not uncommon for SSRS to be installed on a different machine from the SQL Server Database Engine instance it services.

SQL Server Analysis Services (SSAS)

A typical implementation workflow includes installing a SQL Server Analysis Services instance, creating a tabular or multidimensional data model, deploying the model as a database to a server instance, processing the database to load it with data, and then assigning permissions to allow data access. When ready to go, the data model can be accessed by any client application supporting Analysis Services as a data source.

Power BI

Power BI is a business analytics service. It aims to provide interactive visualizations and business intelligence capabilities with an interface simple enough for end users (Those who aren’t developers or DBAs) to create their own reports and dashboards.

NOTE: I think it is important to mention the SQL BI products now so that you can begin to understand how they fit into the overall eco system. However, this learning series will focus on the SQL Server Database Engine.

SQL Server Database Engine

Microsoft SQL Server originally created as a variant of Sybase SQL Server for IBM OS/2 and released in 1989. Since then there have been numerous releases but as of this writing the most recent (and still actively supported) versions are SQL Server 2012, 2014, 2016, 2017, and 2019.

The product names for SQL Server are denoted by the year of their release. The major version is often referenced in code and configuration metadata as a two to three digit number following this scheme:

Product Name	Major Version	RTM Build Number
SQL Server 2019	150	15.0.2000.5
SQL Server 2017	140	14.0.1000.169
SQL Server 2016	130	13.00.1601.5
SQL Server 2014	120	12.00.2000.8
SQL Server 2012	110	11.00.2100.60
SQL Server 2008 R2	100	10.50.1600.1
SQL Server 2008	100	10.00.1600.22
SQL Server 2005	90	9.00.1399.06
SQL Server 2000	80	8.00.194

Notice that the major version and build number correlate as well.

As a beginner the term “SQL Server” may seem confusing because it is often used to mean different things in different contexts including: the physical hardware server, the application, or the database engine. To clarify, when I reference SQL Server in my posts I am referencing the database engine. If I am referencing the software product I will use the full product name which is “Microsoft SQL Server”. If I am referring to the machine which hosts a SQL Server Instance, I will say some variation of “SQL Machine”, “SQL Node”, “SQL Host”, “SQL VM”, “SQL Box”, ect.

On Premises vs. Cloud

NOTE: For this learning series I will be focusing mostly on Microsoft SQL Server On Premises.

On Premises

Microsoft SQL Server was originally and still is developed as an On Premises (abbreviated as On Prem) software product meaning that it is installed on hardware owned and managed by you or your organization on a physical or virtual machine with an operating system (Windows or Linux).

Cloud: Azure SQL Product Family

With the rise of the enterprise cloud, Microsoft began offering different flavors of Microsoft SQL Server in Azure. Each has its own advantages/disadvantage and level of tradeoff between administration/management responsibilities and freedom/flexibility. These offerings include:

SQL Server on Azure Virtual Machines*
Azure SQL Managed Instance
Azure SQL Database
Azure SQL Edge

NOTE: While hosted in the cloud, SQL Server on Azure VMs is the same On Prem Microsoft SQL Server product, just installed on a virtual machine running in Azure instead of on your own machine.

SQL Server Instance

A SQL Server instance is a single installation of the SQL Server software. This instance encapsulates all the other database system structures (database, tables, data, stored procedures, security, ect.)

A given physical server can host or have installed multiple instances of SQL Server of either the same or different versions. (Although certain components are shared between them but more on that later).

Often you will have multiple instances of SQL Server installed either on the same machine or on different machines supporting the same connected application(s) to be used for Production (PROD), Quality Assurance (QA), Testing (TEST), User Acceptance Testing (UAT), and/or Development (DEV).

SQL Server Services

SQL Server components are executable programs that run as a Windows service. Programs that run as a Windows service can continue to run even without a user actively logged in and without displaying a window or other activity in the GUI.

SQL Server Service

The executable process that is the SQL Server Database Engine. The Database Engine can be the default instance (limit one per computer) or can be one of many named instances of the Database Engine. Use SQL Server Configuration Manager to determine which instances of the Database Engine are installed on the computer. The default instance (if you install it) is listed as SQL Server (MSSQLSERVER). Named instances (if you install them) are listed as SQL Server (<instance_name>). By default, SQL Server Express is installed as SQL Server (SQLEXPRESS).

SQL Server Agent

This is a job scheduling service. It connects to the SQL Server instance and executes jobs defined by the user either on demand, on startup or by a user defined schedule. This service is not strictly necessary for SQL Server operation and is actually not included in the Express Edition of SQL Server.

SQL Server Browser

Shared and used by all installed instances of SQL Server on a given machine. It is responsible for responding to incoming requests with client information for connecting to SQL Server. This service can be disabled if the only instance in use is the default instance (MSSQLSERVER) and it is running on the default static port (TCP 1433).

Host/Box/Physical Machine/Bare Metal

The hardware platform (i.e. server) with the virtualization software to support one or many Guest virtual machines. The host may also have SQL Server installed directly on it without any virtualization.

Virtual Machine (VM)

A virtual environment that functions as a virtual computer system with its own CPU, memory, network interface, and storage, simulated on physical hardware (host).

Hyper Visor/ Virtual Machine Monitor (VMM)

The software application running on the host that is responsible for managing one or more virtual machines.

Database

The logical container for stored data. It encapsulates all the other data structures.

System Databases

System database	Description
master Database	Records all the system-level information for an instance of SQL Server.
msdb Database	Is used by SQL Server Agent for scheduling alerts and jobs.
model Database	Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.
Resource Database	Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
tempdb Database	Is a workspace for holding temporary objects or intermediate result sets.

User Databases

All other databases created on a given instance of SQL Server. Each of which has a unique name and an owner. The User Defined database has one or more schemas and each schema has tables, indexes, stored procedures, views, functions and more.

Database Files

SQL Server databases have three types of files, as shown in the following table.

File	Description
Primary	Contains startup information for the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension for primary data files is .MDF.
Secondary	Optional user-defined data files. Data can be spread across multiple disks by putting each file on a different disk drive. The recommended file name extension for secondary data files is .NDF.
Transaction Log	The log holds information used to recover the database. There must be at least one log file for each database. The recommended file name extension for transaction logs is .LDF.

Note that while the .MDF, NDF and LDF file extensions are recommended, you can technically make them whatever you want, but as a Support Engineer I beg you not to.

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.

Table

Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-column format similar to a spreadsheet. In fact, a spreadsheet can be thought of as a “flat” database whereas SQL Server is a “relational database” because it is modeled around the relationships between tables.

A table is a collection of zero or more rows where each row is a sequence of one or more column values.

Row

The row is the smallest unit of data that can be inserted into a table and deleted from a table.

Cardinality

The degree of a table, and the degree of each of its rows, is the number of columns of that table. The number of rows in a table is its cardinality. A table whose cardinality is 0 (zero) is said to be empty.

Transactional Structured Query Language (T-SQL)

A set of programming extensions from Sybase and Microsoft that add several features to the Structured Query Language (SQL), including transaction control, exception and error handling, row processing and declared variables.

Constraints

Constraints are the rules enforced on the data columns of a table. These are used to limit the type of data that can go into a table.

Referential Integrity

Although the main purpose of a foreign key constraint is to control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table. To successfully change or delete a row in a foreign key constraint, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table, which links the foreign key to different primary key data.

Primary Key (PK)

A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table.

Foreign Key (FK)

A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table. In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table.

Indexes (IX)

An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

Heap

A heap is a table without a clustered index. One or more nonclustered indexes can be created on tables stored as a heap. Data is stored in the heap without specifying an order. Usually data is initially stored in the order in which is the rows are inserted into the table, but the Database Engine can move data around in the heap to store the rows efficiently; so the data order cannot be predicted.

Clustered Index (CI)

A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.

Non Clustered Index (NCI)

A nonclustered index can be defined on a table or view with a clustered index or on a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table.

Query

A query is a question or inquiry about a set of data. In SQL Server we use T-SQL to retrieve meaningful and relevant information from databases. When building a structure, we pull data from tables and fields. The fields are columns in the database table, while the actual data makes up the rows.

Store Procedure (SP)

A stored procedure in SQL Server is an executable object that consists of a group of one or more Transact-SQL statements or a reference to a Microsoft .NET Framework common runtime language (CLR) method. They can accept input parameters, call other stored procedures, and return success/failure messages.

Common Language Runtime (CLR)

With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

Ad Hoc Query

Ad hoc is Latin for “for this purpose”. Sometimes ad hoc queries may be informally referred to as “on the fly” because unlike stored procedures they are not predefined. Queries such as SELECT * FROM sys.databases is an example of an Ad hoc query that you may issue from SSMS. However, they can also be dynamically generated by code on the application side and executed against SQL Server.

Data Manipulation Language (DML)

These are statements used to retrieve and work with data. Microsoft SQL Server uses the following DML statements:

BULK INSERT	SELECT
DELETE	UPDATE
INSERT	UPDATETEXT
MERGE	WRITETEXT
READTEXT

Source: https://docs.microsoft.com/en-us/sql/t-sql/statements/statements?view=sql-server-ver15#data-manipulation-language

Data Definition Language (DDL)

These are statements that define data structures. Use these statements to create, alter, or drop data structures in a database. Microsoft SQL Server uses the following DDL statements:

ALTER	Collations
CREATE	DROP
DISABLE TRIGGER	ENABLE TRIGGER
RENAME	UPDATE STATISTICS
TRUNCATE TABLE

Source: https://docs.microsoft.com/en-us/sql/t-sql/statements/statements?view=sql-server-ver15#data-definition-language

Data Control Language (DCL)

These statements are used for access control and permission management for users in the database. Use these statements to allow or deny some actions for users on the tables or records. Microsoft SQL Server uses the following DCL statements:

GRANT	REVOKE
DENY

Source: https://social.technet.microsoft.com/wiki/contents/articles/34477.sql-server-commands-dml-ddl-dcl-tcl.aspx

Transactional Control Language (TCL)

These statements are used for managing transactions in the database. Use these to manage the transactions that are performed by DML statements. Microsoft SQL Server uses the following TCL statements:

BEGIN TRAN	COMMIT TRAN
ROLLBACK	SAVEPOINT
AUTOCOMMIT

Source: https://social.technet.microsoft.com/wiki/contents/articles/34477.sql-server-commands-dml-ddl-dcl-tcl.aspx

Function

A stored program that you can pass parameters into and return a value, much the same as in any programming language.

View

A view is a virtual table whose contents are defined by a query. Like a table, a view consists of a set of named columns and rows of data. You can think of it as instructions for how to piece together two tables temporarily. If not indexed, the view does not persist as stored data (i.e. will be discarded then generated again the next time it is used)

Triggers

A mechanism which fires a particular stored procedure in response to an event.

Logon Triggers

Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server.

DDL Trigger

DDL triggers fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE STATISTICS.

DML Trigger

DML triggers is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML events include INSERT, UPDATE, or DELETE statements. DML triggers can be used to enforce business rules and data integrity, query other tables, and include complex Transact-SQL statements.

Tools

Below are some of the tools that are commonly used when interacting with Microsoft SQL Server.

SQL Server Management Studio (SSMS)

Manage a SQL Server instance or database with full GUI support. Access, configure, manage, administer, and develop all components of SQL Server, Azure SQL Database, and Azure Synapse Analytics. Provides a single comprehensive utility that combines a broad group of graphical tools with a number of rich script editors to provide access to SQL for developers and database administrators of all skill levels.

Azure Data Studio

A light-weight editor that can run on-demand SQL queries, view and save results as text, JSON, or Excel. Edit data, organize your favorite database connections, and browse database objects in a familiar object browsing experience.

SQL Server Data Tools (SSDT)

A modern development tool for building SQL Server relational databases, Azure SQL databases, Analysis Services (AS) data models, Integration Services (IS) packages, and Reporting Services (RS) reports. With SSDT, you can design and deploy any SQL Server content type with the same ease as you would develop an application in Visual Studio.

SQLCMD

A utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt.

SQL Server Powershell

Provides cmdlets for working with SQL.

SQL Server Configuration Manager

Use SQL Server Configuration Manager to configure SQL Server services and configure network connectivity. Configuration Manager runs on Windows.

NOTE: See here for a more comprehensive and current list of Microsoft SQL Server first party tools.

Summary

This introduction to the series is by no means meant to be a comprehensive overview of the SQL Server product, but more of primer to familiarize you with some of the concepts and terminology before we dive deeper. In the next post we will introduce the Transactional SQL (T-SQL) language and the anatomy of transactions.

MSSQL SERVER: Backup and Restore to Network Path Using XP_CMDSHELL

There might be times that you need to backup or restore databases in MSSQL Server to a network location. Out of the box, this isn’t the easiest thing to do. So I will show you the steps you can take to successfully backup and restore databases to and from network mapped drives.

Start by mounting a network drive (optional):

Enter the path for the network drive and click Next:

You will be prompted to enter credentials, do so then Finish

You’ll now see a new drive under Network Locations with your given drive letter:

However, if you go to SSMS and try to backup a database via the GUI you won’t be able to see the drive letter:

If you try to do it via script instead, you’ll get an operating system error:

Msg 3201, Level 16, State 1, Line 1

Cannot open backup device ‘Z:\test.bak’. Operating system error 3(The system cannot find the path specified.).

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

This is because SQL Server is registered and running as a service running under the context of the SQL Server service account. The service runs in the environment of the local console with the security of this service account. Network mapped drives are specific to a session and not visible to services started on the local console.

So how do we work around this? By using XP_CMDSHELL.

First, make sure XP_CMDSHELL is enabled:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

Next, use the following command, replacing Z: \\mymachine\localvmshare with your own network path:

EXEC XP_CMDSHELL 'net use Z: \\mymachine\localvmshare'

The drive should now be mapped. You can test that it is properly mapped with the following:

EXEC XP_CMDSHELL 'Dir Z: '

If you mistype the path you might see an error like these:

System error 53 has occurred. The network path was not found.

If you get this error just correct the typo and run the command again.

NOTE: To remove the mapping once you are done with it, or to remove a mapping that was mistyped, you can run the following command: EXEC XP_CMDSHELL 'net use Z: /delete'

Another error you could possibly get is this one:

The operation was canceled by the user.

If this occurs, modify the XP_CMDSHELL statement like so, where serviceaccount is a user that has permission to mount the network drive and passwordgoeshere is the password for the user account:

EXEC XP_CMDSHELL 'net use Z: \\mymachine\localvmshare /user:serviceaccount passwordgoeshere'

Now, in the GUI, we should be able to see our newly mapped drive in the backup/restore wizard:

If we rerun our script to backup, it is now successful:

And there is our backup file:

NOTE: The next time the SQL Server Service is restarted, the mapping will be removed and if you have automated backup jobs configured to use these drives, they will fail. If you decide to rely on this method for network drive backups, you will need to add logic to your backup jobs to check that the drive mapping exists first or you will need to have a startup proc configured to run and map the drive on restart.

In the case of the client I was working with, we needed to setup database mirroring via backup and restore but the local machine did not have enough disk space free to accommodate the backups. However, it is work noting that XP_CMDSHELL is a powerful feature that runs under the context of the SQL Server service account. Malicious users sometimes attempt to elevate their privileges by using XP_CMDSHELL so you should keep this in mind when deciding if you should use XP_CMDSHELL in your maintenance/backup strategy on a permanent basis.

For more information on XP_CMDSHELL, see here.

MSSQL SERVER: Schedulers and Threads

Microsoft SQL Server is a Service that runs inside of an OS (no longer exclusively on Windows – i.e. SQL Server on Linux). However, it has its own mechanisms for handling memory management, IO and thread scheduling. This is done through access to the host OS API in most cases, but SQL is still the one actively managing these resources.

Thread

A thread is the basic unit to which the operating system allocates processor time. A thread can execute any part of the process code, including parts currently being executed by another thread. Threads are the smallest units of processing that can be executed by an operating system, and allow the application logic to be separated into several concurrent execution paths.

SQL Server Worker Thread

Also known as worker or thread, is a logical representation of an operating system thread. When executing serial requests, the SQL Server Database Engine will spawn a worker to execute the active task (1:1).

Process

When an operating system executes an instance of an application, it creates a unit called a process to manage the instance. The process has a thread of execution. This is the series of programming instructions performed by the application code.

Request

In the scope of SQL Server, a request is the logical representation of a query or batch. A request also represents operations required by system threads, such as checkpoint or log writer. Requests exist in various states throughout their lifetime and can accumulate waits when resources required to execute the request are not available, such as locks or latches.

Task

A task represents the unit of work that needs to be completed to fulfill the request. One or more tasks can be assigned to a single request.

Scheduler

A scheduler, also known as SOS scheduler (meaning SQL OS scheduler but never actually referred to as such), manages worker threads that require processing time to carry out work on behalf of tasks.

Quantum

The amount of time that a thread is permitted to run on the processor before it must yield to another thread and return to the Runnable Queue or the Wait List.

Scheduler Components

At a high and somewhat abstracted level, schedulers have three main components: the Processor, Waiter List and Runnable Queue.

The Processor is the logical processor core which processes the threads one at a time. Each scheduler is mapped to a single processor.

The Waiter List represents the list of threads that are waiting for resources (such as latches and locks).

The Runnable Queue represents the group of threads that are not waiting on resources (as they have already acquired them) and are instead are waiting for their turn to run on a processor.

The thread of a process will move between these three states as they get executed. The threads are always either doing work on the processor, waiting for a particular resource, or they have a resource and are waiting in line to run on the processor again. Once a thread is running on the processor, it will continue to do so until either it has finished its work and needs to again wait for another resource OR it reaches its maximum allotted time on the processor (called the OS quantum). This Quantum value is 4ms is non configurable.

Thread States

At any given time a thread can be in one of three states: RUNNING, SUSPENDED, or RUNNABLE.

RUNNING is the state where the thread is actually executing on the processor and utilizing CPU spins. Only one thread per scheduler can have this state at a time.

During execution, if the thread needs to wait for a particular resource in SQL (such as a lock or latch), it yields its time on the processor and moves to the Waiter List. When here the state of the thread changes to SUSPENDED while it waits for the resource to be acquired.

Once the resource has been acquired the thread is again ready to use the processor again but must wait its turn behind the other threads waiting to execute so it moves to the Runnable Queue and its state is changed to RUNNABLE.

NOTE:

If a RUNNING thread reaches the end of its 4ms quantum and doesn't need to wait to acquire another resource, voluntarily yields for the next thread and enters the Runnable Queue where its state changes back to RUNNABLE.

This dance continues until the thread(s) complete all the work assigned to them. This is called cooperative or non-preemptive scheduling. It relies on all of the processes at play voluntarily yielding when they are supposed to. For a comparison see this post on Preemptive vs Cooperative Multitasking.

MSSQL SERVER: Create an Extended Event Session with a “NOT LIKE” filter criteria on sql_statement_completed

As of the writing of this post, when creating extended events sessions in SQL Server Management Studio (SSMS), on the Set Session Event Filters page of the New Session Wizard, there is an option to include events that are like a specified value (using the like_i_sql_unicode_string operator) but there is not currently anything in the GUI to EXCLUDE queries that meet this filter criteria.

Adding a “NOT LIKE” clause to the extended events filter seems to be a limitation of the GUI currently. The steps below show you how to select all of your extended events via the wizard and then add the “NOT LIKE” functionality at the end by scripting out the Extended Event and making a minor tweak.

Open the New Session Wizard in SSMS:

Give it a name

Select “Do not use a template” and click Next

Search for sql_statement_completed in the list and add it to the Selected Events and click Next

On the Capture Global Fields page, click Next

On the Set Session Event Filters page, select “sqlserver.sql_text from the “Field” drop down list and set the “Operator” to like_i_sql_unicode_string and set the “value” field to the full or partial sql query that you would like to exclude. Then click next.

Continue to the Summary page of the wizard and click on the “Script” button

You should see a script like this:

We’re almost there but this script in its current form will do the opposite of what we want. This script will capture ONLY the sp_BlitzFirst execution. To have it exclude these executions, we have to make the following change:

Here is the TSQL text from this proof of concept:

CREATE EVENT SESSION [XEventFilterTest] ON SERVER

ADD EVENT sqlserver.sql_statement_completed(

    WHERE ( NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'EXEC sp_BlitzFirst%')))

WITH (STARTUP_STATE=OFF)

GO

Other Notes:

The total number of characters allowed for ALL filters on an event is 3,000. If you execeed this threshold, you may see an error that resembles the following:

Msg 25716, Level 16, State 1, Line 1
The predicate on event, "sqlserver.attention", exceeds the maximum length of 3000 characters.

To workaround this issue, split your Extended Events Session into multiple sessions.

MSSQL SERVER: ERROR: CREATE TABLE permission denied in database ‘tempdb’

I was recently working with a client that was periodically but inconsistently getting a strange error when one of their SQL Server Agent jobs ran:

ERROR: CREATE TABLE permission denied in database 'tempdb'

Why is this error confusing? Well, at a minimum every database and every user has at least the public role. The public role is contained in every database, which includes system databases. It cannot be dropped and you cannot add or remove users from it. Permissions granted to the public role are inherited by all other users and roles because they belong to the public role by default.

We also know that any user with the public role can create temporary tables in tempdb. So, how on earth does this error make any sense?

Let me demonstrate.

It is still true that with only the public role a user can create both local and global temporary tables.

If you want to demonstrate this, create a new user in SQL and make sure that it only has the public role then try to create some tables like below:

Local temp table:

CREATE TABLE #test_table_local (
     product_name VARCHAR(MAX),
     list_price DEC(10,2)
 );

Global temp table:

CREATE TABLE ##test_table_global (
     product_name VARCHAR(MAX),
     list_price DEC(10,2)
 );

But watch what happens we we try to create a regular “permanent” table in tempdb:

CREATE TABLE test_table_permanent (
     product_name VARCHAR(MAX),
     list_price DEC(10,2)
 );

So, as you can see, the original statement is still true. The public role allows users to create temp tables in tempdb without any additional permissions. However, to create permanent tables in tempdb you will still need the CREATE TABLE permission.

In my client’s case it was a matter of a simple typo. One particular code path in the SQL Agent job triggered a code path containing the typo where a CREATE TABLE statement was missing a ‘#’ in front of the table name.

If you’re wondering why you would create a “permanent” table in tempdb, you wouldn’t in most cases but it does have some unique properties in that the table is available from other sessions (unlike local temporary tables), and it is not deleted automatically even if there are no connections to it (unlike global temporary tables). However, it does still get deleted when the instance is restarted (just like everything else in tempdb).

Reference Links & More Information

Server and Database Roles in SQL Server | The Public Role

MSSQL Server: Opening the Required Firewall Ports

Firewalls are obviously a very important and fundamental part of network security. However, that doesn’t make them any less annoying when you’re working in development or just trying to learn the nitty gritty. You may be tempted to just disable the firewall. This will work and you’ll be on your merry way but you’ve learned nothing and when its time to deploy it can be a headache.

When it comes to Microsoft SQL Server, the default instance is easy enough. It operates on port 1433 out-of-the-box. Lets look at how to open this port a couple of different ways.

Using the GUI

To open it up for remote access, simply open Windows Defender Firewall -> Advanced Settings -> Inbound Rules -> New Rule… then follow the wizard:

Select Port and click Next.

Select TCP and type 1433 into the Specify local ports field

New Inbound Rule Wizard: Protocol and Ports

Select Allow the connection and click Next.

Select the Network Profiles that you want the rules to apply to.

Give the rule a name and description the click Finish.

Using netsh advfirewall

Using the GUI can be time consuming, if you have multiple machines were you need to open ports we can use the netsh utility to administer the firewall through the command line or a batch file.

Running this command will create an inbound rule that opens communication on port 1433 for the domain network profile:

netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN

By default, the typical ports used by SQL Server and associated database engine services are: TCP 1433, 4022, 135, 1434, UDP 1434. The table below explains these ports in greater detail. A named instance uses dynamic ports.

The following table lists the ports that are frequently used by the Database Engine.

Scenario	Port	Comments
Default instance running over TCP	TCP port 1433	This is the most common port allowed through the firewall. It applies to routine connections to the default installation of the Database Engine, or a named instance that is the only instance running on the computer. (Named instances have special considerations. See Dynamic Ports later in this article.)
Named instances with default port	The TCP port is a dynamic port determined at the time the Database Engine starts.	See the discussion below in the section Dynamic Ports. UDP port 1434 might be required for the SQL Server Browser Service when you are using named instances.
Named instances with fixed port	The port number configured by the administrator.	See the discussion below in the section Dynamic Ports.
Dedicated Admin Connection	TCP port 1434 for the default instance. Other ports are used for named instances. Check the error log for the port number.	By default, remote connections to the Dedicated Administrator Connection (DAC) are not enabled. To enable remote DAC, use the Surface Area Configuration facet. For more information, see Surface Area Configuration.
SQL Server Browser service	UDP port 1434	The SQL Server Browser service listens for incoming connections to a named instance and provides the client the TCP port number that corresponds to that named instance. Normally the SQL Server Browser service is started whenever named instances of the Database Engine are used. The SQL Server Browser service does not have to be started if the client is configured to connect to the specific port of the named instance.
Instance with HTTP endpoint.	Can be specified when an HTTP endpoint is created. The default is TCP port 80 for CLEAR_PORT traffic and 443 for SSL_PORT traffic.	Used for an HTTP connection through a URL.
Default instance with HTTPS endpoint	TCP port 443	Used for an HTTPS connection through a URL. HTTPS is an HTTP connection that uses Transport Layer Security (TLS), previously known as Secure Sockets Layer (SSL).
Service Broker	TCP port 4022.	There is no default port for SQL ServerService Broker, but this is the conventional configuration used in Books Online examples.
Database Mirroring	Administrator chosen port.	There is no default port for database mirroring however Books Online examples use TCP port 5022 or 7022. It is important to avoid interrupting an in-use mirroring endpoint, especially in high-safety mode with automatic failover. Your firewall configuration must avoid breaking quorum. For more information, see Specify a Server Network Address (Database Mirroring).
Replication	Replication connections to SQL Server use the typical regular Database Engine ports (TCP port 1433 for the default instance, etc.) Web synchronization and FTP/UNC access for replication snapshot require additional ports to be opened on the firewall. To transfer initial data and schema from one location to another, replication can use FTP (TCP port 21), or sync over HTTP (TCP port 80) or File Sharing. File sharing uses UDP port 137 and 138, and TCP port 139 if it using NetBIOS. File Sharing uses TCP port 445.	For sync over HTTP, replication uses the IIS endpoint (ports for which are configurable but is port 80 by default), but the IIS process connects to the backend SQL Server through the standard ports (1433 for the default instance. During Web synchronization using FTP, the FTP transfer is between IIS and the SQL Server publisher, not between subscriber and IIS.
Transact-SQL debugger	TCP port 135 See Special Considerations for Port 135 The IPsec exception might also be required.	If using Visual Studio, on the Visual Studio host computer, you must also add Devenv.exe to the Exceptions list and open TCP port 135. If using Management Studio, on the Management Studio host computer, you must also add ssms.exe to the Exceptions list and open TCP port 135.

Service Broker and AlwaysOnAG (mirroring) don’t have defaults. If you need to check what these ports are configured as on an existing system, connect via SSMS or SQLCMD and run the following queries:

AlwaysOn AG (Mirroring):

SELECT name, protocol_desc, port, state_desc 
   FROM sys.tcp_endpoints
   WHERE type_desc = 'DATABASE_MIRRORING'

Service Broker:

SELECT name, protocol_desc, port, state_desc
   FROM sys.tcp_endpoints
   WHERE type_desc = 'SERVICE_BROKER'

Batch Script

Below is an example batch script that creates all of the aforementioned firewall rules. Modify the ag_endpnt and sb_endpnt variable at the beginning if your ports are different. Script should be executed from an elevated command prompt or powershell window. Provided for educational purposes only and as always don’t run anything in production that you haven’t tested first.

rem OpenSQLServerPorts.bat
rem Date Modified: 03/26/2021
rem Author: Avery Lane

set "ag_endpnt=5022"
set "sb_endpnt=4022"

rem SQL Default Ports Inbound/Outbound
netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN
netsh advfirewall firewall add rule name = SQLPort dir = out protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN

rem SQL Dedicated Admin Port Inbound (Default Instance)
netsh advfirewall firewall add rule name = SQLPortDedicatedAdmin dir = in protocol = tcp action = allow localport = 1434 remoteip = localsubnet profile = DOMAIN

rem SQL Database mirroring/AlwaysOn AG (Default is 5022)
netsh advfirewall firewall add rule name = SQLPortAG dir = in protocol = tcp action = allow localport ="%ag_endpnt%" remoteip = localsubnet profile = DOMAIN
netsh advfirewall firewall add rule name = SQLPortAG dir = out protocol = tcp action = allow localport ="%ag_endpnt%" remoteip = localsubnet profile = DOMAIN

rem SQL Browser Service
netsh advfirewall firewall add rule name = SQLPortBrowser dir = in protocol = udp action = allow localport = 1434 remoteip = localsubnet profile = DOMAIN

rem Service Broker (Default is 4022)
netsh advfirewall firewall add rule name = SQLPortServiceBroker dir = in protocol = tcp action = allow localport ="%sb_endpnt%" remoteip = localsubnet profile = DOMAIN
netsh advfirewall firewall add rule name = SQLPortServiceBroker dir = out protocol = tcp action = allow localport = "%sb_endpnt%" remoteip = localsubnet profile = DOMAIN

rem Replication
netsh advfirewall firewall add rule name = SQLReplUDP dir = in protocol = udp action = allow localport = 137,138 remoteip = localsubnet profile = DOMAIN
netsh advfirewall firewall add rule name = SQLReplTCP dir = in protocol = tcp action = allow localport = 139,445 remoteip = localsubnet profile = DOMAIN

netsh advfirewall firewall add rule name = SQLReplUDP dir = out protocol = udp action = allow localport = 137,138 remoteip = localsubnet profile = DOMAIN
netsh advfirewall firewall add rule name = SQLReplTCP dir = out protocol = tcp action = allow localport = 139,445 remoteip = localsubnet profile = DOMAIN

Output of netsh advfirewall firewall add rule ?

Usage:
      add rule name=<string>
      dir=in|out
      action=allow|block|bypass
      [program=<program path>]
      [service=<service short name>|any]
      [description=<string>]
      [enable=yes|no (default=yes)]
      [profile=public|private|domain|any[,...]]
      [localip=any|<IPv4 address>|<IPv6 address>|<subnet>|<range>|<list>]
      [remoteip=any|localsubnet|dns|dhcp|wins|defaultgateway|
         <IPv4 address>|<IPv6 address>|<subnet>|<range>|<list>]
      [localport=0-65535|<port range>[,...]|RPC|RPC-EPMap|IPHTTPS|any (default=any)]
      [remoteport=0-65535|<port range>[,...]|any (default=any)]
      [protocol=0-255|icmpv4|icmpv6|icmpv4:type,code|icmpv6:type,code|
         tcp|udp|any (default=any)]
      [interfacetype=wireless|lan|ras|any]
      [rmtcomputergrp=<SDDL string>]
      [rmtusrgrp=<SDDL string>]
      [edge=yes|deferapp|deferuser|no (default=no)]
      [security=authenticate|authenc|authdynenc|authnoencap|notrequired
         (default=notrequired)]

Remarks:

      - Add a new inbound or outbound rule to the firewall policy.
      - Rule name should be unique and cannot be "all".
      - If a remote computer or user group is specified, security must be
        authenticate, authenc, authdynenc, or authnoencap.
      - Setting security to authdynenc allows systems to dynamically
        negotiate the use of encryption for traffic that matches
        a given Windows Defender Firewall rule. Encryption is negotiated based on
        existing connection security rule properties. This option
        enables the ability of a machine to accept the first TCP
        or UDP packet of an inbound IPsec connection as long as
        it is secured, but not encrypted, using IPsec.
        Once the first packet is processed, the server will
        re-negotiate the connection and upgrade it so that
        all subsequent communications are fully encrypted.
      - If action=bypass, the remote computer group must be specified when dir=in.
      - If service=any, the rule applies only to services.
      - ICMP type or code can be "any".
      - Edge can only be specified for inbound rules.
      - AuthEnc and authnoencap cannot be used together.
      - Authdynenc is valid only when dir=in.
      - When authnoencap is set, the security=authenticate option becomes an
        optional parameter.

Reference Links & More Information

BASE and CAP

BASE Model

NoSQL (actually short for “not only” SQL) often times rely on a softer transaction model than ACID abbreviated as BASE:

Basically Available
Soft State
Eventually Consistent

Basically Available

This has to do with the perceived availability of the data. The idea is for data to always be available even if it is not in a consistent or hardened state. There are no isolation levels. One transaction may access a file that is being actively modified by another.

Soft State

Values stored in the database may change because of the eventual consistency model.

Eventually Consistent

Data and changes to said data are replicated across nodes and eventually become consistent. For example, in Hadoop, when a file is written to the HDFS, the replicas of the data blocks are created in different data nodes after the original data blocks have been written. For the short period before the blocks are replicated, the state of the file system isn’t consistent.

As tongue in cheek as BASE is, it’s a bit misleading. It is not meant to be the polar opposite of ACID because most NoSQL implementations do not abandon all ACID principles. Soft State and Eventually Consistent are also so closely related that their distinction serves no real purpose.

The point though, is that a BASE model uses a more relaxed approach to consistency for the sake of being able to scale horizontally much more easily and provide a high degree of availability.

This model is very popular with social media platforms and search engines where “some (maybe partial) results now” is much more important than “all results no matter how long it takes”.

CAP Theorem

Also known as Brewer’s theorem, named after computer scientist Eric Brewer, proposes that it is only possible for a distributed data store to provide two out of the three following guarantees:

Consistency: Every read receives the most recent write or an error

Availability: Every request receives a (non-error) response, without the guarantee that it contains the most recent write

Partition tolerance: The system continues to operate despite an arbitrary number of messages being dropped (or delayed) by the network between nodes

Transactions and ACID

A database transaction is a unit of work performed against a database that is independent of other transactions. The purpose of a transaction is two-fold:

To provide a reliable record and means to recover in case of failure when performing work.
To provide isolation between applications or processes that are accessing data concurrently.

In any good relational database management system, transactions adhere to a set of strict rules or properties abbreviated as ACID:

Atomicity
Consistency
Isolation
Durability

Atomicity

Guarantees that either all operations in the transaction succeed or they all fail. This prevents partial updates that could leave a database in a questionable or incomplete state. For instance, imagine your database transaction was a financial transaction that transferred money from one account to another. That would consist of two operations: a deduction from account A and a deposit into account B. This is an obvious example where we want both operations to succeed or both to fail. If only one operation was successful we would either be destroying money or inventing new money from nothing (something apparently only the federal reserve is allowed to do).

Consistency

Is similar to atomicity but has more to do with referential integrity. This is where constraints come into play, such as a primary key. If you have an ID column used to track rows based on a unique identifier, you wouldn’t want to allow multiple rows to have the same value for ID; if there were the database would be in an inconsistent state. A transaction that preserves consistency ensures that the database is in a consistent state when the transaction begins and when the transaction ends.

Isolation

This protects transactions from the effects of other transactions via mechanisms like locks and row versioning. Database management systems typically employ different levels of isolation. For instance, in Microsoft SQL Server, the READ UNCOMMITTED isolation level allows transactions to read data that other transactions are modifying, while the stricter SERIALIZABLE isolation level will lock ranges of rows or entire tables to prevent the reading of data until the transaction modifying data completes and commits.

Durability

A transaction is considered durable when it commits and stays committed. This is accomplished by flushing the data to a stable/nonvolatile media (think disk vs in-memory) although as storage mediums continue to advance and innovate, the implementation may change. The important thing is that the work is saved and is not prone to being lost or rolled back without intervention.

Microsoft SQL Server, Oracle, MySQL, and PostgreSQL are some of the most popular database systems which follow ACID properties by default.

Logic: An Introduction (Part III)

Truth Tables

As discussed in the previous post, compound predicates are built from other predicates and propositions using logical connectives (¬, ∧, ∨, ⇒, ↔). The truth value of the compound predicate depends on the truth values of its components.

Truth tables can be used to define the meaning of logical operators and to investigate compound expressions. By investigating the truth values of compound expressions, you can establish new logical concepts. This is especially useful when it comes to rewrite rules which we will discuss a little bit later on.

Components of a Truth Table

Truth tables consists of a header and a body. When defining the meaning of a logical connective, the leftmost column lists the variable you are evaluating and the rightmost column lists the connective.

Example:

This truth table defines the conjunction connective. By looking at this truth table we can see that if P and Q both have a truth value of TRUE then the expression P ∧ Q also has a truth value of TRUE, but any other combination of P and Q means that the truth value of P ∧ Q is FALSE.

Variables
Logical
Expression
PAQ
Header
Body

When investigating the truth value for a compound expression, the left most column headers list the variables and the rightmost header lists the compound expression.

The Body of a truth table always contains one row for every possible combination of the variables involved.

Note: The variables in a truth table are propositional variables. This allows you to work with the most simplified form of possible combinations and outcomes.

Here are truth tables for all of the logical connectives:

Negation
Con•unction
Implication
E uivalence
Disjunction

Note: Regarding the truth table for the Disjunction connective, this table shows the truth values for what is referred to as the inclusive or. This means that the proposition evaluates to TRUE if P and Q are both TRUE. You may encounter times when the exclusive or is specified (sometimes called XOR or EOR). This simply means that one of the criterion must be true but not both.

Equivalence and Implication

These two connectives may not be as immediately apparent or intuitive and the others so I think they warrant a bit more discussion.

Equivalence

This one isn’t too bad. It’s basically the same as the = operator (Note to self: check thesaurus for “same as”). It evaluates to TRUE if and only if the two involved propositional variables evaluate to the same truth value (either both TRUE or both FALSE).

Implication

This one is a bit more confusing because of the way “implies” is used informally versus its mathematical logical meaning. To better explain what the Implication connective means, I think it would be easier to start with what it doesn’t mean:

It doesn’t mean “suggests”.
It doesn’t mean “causes”.

The following sentence is completely logical under the mathematical definition of Implication:

“The sky is blue implies January is a month”

This does not mean “Since the sky is blue January is a month” or “January is a month because the sky is blue”

Note: Always remember that the ONLY time that an implies statement is FALSE is if the Antecedent (P) is TRUE but the Consequent (Q) is FALSE.

So let’s take a closer look at the Implication truth table again:

To better understand this, it might be better to evaluate the conditions in stages. We will start by examining the truth values for P. If the truth value for P is FALSE, we aren’t even going to check what the truth value for Q is because we already know that the expression is TRUE. If P is TRUE then we have to go on to evaluate if Q is TRUE, if it is the expression is TRUE, but if it isn’t then the expression is FALSE.

I like to think of this as “Innocent until proven guilty” or “True until proven false”.

Example:

Proposition: If your pet is a dog, then your pet is a male.

Suppose my pet is a dog… ok now I can check the gender… it turns out my dog is male… the statement must be true

Suppose my pet is a dog… ok now I can check the gender… it turns out my dog is female… I’ve proven the statement false

Suppose my pet is a cat… ok I could check the gender but it doesn’t really matter because the statement only applies to dogs so I wouldn’t be proving anything even if my cat was female… without evidence to prove that the statement wrong, I must assume that it is true.

Suppose my pet is a bird… same logic applies as if my pet was a cat… without evidence to prove that the statement wrong, I must assume that it is true.

Implication is probably the least intuitive of all the connectives and some of that probably comes from how we think about “implications” outside of mathematics. Let’s consider for a moment why this is.

Consider the following scenario:

I have a bag of 10 marbles. One is blue and nine are yellow.

I ask you to pick one marble out of the bag without looking and offer the following implication:

“If you pick a blue marble, the next will be yellow”

So you pick a marble:

Suppose I pick a blue marble… ok now I can pick another marble… it turns out it is yellow… The statement must be true. (T ⇒ T) = T

Suppose I pick a blue marble… ok now I can pick another marble… it turns out it is blue… The statement must be false. (T ⇒ F) = F

Those two seem pretty intuitive right? So let’s look at the tricky ones:

Suppose I pick a yellow marble… ok now I can pick another marble… it turns out it is also a yellow marble… ok so I don’t have enough information to know if the original statement is true or not. In real life if a friend asked me to do this and draw a conclusion I would say that there isn’t enough information to determine if it is TRUE or FALSE because the first marble I drew wasn’t blue so can’t actually test the hypothesis. Ahhh but let’s remember our definition of a proposition: a declarative sentence that when evaluated is either TRUE or FALSE, but not both.

By definition, our implication proposition MUST evaluate to either TRUE or FALSE, it cannot be both or neither. So now the question really becomes given this scenario, do we assume FALSE or do we assume TRUE? When it comes to implication, we always assume TRUE. This is why I use the phrase “innocent until proven guilty”. Just like in a court of law, the defendant is innocent until they can be proven guilty. Likewise when it comes to an implication, we assume that the overall statement (P ⇒ Q) is TRUE unless we can definitively prove that it is FALSE.

Predicate Strength

You can think about Implication as “ordering” the predicates that make up its operands. This order is referred to as the strength of the predicate. The “strongest” predicate is the one that implies the other. Consider P ⇒ Q, in this situation P is the stronger predicate and Q is the weaker.

Example:

The variable x is defined with a type of integer.

Consider the predicate: x > 5 ⇒ x > 0

To state that x is greater than 5 implies that x is greater than 0. This implication is TRUE regardless of the value of x, and the predicate x > 5 is the stronger predicate while x > 0 is the weaker one.

The only time that both predicates are of equal strength is when P ⇒ Q and Q ⇒ P. When the predicates are of equal strength they are equivalent: ↔

Logic: An Introduction (Part II)

Logical Connectives

In my last blog post we discussed propositions and predicates. To summarize, a proposition is a declarative sentence that can be proven TRUE or FALSE. Similarly, a predicate takes the form of a declarative sentence but contains at least one embedded variable (parameter) and cannot be evaluated as TRUE or FALSE unless input for said parameters is provided.

A logical connective is an operator that is used to form more complex predicates by combining predicates together. The most well-known of these logical connectives are conjunction (logical AND), disjunction (logical OR), and negation (logical NOT). Probably less commonly known are implication and equivalence.

Symbol	Meaning	Terminology
¬	NOT	Negation
∧	AND	Conjunction
∨	OR	Disjunction
⇒	IMPLIES	Implication (if…then…)
↔	IS EQUIVALENT TO	Equivalence (if and only if)

Note: If you take a course in mathematical logic, you will have a formal discussion of proofs. You start with a formal language, which describes the symbols you're allowed to use and how to combine them, and the rules of inference, which describe the valid ways of making steps in a proof. This is important because natural languages like English are informal and imprecise, as a consequence, the meaning of words sometimes depends on context; this kind of ambiguity is why formal language and symbols exist. However, throughout my writings I will use a mix of formal and informal language and syntax for the sake of learning.

Simple and Compound Predicates

If a logical connective is regarded as a logical operator, we can say that it takes one or more predicates as its operand and returns another predicate.

Example:

Consider the logical operator ∧

Consider the predicates x = 11 and y = 15 as the operands

The compound predicate would be (x = 11) ∧ (y = 15)

In informal language this reads as: I propose that x equals 11 and y equals 15.

With an AND operator, the compound predicate only evaluates to TRUE if both the simple predicates also individually evaluate to TRUE.

Note: The simple predicates that make up the operands of the logical operator are also sometimes referred to as components of the compound predicate. Isn’t natural language fun?

Propositional Variables

Let’s consider another kind of compound predicate. Consider the following:

P ∧ Q ⇒ P ⇒ Q

This looks a bit different than the predicates we’ve looked at so far. That’s because this example uses the propositional variables P and Q. If you consider that a variable is a “holder” for a value, then you can think of a propositional variable as a “holder” for a proposition.

An assertion that contains at least one propositional variable is referred to as a propositional form. If you substitute all propositional variables (of the given propositional form) with a proposition the propositional form is transformed into a proposition in and of itself.

Phew, even reading that back to myself that is a tricky sentence. You may think at this point that a propositional form seems very similar to a predicate. So what’s the difference? Well, let’s recall that a predicate takes the form of a declarative sentence (just like a proposition) but has embedded variables. A predicate cannot be evaluated to TRUE or FALSE until values are substituted for these variables. Meanwhile a propositional form uses propositional variables which themselves represent a truth value (TRUE or FALSE).

This means that we can treat propositional variables as arbitrary predicates with truth values without having to fully express the details of the underlying proposition

Example:

Let P represent 3 + 5 = 8

Let Q represent 1 + 1 = 2

Consider the propositional form P ∧ Q

If we replace the proposition variables (P,Q) the propositional form is transformed to the following proposition:

3 + 5 = 8 ∧ 1 +1 = 2

Which evaluates to TRUE.

Similarly, we can use letters like P,Q,R, ect. In expressions like this:

P(x), Q(y), R(s,t)

These represent arbitrary predicates with parameters.

Example:

Let P represent 3 + 2x = 9

What is the value of P(x) given that x = 3

You would evaluate this by substituting P(3) which means to pass 3 as the value for parameter x to the predicate 3 + 2x = 9 which forms the proposition 3 + (2*3) = 9 which evaluates to TRUE and thus P(x) evaluates to TRUE

If we pass any other value for x, the resulting proposition evaluates to FALSE.

Logical Operator Precedence and Parentheses

I’m sure everyone remembers PEMDAS (Parentheses, Exponents, Multiplication, Division, Addition, Subtraction), the order of operations from your middle school math class. Just like algebraic expressions follow the order of operations, logical expressions follow an order of precedence. And just like the algebraic expressions, you can override this order using parentheses.

Note: Even though formal rules of the syntax do not always strictly require parentheses, it is a good idea to add them even if redundant to avoid any confusion or misinterpretation. It increases readability and is just as valid.

Here are the Logical Connective Precedence Rules:

Rank	Connective
1	¬
2	∧, ∨
3	⇒, ↔

Note that some operators have the same rank.

Example:

Given P ∧ Q ⇒ R the precedence rules state that this is equivalent to (P ∧ Q) ⇒ R rather than P ∧ (Q ⇒ R)

For operations with equal rank, formal rules state that you should read operations from right to left. This is called the associative rule.

Example:

Given P ⇒ Q ⇒ R, the associative rule states that this should be equivalent to P ⇒ (Q ⇒ R) rather than (P ⇒ Q) ⇒ R

This is an important rule to keep in mind, but for the sake of clarity I will most often use parentheses in my posts.