Alphanumeric types

Alphanumeric types (also known as strings) are commonly discussed in terms of fixed versus variable length, and with Unicode versus without Unicode support. The char and nchar data types are fixed length, and varchar and nvarchar are variable length. The difference is how each is encoded. The nchar and nvarchar data types are always encoded as 16-bit Unicode, using UTF-16. In contrast, char and varchar use 8-bit data types that store data in ASCII or, starting in SQL Server 2019, UTF-8. More information about these new collations and their purpose is included later in this section.

As a database designer, you must understand that the (n) in a [var]char(n) column definition indicates the number of bytes allocated for the column, not the number of characters that can be stored. The same is true for n[var]char(n) columns, though the size indicates the number of byte-pairs that can be stored. This is important because:

• [var]char columns can store strings from double-byte character sets, and can use UTF-8 collations, which may require 2 or 4 bytes to store one character. The following subsection includes full coverage of UTF-8 collations.

• n[var]char columns can store characters in the Unicode supplementary character range, which may require 4 bytes.

Collation

With string data, collation becomes an important consideration. This is determined using the code page, which is one element of the collation. Collation also determines how data is compared and sorted, such as whether casing and accented letters are considered to be different.

• For more information about Unicode supplementary characters, see https://learn.microsoft.com/sql/relational-databases/collations/collation-and-unicode-support#Supplementary_Characters.

SQL Server 2019 introduced a new family of collations that support UTF-8. These collations apply only to the char and varchar data types and store the string data using UTF-8 encoding. They effectively turn these two data types into Unicode data types, including support for supplementary characters. When you define a column or conversion to use a UTF-8 collation, the encoding is automatically updated.

Among other things, collation determines how the high-order bits in each character’s byte(s) are interpreted. Collation supports internationalization by allowing different character representations for characters whose integer values are greater than 127, up to 255.

• For complete details about collation and Unicode support, visit https://learn.microsoft.com/sql/relational-databases/collations/collation-and-unicode-support.

-- If COUNT > 0, then there are rows whose data size will be larger than the
-- current column width supports
SELECT COUNT(*)
FROM dbo.CollationTest
WHERE DATALENGTH(
    CAST(CAST(val AS VARCHAR(32))
        COLLATE Latin1_General_100_CS_AS_SC_UTF8 AS VARCHAR(32))) > 8;

Large value data

No discussion of alphanumeric types would be complete without an examination of varchar(max) and nvarchar(max). By specifying max instead of a value between 1 and 8,000 bytes (for varchar) or between 1 and 4,000 byte-pairs (for nvarchar), the storage limit increases to 2 GB. If the column’s value exceeds 8,000 bytes, the data is not stored in the table’s storage structure. Large value data is stored out of row, though for each such column, 24 bytes of overhead is stored in the row. Of those 24 bytes, the first 16 bytes are used to store metadata, and the last 8 bytes contain the pointer to the row in the row-overflow page.

SQL Server has a row size limit of 8,060 bytes. Even if you do not use [n]varchar(max) columns, some data may be stored out of row or “off-row.” Any data that is stored off-row will incur some overhead when it is read. The flip side is that when a T-SQL statement does not reference a column whose data is stored off-row, there is a performance benefit. If your table’s usage patterns indicate that large value type columns are not frequently included in statements, you can optimize performance by storing the data off-row, even if the row size is less than 8,000 bytes. The following T-SQL statement enables the large_value_types_out_of_row option for the PurchaseOrders table in the WideWorldImporters sample database:

Click here to view code image

DECLARE @TableName NVARCHAR(776) = N'Purchasing.PurchaseOrders';
-- Turn the option on
EXEC sp_tableoption @TableNamePattern = @TableName
    , @OptionName = 'large value types out of row'
    , @OptionValue = 1;
GO
-- Verify the option setting
SELECT [name], large_value_types_out_of_row
FROM sys.tables
WHERE object_id = OBJECT_ID(@TableName);

After this is run, the values are not immediately migrated to out of row storage. This is true for any table, regardless of whether there is data populated. For example, the preceding table has no values in any of the varchar(max) columns; only when an existing data row is updated will the values be stored out of row. You could force such updates to happen by executing an UPDATE statement that sets the column value to any value or even itself, although this operation will be quite expensive on large tables. We don’t recommend this unless you have determined that the immediate benefit of forcing those values to be stored off-row exceeds the cost of the update operation.

In the T-SQL CREATE statement (but not in an ALTER statement), you can opt to store the data for large value type columns in a separate filegroup. In the CREATE TABLE statement, use the TEXTIMAGE_ON clause to specify the name of the filegroup where large object (LOB) data should be stored. If you want to change the TEXTIMAGE_ON setting, you will need to create a new table and copy the data in the table.

• If you need to store more than 2 GB in a single column, consider using the FILESTREAM feature, discussed in the “Store large binary objects” section later in this chapter.

Numeric types

When considering numeric types in computer systems, it is important to understand the nature of your data. One of the most important concepts to understand is the difference between exact and approximate numeric types.

Approximate numeric types store values using a floating-point structure. In SQL Server, the number of bits in the mantissa is limited to 24 or 53, resulting in a respective precision of 7 or 15 digits. Due to the nature of the structure and the limited number of bits, these types cannot accurately store all numbers in the supported range. On the other hand, although exact types store numbers without losing precision, this comes at a loss of range. For approximate floating-point types, the range is very large and useful for scientific-like numbers and operations, where a small loss of precision might not matter. Math with these values is implemented in hardware, so performance is far better than anything other than integers. For exact types, the range is limited, but sufficient for operations requiring precision, such as those involving monetary values.

SQL Server provides real and float as approximate data types, although their implementation is closely related. The real data type is lower precision than the float data type. It is possible to specify the number of bits for the mantissa when defining float, but SQL Server will always use either 24 bits or 53 bits; any other value you specify is rounded up to either 24 or 53. The real data type is the same as specifying float(24), or in effect any number of mantissa bits between 1 and 24.

• For more information about floating-point values, see Randolph West’s blog post on how these values are implemented, at https://bornsql.ca/blog/how-sql-server-stores-data-types-floating-points.

Exact numeric types include tinyint, smallint, int, and bigint, which are all whole numbers of varying byte sizes and therefore range. SQL Server does not support unsigned integers.

Some exact numeric types support decimal-point numbers. Foremost among these is the decimal data type. In SQL Server, another name for decimal is numeric. The decimal data type supports a precision of up to 38 digits, before or after the decimal point. The number of digits determines the storage size. In addition, you can specify the scale, which determines the number of digits to the right of the decimal point.

Other exact numeric types that support decimal point numbers are money and smallmoney. They have the same range as int and bigint, but with the decimal point shifted four places to the left. Because of this, any math calculations will be treated like integers and can be done in registers in the CPU. This enables them to perform calculations faster than non-integer numbers. These data types can store monetary data with a precision of up to four digits to the right of the decimal point—in other words, to the ten-thousandth.

Choosing between decimal and money or smallmoney is primarily determined by your need for range and precision. For monetary values, and if your multiplications and divisions will always return the desired result when using only four significant digits to the right of the decimal point, smallmoney and money may be good choices because they are more efficient in terms of storage space. For higher precision and larger scale, decimal is the right choice. In addition, decimal may be a better choice if the operations performed on the data create precision issues due to intermediate steps in math using only four digits.

Date and time types

Date and time data types available in SQL Server 2022 include the aged datetime and smalldatetime types. Although these are not technically deprecated, we strongly caution against using them for new development due to issues surrounding precision, available date range, and lack of control over the precision and storage size. Additionally, these data types are not aligned with the SQL standard, lowering portability of the data between platforms. Their immediate replacement is datetime2, which in no case consumes more than 8 bytes of storage space (the same as datetime), but addresses precision, increases the date range, and can store dates in less than 8 bytes in return for lower precision. As a matter of detail, specifying datetime2(3) provides the same precision as datetime, but does so while requiring 1 fewer byte.

This does not mean, however, that all date and time-of-day values should be stored in datetime2. There are three additional data types that you should consider for storing date or time values:

• date. If you need to store only a date without time or time zone information, this is your best choice. The date data type stores only a date and supports the same date range as datetime2. It stores the date in only 3 bytes, making it much more efficient than datetime (fixed at 8 bytes) and datetime2 (minimally 6 bytes). An example of such a case is a date of birth. A date of birth is commonly stored to calculate someone’s age, which is not generally treated as dependent on the time zone or on the time. Assume a person is born at 11 p.m. Central European Summer Time. If they moved to Southeast Asia, they would not celebrate their birthday a day later, even though the actual point in time of their birth was the next day in Southeast Asia. (That being said, some applications, such as one used in a neonatal facility, might need to store a more precise “time of birth,” so make sure you understand the requirements before choosing your data type.)

• datetimeoffset. This data type provides the same precision and range as datetime2 but includes an offset value in hours and minutes to indicate the difference from UTC. This data type neither tracks nor understands actual time zones or daylight saving time (DST). It would be up to the application to track the time zone where the value originated to allow the application or recent versions of SQL Server to perform correct date arithmetic. (See the following note for more information.)

  Note

  Before SQL Server 2016, SQL Server had no understanding of time zones or DST. SQL Server 2016 and Azure SQL Database introduced the AT TIME ZONE function, which converts between time zones and applies or reverts a DST offset. The rules SQL Server applies are based on the Windows functionality for time zones and DST. These rules are explained and illustrated with examples at https://learn.microsoft.com/sql/t-sql/queries/at-time-zone-transact-sql.

  With SQL Server on Linux, AT TIME ZONE returns the same results as executing the function on a Windows host.

