How to use SQL Pivot

# How to Insert Data in SQL Server

Here's the shortest and easiest way to insert data into a SQL Server table. You only have to specify the values, but you have to pass all values _in order_. If you have 10 columns, you have to specify 10 values.

```sql
-- assuming the sessions table has only three columns:
-- id, start_date, and end_date, and category, in that order
insert into sessions values (11, '2020-02-02 14:05:15.400', '2020-02-03 14:25:15.400', 1);
```

It's optional, but specifying a column list before the `VALUES` keyword is **highly recommended**:

```sql
insert into sessions (id, start_date, end_date, category)
values (12, '2020-02-02 14:05:15.400', '2020-02-04 16:57:53.653', 2);
```

By specifying a column list, you don't have to remember the column order as defined in the table:

```sql
insert into sessions (id, category, start_date, end_date)
values (12, 2, '2020-02-02 14:05:15.400', '2020-02-04 16:57:53.653');
```

Having a column list has more advantages:

*   You don't have to specify a value for all columns, just the required ones.
*   In case there are many columns, it is easier to match a value to the intended column when you see it in the statement, rather than having to look at the table definition.
*   `INSERT` statements without a column lists are invalidated once a column is added or removed from the table. You need to modify your query to reflect the new or deleted column in order for them to work again.

If you have many columns, but only want to specify some:

```sql
insert into sessions(id, start_date) values (12, '2020-02-02 14:05:15.400');
```

### **Inserting Multiple Rows**

You can insert multiple rows in one `INSERT` statement by having multiple sets of values enclosed in parentheses:

```sql
insert into sessions (id, start_date, end_date, category)
values
  (13, '2020-02-02 15:05:15.400','2020-02-03 15:14:30.400', 3),
  (14, '2020-02-02 17:07:16.300','2020-02-02 19:10:15.400', 4),
  (15, '2020-02-03 15:05:45.127','2020-02-04 18:05:15.400', 2);
```

You can also use `INSERT` with a `SELECT` command to copy data from an existing table. Note the `VALUES` keyword is omitted:

```sql
insert into sessions_dm  (id, start_date, end_date, category)
select *
from sessions
where id > 10;
```

### **Inserting JSON Values**

If you want to insert into a JSON column, just wrap the valid JSON in a single quoted string:

```sql
insert into sessions(dates) values('{ "start_date": "2020-02-02 14:05:15.400", "end_date": "2020-02-02 14:57:45.127" }');
```

