Technology Performance Pulse

Difference between Unique Indexes and Unique Constraints in SQL Server

SQL Shack

JUNE 10, 2020

This article gives you an overview of Unique Constraints in SQL and also the Unique SQL Server index. Along the way, we will look at the differences between them. Introduction Constraints in SQL Server allows defining the rules at the column level in the SQL table.

Servers

Islands T-SQL Challenge

SQL Performance

APRIL 13, 2022

However, these techniques require explicit sorting despite the presence of a supporting index. Finally, I’ll compare the performance of the different solutions. ( grp VARCHAR ( 10 ) NOT NULL , ord INT NOT NULL , val VARCHAR ( 10 ) NOT NULL , CONSTRAINT PK_T1 PRIMARY KEY ( grp , ord ) ) ; GO.

C++

C++ Strategy Testing Servers

Matching Supply With Demand — Solutions, Part 1

SQL Performance

JANUARY 12, 2022

It so happens that the classic predicate-based technique for identifying interval intersection has poor performance since there's no good indexing scheme to support it. ( ID INT NOT NULL IDENTITY ( 1 , 1 ) CONSTRAINT pk_Auctions PRIMARY KEY CLUSTERED , Code CHAR ( 1 ) NOT NULL. DDL and a Small Set of Sample Data.

Code

Code Servers Performance Azure

Emulating the GROUPS Window Frame Option

SQL Performance

JULY 13, 2022

The same goes specifically for handling T-SQL querying tasks in terms of supported language elements. T-SQL, the dialect, supports a subset of the features from standard SQL. This could also happen if you need to migrate code written for another database platform with a different dialect of SQL to T-SQL.

C++

C++ Code Azure Database

NULL complexities – Part 1

SQL Performance

DECEMBER 11, 2019

NULL handling is one of the trickier aspects of data modeling and data manipulation with SQL. Even among people who do have a good grasp of relational theory and SQL, you will hear very strong opinions both in favor and against using NULLs in your database. I then cover NULL treatment inconsistencies in different language elements.

Best Practices

Best Practices C++ Database Code

T-SQL bugs, pitfalls, and best practices – window functions

SQL Performance

AUGUST 14, 2019

This article is the fourth installment in a series about T-SQL bugs, pitfalls and best practices. There are two common pitfalls involving window functions, both of which are the result of counterintuitive implicit defaults that are imposed by the SQL standard. Previously I covered determinism , subqueries and joins. Transactions. (

Best Practices

Best Practices Servers Database Azure

IGNORE_DUP_KEY slower on clustered indexes

SQL Performance

APRIL 5, 2019

The IGNORE_DUP_KEY option for unique indexes specifies how SQL Server responds to an attempt to INSERT duplicate values: It only applies to tables (not views) and only to inserts. Any insert portion of a MERGE statement ignores any IGNORE_DUP_KEY index setting. Clustered unique index inserts.

Storage

Storage Servers Engineering Testing

NULL Complexities – Part 2

SQL Performance

JANUARY 8, 2020

Last month I introduced the NULL as SQL's marker for any kind of missing value. I explained that SQL doesn't provide you with the ability to distinguish between missing and applicable (A-values) and missing and inapplicable (I-values) markers. This article is the second in a series on NULL complexities.

Design

Design C++ Code Database

NULL complexities – Part 4, Missing standard unique constraint

SQL Performance

MARCH 11, 2020

In the previous articles ( Part 1 , Part 2 , and Part 3 ), I covered the meaning of the NULL as a marker for a missing value, how NULLs behave in comparisons and in other query elements, and standard NULL handling features that are not yet available in T-SQL. Standard UNIQUE constraint. Unique constraint.

C++

C++ Code Servers Database

From Proprietary to Open Source: The Complete Guide to Database Migration

Percona

OCTOBER 18, 2023

It’s a complex process involving various factors and meticulous planning. Today, we’ll be taking a deep dive into the intricacies of database migration, along with specific solutions to help make the process easier. The savings can be particularly significant for organizations looking to optimize their IT spending.

Open Source

Open Source Database Hardware Strategy

Fundamentals of table expressions, Part 1

SQL Performance

APRIL 8, 2020

This article is the first in a series about the fundamentals of table expressions in T-SQL. I will mainly focus on four types of named table expressions, which are known in T-SQL as derived tables, common table expressions (CTEs), views, and inline table-valued functions (inline TVFs). They are all named table expressions.

C++

C++ Azure Database Servers

Fundamentals of Table Expressions, Part 11 – Views, Modification Considerations

SQL Performance

SEPTEMBER 8, 2021

As part of the coverage, I’ll describe a curious inconsistency between how the CHECK OPTION in a view and a CHECK constraint in a table handle modifications—specifically ones involving NULLs. CONSTRAINT PK_Orders PRIMARY KEY , orderdate DATE NOT NULL , shippeddate DATE NULL. ) Sample Data. DROP TABLE IF EXISTS dbo.

