Types of tables

Links:

• Memory-Optimized Tables
  • https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/introduction-to-memory-optimized-tables
  • https://www.red-gate.com/simple-talk/sql/sql-development/beginner-guide-to-in-memory-optimized-tables-in-sql-server/
• Temporal tables: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables
• Graph Table
  • https://novacontext.com/getting-started-with-sql-server-graph/
  • https://www.mssqltips.com/sqlservertip/4883/sql-server-2017-graph-database-example/
  • https://www.sqlshack.com/introduction-sql-server-2017-graph-database/
• External Table: https://www.c-sharpcorner.com/article/external-tables-in-sql-server-2016-or-2017-and-work-with-polybase/
• File Table: https://docs.microsoft.com/en-us/sql/relational-databases/blob/filetables-sql-server

Creating table

In SSMS

• Expand Database > Right Click on Tables > New Table
• Use Column properties below to set column settings
• Use Properties Window on right side to set table related settings (Menu > View > Properties Window)

By Query

CREATE TABLE [TestDB].[dbo].[Bar] (
	[BarId] INT NOT NULL IDENTITY (1000, 1),
	[Name] VARCHAR(50) NOT NULL,
	[Details] NVARCHAR(MAX), -- NULL is allowd
	[Registered At] DATETIME,
	[Nonce] INT NOT NULL DEFAULT 0,
	[FooId] INT NOT NULL,
	CONSTRAINT PK_Bar PRIMARY KEY ([BarId]),
	CONSTRAINT FK_Foo_Bar FOREIGN KEY ([FooId]) REFERENCES [TestDB].[dbo].[Foo] ([FooId])
)

More:

• Transact-SQL statements
• SQL Server CREATE TABLE

Altering table

In SSMS

• Right Click on Table > Design
• Modify as needed

If SSMS does not allow modification, then

• go to: Menu > Tools > Options > Designers
• Uncheck: Prevent saving changes …

By Query

CREATE TABLE Foo (column_a INT);
GO

ALTER TABLE Foo
ADD column_b VARCHAR(50) NULL;
GO

ALTER TABLE Foo ALTER COLUMN column_b VARCHAR (10) NOT NULL; -- previously was VARCHAR(20) NULL
GO

Details:

• Microsoft doc - statements -> alter -> table