What works more effectively Join Composite Key or Primary Key?

Question

SQL Server

What works more efficiently for Composite Key or Primary Key for SqlServer performance selections and Joins? For example: 1) 1 PK - Int, 2) 1 PK - Datetime, 3) Year, Month - int Composite Key, 4) Year, Month, Day - int Composite Key. How can performance be measured? What is better for Select + Where or for Select + Join with the table of the same type of keys?

Answer 1

There is no exact answer. Even if at some point something is faster, depending on the usage, caches and statistics will be built and the next request will work differently (turn on client statistics: it will show changes from request to request +/- red / green) .
This is MS SQL, you can never do better than its optimizer. You can help: there is a "DBMS Kernel Tuning Assistant". Or the very first level: include the actual execution plan and statistics of active queries - if the optimizer has a suggestion, it will be green in the form of a script CREATE(just substitute the names of the objects).
And the use datetimeas a primary key (only now it has reached) is not allowed:

testing

CREATE TABLE [dbo].[Table_5](
  [ID] [datetime] NOT NULL,
  [Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Table_5] PRIMARY KEY CLUSTERED 
(
  [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table_5] ADD  CONSTRAINT [DF_Table_5_ID]  DEFAULT (getdate()) FOR [ID]
GO

Let's try to add:

INSERT INTO Table_5 (Name)
  SELECT TOP(5) 'TEST' + CAST(ROW_NUMBER() OVER(ORDER BY id) AS VARCHAR)
         FROM sysobjects

Does not work:

Нарушено "PK_Table_5" ограничения PRIMARY KEY. 
Не удается вставить повторяющийся ключ в объект"dbo.Table_5". 
Повторяющееся значение ключа: (ноя 12 2021  3:06PM).

In more detail: the Windows system timer, by default, fires 1000/64 times per second, and the accuracy is datetime1000/300, which should ensure the uniqueness of consecutive values. But MS SQL, with the help of timeBeginPeriod, increases the frequency of the timer to 1000/1000. That is, when writing to datetimeand rounding to its precision, the values ​​will lose their uniqueness. It is a known collision of this data type that prevents it from being used as a primary key. 1C has special processing for building chronological chains (for example, to distinguish between payment and advance payment regarding shipment), which sets mismatched times on documents.

1) 1 PK - Int, 2) 1 PK - Datetime, 3) Year, Month - int Composite Key, 4) Year, Month, Day - int Composite Key

datetime- this int+int. In the first - the date, in the second - the time, which, based on points 3 and 4, will not be used (and these points are redundant, of course). If you want to use the date as the primary key, use the data type date(3 bytes).

Answer 2

How can performance be measured?
How to do it right?

Sql server profiler + query plan
Normal PK will be faster than composite one. int will be faster than DateTime