Overview

• Primary keys can't assign to the nullable columns.
  • NULL is not a value which can uniquely identify any row.
  • All columns that are part of a table’s a PK must contain aggregate unique values other than NULL.
• It is possible to add unique constrains to a nullable column.
  • When we create Primary Key Constraint, it automatically creates Clustered Index.
• Specify the index type to be non-clustered by adding "NONCLUSTERED" keyword in the command.
• You can't create a primary key without any index.
  • Primary Keys exists with either the Clustered Index or the Non-Clustered Index.
  • If you drop the constraint, it will also drop the index along with it and if you drop the constraint, it will also drop your index.
  • As the matter of the fact, the script which you generate to drop the index, will generate drop constraint script only.
• If the Primary Key is the clustered key then dropping the primary key will drop the clustered key because they are one and the same.
  • If the Primary Key is not the clustered key and you have a separate clustered index on exactly the same columns then dropping the primary key does not drop the clustered index.
• We can have both clustered and non-clustered index on the same column.
  • Creation of primary key on a column of a table which already have a clustered index on the same column, will create a separate non-clustered index.
  • So any future dropping attempts will only drop the new non-clustered index.
• If you try to drop clustered index on the primary key, it will raise an error.

CREATE TABLE

CREATE TABLE [User]
(
	-- Primary Key - Auto Number (Seed, Increment)
	[UserId] INT IDENTITY(1,1) NOT NULL,
	/* We can Set PRIMARY KEY on [UserId] this way: 
			-- [UserId] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
			The difference is that the DBMS chooses the name of the primary key.
	*/
	/* Here we define the Constraint for the Primary key. */
	CONSTRAINT [PK_User_Id] PRIMARY KEY CLUSTERED ([UserId] ASC) ON [PRIMARY]
	[FirstName] NVARCHAR(40) NULL,
	[LastName] NVARCHAR(40) NOT NULL,
	[ParentUserId] INT NOT NULL
	CONSTRAINT [FK_dbo_User_dbo_User]
		FOREIGN KEY	([ParentUserId])
		REFERENCES [dbo].[User]([UserId])
		ON UPDATE CASCADE 
		ON DELETE CASCADE,
)

Add Computed Columns

• Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table.
• Their values are recalculated every time they are referenced in a query.
• The Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table.

ALTER TABLE [Production].[WorkOrder]
ADD [OrderVol] AS 
	CASE
		WHEN [OrderQty] < 10 THEN 'Single Digit' 
		WHEN [OrderQty] >= 10 AND [OrderQty] < 100 THEN 'Double Digit' 
		WHEN [OrderQty] >= 100 AND [OrderQty] < 1000 THEN 'Three Digit'
		ELSE 'Super Large'
	END PERSISTED

• SQL Server normally expands computed columns into their underlying definitions during the binding phase of query normalization.
  • This is a very early phase in the query compilation process, well before any plan selection decisions are made (including trivial plan).
  • In theory, performing early expansion might enable optimizations that would otherwise be missed.
• For example, the optimizer might be able to apply simplifications given other information in the query and metadata (e.g. constraints). This is the same sort of reasoning that leads to view definitions being expanded (unless a NOEXPAND hint is used).
• Later in the compilation process (but still before even a trivial plan has been considered), the optimizer looks to match back expressions to persisted or indexed computed columns.
• The problem is that optimizer activities in the meantime may have changed the expanded expressions such that matching back is no longer possible.
• When this occurs, the final execution plan looks as if the optimizer has missed an "obvious" opportunity to use a persisted or indexed computed column.