• time. This data type stores a time-of-day value consisting of hours, minutes, seconds, and fractional seconds, with a precision up to 100 nanoseconds. The exact fractional second precision and storage size is user defined by specifying a precision between 0 and 7. The time data type is a good choice when storing only a time-of-day value that is not time-zone sensitive, such as for a reminder. A reminder set for 11 a.m. might need to be activated at 11 a.m. regardless of time zone and date.

  Note

  The time data type can store no more than 23 hours, 59 minutes, 59 seconds, and 0.9999999 fractions of a second. This can make this data type unsuitable for storing elapsed time if there is a chance that elapsed time will be 24 hours or more. We typically suggest storing elapsed time in an integer value that holds the number of time units that have passed, based on the minimum precision you desire. Any fields that could exceed 2.1 billion in the lifetime of the application should use the bigint data type.

• For detailed technical comparisons between the available date and time data types, visit https://learn.microsoft.com/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql.

Binary types

Some data cannot be efficiently represented as an alphanumeric string. For example, data that has been encrypted by the application should be stored as a binary value. The same might also apply to storing contents of binary file formats, such as PDF files.

SQL Server provides the binary data type to store fixed-length binary values, and varbinary to store variable-length binary values. (The image data type has been deprecated for almost two decades, and you should not use it.) For both data types, you specify the number of bytes that will be stored, up to 8,000. If you need to store more than 8,000 bytes, you can specify varbinary(max). This will allow up to 2 GB to be stored, although if the value exceeds 8,000 bytes, those bytes are not stored in the data row. As with [n]varchar, varbinary values may be stored out of row if the total row size would exceed 8,000 bytes.

• Refer to the section “Large value data” earlier in this chapter for details on how varbinary(max) data is stored and access can be optimized.

Spatial data types: geometry and geography

The spatial data types provide a way to work with flat (planar) or ellipsoidal (round-earth) coordinates. The geometry data type is for a flat coordinate system, whereas the geography data type is for round-earth coordinates. In addition, both data types also support elevation, or Z, values. Both data types are CLR types that are available in every database, regardless of whether the SQL CLR feature is enabled.

SQL Server provides several methods to work with the values of these data types, including finding intersections, calculating surface area and distance, and many more. SQL Server supports methods defined by the Open Geospatial Consortium (OGC) as well as extended methods designed by Microsoft. The methods defined by the OGC are identified by their ST prefix.

Generally, you create a geometry or geography value by using the static STGeomFromText method. You can use this method to define points, lines, and polygons (closed shapes). The code example that follows creates two geometric points, one with coordinates (0, 0) and the second with coordinates (10, 10). Then, it calculates and outputs the distance between both points:

Click here to view code image

-- Define the variables
DECLARE @point1 GEOMETRY, @point2 GEOMETRY, @distance FLOAT;
-- Initialize the geometric points
SET @point1 = geometry::STGeomFromText('POINT(0  0)', 0);
SET @point2 = geometry::STGeomFromText('POINT(10 -10)', 0);
-- Calculate the distance
SET @distance = @point1.STDistance(@point2);
SELECT @distance;

The result in the output is approximately 14.14. (See Figure 7-1; note that no units are defined here.) The second argument in the STGeomFromText method is the spatial reference ID (SRID), which is relevant only for the geography data type. Still, it is a required parameter for the function, and you should specify 0 for geometry data.

Using spatial data types in a database is valuable when you use the Database Engine to perform spatial queries. You have probably experienced the results of spatial queries in many applications—for example, when searching for nearby pizza restaurants on Bing Maps. Application code can certainly also perform those spatial queries; however, it would require the database to return all pizza restaurants along with their coordinates. By performing the spatial query in the database, the size of the data returned to the application is significantly reduced. SQL Server supports indexing spatial data such that spatial queries can perform optimally.

• For a complete reference on the geometry and geography data types, the methods they support, and spatial reference identifiers, visit https://learn.microsoft.com/sql/t-sql/spatial-geometry/spatial-types-geometry-transact-sql and https://learn.microsoft.com/sql/t-sql/spatial-geography/spatial-types-geography.

The XML data type

The xml data type is designed to store XML documents or snippets. But support for XML goes beyond just storing XML data. The XML data type can enforce an XML schema, in which case the column is referred to as typed. XML data can also be queried using XQuery syntax. SQL Server further supports XML by formatting relational data output as XML or retrieving XML data in a relational structure.

A relational database is generally used to store highly structured data, by which we mean data that has a known schema. And even though schemas can change, at any given time every row in a table will have the same columns. Yet, for some scenarios, this strict schema is not appropriate. It might be necessary to accommodate storing data where different rows have different attributes. Sometimes, you can meet this requirement by adding additional nullable sparse columns.

A column set is a feature by which you can manage a group of sparse columns as XML data. Column sets come with significant limitations. Defining many sparse columns becomes onerous because a substantial number of columns can introduce challenges in working with the table. There, just storing the data as plain XML in an xml data type can alleviate the column sprawl. Additionally, if data is frequently used in XML format, it might be more efficient to store the data in that format in the database.

• You can read more about sparse columns in the “Sparse columns” section later in this chapter. For detailed guidance on the use of column sets, see https://learn.microsoft.com/sql/relational-databases/tables/use-column-sets.

Although XML data could be stored in (n)varchar columns, using the specialized data type allows SQL Server to provide functionality for validating, querying, indexing, and modifying the XML data.

• Refer to the section “Large value data” earlier in this chapter for details on how xml data is stored and access can be optimized.

• For complete information on handling JSON-formatted data in SQL Server and Azure SQL Database, refer to https://learn.microsoft.com/sql/relational-databases/json/json-data-sql-server.

The rowversion data type

This data type generates a database-wide unique binary value upon each modification of row data. This binary value increments with each INSERT or UPDATE statement that affects the row, even if no other row data is modified. A common function of this data type is as a row change indicator for use with applications that use optimistic concurrency or as a database-wide change indicator.

The name timestamp is the same as the SQL ISO standard timestamp, but it does not work according to the ISO standard. Contrary to what the timestamp name might imply, the data in a rowversion column does not map to a moment in time.

When designing tables with rowversion, keep the following restrictions in mind:

• A table can have only a single rowversion column. Considering the context of rowversion, this restriction is perfectly sensible, and we’ve included it here only for completeness.

• You cannot specify a value for the rowversion column in INSERT or UPDATE statements. However, unlike with identity or computed columns, you must specify a column list in INSERT statements for tables with a rowversion column. Note that specifying the column list is recommended anyway.

• Although the Database Engine will not generate duplicate rowversion values within a database, rowversion values are not unique across databases or instances.

Duplicate rowversion values can exist in a single database if a new table is created by using the SELECT INTO syntax. The new table’s rowversion values will be the same as those of the source table. This behavior might be desired when, for example, modifying a table’s schema by creating a new table and copying all the data into it. In other instances, this behavior might not be desired. In those cases, you should not include the rowversion column in the SELECT INTO statement. You should then alter the new table to add a rowversion column. This behavior and workaround are illustrated in an extra sample script file in the accompanying downloads for this book.

Implement optimistic concurrency

Including a rowversion column in a table is an excellent way to implement a row change indicator to achieve optimistic concurrency. With optimistic concurrency, a client reads data with the intent of updating it. Unlike with pessimistic concurrency, however, a lock is not maintained. Instead, in the same transaction as the UPDATE statement, the client will verify that the rowversion was not changed by another process. If it wasn’t, the update proceeds. But if the rowversion no longer matches what the client originally read, the update will fail. The client application can then retrieve the current values and present the user with a notification and suitable options, depending on the application needs. Many object-relational mappers (ORMs), including Entity Framework, support using a rowversion column type to implement optimistic concurrency.

The uniqueidentifier data type

The uniqueidentifier data type stores a 16-byte value known as a globally unique identifier (GUID). SQL Server can generate GUIDs using one of two functions: NEWID() and NEWSEQUENTIALID(). NEWSEQUENTIALID() generates a GUID that is greater than a previously generated GUID by this function since the last restart of the server. You can use NEWSEQUENTIALID() only in a default constraint for a column; it is more suitable for use as a clustered primary key than NEWID(). Unlike NEWID(), which generates random values, the increasing nature of the GUIDs generated by NEWSEQUENTIALID() means that data and index pages will fill completely.

• The uniqueidentifier data type plays an important role in some replication techniques. For more information, see Chapter 11, “Implement high availability and disaster recovery.”

The hierarchyid data type

The hierarchyid data type enables an application to store and query hierarchical data in a tree structure. A tree structure means that a row will have zero or one parent and zero or more children. There is a single root element denoted by a single forward slash (/). hierarchyid values are stored as a binary format but are commonly represented in their string format. Each element at the same level in the hierarchy (referred to as a sibling) has a unique numeric value (which might include a decimal point). In the string representation of a hierarchyid value, each level is separated by a forward slash. The string representation always begins with a slash (to denote the root element) and ends with a slash.