See also: our tutorial on [querying JSON columns](https://popsql.com/learn-sql/sql-server/how-to-query-a-json-object-in-sql-server/).

### **Handling Conflicts/Duplicates**

If inserting a row would violate a unique constraint, there is a way to handle it.

```sql
--if you want to insert the row if doesn't exists, otherwise ignore
insert into sessions(id, dates)
select
  13,
  '{ "start_date": "2020-02-02 14:05:15.400", "end_date": "2020-02-02 14:57:45.127" }'
where not exists (select id from sessions where id=13);

--if you want to update the row if exists, otherwise insert:
if exists (select * from sessions where id=14)
  update sessions
  set dates=null
  where id=14;
else
  insert sessions (id, dates)
  values (14, '{ "start_date": "2020-02-02 14:05:15.400", "end_date": "2020-02-02 14:57:45.127" }' );
```

How to Insert Data

# How to Update Data in SQL Server

Usually you only want to update rows that match a certain condition. You do this by specifying a `WHERE` clause:

```sql
--this will update only one row that matches id=1
update sessions
set start_date='2020-02-20 10:12:15.653',
    end_date='2020-02-22 15:40:30.123'
where id = 1;

--this will update multiple rows that match category=1
update sessions
set end_date = null
where category = 1;
```

To update all rows in a SQL Server table, just use the `UPDATE` statement without a `WHERE` clause:

```sql
update sessions
set end_date = '2020-02-04 16:57:53.653';
```

You can also update multiple columns at a time:

```sql
update sessions
set start_date = '2020-02-02 14:05:15.400',
    end_date = '2020-02-04 16:57:53.653';
```

How to Update Data

# How to Use SQL Server delete from Statement

To delete rows in a SQL Server table, use the `DELETE` statement:

```sql
delete from sessions where id = 10;
```

The `WHERE` clause is optional, but you'll usually want it, unless you really want to delete every row from the table.

```sql
delete from sessions;
```

How to Delete Data

# How to Create a Table in SQL Server

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

```sql
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](https://popsql.com/learn-sql/sql-server/how-to-add-a-not-null-constraint-in-sql-server/) and [default values](https://popsql.com/learn-sql/sql-server/how-to-add-a-default-value-to-a-column-in-sql-server/):

```sql
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:

```sql
--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;
```

How to Create a Table

# How to Drop a Table in SQL Server

Dropping a table in SQL Server is simple:

```sql
drop table users;
```

Be careful: `drop table` command drops the table along with the data in it, and there is no "undo“ option!

How to Drop a Table

# How to Rename a Table in SQL Server

Renaming a table in SQL Server is performed by using `SP_RENAME` stored procedure:

```sql
--the syntax
exec sp_rename  'old_table_name',  'new_table_name';

--rename users_marketing to users table
exec sp_rename  'users_marketing',  'users';
```

How to Rename a Table

# How to Truncate a Table in SQL Server

Be very careful with `TRUNCATE` command. It will empty the content of your SQL Server table. This is useful in development, but you'll rarely want to do this in production.

```sql
--syntax
truncate table table_name;

truncate table users;
```

If the table contains an `AUTO_INCREMENT` column, the counter for it will also get reset. Note that this behavior is different vs some other databases like [PostgreSQL](https://popsql.com/learn-sql/postgresql/how-to-alter-sequence-in-postgresql/) for example, where you need to reset the counter yourself.

How to Truncate a Table

# How to Duplicate a Table in SQL Server

Sometimes it's useful to duplicate a table. There are various methods, depending on your intent:

Copy entire table structure along with particular data set:

```sql
select *
into users_1
from users
where domain = 1;
```

Copy only particular columns into new table along with particular data set:

```sql
select
  id,
  username,
  password
into users_1
from users
where domain = 1;
```

Copy only particular columns from more tables into new table along with particular data set:

```sql
select
  u.username,
  s.start_date as session_start_date,
  s.end_date as session_end_date
into users_sessions_1_rpt
from sessions s
left join user_sessions us on s.id=us.session_id
left join users u on us.user_id=u.id
where u.domain = 1;
```

Copy only table structure, no data copying:

```sql
select *
into users_dm
from users
where 1 = 0;
```

How to Duplicate a Table

# How to Add a Column to SQL Server Table

Adding a column in SQL Server involves using the `ALTER TABLE` command.

Adding a brand\_id smallint column:

```sql
alter table products
add brand_id smallint;
```

Adding a brand\_id smallint column with a default value:

```sql
alter table products
add brand_id smallint default 1;
```

Adding a string (varchar) column with a not null constraint:

```sql
-- note: this is possible only if the table contains no data!!
alter table products
add description varchar(100) not null;
```

Adding more columns at the same time:

```sql
alter table products
add
  brand_id smallint default 1,
  description varchar(100) not null;
```

How to Add a Column

# How to Drop a Column in SQL Server

Dropping a column in SQL Server involves using the `ALTER TABLE .. DROP COLUMN` command.

Drop one column:

```sql
alter table products
drop column description;
```

Drop multiple colums at the same time:

```sql
alter table products
drop column price, description;
```

How to Drop a Column

# How to Rename a Column in SQL Server using sp\_rename

Renaming a column in SQL Server is performed by using `SP_RENAME` stored procedure:

```sql
-- syntax
sp_rename 'table_name.old_column_name', 'new_column_name', 'column';

-- rename product_category column in products table
exec sp_rename  'products.product_category', 'category', 'column';
```

How to Rename a Column

# How to Add a Default Value to a Column in SQL Server

To add a default value to a column in SQL Server, use the `ALTER TABLE .. ADD CONSTRAINT .. DEFAULT .. FOR` command:

```sql
-- example: products have a default category of 1
alter table products
add constraint df_category
default 1 for category;
```

How to Add a Default Value to a Column

# How to Remove a Default Value to a Column in SQL Server

To remove a default value to a column in SQL Server, use the `ALTER TABLE .. DROP CONSTRAINT` command:

```sql
alter table products
drop constraint df_category;
```

How to Remove a Default Value to a Column

# How to Add a NOT NULL Constraint in SQL Server

Not null constraints are a great way to add another layer of validation to your data.

Of course, you could perform this validation in your application layer as well, but be aware that inconsistencies happen: someone will forget to add the validation, someone will remove it by accident, someone will bypass validations in a console and insert nulls, etc. The only way to really be sure is to enforce it in your column definition. If you're validating nulls on the database layer as well, you're protected.

To enforce `NOT NULL` for a column in SQL Server, use the `ALTER TABLE .. ALTER COLUMN` command and restate the column definition, adding the `NOT NULL` attribute.

```sql
alter table products
alter column brand_id smallint not null;
```

How to Add a NOT NULL Constraint

# How to Remove a NOT NULL Constraint in SQL Server

To remove a `NOT NULL` constraint for a column in SQL Server, you use the `ALTER TABLE .... ALTER COLUMN` command and restate the column definition.

```sql
alter table products
alter column brand_id smallint; -- undeclared attributes as NOT NULL will go back to default settings which is null in this case

alter table products
alter column brand_id smallint null; -- define the NULL attribute over a column
```

How to Remove a NOT NULL Constraint

# How to Drop an Index in SQL Server

To drop a non-primary key index, use the `DROP INDEX` command:

```sql
-- syntax
drop index table_name.index_name;

drop index products.products_category;
```

To drop a primary key index, use the `ALTER TABLE .. DROP CONSTRAINT` command :

```sql
alter table products
drop constraint pk_products;
```

How to Drop an Index

# How to Create a View in SQL Server

Views allow to encapsulate or “hide” complexities, or allow limited read access to part of the data.

To create a view, use the `CREATE VIEW` command:

```sql
-- syntax
create view view_name
as select_statement;
```

Some examples:

```sql
-- a view to show only products within category 1
create view category_1_products_v as
select *
from products
where category = 1;

-- a view to limit read access to only certain columns
create view category_products_basic_v as
select name, category, price
from products;

-- a view that displays top 10 products that provide highest sold value
create view top_10_products_v as
select
  top 10 p.name,
  p.category,
  p.price,
  ps.quantity_sold,
  p.price * ps.quantity_sold as sold_value
from products p
left join products_sold ps on p.id=ps.product_id
order by sold_value desc;
```

How to Create a View

# How to Drop a View in SQL Server

To drop a view, use the `DROP VIEW` command:

```sql
drop view category_1_products_v;
```

How to Drop a View

# How to Alter Sequence in SQL Server

Auto-incrementing columns in tables start at 1 by default, but sometimes you may want them to start at a different number and to have multiple increments. These numbers are known as “sequences”, and this is how to create them in SQL Server:

```sql
-- syntax
create sequence schema_name.sequence_name as data_type
start with value
increment by value;
```

Some examples:

```sql
create sequence dbo.even_numbers as bigint
start with 2
increment by 2
minvalue 2 -- setting the minimum sequence value
maxvalue 10000; -- setting the maximum sequence value

create sequence dbo.negative_numbers as bigint
start with 0
increment by -1; -- sequence that increments backward
```

To alter the sequence so that IDs start with a different number, you can't just do an `UPDATE`. You have to use the `ALTER SEQUENCE` command:

```sql
alter sequence dbo.even_numbers with 10;
```

How to Alter Sequence

# How to Query Date and Time in SQL Server using Datetime and Timestamp

Get the date and time right now (where SQL Server is running):

```sql
select current_timestamp; -- date and time, standard ANSI SQL so compatible across DBs
select getdate();  -- date and time, specific to SQL Server
select getutcdate(); -- returns UTC timestamp
select sysdatetime(); -- returns 7 digits of precision
```

Find rows between two dates or timestamps:

```sql
select  *
from events
where event_date between '2020-01-01' and '2020-01-31';

-- can include time by specifying in YYYY-MM-DD hh:mm:ss format:
select  *
from events
where event_date between '2020-01-01 12:00:00' and '2020-01-01 23:30:00';
```

Find rows created within the last week:

```sql
select *
from events
where event_date > (select dateadd(week, -1, getdate()));
```

Find events scheduled between one week ago and 3 days from now:

```sql
select *
from events
where event_date between (select dateadd(week, -1, getdate())) and (select dateadd(day, +3, getdate()));
```

Extracting part of a timestamp and returning an integer:

```sql
select day(getdate()); -- or month() or year()
select datepart(day, getdate()); -- or hour, week, month, quarter, year
```

Extracting part of a timestamp and returning a string (e.g. "February" or "Monday"):

```sql
select datename(month, getdate()); -- or day
```

Get the day of the week from a timestamp:

```sql
select datepart(weekday, getdate()); -- returns 1-7 (integer), where 1 is Sunday and 7 is Saturday
```

How to convert a timestamp to a unix timestamp:

```sql
-- all SQL Server versions: output example - 1580718382
select datediff(second, '1970-01-01' , getutcdate())

-- SQL Server 2016 and later: output example - 2147483648
select datediff_big(second, '1970-01-01' , '2038-01-19 03:14:08')
```

To calculate the difference between two timestamps, convert them to unix timestamps then subtract:

```sql
select datediff(second, '1970-01-01' , timestamp2) - datediff(second, '1970-01-01' , timestamp1) -- output in seconds
```

> _Note: this simple approach lacks millisecond precision._

How to Query Date and Time

# How to Group by Day, Date, Hour, Month or Year in SQL Server

When you want to group by minute, hour, day, week, etc., you may be tempted to just group by your timestamp column.

If you do that, though, you'll get one group per second -- likely not what you want. Instead, you should “truncate” your timestamp to the granularity you want, like minute, hour, day, week, etc. The functions you need here are `datepart()` and `datename()`:

```sql
-- returns number of sessions grouped by particular timestamp fragment
select
  max(start_date) as session_start_date,
  count(id) as number_of_sessions
from [dbo].[sessions]
-- or datename()
group by
  datepart(year, start_date),
  datepart(month, start_date), -- omit if grouping by year
  datepart(week, start_date), -- omit if grouping by month
  datepart(day, start_date), -- omit if grouping by week
  datepart(hour, start_date), -- omit if grouping by day
  datepart(minute, start_date); -- omit if grouping by hour
```

The `datepart()` function has the same syntax as the `datename()` function. Therefore, both functions can be used in the same way.

How to Group by Time

# How to Round Timestamps in SQL Server

Rounding or truncating timestamps are especially useful when you're [grouping by time](https://popsql.com/learn-sql/sql-server/how-to-group-by-time-in-sql-server/). If you are rounding by year, you can use the corresponding function:

```sql
select year(getdate()) as 'Year';
```

Be careful if you are grouping by months or smaller timestamp fragments (weeks, days, hours, minutes). Using the `month()` function will, for example, make January 2020 and January 2019 both just translate to `1`. If that is what you want, then you can use `month()`.

However, if you want to distinguish between months of different years, you need to use `format()` function:

```sql
select format(getdate(),'MM-yyyy'); -- round to month
select format(getdate(),'dd-MM-yyyy'); -- round to day
select format(getdate(),'dd-MM-yyyy hh'); -- round to hour
select format(getdate(),'dd-MM-yyyy hh:mm'); -- round to minute
select format(getdate(),'dd-MM-yyyy hh:mm:ss'); -- round to second
```

How to Round Timestamps

# How to Convert UTC to Local Time Zone in SQL Server

SQL Server does not store time zone data when storing timestamps. It uses the host server time as the basis for generating the output of `getdate()`.

To convert a UTC timestamp to a local time zone, you can use the following:

```sql
-- all SQL Server versions
declare @utc_date datetime = getdate()
select @utc_date as utc_time_zone,
  dateadd(hh, datediff(hh, getutcdate(), getdate()), @utc_date) as local_time_zone

--SQL Server 2016 and later
declare @utc_date datetime = getdate()
select @utc_date as utc_time_zone,
  getdate() at time zone 'US Eastern Standard Time' as time_zone_est;
```

To see the list of time zones SQL Server supports:

```sql
select * from sys.time_zone_info;
```

```sql
name                            | current_utc_offset | is_currently_dst
--------------------------------+--------------------+------------------
Dateline Standard Time          | -12:00             | false
UTC-11                          | -11:00             | false
Aleutian Standard Time          | -10:00             | false
Hawaiian Standard Time          | -10:00             | false
Marquesas Standard Time         | -09:30             | false
Alaskan Standard Time           | -09:00             | false
UTC-09                          | -09:00             | false
Pacific Standard Time (Mexico)  | -08:00             | false
UTC-08                          | -08:00             | false
Pacific Standard Time           | -08:00             | false
US Mountain Standard Time       | -07:00             | false
Mountain Standard Time (Mexico) | -07:00             | false
Mountain Standard Time          | -07:00             | false
Central America Standard Time   | -06:00             | false
Central Standard Time           | -06:00             | false
Easter Island Standard Time     | -05:00             | true
Central Standard Time (Mexico)  | -06:00             | false
Canada Central Standard Time    | -06:00             | false
SA Pacific Standard Time        | -05:00             | false
Eastern Standard Time (Mexico)  | -05:00             | false
Eastern Standard Time           | -05:00             | false
Haiti Standard Time             | -05:00             | false
Cuba Standard Time              | -05:00             | false
US Eastern Standard Time        | -05:00             | false
Turks And Caicos Standard Time  | -05:00             | false
Paraguay Standard Time          | -03:00             | true
Atlantic Standard Time          | -04:00             | false
Venezuela Standard Time         | -04:00             | false
Central Brazilian Standard Time | -03:00             | true
SA Western Standard Time        | -04:00             | false
Pacific SA Standard Time        | -03:00             | true
Newfoundland Standard Time      | -03:30             | false
Tocantins Standard Time         | -03:00             | false
E. South America Standard Time  | -02:00             | true
SA Eastern Standard Time        | -03:00             | false
Argentina Standard Time         | -03:00             | false
Greenland Standard Time         | -03:00             | false
Montevideo Standard Time        | -03:00             | false
Magallanes Standard Time        | -03:00             | false
Saint Pierre Standard Time      | -03:00             | false
Bahia Standard Time             | -03:00             | false
UTC-02                          | -02:00             | false
Mid-Atlantic Standard Time      | -02:00             | false
Azores Standard Time            | -01:00             | false
Cape Verde Standard Time        | -01:00             | false
UTC                             | +00:00             | false
GMT Standard Time               | +00:00             | false
Greenwich Standard Time         | +00:00             | false
W. Europe Standard Time         | +01:00             | false
Central Europe Standard Time    | +01:00             | false
Romance Standard Time           | +01:00             | false
Morocco Standard Time           | +01:00             | false
Sao Tome Standard Time          | +01:00             | false
Central European Standard Time  | +01:00             | false
W. Central Africa Standard Time | +01:00             | false
Jordan Standard Time            | +02:00             | false
GTB Standard Time               | +02:00             | false
Middle East Standard Time       | +02:00             | false
Egypt Standard Time             | +02:00             | false
E. Europe Standard Time         | +02:00             | false
Syria Standard Time             | +02:00             | false
West Bank Standard Time         | +02:00             | false
South Africa Standard Time      | +02:00             | false
FLE Standard Time               | +02:00             | false
Israel Standard Time            | +02:00             | false
Kaliningrad Standard Time       | +02:00             | false
Sudan Standard Time             | +02:00             | false
Libya Standard Time             | +02:00             | false
Namibia Standard Time           | +02:00             | false
Arabic Standard Time            | +03:00             | false
Turkey Standard Time            | +03:00             | false
Arab Standard Time              | +03:00             | false
Belarus Standard Time           | +03:00             | false
Russian Standard Time           | +03:00             | false
E. Africa Standard Time         | +03:00             | false
Iran Standard Time              | +03:30             | false
Arabian Standard Time           | +04:00             | false
Astrakhan Standard Time         | +04:00             | false
Azerbaijan Standard Time        | +04:00             | false
Russia Time Zone 3              | +04:00             | false
Mauritius Standard Time         | +04:00             | false
Saratov Standard Time           | +04:00             | false
Georgian Standard Time          | +04:00             | false
Volgograd Standard Time         | +04:00             | false
Caucasus Standard Time          | +04:00             | false
Afghanistan Standard Time       | +04:30             | false
West Asia Standard Time         | +05:00             | false
Ekaterinburg Standard Time      | +05:00             | false
Pakistan Standard Time          | +05:00             | false
India Standard Time             | +05:30             | false
Sri Lanka Standard Time         | +05:30             | false
Nepal Standard Time             | +05:45             | false
Central Asia Standard Time      | +06:00             | false
Bangladesh Standard Time        | +06:00             | false
Omsk Standard Time              | +06:00             | false
Myanmar Standard Time           | +06:30             | false
SE Asia Standard Time           | +07:00             | false
Altai Standard Time             | +07:00             | false
W. Mongolia Standard Time       | +07:00             | false
North Asia Standard Time        | +07:00             | false
N. Central Asia Standard Time   | +07:00             | false
Tomsk Standard Time             | +07:00             | false
China Standard Time             | +08:00             | false
North Asia East Standard Time   | +08:00             | false
Singapore Standard Time         | +08:00             | false
W. Australia Standard Time      | +08:00             | false
Taipei Standard Time            | +08:00             | false
Ulaanbaatar Standard Time       | +08:00             | false
Aus Central W. Standard Time    | +08:45             | false
Transbaikal Standard Time       | +09:00             | false
Tokyo Standard Time             | +09:00             | false
North Korea Standard Time       | +09:00             | false
Korea Standard Time             | +09:00             | false
Yakutsk Standard Time           | +09:00             | false
Cen. Australia Standard Time    | +10:30             | true
AUS Central Standard Time       | +09:30             | false
E. Australia Standard Time      | +10:00             | false
AUS Eastern Standard Time       | +11:00             | true
West Pacific Standard Time      | +10:00             | false
Tasmania Standard Time          | +11:00             | true
Vladivostok Standard Time       | +10:00             | false
Lord Howe Standard Time         | +11:00             | true
Bougainville Standard Time      | +11:00             | false
Russia Time Zone 10             | +11:00             | false
Magadan Standard Time           | +11:00             | false
Norfolk Standard Time           | +11:00             | false
Sakhalin Standard Time          | +11:00             | false
Central Pacific Standard Time   | +11:00             | false
Russia Time Zone 11             | +12:00             | false
New Zealand Standard Time       | +13:00             | true
UTC+12                          | +12:00             | false
Fiji Standard Time              | +12:00             | false
Kamchatka Standard Time         | +12:00             | false
Chatham Islands Standard Time   | +13:45             | true
UTC+13                          | +13:00             | false
Tonga Standard Time             | +13:00             | false
Samoa Standard Time             | +14:00             | true
Line Islands Standard Time      | +14:00             | false
```

How to Convert UTC to Local Time Zone

# How to Query a JSON Object in SQL Server

SQL Server has supported querying JSON columns since the version launched in 2016. This gives the advantage of storing and querying unstructured data. Here's how you can query a JSON column in SQL Server.

Get only `home_address.state` from the employees table:

```sql
select
  json_value(home_address,'$.state') as state
from employees
```

Get only employees from a specific state:

```sql
select  *
from employees
where  json_value(home_address,'$.state')='AR'

declare @json nvarchar(max) = N'{"employee":[{"id":1,"name":{"first":"Jane","middle":"Laura","last":"Jones"},"address":{"home":"11th Ave W","work":"10th Ave W","city":"Tampa","zipcode":"33601","state":"Florida"}}]}';
```

Get first key and its value of a JSON array:

```sql
select
  top 1 [key]
from openjson(@json);

--going deeper to get only the employee name
select
  [key],
  value
from openjson(@json, '$.employee[0].name');
```

Get JSON keys as columns populated with key values:

```sql
select *
from openjson(@json, '$.employee')
with(first_name nvarchar(25) '$.name.first',
     middle_name nvarchar(25) '$.name.middle',
     last_name nvarchar(25) '$.name.last');
```

How to Query JSON Object

# How to Calculate Cumulative Sum/Running Total in SQL Server

Let's say we want to see a hockey stick graph of our cumulative sessions by day in SQL Server. First, we'll need a table with a day column and a count column:

```sql
select
  convert(varchar(10), start_date, 105) as day,
  count(1)
from sessions
group by convert(varchar(10), start_date, 105);
```

```sql
     day    | count
------------+-------
 02-02-2020 | 3
 03-02-2020 | 3
 04-02-2020 | 4
```

Next, we'll write a [SQL Server common table expression (CTE)](https://popsql.com/learn-sql/sql-server/how-to-write-a-common-table-expression-in-sql-server/) and use a window function to keep track of the cumulative sum/running total:

```sql
with data as (
  select
    convert(varchar(10), start_date, 105) as day,
    count(1)  as number_of_sessions
  from sessions
  group by convert(varchar(10), start_date, 105)
)

select
  day,
  sum(number_of_sessions) over (order by day asc rows between unbounded preceding and current row)
from data;
```

```sql
     day    | sum
------------+-------
 02-02-2020 | 3
 03-02-2020 | 6
 04-02-2020 | 10
```

How to Calculate Cumulative Sum/Running Total

# How to Have Multiple Counts in SQL Server

To do multiple counts in one query in SQL Server, you can combine `sum()` with the [CASE statement](https://popsql.com/learn-sql/sql-server/how-to-write-a-case-statement-in-sql-server/):

```sql
select
  count(1), -- count all products
  sum(case when name = 'Table' then 1 else 0 end), -- count all tables
  sum(case when category = 5 then 1 else 0 end) -- count all category 5 products
from products;
```

How to Have Multiple Counts

# How to Write a Case Statement in SQL Server

Case statements are useful when you're reaching for an if statement in your `select`clause.

```sql
select
  id,
  name,
  category,
  price,
  case
    when category = 5 then 'Premium'
    when category = 4 then 'Gold'
    when category = 3 then 'Standard'
    when category <= 2 then 'Basic'
    else 'unknown'
  end as quality_level
from products;
```

How to Write a Case Statement

# How to Use Coalesce in SQL Server

Imagine you're looking at a SQL Server integer column where some rows are null:

```sql
select day, tickets
from stats;
```

```sql
    day     | tickets
------------+---------
 2020-02-01 | 1
 2020-02-02 | null
 2020-02-03 | 3
```

Instead of having that null, you might want that row to be 0. To do that, use the `coalesce()` function, which returns the first non-null argument it's passed:

```sql
select day, coalesce(tickets, 0)
from stats;
```

```sql
    day     | tickets
------------+---------
 2020-02-01 | 1
 2020-02-02 | 0
 2020-02-03 | 3
```

How to Use Coalesce

# How to Avoid Gaps in Data in SQL Server

Let's say you're grouping by time in SQL Server and you don't want gaps in your report data.

First, generate a series of date/time values that have no gaps using a [common table expression](https://popsql.com/learn-sql/sql-server/how-to-write-a-common-table-expression-in-sql-server/):

```sql
declare @start_datetime datetime = '2020-02-01';
declare @end_datetime datetime = '2020-02-29';

with cte_date (datetime) as (
  select  @start_datetime
  union all
  select  dateadd(hour, 1, datetime) --or minute, day, week, month instead of hour
  from  cte_date
  where  datetime < @end_datetime
)
select datetime
from cte_date
option (maxrecursion 0);
```

> Note: highlight the _entire_ statement before running in PopSQL as semi-colons usually denote multiple statements.

```sql
datetime
------------------------
2020-02-01T00:00:00.000Z
2020-02-01T01:00:00.000Z
2020-02-01T02:00:00.000Z
2020-02-01T03:00:00.000Z
...
```

Now you can left join your date/time series data against this gapless series. Here's how you could create a count of sessions for each day:

```sql
declare @start_datetime datetime = '2020-02-01';
declare @end_datetime datetime = '2020-02-29';

with cte_date (datetime) as (
  select  @start_datetime
  union all
  select  dateadd(day, 1, datetime)
  from  cte_date
  where  date < @end_datetime
)

select
  cte_date.datetime,
  COUNT(s.id) as session_ct
from cte_date
left outer join sessions s on convert(varchar(10), s.created_at, 105) = convert(varchar(10), cte_date.datetime, 105)
group by datetime
option (maxrecursion 0);
```

> Note: the `left outer join` uses the `convert()` function to make sure both times are formatted identically. `105` denotes '19-02-2020' format, but here's a [full list of format codes](https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/).

How to Avoid Gaps in Data

# How to Import a CSV in SQL Server

Importing a CSV file into SQL Server can be done within PopSQL by using either `BULK INSERT` or `OPENROWSET(BULK...)` command.

The `BULK INSERT` command is used if you want to import the file as it is, without changing the structure of the file or having the need to filter data from a file. You need to create a table within the database that has the same structure as the CSV file you want to import prior to running the `BULK INSERT` command.

```sql
BULK INSERT sessions
from 'C:\TempDocs\Sessions.csv'
with (firstrow = 2,
      fieldterminator = ',',
      rowterminator='\n',
      batchsize=10000,
      maxerrors=10);
```

If you want to filter out data from a source file and import only particular table columns, use the `OPENROWSET(BULK...)` command. You don't need to create a database table previously: the command will create it automatically once you run the query. However, you need to define a format file that will hold a table definition of a table to be created automatically.

```sql
select id, start_date
into sessions
from OPENROWSET(BULK 'C:\TempDocs\Sessions.csv',
                formatfile='C:\TempDocs\Sessions.fmt',
                firstrow=2) as sessions_insert;
```

How to Import a CSV

# SQL Server: How to create an index on a table

## Goal

Having the right indexes on tables are critical to making your queries performant, especially when your data grows. Not having the needed indexes will typically result in high CPU usage in your database server, slow response times, and ultimately unhappy users. Indexing also helps improve performance by avoiding duplicate records, improving JOIN operations and table partitioning, and enabling full text search capability in SQL Server. In this tutorial, you will learn how to create an index in SQL Server, and various types of indexing that you can do.

## Instructions

### Creating index

To create indexes, use the `CREATE INDEX` command:

```sql
-- syntax
create index index_name
on table_name(column1, column2, .., columnN);
-- create index on one column
create index products_category
on products(category);
```

### Creating index on multiple columns

You can create an index on multiple columns. When used for columns that are frequently used together as filters, a multiple-column index performs better than multiple single-column indexes:

```sql
-- create index on multiple columns
create index products_category_brand
on products(category, brand_id);
```

### Creating unique index

A unique index prevents duplicate data for a defined key:

```sql
-- two rows can not have same category and brand_id key values
create unique index products_category_brand
on products(category, brand_id);
```

### Creating filter indexes

Create filter indexes to only index rows where a certain condition is met:

```sql
create index products_brand
on products(brand_id)
where brand_id = 1;
```

### Creating unique filter index

You can also have a _unique_ filter index. For example, imagine if you want to prevent rows having duplicate category and brand\_id key values for particular brand:

```sql
-- this will prevent duplicate categories within a brand that has id=1
create unique index products_brand_1_category
on products (category, brand_id)
where brand_id = 1;
```

## Troubleshooting

Troubleshooting indexing issues in SQL Server involves identifying problems with existing indexes and optimizing them for better performance. Here are some common indexing issues and how to address them with short code examples:

### Missing indexes

While there is always a lot of data at hand, data from a few fixed columns is used the most by the entire business. Due to missing indexes, queries become slow. If your queries are too slow, it is worth checking if there are no suitable indexes.

Creating new indexes for frequently used columns helps:

```sql
-- Identify missing indexes using SQL Server's built-in tools
SELECT
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    OBJECT_NAME(mid.[object_id]) AS TableName,
    'CREATE INDEX [IX_' + OBJECT_NAME(mid.[object_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') 
    + CASE WHEN mid.[equality_columns] IS NOT NULL AND mid.[inequality_columns] IS NOT NULL THEN '_' ELSE '' END
    + REPLACE(REPLACE(REPLACE(ISNULL(mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '') + ']'
    + ' ON ' + mid.statement
    + ' (' + ISNULL (mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
    + ISNULL (mid.inequality_columns, '')
    + ')' 
    + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
    migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID() -- Replace with your database ID
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;
```

### Overlapping indexes

Unlike missing indexes, when there are many indexes for the same columns, we end up with overlapping indexes. These indexes need to be cleaned up; otherwise, they cause redundant storage and maintenance overhead. Here’s how you can identify and remove overlapping indexes:

```sql
-- Identify overlapping indexes
SELECT
    OBJECT_NAME(i.[object_id]) AS TableName,
    i.name AS IndexName,
    COL_NAME(ic.[object_id], ic.column_id) AS ColumnName
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.[object_id] = ic.[object_id] AND i.index_id = ic.index_id
WHERE i.[object_id] = OBJECT_ID('YourTableName') -- Replace with your table name
ORDER BY TableName, IndexName, ColumnName;
-- Remove redundant indexes (use DROP INDEX statement)
-- Example: DROP INDEX [IX_RedundantIndex] ON [YourTableName];
```

### Fragmented indexes

Fragmented indexes also cause performance issues. Here’s how you can rebuild or reorganize fragmented indexes:

```sql
-- Rebuild index to reduce fragmentation
ALTER INDEX [YourIndexName] ON [YourTableName] REBUILD;
-- Reorganize index to reduce fragmentation
ALTER INDEX [YourIndexName] ON [YourTableName] REORGANIZE;
```

## Conclusion

Indexing is a crucial performance optimization technique in SQL Server (and other relational database management systems) that helps improve the speed and efficiency of querying and retrieving data from database tables. Now you have everything you need to work with indexes in SQL Server. You learned how to create an index, create index on multiple columns, and create unique and filter indexes. You also learned how to troubleshoot indexing for your project to improve query performance and avoid redundancy. 

## Next steps

Now that you learned how to create an index in SQL Server, it is time to undo it (only if you need to)! Learn [how to drop an index](https://popsql.com/learn-sql/sql-server/how-to-drop-an-index-in-sql-server) in the next tutorial.

## FAQs

### 1\. What is the primary purpose of indexing in SQL Server?

Indexing in SQL Server primarily serves to improve query performance by providing a faster way to locate and retrieve data. It does this by creating a data structure that allows SQL Server to quickly find the rows that match a query's search criteria.

### 2\. When should I consider creating a clustered index, and what is its impact on data storage?

A clustered index determines the physical order of data in a table, so it should be chosen carefully. Typically, it's best suited for columns with unique values, like a primary key. However, it's essential to remember that a table can have only one clustered index, and its choice affects data storage, as the data is physically organized based on the clustered index.

### 3\. How do I identify missing indexes in SQL Server, and what should I do when I find them?

You can identify missing indexes by using tools like SQL Server's Database Engine Tuning Advisor or by analyzing execution plans. When you find missing indexes, carefully evaluate their potential impact on query performance before implementing them. Consider factors like query workload and the balance between improving reads and the cost of maintaining the indexes.

### 4\. What is index fragmentation, and why is it a concern?

Index fragmentation occurs when data pages within an index are disordered or scattered. This can slow down query performance as SQL Server needs to read more pages to satisfy a query. To address fragmentation, you can rebuild or reorganize indexes regularly.

### 5\. Can too many indexes on a table be detrimental to performance?

Yes, having too many indexes on a table can lead to performance issues. While indexes improve read performance, they come with storage and maintenance overhead. Excessive indexes can lead to increased storage requirements, slower data modification operations, and potentially suboptimal query plans. It's essential to strike a balance between indexing for reads and considering the impact on data modification and overall database performance.

How to Create an Index

# How to Get First Row Per Group in SQL Server

Let's say you have tables that contains data about users and sessions, and you want to see the first session for each user for particular day. The function you need here is `row_number()`. Here's an example of how to use it:

```sql
select
  us.user_id,
  us.session_id,
  s.start_date,
  s.end_date,
  row_number() over (partition by user_id order by start_date desc) as row_number
from user_sessions us
left outer join sessions s on s.id = us.session_id
where convert(varchar(10), start_date, 105) = '02-02-2020';
```

This gives you all the session IDs for the day, plus their row number. Since you only want the first session for the day, you only want rows that have row\_number: 1. To do that, you can use a [common table expression](https://popsql.com/learn-sql/sql-server/how-to-write-a-common-table-expression-in-sql-server/):

```sql
with cte_sessions as (
  select
    us.user_id,
    us.session_id,
    s.start_date,
    s.end_date,
    row_number() over (partition by user_id order by start_date desc) as row_number
  from user_sessions us
  left outer join sessions s on s.id = us.session_id
  where convert(varchar(10), start_date, 105) = '02-02-2020'
)
select *
from cte_sessions
where row_number = 1;
```

How to Get First Row Per Group

# How to Compare Two Values When One is NULL in SQL Server

Say you're comparing two SQL Server columns and you want to know how many rows are different. No problem, you think:

```sql
select count(1)
from sessions
where start_date != end_date;
```

Not so fast. If some of the start or end dates are null, they won't be counted! Surely that's not what you wanted. That's why you need to use the `INTERSECT` operator:

```sql
select count(1)
from sessions
where not exists(select start_date intersect select end_date);
```

Now, your count will be “null aware” and you'll get the result you want.

How to Compare Two Values When One is NULL

# How to Write a Common Table Expression in SQL Server

Common table expressions (CTEs) are a great way to break up complex queries. SQL Server has supported this from the early beginning, starting with the 2005 version. Here's a simple query to illustrate how to write a CTE:

```sql
with free_users as (
  select *
  from users
  where plan = 'free'
)
select user_sessions.*
from user_sessions
inner join free_users on free_users.id = user_sessions.user_id
order by free_users.id;
```

You can find more complex examples of using CTE's in [How to Avoid Gaps in Data in SQL Server](https://popsql.com/learn-sql/sql-server/how-to-avoid-gaps-in-data-in-sql-server/) and in [Calculating Cumulative Sums in SQL Server](https://popsql.com/learn-sql/sql-server/how-to-calculate-cumulative-sum-running-total-in-sql-server/).

How to Write a Common Table Expression

# How to Calculate Percentiles in SQL Server

Let's say you want to look at the percentiles for products. You can use SQL Server's `percentile_cont()`function to do that:

```sql
select
  percentile_cont(0.25) within group(order by price) over () as percentile_cont_25,
  percentile_cont(0.50) within group(order by price) over () as percentile_cont_50,
  percentile_cont(0.75) within group(order by price) over () as percentile_cont_75,
  percentile_cont(0.95) within group(order by price) over () as percentile_cont_95
from products;
```

If you want to view those percentiles by category:

```sql
select distinct category,
  percentile_cont(0.25) within group(order by price) over (partition by category) as percentile_cont_25,
  percentile_cont(0.50) within group(order by price) over (partition by category) as percentile_cont_50,
  percentile_cont(0.75) within group(order by price) over (partition by category) as percentile_cont_75,
  percentile_cont(0.95) within group(order by price) over (partition by category) as percentile_cont_95
from products
order by category;
```

How to Calculate Percentiles

# How to Do Type Casting in SQL Server

By default, SQL Server is not strict with type casting. For example, adding a numeric value in string quotes to another numeric value with not give the usual errors other databases and programming languages will give:

```sql
select 10 + '10';
```

However, should the need arise, you can use either the `cast()` or the `convert()` function to force the type of a value.

```sql
-- cast float to integer
select cast(1.0123456789 as int);
select convert(int,1.0123456789);

-- cast string to date
select cast('11-02-2020' as date);
select convert(date,'11-02-2020');

-- cast string to decimal
select cast('12.345' as decimal(5,2));
select convert(decimal(15,2),'12.345');

-- cast string to time
select cast('12:45' as time);
select convert(time,'12:45');
```

You can cast to many SQL Server types such as `binary`, `char`, `varchar`, `date`, `datetime`, `time`, `decimal`, `bigint`, and `float`.

SQL Server

Ready for a modern SQL editor?