DEV Community: rajanand ilangovan

What happens to the records inserted into a table variable, if the transaction rollback?

rajanand ilangovan — Thu, 24 Mar 2022 20:25:41 +0000

DECLARE @People TABLE (first_name VARCHAR(50), age int);
BEGIN TRAN
INSERT INTO @People VALUES ('John', 25);
INSERT INTO @People VALUES ('Daniel', 30);

ROLLBACK

SELECT * FROM @People;

Answer:

The records inserted into a table variable will not be affected by the transaction rollback.

Reference: Brent Ozar

If you like this SQL interview question, you may also like the below interview question and answers.

What happens to the variable value set during the transaction, if its rollback?

rajanand ilangovan — Fri, 18 Mar 2022 19:12:59 +0000

DECLARE @my_age INT = 20;

BEGIN TRAN
SET @my_age += 30;
ROLLBACK;

SELECT @my_age;

Answer:

Even though the transaction is rolled back, the value set to the variable remains same.

Changes to variables aren't affected by the rollback of a transaction.

Reference:

Microsoft docs

Brent Ozar

If you like this SQL interview question, you may also like the below interview question and answers.

10 tips to increase your SQL Server Management Studio productivity

rajanand ilangovan — Sat, 19 Feb 2022 17:37:28 +0000

In this post, I will show you few tips that you can use in your day-to-day SQL development workflow. If you are working with SQL Server, most likely that you will using SQL Server Management Studio on daily basic to manage and develop new SQL queries. These tips you can easily incorporate and your productivity will increase instantly.

1. Execute SQL query in loop

If you want any query to be executed more than once, either you can select the query and execute (F5) it multiple times and write a while loop to execute desired number of times. You might not have come across that the batch separator "GO" can be used to execute the query n number of times.

SELECT 'execute 3 times' 
GO 3

This trick will be extremely helpful if you want to insert some dummy data to a table for testing purpose.

--Execute SQL command in loop with "GO"
use demo;
create table marks (
    student_id int identity(1,1), 
    marks tinyint, 
    create_datetime datetime2
)
GO

insert into marks 
select cast(rand()*100 as int), getdate()
GO 1000 -- 1000 records will be inserted

select * from marks
drop table marks

2. Copy and paste multiple values at once.

Ctrl+C - Copy multiple values. Hold down Ctrl+Shift and then press V few times to cycle through copied values from clipboard.

3. Write down column names instead of 'select *'

If you have working with SQL for some time, you may know that selecting all the columns in select clause when all the columns are not necessary. But it is easy to just type * (asterisk) to select all columns than type out all the column names. This will be even time consuming if the number of columns in a table is quite large.

An easy way to do that is just drag and drop from object explorer. You have to expand the table name and drag the columns folder folder to the Query editor window. By default, the columns will be surrounded by square brackets when dragged from object explorer. If you prefer without square brackets, you can change it in Tools => Options => SQL Server Object Explorer => Surround object names with brackets when dragged to false.

4. CTRL+3 is your friend

You can configure frequent used queries in shortcuts and use that to execute. Tools => Options => Environment => Keyboard => Query Shortcuts

I like to configure these 3 query shortcuts whenever I install a new SQL Server Management Studio in new computer.

Ctrl+3 => SELECT * FROM

Ctrl+4 => SELECT Top(5) * FROM

Ctrl+5 => SELECT COUNT(1) FROM

Note that there is a space after FROM clause above.

You can configure other shortcuts also. Once you configure the above three shortcuts, you can just select any valid tables and then press the shortcuts to see the results.

5. Change column names in single-line to multi-line and vice versa

If you have all the column name listed as a comma separated in a single line and you want each column names in a separate line, you can do that with a simple 'Find and Replace'Ctrl+H - To open find and replace window. Type comma , in find field and ,\r in replace field. Then Alt+E to enable regular expression and then Alt+A to replace all.

Similarly, you can change from multi-line to single-line by just swapping the values in Find and replace. Find - ,\rReplace - ,

6. Modify multiple lines at once.

Hold down Shift+Alt keys and then click in query editor for start region and then the click the end region to select. Now if you check based on how many lines (region) that you have selected that many cursor will be blinking and you can just start typing it. This will be especially useful if you are just copy and pasting column name from result pane and you want to add comma , at the start (or end) of the column name.

7. Query editor in full screen.

If you using a computer with small screen resolution size, you SQL Server management studio's various windows might cluster query editor space and you may not have enough space. If you switch to full screen mode, you can have a distraction free query query editor. to achieve this, either you can close all the other windows or you can just make the query editor window as full screen using the shortcut Shift+Alt+Enter. This way all other windows will remain there when you exit the full screen.

8. Never execute a query in production database inadvertently

If you have worked with databases for quite sometime, either you would have run some query in production that you didn't wanted to run or someone from your team would have done that. Dropping a database or a table or you would have updated/deleted all the records of the table. Because you forget the where clause.

You should pay extra attention when you execute any query in production server. In this trick, you will configure the status bar colour for to something different (eye catching) for production servers. So that when you connect to the server, you get a visual cue that you are connected to production server. It's not guaranteed that you will never execute that something in production server inadvertently. There is nothing will stop you from doing that. It's just a visual cue for you to know that you are working in production server.

Go to Change connection => Options => Connection Properties => Use custom color to set different colour.

9. Don't google for even a simple SQL syntax

Sometimes, you don't remember even a simple SQL syntax and you want to google for it to get the syntax and then copy paste and modify according to your need. Instead of that you can just use Snippets ( Ctrl+K and Ctrl+X ) You can add your own snippets also. That's for some other day.

10. Surround your code with IF condition or While loop.