For example, as illustrated in Figure 7-2, a hierarchyid whose string representation is /1/10/ is a descendant of the /1/ element, which itself is a descendant of the implicit root element /. It must be noted, however, that SQL Server does not enforce the existence of a row with the ancestor element. This means it is possible to create an element /3/1/ without its ancestor /3/ being a value in a row. Implicitly, it is a child of /3/, even if no row with hierarchyid value /3/ exists. Similarly, the row with hierarchyid element /1/ can be deleted if another row has hierarchyid value /1/10/. If you don’t want this, the application or database will need to include logic to enforce the existence of an ancestor when inserting and to prevent the deletion of an ancestor.

Perhaps surprisingly, SQL Server does not enforce uniqueness of the hierarchyid values unless you define a unique index or constraint on the hierarchyid column. It is, therefore, possible for the /3/1/ element to be defined twice. This is likely not the desired situation, so we recommend that you ensure uniqueness of the hierarchyid values.

Using the hierarchyid data type is appropriate if the tree is most commonly queried to find descendants, such as children, children-of-children, and more. This is because hierarchyid processes rows depth-first if it is indexed. You can create a breadth-first index by adding a computed column to the table (which uses the .GetLevel() method on the hierarchyid column) and then creating an index on the computed column followed by the hierarchyid column. You cannot, however, use a computed column in a clustered index, so this solution will still be less efficient compared to creating a clustered index on the hierarchyid value alone.

A hierarchyid method worth mentioning is .GetAncestor(). This method returns the hierarchyid value of the current node’s parent. Conversely, .IsDescendantOf() determines whether a node is a descendant, direct or otherwise, of the hierarchyid provided as the function’s parameter.

• For a complete overview of the hierarchyid data type, refer to https://learn.microsoft.com/sql/relational-databases/hierarchical-data-sql-server.

The sql_variant data type

The sql_variant data type enables a single column to store data of diverse types. You can also use this type as a parameter or a variable. In addition to storing the actual value, each sql_variant instance also stores metadata about the value, including its system data type, maximum size, scale and precision, and collation. Using sql_variant can be indicative of a poor database design, and you should use it judiciously. Client libraries that do not know how to handle that data might convert it to nvarchar(4000), with potential consequences for data that doesn’t convert well to character data.

In queries, you can retrieve the base type of the stored value and the base types’ properties using the SQL_VARIANT_PROPERTY() function. For example, using SQL_VARIANT_PROPERTY(<columnname>, 'BaseType'), you can retrieve the sysname of the underlying type. Other values that can be provided as the property parameter value of the function are Precision, Scale, TotalBytes, Collation, and MaxLength. If a particular property doesn’t apply to the underlying data type, the way precision doesn’t apply to varchar, the function returns NULL.

Primary keys, foreign keys, and relationships

Proper relational database design calls for a process called normalization. Through normalization, logical entities are broken into multiple related tables. Primary keys are created for entities, and foreign keys establish relationships between entities. A detailed discussion of normalization is beyond the scope of this book.

Without intending to wax poetic, keys are the nervous system of a relational database. They establish the relationships between the multiple tables created through normalization. A relational database system uses both primary keys and foreign keys. In a single table, the primary key values must be unique because those values can be used as foreign key values in the related table. The foreign key values can also be unique in the related table, in which case the established relationship is a one-to-one relationship. This is discussed in the “Vertical partitions” section later in the chapter.

A table can have exactly one primary key. This primary key can consist of multiple columns, in which case it’s referred to as a compound primary key. A simple primary key only has one column. In no case can a nullable column be (part of) the primary key. If additional columns’ values should be unique, you can apply a unique index or constraint. (See the next sections for coverage on additional constraint types.)

Foreign keys are intended to establish referential integrity. Referential integrity allows values found in the foreign key column(s) to exist in either the primary key, unique constraint, or unique index column(s). By default, foreign keys in SQL Server have referential integrity enforced. It is possible to establish a foreign key without referential integrity enforced, or to alter the foreign key to turn referential integrity off and on. This functionality is useful during import operations or certain types of database maintenance.

This applies to check constraints, too, which are discussed later in this section. The same is also true for primary keys if they are not enabled. However, during normal operations, foreign keys should have referential integrity enabled to protect the integrity of your data. Otherwise, establishing the relationship is useful only for documentary purposes, which can be helpful, but less so than knowing the foreign key references are always correct.

One table can have multiple foreign keys.

When defining a foreign key, you can specify how to handle an operation in the parent row that would invalidate the relationship. Cascading specifically means that the same operation will be run on the child row(s) as was run on the parent. Thus, if the primary key value is updated, the foreign key values will be updated, and if the parent row is deleted, the foreign key values will be deleted. Alternatively, on updates or deletes in the parent table, no action can be taken (the default, which would cause the update or delete statement to fail if referential integrity is enforced), the foreign key value can be set to NULL (effectively creating an orphaned row), or the foreign key value can be set to its default constraint’s specification (effectively mapping the child row to another parent).

Unique constraints

A unique constraint enforces unique values in one column or selected columns. Unlike a primary key, the unique constraint allows the column(s) to be nullable, though NULL values in a constraint are treated as one value in SQL Server, not an unknown value. This means it is possible to have many rows with a NULL value if you are using a compound key. Otherwise, there will only be one NULL value.

• Refer to Chapter 15 for guidance on unique filtered indexes, which can be used to work around this limitation.

Like primary and foreign keys, referential integrity is enabled by default for unique constraints. It is possible to disable the unique index, which will also disable the unique constraint.

SQL Server 2022 enables you to allow resumable add table constraints. The following sample adds a unique constraint to the column CountryName in the Application.Countries table in the WideWorldImporters sample database with a resumable add table constraint.

Click here to view code image

ALTER TABLE [Application].Countries WITH CHECK
    ADD CONSTRAINT UC_CountryName_Resume UNIQUE (CountryName)
    WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60);

When creating any type of unique constraint or index, the name must be distinct. The following could also be added to the table as well (which would create the exact same duplicate physical structure, which is not of any value):

Click here to view code image

ALTER TABLE [Application].Countries WITH CHECK
    ADD CONSTRAINT UC_CountryName_Resume2 UNIQUE (CountryName)
    WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60);

Check constraints

A check constraint enforces rules that can be expressed by using a Boolean expression. For example, in the Sales.Invoices table in the sample WideWorldImporters database, there is a check constraint defined that requires the ReturnedDeliveryData column to either be NULL or contain valid JSON, as shown below.

Click here to view code image

ALTER TABLE Sales.Invoices WITH CHECK
     ADD CONSTRAINT CK_Sales_Invoices_ReturnedDeliveryData_Must_Be_Valid_JSON
     CHECK ((ISJSON(ReturnedDeliveryData)<>(0)));

Check constraints can reference more than one column. A frequently encountered requirement is that when one column contains a specific value, another column cannot be NULL.

Using constraints with compound conditions also provides an opportunity to provide check constraints in the face of changing requirements. If a new business rule requires that a nullable column must now contain a value, but no suitable default can be provided for the existing rows, you should consider creating a check constraint that verifies whether an incrementing ID column or date column is larger than the value it held when the rule took effect. For example, consider the table Sales.Invoices in the previous sample, which has a nullable column Comments. If effective September 1, 2022, every new and modified invoice must have a value in the Comments column, the table could be altered using the following script:

Click here to view code image

ALTER TABLE Sales.Invoices WITH CHECK
   ADD CONSTRAINT CH_Comments CHECK (LastEditedWhen < '2022-09-01'
   OR Comments IS NOT NULL);

A problem that you cannot solve by using a constraint is when a column must contain unique values if a value is provided. In other words, the column should allow multiple rows with NULL, but otherwise should be unique. The solution then is to use a filtered unique index.

• Read about filtered unique indexes in Chapter 15.

Default constraints

The fourth and final constraint type is the default constraint. A default constraint specifies the value that will be used as the default value when an INSERT statement does not specify a value for the column.

Default constraints are useful in a number of scenarios, most notably when adding a new non-nullable column to a table with existing data. This scenario is demonstrated in the following code sample, which adds a PrimaryLanguage column to the Application.People table in WideWorldImporters.

Click here to view code image

ALTER TABLE [Application].People
    ADD PrimaryLanguage nvarchar(50) NOT NULL
        CONSTRAINT DF_Application_People_PrimaryLanguage DEFAULT 'English';

User-defined data types

Alias data types are merely a new name for an existing system data type including the same length and precision as the original data type, optionally a default nullability specification. Specifying a default value or a validation rule for the alias is deprecated functionality.

For example, if you want to ensure that a customer name was always defined as an nvarchar column with a maximum length of 100 characters, you might use the CREATE TYPE statement as shown here:

Click here to view code image

CREATE TYPE CustomerNameType FROM nvarchar(100);
GO

After creating this UDT, in any place where you would ordinarily specify nvarchar(100), you can use CustomerNameType instead. This can be in a table’s column definition, as the return type of a scalar function, or as a parameter to a stored procedure.

