Author : HASSAN MD TAREQ

Types of tables

sql-server-types-of-tables

Links:

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:

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 …

Disable prevent saving changes in SSMS

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: