SQL extensions

Introduction to SQL extensions

A sql extensions is a file extension used for files containing standard query language(SQL) queries and commands to create and modify relational database objects. It is identified as .sql type. The SQL file format is written in ASCII. For the uninitiated, it can be considered a flat-file with nothing but a set of SQL statements. For example, statements for creating, inserting, extracting, updating, or deleting a data table in SQL databases. It does not contain the entire database in it.

You must be wondering when and how we are going to use the SQL extension. The most frequent use of the .sql extension is when we have to run certain queries multiple numbers of times. Imagine writing the same query those many times. It’s terrifying right. We can instead save the query with .sql extension and open, re-run or edit it n number of times without having to rewrite the entire query again and again. Another important usage of sql extension is backing and restoring database backups. Some database servers such as SQL Server and MYSQL support this. One more prominent usage of .sql extension is exporting and sharing user-defined schemas, functions, procedures, and triggers with other users.

Examples of SQL extensions

Next, let us cover some fundamentals such as creating and opening a file with sql extension.

Example #1 – Creating a sql file extension

To create a table or schema, we need a database first. Ergo, let us create a new database called “Test_db”.  We can use the following statement for this.

CREATE DATABASE Test_db;

Having created the database, let us create a table called “cities” in it that contains a list of cities with their ids and country. Here is the create table statement for the city table.

CREATE TABLE cities (
id int NULL,
country varchar (50) NULL,
city varchar (50) NULL
);

Now we are all set to script this create table statement using extension so that we can reuse the code. Follow the following steps for more.

Step 1: Go to object explorer

Step 2: Select the desired database

Step 3: Select the table which you want to script

Here is an image to illustrate the flow.

Once you are done with navigation, move to step 4.

Step 4: SELECT the desired statement that has to be scripted.

Step 5: Choose a file from the next menu and save the script with the desired name. Here, the file of the extension type by default. If that’s not the case for you, mention .sql as a file extension.

Example #2 – Opening a sql file extension

Opening a file of the sql extension type is as easy as creating one. You just follow the following steps.

Step 1: Go to File Menu

Step 2: Select Open and go to File

A dialog box as shown below will appear in front of you.

Step 3: Select the desired .sql file and click on open.

The file of the sql extension type will have the following statements. It is basically the CREATE To script for the cities table. Just run this script to create the table.

USE [Test_db] GO
/****** Object:  Table [dbo].[cities]    Script Date: 07/10/2020 12:04:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[cities](
[id] [int] NULL,
[country] [varchar](50) NULL,
[city] [varchar](50) NULL
) ON [PRIMARY] GO
SET ANSI_PADDING OFF
GO

We have successfully created the table from a script (a file of sql extension type).

 

Example #3 – Working with .sql files

Suppose we do not have access to the Server query editor, but we want to write a query to insert a few records into the cities table. We can use text editors such as notepad, notepad++, atom, sublime, vim, etc. to view, write, or edit .sql files.

Here is an example of an INSERT statement written in notepad++. Simply write the query in a text editor and save it with .sql extension.

INSERT INTO [Test_db].[dbo].[cities] ([id] ,[country] ,[city])
VALUES
(1,'India', 'New Delhi'),
(2,'U K','London')
GO

Having successfully written and saved the query in the text editor, the next task is to open the file. So open the .sql file in the server as discussed above. The query will be automatically opened in the query editor as shown below.

Now execute the query to insert records in the city table. Let us check using a SELECT statement if the data has been successfully entered.

SELECT * FROM cities;

Yes, it has been successfully populated. If you wish to re-run the same SELECT query or share it with your teammates, just save it (by pressing Ctrl + S or from the file menu). Again a dialog box as shown below will appear.

Just save it and you are good to go.

Conclusion

SQL extension is a file format that is used to store and save sql commands, queries, functions, triggers, etc. so that they can be reused and shared.

Recommended Articles

This is a guide to SQL extensions. Here we discuss some fundamentals such as creating and opening a file with sql extension through examples. You may also have a look at the following articles to learn more –

1. MySQL EXISTS
2. SQL NOT Operator
3. SQL Cluster
4. SQL DROP TRIGGER