The following abbreviated CREATE TABLE statement, which is based on the WideWorldImporters sample Customers table, illustrates how CustomerNameType replaces nvarchar(100):

Click here to view code image

CREATE TABLE Sales.Customers (
    CustomerID INT NOT NULL,
    CustomerName CustomerNameType, -- can override nullability of the type here
…

UDTs can adversely affect data quality, as there are no additional methods of providing data protection.

CLR user-defined types

You develop user-defined types in a .NET language such as C#, and you must compile them into a .NET assembly. This .NET assembly is then registered in the database where the type will be used. A database can use these types only if SQL CLR is enabled.

Use caution when enabling CLR and granting permissions to assemblies, especially for trustworthy databases. Depending on the permissions granted, an assembly may be able to acquire sysadmin privileges, gain access to system resources, access user data, or any combination of the above.

Create a system-versioned temporal table

The simple CREATE TABLE statement that follows illustrates the use of these clauses to create a system-versioned temporal table with an anonymous history table:

Click here to view code image

CREATE TABLE dbo.Products (
    -- Clustered primary key is required
    ProductId int NOT NULL PRIMARY KEY CLUSTERED
  , ProductName varchar(50) NOT NULL
  , CategoryId int NOT NULL
  , SalesPrice money NOT NULL
  , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
  , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
  -- PERIOD FOR SYSTEM_TIME to indicate columns storing validity start and end
  , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime))
-- SYSTEM_VERSIONING clause without HISTORY_TABLE option creates
-- an anonymous history table, meaning the name will be auto-generated
WITH (SYSTEM_VERSIONING = ON);

An existing table can also be altered to become a temporal table. This is a two-stage process, in which you first alter the table to include the two required datetime2 columns and then alter the table to turn on system versioning while optionally specifying a history table name. The history of all columns in the table will be captured, so if the table is very volatile, and if it includes columns that you don’t care to track, this feature may not be useful.

When creating a new table or altering an existing one, you can apply the optional HIDDEN property to the columns for the validity period to exclude the columns from a standard SELECT statement. This might be useful to ensure backward compatibility with existing applications that query the table.

Understand data movement in temporal tables

The Database Engine manages the movement of data from the current table to the history table. The following list details the data movements that take place with each Data Manipulation Language (DML) operation:

• INSERT and BULK INSERT. A new row is added to the current table. The row’s validity start time is set to the transaction’s start time. The validity end time is set to the datetime2 type’s maximum value—December 31, 9999—at a fractional second, or a whole second when using datetime2(0). There is no change in the history table.

• UPDATE. A new row is added to the history table with the old values. The validity end time of the history row is set to the transaction’s start time. In the current table, the row is updated with the new values and the validity start time is updated to the transaction’s start time. If the same row is updated multiple times in the same transaction, multiple history rows with the same validity start and end time will be inserted. Those rows will not be retrieved using typical queries; only the current version of non-deleted rows will be returned. For instance, if only one column is changed, the entire row will be duplicated in the history structure.

• DELETE. A new row is added to the history table containing the values from the current table. The validity end period of the history row is set to the transaction’s start time. The row is removed from the current table.

MERGE statements need no special consideration. A MERGE operation behaves as if separate INSERT, UPDATE, and DELETE statements are executed, as determined necessary by the MATCH clauses. Those statements add rows to the history table, as just described.

Query temporal tables

Querying a temporal table is no different from querying another table if your query only needs to return current data. This makes it possible to modify an existing database and alter tables into temporal tables without requiring application modifications.

When designing queries that need to return historical data or even a mix of current and historical data, you use the FOR SYSTEM_TIME clause in the FROM clause of the SELECT statement. There are five subclauses used with FOR SYSTEM_TIME that help you define the time frame for which you want to retrieve rows. The following list describes these subclauses. It also provides a sample T-SQL statement for each one that you can run on the WideWorldImporters sample database to see its effects.

• ALL. The result set is essentially the union between the current and the history tables. Multiple rows can be returned for the same primary key in the current table. This will be the case for any row that has one or more history entries, as shown here:

  Click here to view code image

  SELECT PersonID, FullName,
        CASE WHEN ValidTo = '9999-12-31 23:59:59.9999999' THEN 1
           ELSE 0 END AS IsCurrent
  FROM Application.People FOR SYSTEM_TIME ALL
  WHERE PeriodId = 11
  ORDER BY ValidFrom;

• AS OF. This returns rows that were valid at the single point in time in the UTC time zone. Rows that have been deleted from the current table or that didn’t exist yet will not be included:

  Click here to view code image

  /* AS OF sub-clause returns all rows that were valid at one point in time.
   * Recall the SYSTEM_TIME is UTC.
   * Showing an example here of how to convert a local time to UTC:
   * Local time is March 13, 2022 12:00 AM (midnight) US Pacific Time
   * (the start of the day).
   * March 13 is not in daylight saving time, so the offset is -8 hours.
   * Thus, records we're looking for were active on March 13, 2022 8 AM UTC.
   * Calling the AT TIME ZONE function twice gives the desired time in UTC.
   */
  DECLARE @AsOfTime datetime2(7) = CONVERT(datetime2(7), '2022-03-13T00:00:00', 126)
      AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'UTC';
  SELECT PersonID, FullName
      , CASE WHEN ValidTo = '9999-12-31 23:59:59.9999999' THEN 1
             ELSE 0 END 'IsCurrent'
  FROM [Application].People FOR SYSTEM_TIME AS OF @AsOfTime
  ORDER BY ValidFrom;

• FROM … TO. This returns all rows that were active between the specified lower bound and upper bound. In other words, if the row’s validity start time is before the upper bound or its validity end time is after the lower bound, the row will be included in the result set. Rows that became active exactly on the upper bound or that closed exactly on the lower bound are not included. This clause might return multiple rows for the same primary key value:

  Click here to view code image

  SELECT PersonID, FullName,
       CASE WHEN ValidTo = '9999-12-31 23:59:59.9999999' THEN 1
           ELSE 0 END AS IsCurrent
  -- SYSTEM_TIME uses UTC so provide date range in UTC as well
  FROM Application.People FOR SYSTEM_TIME FROM '2022-03-13' TO '2022-04-23'
  ORDER BY ValidFrom;

• BETWEEN … AND. This is like FROM … TO, but rows that opened exactly on the upper bound are included:

  Click here to view code image

  SELECT PersonID, FullName,
       CASE WHEN ValidTo = '9999-12-31 23:59:59.9999999' THEN 1
           ELSE 0 END AS IsCurrent
  FROM Application.People FOR SYSTEM_TIME BETWEEN '2022-03-13' AND '2022-04-23'
  ORDER BY ValidFrom;

• CONTAINED IN (,). This returns rows that were active exclusively between the lower and the upper bound. If a row was valid earlier than the lower bound or valid past the upper bound, it is not included. A row that was opened exactly on the lower bound or closed exactly on the upper bound will be included. If the upper bound is earlier than the maximum value for datetime2, only history rows will be included:

  Click here to view code image

  DECLARE @now datetime2(7) = SYSUTCDATETIME();
  SELECT PersonID, FullName,
       CASE WHEN ValidTo = '9999-12-31 23:59:59.9999999' THEN 1
           ELSE 0 END AS IsCurrent
  FROM Application.People FOR SYSTEM_TIME CONTAINED IN ('2022-03-13', @now)
  ORDER BY ValidFrom;

Manage temporal tables

Altering a temporal table will generally cause its associated history table to be altered in the same way. This applies when adding, altering, or removing columns. However, there are a few operations that require you to disable system versioning. These include adding an identity, rowguidcol, or computed column; adding a sparse column in most cases; and adding a column set.

To drop a temporal table, you must disable system versioning and then drop both the current table and the history table using two separate DROP TABLE statements.

Database preparation for memory-optimized tables

Before creating memory-optimized tables, you must prepare the database. The database compatibility level must be at least 130. For SQL Server, you need to create a memory-optimized filegroup. There is no such requirement for Azure SQL Database—or, more accurately, the filegroup is intrinsically present.

Microsoft provides a T-SQL script to ensure that these settings are correct and that a memory-optimized filegroup is created. You can even run the script in Azure SQL Database to ensure that the database supports memory-optimized tables.

• Rather than reprinting this script here, we refer you to the SQL Server samples GitHub page at https://raw.githubusercontent.com/microsoft/sql-server-samples/main/samples/features/in-memory-database/in-memory-oltp/t-sql-scripts/enable-in-memory-oltp.sql.

The script first checks to ensure that the instance or database supports memory-optimized tables, using the SERVERPROPERTY(N'IsXTPSupported') function call. On SQL Server, the script will create a memory-optimized filegroup and container if none already exist. The script also checks and sets the database-compatibility level.

After these actions are complete, you are ready to create one or more memory-optimized tables. The WITH (MEMORY_OPTIMIZED = ON) is the key clause of the CREATE TABLE statement that makes your table a memory-optimized table. Memory-optimized tables support indexing, but you must create and delete them using an ALTER TABLE … ADD/DROP INDEX statement instead of a CREATE/DROP INDEX statement.

Natively compiled stored procedures and user-defined functions

You can access memory-optimized tables via interpreted T-SQL statements and stored procedures. However, you can achieve significant additional performance gains if you use natively compiled stored procedures. These stored procedures are compiled to machine code the first time they are run rather than evaluated every time they run.

