How to Create a Table in SQL Server in SQL Server

Here's an example of creating a users table in SQL Server:

create table users  (
  id bigint  primary  key  identity(1,1),  -- auto incrementing IDs
  name  varchar(100),  -- variable string column
  preferences nvarchar(max),  -- column used to store JSON type of data
  created_at timestamp
);

Within the parentheses are what's called column definitions, separated by commas. The minimum required fields for a column definition are column name and data type (shown above for columns name, preferences, and created_at). The id column has extra fields to identify it as the primary key column and use an auto-incrementing feature to assign it values.

This is also a chance to specify not null constraints and default values:

create table users(
  id bigint primary key identity(1,1),
  name varchar(100) not null,
  active bit  default 1
);

You can also create temporary tables that will stick around for the duration of your session. This is helpful to break down your analysis into smaller pieces. There are two ways to create them:

--the name of the temporary table starts with # sign
create table #active_users(
  id bigint primary key identity(1,1),
  name varchar(100) not null,
  active bit default 1
);

select
  id,  
  name
into #active_users -- temporary table
from users
where active = 1;

Previous

Next

Ready for a modern SQL editor?