Servers

Servers Code Engineering Database

Fun with (columnstore) compression on a very large table – part 3

SQL Performance

FEBRUARY 3, 2020

In part 1 of this series, I tried out a few ways to compress a 1TB table. While I got decent results in my first attempt, I wanted to see if I could improve the performance in part 2. I still need to make it so that each scheduler writes exclusively to its own partition. First, I need to make changes to the batch table I created. OpAffinity. (

Speed

Speed Processing Servers Storage

Row numbers with nondeterministic order

SQL Performance

NOVEMBER 13, 2019

Examples include assigning unique values to result rows, deduplicating data, and returning any row per group. Examples include assigning unique values to result rows, deduplicating data, and returning any row per group. This article explores different techniques for computing row numbers with nondeterministic order.

Servers

Servers C++ Storage Performance

Simple Parameterization and Trivial Plans — Part 6

SQL Performance

AUGUST 4, 2022

Let’s now continue following the compilation process to see how SQL Server decides if simple parameterization is safe or unsafe. Code examples use the Stack Overflow 2010 database on SQL Server 2019 CU 16 with the following additional nonclustered index: CREATE INDEX [ IX dbo. Timing is a key point here.

Servers

Servers Cache Database Availability

Nested Loops Joins and Performance Spools

SQL Performance

SEPTEMBER 25, 2019

They come in three varieties: Lazy Table Spool , Lazy Index Spool , and Lazy Row Count Spool. To be clear, this difference is about execution plan operators , not T-SQL query syntax. Performance spools are lazy spools added by the optimizer to reduce the estimated cost of the inner side of nested loops joins. Performance Spools.

Performance

Performance Cache Servers Education

NoSQL Data Modeling Techniques

Highly Scalable

MARCH 1, 2012

First, we should note that SQL and relational model in general were designed long time ago to interact with the end user. This user-oriented nature had vast implications: The end user is often interested in aggregated reporting information, not in separate data items, and SQL pays a lot of attention to this aspect.

Database

Database Ecommerce Efficiency Engineering

SQL Server I/O Basics Chapter #1

SQL Server According to Bob

JANUARY 11, 2020

Microsoft SQL Server I/O Basics Author: Bob Dorr, Microsoft SQL Server Escalation Published: December, 2004 SUMMARY: Learn the I/O requirements for Microsoft SQL Server database file operations. © 2004 Microsoft Corporation.

Servers

Servers Cache Media Hardware

Technology Performance Pulse

Difference between Unique Indexes and Unique Constraints in SQL Server

Islands T-SQL Challenge

Trending Sources

Matching Supply With Demand — Solutions, Part 1

Emulating the GROUPS Window Frame Option

NULL complexities – Part 1

T-SQL bugs, pitfalls, and best practices – window functions

IGNORE_DUP_KEY slower on clustered indexes

NULL Complexities – Part 2

NULL complexities – Part 4, Missing standard unique constraint

From Proprietary to Open Source: The Complete Guide to Database Migration

Fundamentals of table expressions, Part 1

Fundamentals of Table Expressions, Part 11 – Views, Modification Considerations

Fun with (columnstore) compression on a very large table – part 3

Row numbers with nondeterministic order

Simple Parameterization and Trivial Plans — Part 6

Nested Loops Joins and Performance Spools

NoSQL Data Modeling Techniques

SQL Server I/O Basics Chapter #1

Stay Connected