To create a natively compiled stored procedure, use the WITH NATIVE_COMPILATION clause of the CREATE PROCEDURE statement. Natively compiled stored procedure objects require the use of the SCHEMABINDING option. The BEGIN ATOMIC statement is also required; it replaces BEGIN TRANSACTION for natively compiled procedures and functions. This statement either begins a new transaction or creates a save point in an existing transaction on the session. When creating a save point in an existing transaction, only the changes made by the stored procedure would be rolled back if the stored procedure were to fail.

The BEGIN ATOMIC statement has two required options:

• TRANSACTION_ISOLATION. You must set this value to one of the three supported isolation levels: snapshot, repeatable read, or serializable.

• LANGUAGE. This is a name value from the sys.syslanguages system compatibility view. For example, for United States English, it is us_english, and for Dutch it is Nederlands.

The BEGIN ATOMIC statement is also where delayed durability can be specified (DELAYED_DURABILITY = ON). With delayed durability, the Database Engine reports to the client that the transaction committed before the log record has been committed to a drive. This creates a risk of data loss should the service or server shut down before the asynchronous log write is completed. You should take the same care to use delayed durability with BEGIN ATOMIC as with BEGIN TRANSACTION. To use delayed durability, it must not be disabled at the database level. Schema-only memory-optimized tables do not use transaction logging, so when modifying data in those tables, there is no benefit in specifying delayed durability.

• For more information on delayed durability, see Chapter 14.

The following short sample script creates a memory-optimized table and natively compiled stored procedure in a database that has been prepared previously.

Click here to view code image

CREATE TABLE dbo.UserDetails (
    UserId    int NOT NULL,
    DetailId  int NOT NULL,
    Detail    nvarchar(50) NOT NULL,
    CONSTRAINT PK_UserDetails PRIMARY KEY NONCLUSTERED (UserId, DetailId)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
CREATE PROCEDURE dbo.GetUserName
    @userId int
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
          LANGUAGE = N'us_english')
    SELECT Detail
    FROM dbo.UserDetails
    WHERE UserId = @userId
        -- Assume this refers to the name
        AND DetailId = 1;
END;
GO

Caveats to memory-optimized tables

To put it plainly, you should probably not convert all of your tables to memory-optimized tables. There are several caveats you must consider before adopting memory-optimized tables and when deciding which tables to turn into memory-optimized tables. This section discusses these caveats.

Memory-optimized tables support only three transaction isolation levels: snapshot, repeatable read, and serializable. If your application needs other isolation levels, you will not be able to implement memory-optimized tables. Refer to Chapter 14 for complete details about transaction isolation levels.

Because all memory-optimized table data is kept in memory, you would correctly expect additional memory requirements. When planning for memory size, however, you should consider that the memory requirement of a memory-optimized table can be more than twice the size of the data in the table. This is due to processing overhead requirements, including the row versions that are kept.

• To review specific guidance on planning for memory size, refer to https://learn.microsoft.com/sql/relational-databases/in-memory-oltp/estimate-memory-requirements-for-memory-optimized-tables.

When persisted memory-optimized tables are used, upon service start, the Database Engine will load all data from the drive to memory. Indexes of memory-optimized tables are not persisted, and they are rebuilt entirely upon service start as the index operations are not logged. The service is not available while these operations take place. With large tables, this can lead to significantly longer service start times. Even though you might carefully plan your service or server restarts for a maintenance window, an unplanned failover on a failover cluster instance (FCI) will also take that amount of time. This might be detrimental to meeting your Service-Level Agreement (SLA), which might have been the entire reason to configure an FCI in the first place. If the performance of memory-optimized tables is needed in combination with a high-availability configuration, you might consider availability groups instead. Because the Database Engine service is running on the secondary, there is no delay caused by having to read the data from a drive and rebuilding indexes.

• Read about FCI and availability groups in Chapter 11.

One way to reduce database startup time due to memory-optimized tables is to ensure that checkpoints are taken frequently. This is because checkpoints cause the updated rows in the memory-optimized table to be committed to the data file. Any data that is not committed to the drive must be read from the transaction log. For large tables, this benefit is likely small.

Another contributor to delays, though after service start, is when natively compiled stored procedures are run for the first time. This can take about as long as running a traditional stored procedure because the compiled version of the stored procedure is not saved. Any time a natively compiled stored procedure is run subsequently, the compiled version will be faster.

Memory-optimized tables use an optimistic concurrency model. While memory-optimized tables don’t use locks, latches, or spinlocks, they are subject to isolation level errors and update conflicts. This means a client application might experience unexpected conflicts (which are shown as errors that are truly just messages to the client that their data is being used in an incompatible manner). You should design the application to handle those. Ironically, one of the greatest benefits of memory-optimized tables is optimistic concurrency, but it also causes one of the largest drawbacks, in that applications must be designed to correctly handle the errors generated.

Not unlike when faster drive storage is used for SQL Server, when adopting memory-optimized tables, you might find that the CPU usage is much higher. This is because much less time is spent waiting for I/O operations to complete. This is exactly why you implemented memory-optimized tables: CPU utilization is higher because data is being processed faster! However, you might inadvertently reduce the number of concurrent requests that can be served, especially if one instance runs multiple databases. If this is a concern, consider using Resource Governor to manage the relative CPU usage for specific workloads.

Define graph tables

In SQL Server, you store graph data in two table types: node and edge tables. These table types are still stored internally as relational structures, but the Database Engine has additional capabilities to manage and query the data that is stored within them.

The T-SQL CREATE TABLE syntax has two clauses: AS NODE and AS EDGE. The following T-SQL script creates a People node table and a Relationships edge table. You can run this script in any existing or new database; there are no specific requirements of the database:

Click here to view code image

CREATE TABLE dbo.People (
     PersonId int NOT NULL PRIMARY KEY CLUSTERED,
     FirstName nvarchar(50) NOT NULL,
     LastName nvarchar(50) NOT NULL
) AS NODE;
CREATE TABLE dbo.Relationships (
    RelationshipType nvarchar(50) NOT NULL,
    -- Two people can only be related once
    CONSTRAINT UX_Relationship UNIQUE ($from_id, $to_id),
    CONSTRAINT EC_People_ConnectsTo_People CONNECTION (dbo.People TO dbo.People)
) AS EDGE;

In the sample script, both the node and the edge table contain user-defined columns. Edge tables are not required to have user-defined columns, but node tables must have at least one. In the case of edge tables, which model relationships, simply modeling the relationship without additional attributes can be all you need. In the case of node tables, which model entities, there is no value in a node without properties, as the nodes exist to compare properties to one another. Designing a node table is comparable to designing a relational table; you would still consider normalization and other concepts.

The sample script also defines an edge constraint, a feature introduced in SQL Server 2019. Edge constraints restrict which node types can be associated using a particular edge. In this case, a Relationships edge is defined between two nodes of type People.

The edge constraint may be repeated multiple times in a single-edge table definition, but the entries in the table must comply with all constraints. Alternatively, multiple edge constraint clauses may be defined within the same edge constraint, in which case any of the constraint clauses must be satisfied.

The next script defines a second node table, Animals; removes the existing edge constraint; and finally creates a new edge constraint with two clauses, allowing a relationship to exist between two People rows or between a People row and an Animals row.

Click here to view code image

CREATE TABLE dbo.Animals (
   AnimalId int NOT NULL PRIMARY KEY CLUSTERED,
   AnimalName nvarchar(50) NOT NULL
) AS NODE;
-- Drop and re-create the constraint, because an edge constraint cannot be altered
ALTER TABLE Relationships
    DROP CONSTRAINT EC_Relationship;
ALTER TABLE Relationships
    ADD CONSTRAINT EC_Relationship CONNECTION (dbo.People TO dbo.People,
        dbo.People TO dbo.Animals);

In addition to user-defined columns, both table types also have one or more columns that are added to the table implicitly to support graph operations. Node tables have two system-generated implicit (also called pseudo) columns, $graph_id and $node_id:

• graph_id_<hex_string_1>. This is a bigint column, which stores the internally generated graph ID for the row. This column is internal and cannot be explicitly queried. However, information about this column will be accessible in sys.columns.

• $node_id_<hex_string_2>. This returns a computed nvarchar value that includes the internally generated bigint value and schema information. This column can be queried but you should avoid explicitly querying this column. Instead, you should use the NODE_ID_FROM_PARTS() query to access the JSON.

In addition to optional user-defined columns, edge tables have three implicit columns:

• $edge_id_<hex_string_3>. This is a system-managed value, comparable to the $node_id column in a node table.

• $from_id_<hex_string_4>. This references a node ID from any node table in the graph that meets the edge constraints. This is the source node in the directed graph.

• $to_id_<hex_string_5>. This references a node ID from any node table in the graph that meets the edge constraints. This is the target node in the directed graph.

Work with graph data

DML statements generally work the same in graph tables as they do in relational tables. Some operations are not supported. An edge table does not support updating either the $frHi, om_id, or $to_id column value. Thus, to update a relationship, the existing edge row must be deleted and a new one inserted. User-defined columns of edge tables do support update operations.