This tip also similar to the previous one. If you have a block of code that you want to surround with a If , While or Begin..End, you can use Surround with using the short cut ( Ctrl+K and Ctrl+S ) or you can simple select the statements and right click and select Surround with.

Conclusion

Thanks for reading this post till the end. I hope these tips and tricks help you to improve your SQL development productivity as it does for me. If you have any tricks that you use are not listed in this post or any other questions, let me know in comments section below.

This article was originally published in blog.rajanand.org

If you would like to receive these tips and tricks directly to your inbox, consider subscribe my newsletter.

How to generate Permutations in SQL?

rajanand ilangovan — Sun, 13 Feb 2022 06:35:43 +0000

Question:

You are planning to go for a summer vacation and finalized the cities you want to visit. But you have not finalized in which order you want to visit them yet.

Write a SQL query to list out all different possible order you can visit these cities.

Note that you don't want to visit the same city again and you don't want to skip any city in your travel plan either.

Expected output:

Solution:

Let us create the test data first for the demo.

-- test data
use demo;
if object_id (N'dbo.city', N'u') is not null 
drop table dbo.city
go

create table dbo.city (
    id int identity(1,1),
    city_name varchar(100)
)

insert into dbo.city (city_name) values 
  ('Oslo'),
  ('Helsinki'),
  ('Stockholm'),
  ('Copenhagen')

select id, city_name from dbo.city

This solution is implemented using the Recursive CTE in SQL Server. If you are using other database engines, you can implement it in similar way.

-- solution
declare @total_cities int = (select count(1) from dbo.city);
with travel (travel_path, level) as (
    select cast(city_name as varchar(200)), 
    level = 1 
    from dbo.city
    union all
    select cast(travel.travel_path + ' -> ' + city.city_name as varchar(200)), 
    level = level + 1
    from dbo.city
    inner join travel on level < @total_cities
    where charindex(city.city_name, travel.travel_path) = 0
    )

select 
id = row_number() over(order by travel_path),
travel_path
from travel
where level = @total_cities
order by id

If you like this interview question, you may also like this interview question and answer series.

How to find an account balance in SQL?

rajanand ilangovan — Tue, 01 Feb 2022 19:39:07 +0000

Question:

You have a transaction_detail table with a record for each transaction. The transaction type could be debit, credit or refund. You need to add an additional column to track account balance.

Write a SQL query to achieve the expected output.

Answer:

Let us create the test data first.

-- create test data
USE demo;
GO
CREATE TABLE dbo.transaction_detail (
    date date,
    transaction_type varchar(50),
    amount money
    )
GO

INSERT INTO dbo.transaction_detail VALUES
('2022-01-01','credit',1000),
('2022-01-16','debit',300),
('2022-01-31','credit',150),
('2022-02-04','debit',50),
('2022-02-13','refund',50),
('2022-02-28','credit',400),
('2022-03-21','debit',500)
GO

To get the desired result, you can use aggregate function sum with window function to calculate the running total for each row in a window. But, we have not defined the window (i.e, partition by is not defined to define the window) here. Had we have to calculate the account balance for each account, we would have defined the window as account. So this query just calculates the running total for all the records in the table.

-- solution
SELECT 
date,
transaction_type,
amount,
balance = SUM(CASE WHEN transaction_type = 'debit' then amount * -1 
              else amount end) 
          OVER(ORDER BY date)
FROM dbo.transaction_detail

-- clean up
-- USE demo;
-- DROP TABLE dbo.transaction_detail

This is the solution I have come up with. If you have achieved the output in any other way, let me know in comments. Thank you.

If you would like to receive these interview questions directly to your inbox, sign up for my newsletter.

How to calculate total travel hours between city x and y and vice versa

rajanand ilangovan — Tue, 01 Feb 2022 07:41:25 +0000

Question

You are working for an airline company and you have received a dataset of Pilot's travel time between cities. You need to determine the reciprocal and find the total travel hours between cities. That means, the pilot's travel from city '1' to '2' and city '2' to '1' should be considered as one and then the total travel hours has to be calculated.

Write an SQL query to achieve the expected output.

Answer

Let us create the test data first.

--create test data
USE demo;

CREATE TABLE dbo.travel_detail (
    id int identity(1,1),
    from_city varchar(100),
    to_city varchar(100),
    travel_time_hours smallint
)
GO

INSERT INTO dbo.travel_detail values
    ('Oslo','Helsinki',125),
    ('Helsinki','Oslo',110),
    ('Stockholm','Oslo',132),
    ('Oslo','Stockholm',180),
    ('Copenhagen','Helsinki',148),
    ('Helsinki','Copenhagen',84),
    ('Stockholm','Copenhagen',116),
    ('Helsinki','Stockholm',124)
GO

SELECT * FROM dbo.travel_detail

--clean up
--USE demo;
--DROP TABLE dbo.travel_detail

To achieve the desired result, you should swap the city names within the row based on some order. That means, you can sort the from_city and to_city names in a row in ascending or descending order and then keep the first city as city_1 and the other as city_2. To do this, you can use less than < or greater than > operator on from_city and to_city.

This way you will be able to aggregate the travel_time_hours by grouping city_1 and city_2.

--solution
USE demo;

select 
city_1,
city_2,
total_travel_hours = sum(travel_time_hours)
from (
    select 
    city_1 = case when from_city < to_city then from_city else to_city end,
    city_2 = case when from_city > to_city then from_city else to_city end,
    travel_time_hours
    from dbo.travel_detail
)x
group by city_1, city_2
order by city_1, city_2

This is the solution I have come up with. If you have achieved the output in other way, let me know in comments.