When querying graph data, you can write your own table joins to join nodes to edges to nodes, though this approach offers none of the benefits of graph tables. Instead, we recommend using the MATCH comparison operator in the WHERE clause. The MATCH operator uses a style of expression referred to as ASCII art to indicate how nodes and edges should be traversed. You might be surprised to find that the node and edge tables are specified using old-style join syntax first. The MATCH subclause then performs the actual equi-joins necessary to traverse the graph.

The brief examples that follow are intended to provide an introduction only. They build on the creation of the People and Relationship tables shown in the previous example. First, a few rows of sample data are inserted. Then, the sample data is queried using the MATCH subclause:

Click here to view code image

-- Insert a few sample people
-- $node_id is implicit and skipped
INSERT INTO dbo.People VALUES
    (1, 'Karina', 'Jakobsen'),
    (2, 'David', 'Hamilton'),
    (3, 'James', 'Hamilton'),
    (4, 'Stella', 'Rosenhain');
-- Insert a few sample relationships
-- The first sub-select retrieves the $node_id of the from_node
-- The second sub-select retrieves the $node_id of the to_node
INSERT INTO dbo.Relationships VALUES
    ((SELECT $node_id FROM People WHERE PersonId = 1),
     (SELECT $node_id FROM People WHERE PersonId = 2),
     'spouse'),
     ((SELECT $node_id FROM People WHERE PersonId = 2),
     (SELECT $node_id FROM People WHERE PersonId = 3),
     'father'),
     ((SELECT $node_id FROM People WHERE PersonId = 4),
     (SELECT $node_id FROM People WHERE PersonId = 2),
     'mother');
-- Simple graph query
SELECT P1.FirstName + ' is the ' + R.RelationshipType +
    ' of ' + P2.FirstName + '.'
FROM dbo.People P1, dbo.People P2, dbo.Relationships R
WHERE MATCH(P1-I->P2);

The arrow used in the MATCH subclause means that a node in the People table should be related to another node in the People table using the Relations edge. As with self-referencing many-to-many relationships, the People table needs to be present in the FROM clause twice to allow the second People node to be different from the first. Otherwise, the query would retrieve only edges in which people are related to themselves. (There are no such relationships in our sample.)

The true power of the MATCH subclause is evident when traversing the graph between three or more nodes. One such example would be finding restaurants your friends have liked in the city where your friends live and where you intend to travel.

• For a more comprehensive sample graph database, refer to https://learn.microsoft.com/sql/relational-databases/graphs/sql-graph-sample.

SQL Server 2019 introduced support for the shortest path algorithm. With this support, it is now possible for the MATCH clause to traverse an arbitrary number of nodes to find a related node. Several T-SQL syntax elements are required, including a SHORTEST_PATH function as well as FOR PATH and WITHIN GROUP (GRAPH PATH) clauses. The following sample retrieves all the direct descendants of one of the people in the same table:

Click here to view code image

-- Construct Stella Rosenhain's direct descendants' family tree
-- In our example data, two rows will be returned
SELECT P1.FirstName
        , STRING_AGG(P2.FirstName, '->') WITHIN GROUP (GRAPH PATH) AS Descendants
FROM dbo.People P1
    , dbo.People FOR PATH P2
    , dbo.Relationships FOR PATH related_to1
WHERE (MATCH(SHORTEST_PATH(P1(-(related_to1)->P2)+))
    -- Stella Rosenhain
    AND P1.PersonId = 4);

Running the preceding query shows that David is the direct descendant of Stella. The query also returns a record showing that James is David’s descendant. However, Karina is not returned in the query because she is not a descendant of Stella. A more complex example is included in the accompanying files for this book.

Graph table shortcomings

Since the first graph features were released with SQL Server 2017, additional investments have been made to overcome the limitations found in that release. Most notably, the SHORTEST_PATH function enables both the shortest path graph analytic function and transitive closures (the ability to recursively traverse edges). Still, many limitations compared to native graph databases remain.

The following list contains two notable syntax limitations starting in SQL Server 2019 and a brief description of their significance for implementing a graph. Hopefully, this will provide the information you need to make an informed decision about using SQL Server for graph data.

• Need to explicitly define edges as tables. Graphs model pairwise relations between entities (the nodes). Flexibility can be key in maximizing the benefits of graph models. Even though the nodes and their properties are often well understood, new relationships can be modeled as new needs arise or additional possibilities emerge. The need to make schema modifications to support new types of edges reduces flexibility. Some of this can be addressed by defining one or a few edge tables and storing the edge properties as XML or JSON. This approach, too, has drawbacks in terms of performance and ease of writing queries against the data.

• Limited polymorphism. Polymorphism is the ability to find a node of any type connected to a specified starting node. In SQL Server, a workaround for graph models with few node and edge types is to query all known node and edge types and combine the result sets by using a UNION clause. For large graph models, this solution becomes impractical.

About horizontal partitions

SQL Server’s partitioning feature supports horizontal partitioning with a partition function, which determines in which partition of the table a given row will be stored. Each partition can be stored in its own filegroup in the same database.

When partitioning a table, the rows of the table are not all stored in the same physical place. So, when designing partitions, you must decide on a partition key, which is the column that will be used to assign a row to exactly one partition. From a logical viewpoint, however, all rows belong to the same table.

A query without a WHERE clause returns all rows, regardless of which partition they are stored in. This means the Database Engine must do more work to retrieve rows from different partitions. Your goal when partitioning for query performance should be to write queries that eliminate partitions. You can accomplish this by including the partition key in the WHERE clause.

Additional benefits of horizontal partitioning include the ability to set specific filegroups to read-only. By mapping partitions containing older data to read-only filegroups, you can be assured that this data is unchangeable without affecting your ability to insert new rows. In addition, you can exclude read-only filegroups from regular backups. Finally, during a restore, filegroups containing the most recent data could be restored first, allowing new transactions to be recorded faster than if the entire database needed to be restored.

Index partitioning

In addition to horizontal table partitioning, SQL Server also supports index partitioning. A partitioned index is said to be aligned with the table if the table and the index are partitioned in the same number of partitions using the same column and boundary values.

When a partitioned index is aligned, you can direct index maintenance operations to a specific partition. This can significantly speed up the maintenance operation because you can rebuild or reorganize a partition rather than the entire index. On the other hand, if the entire index needs to be rebuilt, SQL Server will attempt to do so in a parallel fashion. Rebuilding multiple indexes simultaneously creates memory pressure. Because of this, we recommend that you not use partitioning on a system with less than 16 GB of RAM.

You might benefit from creating a partitioned index without partitioning the table. You can still use this nonaligned index to improve query efficiency if only one or a few of the index partitions need to be used. In this case, you must also use the index’s partition key in the WHERE clause to gain the performance benefit of eliminating partitions.

Define partitions and partition a table

We now demonstrate how to create a horizontally partitioned table using the SQL Server feature. Three database objects are involved in defining partitions and partitioning a table:

• A partition function, which defines the number of partitions and the boundary values

• A partition scheme, which defines on which filegroup each partition is placed

• The table to be partitioned

Click here to view code image

-- Create a partition function for February 1, 2019, through January 1, 2020
CREATE PARTITION FUNCTION MonthPartitioningFx (datetime2)
    -- Store the boundary values in the right partition
    AS RANGE RIGHT
   -- Each month is defined by its first day (the boundary value)
    FOR VALUES ('20190201', '20190301', '20190401',
      '20190501', '20190601', '20190701', '20190801',
      '20190901', '20191001', '20191101', '20191201', '20200101');
-- Create a partition scheme using the partition function
-- Place each trimester on its own partition
-- The most recent of the 13 months goes in the latest partition
CREATE PARTITION SCHEME MonthPartitioningScheme
    AS PARTITION MonthPartitioningFx
    TO (FILEGROUP2, FILEGROUP2, FILEGROUP2, FILEGROUP2,
        FILEGROUP3, FILEGROUP3, FILEGROUP3, FILEGROUP3,
        FILEGROUP4, FILEGROUP4, FILEGROUP4, FILEGROUP4, FILEGROUP4);

If you visualize the table data as being sorted by the partition key in ascending order, the left partition is the partition on top. When defining a partition function, you indicate whether the boundary value—in this example, the first day of each month—will be stored in the partition on the left (the default) or the partition on the right (as specified in the sample).

Figure 7-4 shows the relationship between the partition function and the partition scheme. In the sample, the partition function created 13 partitions using 12 boundary values. The partition scheme then directed these 13 partitions to three filegroups by specifying each filegroup four times and the last filegroup five times (because it will hold the last partition).

Horizontal partition design guidelines

When designing horizontal partitions, keep these guidelines in mind, with the understanding that your mileage may vary:

• The number of parallel operations that can be run per query depends on the number of processor cores in the system. Using more partitions than processor cores limits the number of partitions that will be processed in parallel. So, even though SQL Server now supports up to 15,000 partitions, on a system with 12 processor cores, at most 12 partitions will be processed in parallel. You may choose to use fewer partitions than the number of available processor cores to set aside capacity for other queries. There is also the option to disable parallel partition processing or to change the number of processors that a single query can use. Be advised that changing the number of processors per query is a server configuration.

• Choose the partition key to accommodate growing column values. This could be a date value or an incrementing identity column. Usually, you will want to have new rows added to the rightmost partition.

• The selected partition key should be immutable, meaning there should be no business reason for this key value to change. If the value of a partition key changes, SQL Server will execute the UPDATE statement as a DELETE and INSERT statement; there is no provision to “move” a row to another filegroup. This approach is similar to when the value of a clustered index changes.

• For the partition key, a narrow data type is preferable over a wide data type.

• To achieve most benefits of partitioning, specifically those related to performance, you will need to put each partition into its own filegroup. This is not a requirement, and some or all partitions can share a single filegroup. For example, the next section discusses a sliding window partition strategy, in which partitioning is beneficial even if all are in the same filegroup.

• Consider the storage backing the filegroups. For example, your storage system might not provide higher performance if all filegroups have been placed on the same physical drives. Be aware that even if the drive letters are different, they may still all be on the same physical drive.

• Tables that are good candidates for partitioning are tables with many—as in millions or billions—rows for which data is mostly added as opposed to updated, and on which queries are frequently run that would return data from one or a few partitions.

Implement a sliding window partition strategy

Horizontal partitioning is often applied to relational data warehouses. A common data warehouse operation is loading a significant amount of data to a fact table while simultaneously purging old data. The sliding window partition strategy is particularly well-suited for tables for which data is regularly added and removed. For example, data in a fact table can be purged after 13 months. Perhaps each time data is loaded into the data warehouse, rows older than 13 months are removed while new rows are added. This is a sliding window in that the fact table always contains the most recent 13 months of data.

To set up a sliding window, you need a partition function and scheme as well as the fact table. You should also set up a stored procedure that modifies the partition function to accommodate the new boundary values. Finally, you will need a staging table with the same columns and clustered index as the partitioned table.

Figure 7-5 illustrates what happens on March 1, 2020, when data is loaded for the month of February 2020. The fact table is partitioned into 13 partitions, one for each month. An automated process, which is not depicted here, modifies the partition function to accommodate the new date range by splitting the rightmost partition, holding the most recent data, in two. Then, the partition holding the oldest month’s data is switched out to a staging table and the new data is switched in from a staging table. Finally, the leftmost partition, which held the oldest data but is now empty, is merged with the second leftmost partition.

You can optimize the process of switching the old partition out and the new partition in by using a memory-optimized table as the staging table.

Query change tracking

The following code sample updates a row in the Sales.Orders table, which has change tracking enabled. The next statement in the sample then demonstrates how to query the information gathered by change tracking.

Click here to view code image

-- Modify a row in the Orders table,
-- which has change tracking enabled
UPDATE Sales.Orders
    SET Comments = 'I am a new comment!'
    WHERE OrderID = 1;
DECLARE @OrderCommentsColumnId int =
    COLUMNPROPERTY(OBJECT_ID('Sales.Orders'), N'Comments', 'ColumnId'),
    @DeliveryInstructionsColumnId int =
    COLUMNPROPERTY(OBJECT_ID('Sales.Orders'), N'DeliveryInstructions', 'ColumnId');
-- Query all changes to Sales.Orders
SELECT *
    -- Determine if the Comments column was included in the UPDATE
    , CHANGE_TRACKING_IS_COLUMN_IN_MASK(@OrderCommentsColumnId,
        CT.SYS_CHANGE_COLUMNS) CommentsChanged
    -- Determine if the DeliveryInstructions column was included
    , CHANGE_TRACKING_IS_COLUMN_IN_MASK(@DeliveryInstructionsColumnId,
        CT.SYS_CHANGE_COLUMNS) DeliveryInstructionsChanged
FROM CHANGETABLE(CHANGES Sales.Orders, 0) as CT
ORDER BY SYS_CHANGE_VERSION;

The output includes the values of the primary key—in this case the values of the single column OrderID—and a SYS_CHANGE_OPERATION column indicating that an UPDATE statement was executed using the value U. Because you enabled column tracking for the Orders table, there is a non-NULL value for the SYS_CHANGE_COLUMNS column in the output.

For each column for which you’d like to determine whether it was included in the UPDATE statement, use the CHANGE_TRACKING_IS_COLUMN_IN_MASK function. It uses the column as the position in the SYS_CHANGE_COLUMNS bitmask to indicate if the value is changed. In the sample, two output columns are added that determine if the Comments and DeliveryInstructions columns were included in the UPDATE statement. A value of 1 indicates yes, and 0 indicates no.

Query change data capture

The following code sample updates a row in the Sales.Invoices table that has change data capture enabled. Then the change data capture table is queried with the option all update old, which causes the single UPDATE statement to have two rows in the output, one containing the old image and the other row containing the updated data.

Click here to view code image

-- Modify a row in the Invoices table,
-- which has change data capture enabled
UPDATE Sales.Invoices
    SET Comments = 'I am a new invoice comment again'
    WHERE InvoiceID = 1;
DECLARE @from_lsn binary(10) = sys.fn_cdc_get_min_lsn('Sales_Invoices'),
    @to_lsn binary(10) = sys.fn_cdc_get_max_lsn();
-- Each capture instance will have unique function names
-- By default, the capture instance name is schema_table
-- Note: there may be a slight delay before output is returned
SELECT *
FROM cdc.fn_cdc_get_all_changes_Sales_Invoices(@from_lsn, @to_lsn,
    N'all update old');

Pushdown computation

Pushdown computation is supported for numerous data sources, including generic ODBC, Oracle, SQL Server, Teradata, MongoDB, and Azure Blob Storage.

The purpose of pushdown computation is to allow specific computations to be performed on the external data source. This distributes some of the workload of the query to the remote data source, reducing the amount of data that needs to be transmitted and therefore improving query performance. The computations include joins, aggregations, expressions, and operators.

• For more information about what computations work with pushdown, visit https://learn.microsoft.com/sql/relational-databases/polybase/polybase-pushdown-computation#enable-pushdown-computation.

Installation

When installing PolyBase on a Windows machine, keep these points top of mind:

• PolyBase can be installed on only one instance per server.

• To use PolyBase, you need to be assigned the sysadmin role or Control Server permission.

• In SQL Server 2019 and after, you must enable PolyBase using sp_configure after installation. This is an important step to remember in both Windows and Linux.

• If the Windows firewall service is running during installation, the necessary firewall rules will be set up automatically. Other firewalls external to your SQL Server instance may still require changes.

For step-by-step instructions on how to install PolyBase, see the Microsoft documentation for your specific version of SQL Server:

• On Windows, see https://learn.microsoft.com/sql/relational-databases/polybase/polybase-installation.

• On Linux (Red Hat, Ubuntu, or SUSE), see https://learn.microsoft.com/sql/relational-databases/polybase/polybase-linux-setup.

With the installation complete, you accomplish the next steps via T-SQL syntax:

1. Create the database master key, if it doesn’t already exist, with CREATE MASTER KEY.

2. Create a database scoped credential for the data source with CREATE DATABASE SCOPED CREDENTIAL.

3. Configure the external data source with CREATE EXTERNAL DATA SOURCE.

4. Create external tables, with CREATE EXTERNAL TABLE.

For additional details, follow the steps at https://learn.microsoft.com/sql/relational-databases/polybase/polybase-configure-sql-server. These steps are also covered in this chapter.

Configure and enable

A master key is a symmetric key used to protect other keys and certificates in the database. It is encrypted with both an algorithm and password. A master key is necessary to protect the credentials of the external tables. Details on how to create and update a master key, and the instructions to set it up, can be found in this chapter in the section “External tables.”

Starting in SQL Server 2019, you need to enable PolyBase globally using sp_configure. Once complete, you must run RECONFIGURE, and then restart the SQL Server service. Both PolyBase services will have to be started manually as they are turned off during this process and do not restart automatically.

This can be done with these commands:

Click here to view code image

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE [ WITH OVERRIDE ] ;

• For more details on this command, visit https://learn.microsoft.com/sql/database-engine/configure-windows/polybase-connectivity-configuration-transact-sql.

SELECT SERVERPROPERTY ('IsPolyBaseInstalled') AS IsPolyBaseInstalled;

Shared access signatures

As the name suggests, a shared access signature (SAS) is a way to share an object in your storage account with others without exposing your account key. This gives you granular control over the access you grant, at the account, service, or user level.

• You can set a start time and expiry time.

• Azure Blob Storage containers, file shares, queues, and tables are all resources that accept SAS policies.

• You can set an optional IP address or range from which access will be accepted.

• You can restrict access from HTTPS clients by specifying the accepted protocol.

An SAS is used to allow clients to read, write, or delete data in your Azure Storage account without access to your account key. This is typically necessary when a client wants to upload large amounts of data or high-volume transactions to your storage account, and creating a service to scale and match demand is too difficult or expensive.

The SAS is a signed Uniform Resource Identifier (URI) that points to resources and includes a token containing a set of query parameters that indicate how the resource can be accessed. Azure Storage checks both the storage piece and the provided token piece of the SAS URI to verify the request. If for any reason the SAS is not valid, it receives an error code 403 (Forbidden), and access is denied.

There are three different types of SAS:

• User delegation shared access signatures rely on Azure AD credentials.

• The service SAS delegates access via the storage account key to a resource in a single storage service (blob, queue, table, or file service).

• The account-level SAS delegates access via the storage account key to resources in one or more storage services within a storage account. This also includes the option to apply access to services such as Get/Set Service Properties and Get Service Stats.

  • For details on all the parameters that can be set with shared access signatures, see https://learn.microsoft.com/azure/storage/common/storage-dotnet-shared-access-signature-part-1#shared-access-signature-parameters.

You can create an ad hoc SAS either as an account SAS or a service SAS, and the start time, expiry time, and permissions are all specified in the SAS URI, the SECRET that is used to eventually create the CREDENTIAL in SQL Server.

You can also create policies to use for many shared access signatures. You may find that using the free Azure Storage Explorer application makes creating stored access policies and shared access signatures easy. You can download it here: https://aka.ms/storageexplorer. You can also create policies and shared access signatures in PowerShell.

Data uploaded with a SAS is not validated in any way, so a middle tier that performs rule validation, authentication, and auditing may still be the better option. Regardless of what you choose, monitor your storage for spikes in authentication failures.

• Examples of how to create account and service SAS can be found at https://learn.microsoft.com/azure/storage/common/storage-dotnet-shared-access-signature-part-1#sas-examples.

Port requirements for Hadoop

If you plan to access Hadoop with PolyBase in SQL Server 2016 through SQL Server 2019, there are some additional requirements. These relate to which cluster components and ports need to be open for PolyBase to interact correctly with a Hadoop external data source. The ports for the following cluster components must be open:

• HDFS ports

  • NameNode

  • DataNode

• Resource manager

  • Job submission

  • Job history

Table 7-2 shows the default ports for these components. These ports are dependent on the version of Hadoop. In addition, the ports may be different if Hadoop is using a custom configuration.

Database scoped credential

Database scoped credentials are used to access non-public blob storage accounts from SQL Server or Azure Synapse with PolyBase. SQL Server with PolyBase also requires database scoped credentials for several types of connectors for PolyBase, including S3-compliant object storage, ODBC generic types, and Azure Blob Storage.

• Find the syntax to create scoped credentials at https://learn.microsoft.com/sql/t-sql/statements/create-database-scoped-credential-transact-sql.

External data sources

External data sources are used to establish connectivity to systems outside of SQL Server for data virtualization or loading data using PolyBase. The most common use cases are loading data with bulk INSERT or OPENROWSET activities, or accessing data that would otherwise not be available in SQL Server because it resides on another system—even a non-relational storage system.

The details of the CREATE EXTERNAL DATA SOURCE T-SQL syntax have changed with every major version of SQL Server. In Microsoft Docs, pay special attention to the version selector (top-left) in the CREATE EXTERNAL DATA SOURCE reference article: https://learn.microsoft.com/sql/t-sql/statements/create-external-data-source-transact-sql.

The following example shows how to load data from a CSV file in an Azure Blob Storage location that has been configured as an external data source. This requires a database scoped credential using a shared access signature.

Click here to view code image

-- Create the External Data Source
-- Remove the ? from the beginning of the SAS token
-- Do not put a trailing /, file name, or shared access signature parameters at the end
of the LOCATION URL when configuring an external data source for bulk operations.
CREATE DATABASE SCOPED CREDENTIAL AccessPurchaseOrder
WITH
     IDENTITY = 'SHARED ACCESS SIGNATURE'
, SECRET = '******srt=sco&sp=rwac&se=2022-02-01T00:55:34Z&st=2023-12-
29T16:55:34Z***************'
;
CREATE EXTERNAL DATA SOURCE ExternalPurchaseOrder
WITH
(LOCATION   = 'https://newinvoices.blob.core.windows.net/week3'
,CREDENTIAL = AccessPurchaseOrder, TYPE = BLOB_STORAGE)
;
--Insert into
BULK INSERT Sales.Orders
FROM 'order-2022-11-04.csv'
WITH (DATA_SOURCE = ' ExternalPurchaseOrder');

Creating the external data source can be tricky. Your external data source may require some specifics of the connection string to be provided via the CONNECTION_OPTIONS parameter of the CREATE EXTERNAL DATABASE statement.

• Microsoft recently documented the details of potentially required third-party provider parameters in an article for CREATE EXTERNAL DATA SOURCE CONNECTION_OPTIONS, at https://learn.microsoft.com/sql/t-sql/statements/create-external-data-source-connection-options.

External file format

The external file format is required before you can create the external table, but only for some external data source types. As suggested, the file format specifies the layout of the data to be referenced by the external table. Hadoop, Azure Blob Storage, and Azure Data Lake Storage all need an external file format object defined for PolyBase. Delimited text files, Parquet, and both RCFile and ORC Hive files are supported.

Here’s an example:

Click here to view code image

CREATE EXTERNAL FILE FORMAT skipHeader_CSV
WITH (FORMAT_TYPE = DELIMITEDTEXT,
      FORMAT_OPTIONS(
          FIELD_TERMINATOR = ',',
          STRING_DELIMITER = '"',
          FIRST_ROW = 2,
          USE_TYPE_DEFAULT = True)
);

• Syntax for this command can be found at https://learn.microsoft.com/sql/t-sql/statements/create-external-file-format-transact-sql.

Starting with SQL Server 2022, you can also query data directly from the folder of Delta tables in Azure Blob Storage. Creating an external file format is required, but simpler:

Click here to view code image

CREATE EXTERNAL FILE FORMAT deltaTable1 WITH (FORMAT_TYPE = DELTA);

External tables

External tables are used to read specific external data and to import data into SQL Server. You don’t have to create an external table. You can use OPENROWSET to query data on some external data sources, but external tables provide strong data typing and easy joins to traditional SQL Server tables using T-SQL queries. External tables are an important part of the promise PolyBase brings as a single plane of data, inside SQL Server, for your entire data estate.

No actual data is moved to SQL Server during the creation of an external table; only the metadata and basic statistics about the folder and file are stored. The intent of the external table is to be the link connecting the external data to SQL Server to create the data virtualization. The external table looks much like a regular SQL Server table in format and has a similar syntax, as you see here with this external Oracle table.

Click here to view code image

-- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';
-- LOCATION: Location string for data
   CREATE EXTERNAL DATA SOURCE external_data_source_name
   WITH ( LOCATION = 'oracle://<server address>[:<port>]',
   CREDENTIAL = credential_name)
--Create table
   CREATE EXTERNAL TABLE customers(
   [O_ORDERKEY] DECIMAL(38) NOT NULL,
   [O_CUSTKEY] DECIMAL(38) NOT NULL,
   [O_ORDERSTATUS] CHAR COLLATE Latin1_General_BIN NOT NULL,
   [O_TOTALPRICE] DECIMAL(15,2) NOT NULL,
   [O_ORDERDATE] DATETIME2(0) NOT NULL,
   [O_ORDERPRIORITY] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_CLERK] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_SHIPPRIORITY] DECIMAL(38) NOT NULL,
   [O_COMMENT] VARCHAR(79) COLLATE Latin1_General_BIN NOT NULL
   )
   WITH ( LOCATION='customer', DATA_SOURCE= external_data_source_name   );

In ad hoc query scenarios, such as querying Hadoop data, PolyBase stores the rows retrieved from the external data source in a temporary table. After the query completes, PolyBase removes and deletes the temporary table. No permanent data is stored in SQL tables.

In an import scenario, such as SELECT INTO from an external table, PolyBase stores rows returned as permanent data in the SQL table. The new table is created during query execution when PolyBase retrieves the external data.

PolyBase can push some of the query computation to improve query performance. This action is called predicate pushdown. It is used by specifying the Hadoop resource manager location option when creating the external data source and enabling pushdown using these parameters:

Click here to view code image

PUSHDOWN                  = [ON | OFF]
, RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]'

With some third-party data providers, instead of using the PUSHDOWN keyword, you may need to specify pushdown-related keywords in the CONNECTION_OPTIONS argument of CREATE EXTERNAL DATA SOURCE.

You can create many external tables that reference the same or different external data sources.

Statistics

Statistics on external tables are created the same as other tables.

Syntax for CREATE STATISTICS can be found at https://learn.microsoft.com/sql/t-sql/statements/create-statistics-transact-sql.

Catalog views

There are catalog views for the installation and running of PolyBase.

• sys.external_data_sources. Used to identify external data sources and to give visibility into related metadata. This includes the source, type, and name of the remote database, and in the case of Hadoop, the resource manager’s IP and port. This can be very helpful.

• sys.external_file_formats. Used to obtain details on the external file format for the sources. Along with the file format type, it includes details about the delimiters, general format, encoding, and compression.

• sys.external_tables. Contains a row for each external table in the current database. It details information needed about the tables, such as ID links to preceding tables. Many of the columns provide details about external tables over the shard map manager; this is a special database that maintains global mapping information about all shards (databases) in a shard set. The metadata allows an application to connect to the correct database based upon the value of the sharding key. Every shard in the set contains maps that track the local shard data (known as shardlets).

Dynamic management views

Several dynamic management views can be used with PolyBase to troubleshoot issues, such as finding long-running queries, and to monitor nodes in a PolyBase group.

• A list of relevant views can be found at https://learn.microsoft.com/sql/relational-databases/polybase/polybase-troubleshooting#dynamic-management-views.