SQL CREATE TABLE - Create New or Replace Existing Table

The SQL CREATE TABLE statement is used to create a new table in a database.

Tables are organized into rows and columns; and each table must have a name.

CREATE TABLE your_table_name
(
table_column_name1 datatype(size),
table_column_name2 datatype(size),
table_column_name3 datatype(size),
....
table_column_nameN datatype(size),
);

CREATE TABLE Books
(
BookID INT,
BookName VARCHAR(255),
AuthorName VARCHAR(255),
BookPrice DECIMAL (7, 2)
);

1. To select a column name with spaces, use the back tick symbol with column name. The symbol is ( ` `). Back tick is displayed in the keyboard below the tilde operator ( ~).

CREATE table SpaceColumn
(
`Student Name` varchar(100)
);

2. We must specify the column in the square bracket or double quote. The query should be written as follows.

use DemoDatabase
go
/*Query with square bracket*/
select  [country code], [country name]
from tblCountries
Go

/*Query with double quotes*/
select  "country code", "country name"
from tblCountries
Go

3. Alternatively, you can specify the column name in between double quotes (“).

use DemoDatabase
go
create table tblCountries
(
"Country code" varchar(15),
"Country Name" varchar(15)
)

The CREATE TABLE statement allows us to specify constraints when constructing a table. The ALTER TABLE statement can also be used to specify constraints after a table has been created.

The rules that we can apply to the type of data in a table are known as constraints. That is, we can use constraints to limit the type of data that can be recorded in a specific column in a table.

The available constraints in SQL are:

NOT NULL: This constraint states that a null value cannot be stored in a column. That is, if a column is marked as NOT NULL, we will no longer be capable of storing null values in that column.

If we declare a table field to be NOT NULL. The field will never accept a null value after that. That is, you will not be able to insert a new row in the table without giving this field a value.

UNIQUE: When used with a column, this constraint specifies that all of the values in the column must be unique. In other words, the values in any row of a column cannot be repeated. This restriction aids in the unique identification of each table row. i.e., for a given column, all rows should have the same value. In a table, we can have many UNIQUE columns.

PRIMARY KEY: A primary key is a field that allows each row in a table to be uniquely identified. And this constraint is used to designate a table field as the main key.

If a field in a table is designated as a primary key, it cannot include NULL values, and all rows must have unique values for this field. To put it another way, this is a combination of NOT NULL and UNIQUE constraints.

A primary key can only be one field in a table.

FOREIGN KEY: A foreign key is a field that allows each row in another table to be uniquely identified. This constraint is also used to designate a field as a Foreign key. This field, in other words, refers to a table's primary key.

CHECK: This constraint assists in the validation of a column's values to ensure that they meet a set of criteria. That is, it assists in ensuring that the value stored in a column satisfies a set of criteria.

We can provide a condition for a field using the CHECK constraint, which must be met when entering data for that field.

DEFAULT: When no value is supplied by the user, this constraint defines a default value for the column. That is, if the user does not indicate a value for these fields while adding new records to the database, the default value will be set to them.

1. NOT NULL:

The query below creates a Student table with the fields ID and NAME set to NOT NULL. That is, whenever we want to insert a new row, we must specify values for these two fields.

CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
ADDRESS varchar(20)
);

2. UNIQUE:

The query below creates a table Student with the field ID set to UNIQUE. To put it another way, no two students can have the same ID. A one-of-a-kind constraint in terms of specifics.

CREATE TABLE Student
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20)
);

3. PRIMARY KEY:

Query will create a table named Student and specifies the field ID as primary key.

CREATE TABLE Student
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20),
PRIMARY KEY(ID)
);

4. FOREIGN KEY:

This usually creates a kind of link between the tables.

Consider the two tables as shown below:

Orders

O_ID	ORDER_NO	C_ID
1	2253		3
2	3325		3
3	4521		2
4	8532		1

Customers

C_ID	NAME		ADDRESS
1	RAMESH		DELHI
2	SURESH		NOIDA
3	DHARMESH	GURGAON

As we can see clearly that the field C_ID in Orders table is the primary key in Customers table, i.e. it uniquely identifies each row in the Customers table. Therefore, it is a Foreign Key in Orders table.

Syntax:

CREATE TABLE Orders
(
O_ID int NOT NULL,
ORDER_NO int NOT NULL,
C_ID int,
PRIMARY KEY (O_ID),
FOREIGN KEY (C_ID) REFERENCES Customers(C_ID)
)

(i) CHECK:

The query below generates a table Student with the criteria (AGE >= 18) for the field AGE. That example, the user will be unable to enter any records in the database that have an AGE of less than 18. Examine the constraint in depth.

CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
AGE int NOT NULL CHECK (AGE >= 18)
);

(ii) DEFAULT :

The below query will create a table named Student and specify the default value for the field AGE as 18.

CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
AGE int DEFAULT 18
);

SQL Server created calculated columns to allow computation code to be reused over many queries.

A calculated column is one that is created using an expression that can reference other columns in the same table. A non-computed column name, constant, function, or any combination of these coupled by one or more operators can be used in the expression, but the subquery cannot be used for computed columns.

Unless the column is designated PERSISTED, a calculated column is a virtual column that is not physically saved in the table. A calculated column expression can be used to calculate a value for the column to which it belongs using data from other columns.

Limitations and Restrictions

calculated column cannot be used with a NOT NULL constraint definition or as a DEFAULT or FOREIGN KEY constraint specification. A calculated column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns. If the table has integer columns a and b, for example, the computed column a + b can be indexed, but the computed column a + DATEPART(dd, GETDATE()) cannot be indexed because the value may vary in consecutive invocations.

An INSERT or UPDATE statement cannot be used to update a calculated column.

Example 1: To add a computed column when creating a table

The example below builds a table with a calculated column that multiplies the QtyAvailable column value by the UnitPrice column value.

CREATE TABLE dbo.Products
   (
      ProductID int IDENTITY (1,1) NOT NULL
      , QtyAvailable smallint
      , UnitPrice money
      , InventoryValue AS QtyAvailable * UnitPrice
    );

-- Insert values into the table.
INSERT INTO dbo.Products (QtyAvailable, UnitPrice)
   VALUES (25, 2.00), (10, 1.5);

-- Display the rows in the table.
SELECT ProductID, QtyAvailable, UnitPrice, InventoryValue
FROM dbo.Products;

-- Update values in the table.
UPDATE dbo.Products 
SET UnitPrice = 2.5
WHERE ProductID = 1;

-- Display the rows in the table, and the new values for UnitPrice and InventoryValue.
SELECT ProductID, QtyAvailable, UnitPrice, InventoryValue

Example 2: Using samples is the best method to learn how calculated columns function. You'll find a file at the conclusion of the article that contains all of the scripts used in the article, and we'll show you a few of them in the text to illustrate the explanations. To begin, we'll establish two tables: one to retain invoice information and another to hold the detail lines of those invoices. In the script file, you'll also find some inserts for creating sample data.

CREATE TABLE invoices
(   id_invoice    INT PRIMARY KEY IDENTITY
  , customer_name VARCHAR(25));
CREATE TABLE detail_lines
(   id_detail         INT PRIMARY KEY IDENTITY
  , id_invoice_detail INT
  , product           VARCHAR(30)
  , unit_price        MONEY
  , quantity          INT
  , FOREIGN KEY (id_invoice_detail) REFERENCES invoices (id_invoice));

An index is a performance-tuning method for retrieving records quicker. For each value that occurs in the indexed columns, an index generates an entry.

An index can be made in two ways. You can either construct an index when you first create a table with the CREATE TABLE statement or after the table has been formed with the CREATE INDEX statement.

Syntax:

The syntax to create an index using the CREATE TABLE statement in SQL is:

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
  column_n datatype [ NULL | NOT NULL ],

  INDEX index_name [ USING BTREE | HASH ]
    (index_col1 [(length)] [ASC | DESC], 
     index_col2 [(length)] [ASC | DESC],
     ...
     index_col_n [(length)] [ASC | DESC])
);

OR

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
  [ USING BTREE | HASH ]
  ON table_name
    (index_col1 [(length)] [ASC | DESC], 
     index_col2 [(length)] [ASC | DESC],
     ...
     index_col_n [(length)] [ASC | DESC]);

Parameters:

• UNIQUE: Optional. The UNIQUE modifier specifies that the indexed columns' value combinations must be unique.
• FULLTEXT: Optional. The FULLTEXT modifier does not allow prefixing and indexes the entire column. This option is supported by InnoDB and MyISAM tables.
• SPATIAL: Optional. The SPATIAL modifier indexes the entire column and prevents NULL values in indexed columns. This option is supported by InnoDB (beginning with MariaDB 5.7) and MyISAM tables.
• index_name: The name to assign to the index.
• table_name: The name of the table in which to create the index.
• index_col1, index_col2, ... index_col_n: The columns to use in the index.
• length: Optional. If only a prefix of the column is supplied, the complete column is not indexed. This value represents the number of characters in the column to index for non-binary string columns. This value is the number of bytes in the column to index for binary string columns.
• ASC: Optional. The index is sorted in ascending order for that column.
• DESC: Optional. The index is sorted in descending order for that column.

Example 1: Let's look at an example of how to use the Establish TABLE statement in MariaDB to create an index. At the same time, this statement will build the table and the index.

CREATE TABLE websites
( website_id INT(11) NOT NULL AUTO_INCREMENT,
  website_name VARCHAR(25) NOT NULL,
  server_name VARCHAR(20),
  creation_date DATE,
  CONSTRAINT websites_pk PRIMARY KEY (website_id),
  INDEX websites_idx (website_name)
);

We've established the websites table as well as a websites idx index that has the website name column in this instance.

Next, we'll teach you how to construct the table first, then use the CREATE INDEX statement to create the index.

Example 2: Here's a simple table with three columns for a customer's first and last name, as well as their email address. In the Email column, I've created an index.

CREATE TABLE Customer(
    LastName CHAR(30) NOT NULL,
    FirstName CHAR(30) NOT NULL,
    Email CHAR(50) NOT NULL,
    INDEX (Email)
);

To create a descending index (and assuming you’re working with MySQL 8 or above) you add DESC to the name of the column to index.

CREATE TABLE Customer(
    LastName CHAR(30) NOT NULL,
    FirstName CHAR(30) NOT NULL,
    Email CHAR(50) NOT NULL,
    INDEX (Email DESC)
);

It's pretty similar to creating a multi-column index. A comma is used to divide each column. You can also add a primary key instead of an index, just as single column indexes.

CREATE TABLE Customer(
    LastName CHAR(30) NOT NULL,
    FirstName CHAR(30) NOT NULL,
    Email CHAR(50) NOT NULL,
    INDEX (LastName,FirstName)
);

We occasionally need to save information in our database, including images. For instance, a photograph of the product or a photograph of the team members. However, how do we save photos in SQL Server.

Many SQL Server database-driven applications require you to save images to a database table. In addition to ASP.NET web applications, database administrators can utilise T-SQL commands to insert images into SQL Server database tables. The simplest way to save an image in binary format in a SQL Server database table is to use the SQL OPENROWSET command with the BULK and SINGLE BLOB parameters.

We could write an application in.NET or Java, but we could also use SQL Server tools if we don't have any knowledge with such programming languages.

Example 1: insert one image into SQL Server

We will first learn how to load 1 single image into a table in SQL Server.

We will load a file named 1.png that contains the image of your humble author into the table myimages:

We will first create a table named myimages in SQL Server:

CREATE TABLE myimages(id int, img varbinary(max))

This table will have an integer (int) id and the image column named img. The data type that we are going to use to store images is the varbinary(max).

We will now insert an image into the table myimages:

INSERT INTO adventureworks.dbo.myimages values 
(1, (SELECT * FROM OPENROWSET(BULK N'C:\img\1.png', SINGLE_BLOB) as T1))

The INSERT statement adds the number 1 as the id, followed by the picture 1.png from the img folder on the c drive. I'm guessing you have a file in that folder. The OPENROWSET is used to access distant data, such as OLE DB data sources.

Example 2:

-- Query to Insert Images into SQL Server is: 

CREATE TABLE SaveFiles
(
    FileID INT IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(50) NOT NULL,
    Files VARBINARY(MAX) NOT NULL
)

Insert Images into SQL Server Example

In this example, we write a Server Query to insert an image into the table using the OPENROWSET

Query to Insert Images into SQL Server is:

 INSERT INTO [dbo].[SaveFiles] (Name, Files)
SELECT 'Home Page 2', 
BulkColumn FROM OPENROWSET(BULK N'D:\LOGOS\Home Page.png', SINGLE_BLOB) image;

You can also write the above frequently asked query (SQL Insert Image) differently:

Query to Insert Images into SQL Server is:

INSERT INTO [dbo].[SaveFiles] (Name, Files)
SELECT 'Home Page 2', 
* FROM OPENROWSET(BULK N'D:\LOGOS\Home Page.png', SINGLE_BLOB) image;

Let me open the SaveFile table to check whether we successfully inserted two images into the table or not.

Example 3:

Step 1: Create MySQL Table

We are creating a mysql table with a blob type field in it, so that we can save image in it.

In our table we have only two fields:

• image_id of int type
• image of blog type

Here is the query to create table:

CREATE TABLE pictures (
image_id int(10) NOT NULL auto_increment,
image blob,
PRIMARY KEY (`image_id`)
);

Step 2: insert image into table

Now we can insert the image into table using the insert into sql. Following is the example of sql:

INSERT INTO pictures VALUES(1, LOAD_FILE('d:\\flower.gif'));

We have used the LOAD_FILE() function of MySQL to insert the image data into database.

Example 4: Imagine that your SQL Server database administrator uses the SQL create script to create a database table named DatabaseImageTable.

The table has a column called image that stores binary data from picture files.

CREATE TABLE DatabaseImageTable (
 [image name] nvarchar(100),
 [image] varbinary(max)
)

After we've constructed a sample storage SQL table in which to save the image, we're ready to run the SQL OPENROWSET command.

The image file will be retrieved from the supplied path on the server where the SQL Server database instance is running and saved to the varbinary() column using the INSERT INTO table command below.

INSERT INTO DatabaseImageTable ([image name], [image])
SELECT 'SQL Server Image', *
FROM OPENROWSET(BULK N'C:\images\sql-server-image-file.jpg', SINGLE_BLOB) image;

save image in SQL Server database table

You should be able to insert and save image files or any other binary files into the target database table if you have sufficient server rights, such as the ability to execute bulk operations, or if you are a member of the BulkAdmin server role. Otherwise, you may receive the SQL Server error notice below:

To remedy this problem, use the SQL instructions below to permit the user access to perform BULK Insert operations on the SQL Server instance.

Add the bulkadmin role to SQL login

ALTER SERVER ROLE [bulkadmin] ADD MEMBER [login_user]

OR

Grant bulk operations permissions to SQL login

GRANT ADMINISTER BULK OPERATIONS TO [login_user]

you can take back the granted permissions by running REVOKE command later.

REVOKE ADMINISTER BULK OPERATIONS TO [login_user]

This easy Insert Into command using OPENROWSET can be used by SQL Server database developers and administrators to store a picture to a database table.

You may also construct a table from an existing table by copying its columns with the SQL CREATE TABLE AS statement.

It's worth noting that when you create a table this manner, the old table's records will be copied into the new table (based on the SELECT Statement).

1. Create Table - By Copying all columns from another table

Syntax:

The syntax for the CREATE TABLE AS statement when copying all of the columns in SQL is:

CREATE TABLE new_table
  AS (SELECT * FROM old_table);

Example: Let's look at an example that shows how to create a table by copying all columns from another table.

CREATE TABLE suppliers
AS (SELECT *
    FROM companies
    WHERE id > 1000);

This would result in the creation of a new table named suppliers, which would contain all of the columns from the companies table.

If there were records in the firms table, the records picked by the SELECT query would also be in the new suppliers table.

2. Create Table - By Copying selected columns from another table

Syntax :

The syntax for the CREATE TABLE AS statement copying the selected columns is:

CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n
      FROM old_table);

Example: Let's look at an example that shows how to create a table by copying selected columns from another table.

CREATE TABLE suppliers
  AS (SELECT id, address, city, state, zip
  FROM companies
  WHERE id > 1000);

This would create a new table called suppliers, but it would only contain the columns from the companies database that were specified.

If there were records in the firms table, the records picked by the SELECT query would also be in the new suppliers table.

3. Create Table - By Copying selected columns from multiple tables

Syntax :

The syntax for the CREATE TABLE AS statement copying columns from multiple tables is:

CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n
  FROM old_table_1, old_table_2, ... old_table_n);

Example: Let's look at an example that shows how to create a table by copying selected columns from multiple tables.

CREATE TABLE suppliers
  AS (SELECT companies.id, companies.address, categories.cat_type
  FROM companies, categories
  WHERE companies.id = categories.id
  AND companies.id > 1000);

This would create a new table called suppliers based on columns from both the companies and categories tables.

Example 1:

1. Create Sample SQL Database with SQL Data

Before moving forward, let’s create a test environment using the following syntax below:

USE master
GO
 
CREATE DATABASE TestDB
GO
 
USE TestDB
GO
 
CREATE TABLE TestTable
(
   ID INT IDENTITY(1,1) PRIMARY KEY,
   Val INT NOT NULL,
   CHECK(Val > 0)
)
GO
 
CREATE UNIQUE INDEX UIX_TestTable_Val ON TestTable(Val)
GO
 
INSERT INTO TestTable(Val) 
VALUES(10),(20),(30),(40),(50)
GO
 
SELECT * FROM TestTable

As we can see, the code creates a database, a table within this database with constraints, indexes, etc. and inserts sample data into it.

2. Creating a table from another table using SELECT INTO

We can construct a new table based on another table using the SELECT INTO query. The code below generates a new table, TableA, and populates it with all rows from the source table:

USE TestDB
GO
 
SELECT * INTO TableA FROM TestTable
GO 
 
SELECT * FROM TableA

The code's final SELECT line obtains all of the new table's columns and rows. As a result, we can observe that the result set has the same columns and data:

TableA and TestTable appear to be identical at first view, but a closer examination of their architecture reveals many discrepancies. What is different may be seen in the figure below from SSMS table properties:

table properties

We may conclude from a careful examination of their structures that neither the indexes, primary keys, nor the check constraints of TestTable are passed to TableA.

The column types, NOT NULL constraint, and identity specification are all transferred to the new table, as can be seen.

3. Creating a table from another table using SELECT INTO and copying filtered data

Only a portion of the data from the source table needs to be copied to the destination table at times. In that situation, a WHERE condition can be used to filter the data transmitted:

USE TestDB
GO
 
SELECT Val INTO TableB FROM TestTable WHERE ID > 3
GO
 
SELECT * FROM TableB

The T-SQL code above created a new table – TableB, based on TestTable but copies only data that meets the search condition.

4. Creating an empty table using SELECT INTO based on another table

What if we just need an empty copy of a specific table? In that case, we can use a condition in the WHERE clause that is always false, such as this:

USE TestDB
GO
 
SELECT * INTO TableC FROM TestTable WHERE 0 > 1
GO
 
SELECT * FROM TableC

The condition in the WHERE clause is always false since 0 is always less than 1. As a result, TestTable generates an empty table named TableC. The column names, identity specification, and nullability of the columns are all inherited from the base table, as in the previous examples:

It's also possible to get the same outcome using a different statement. To build an empty table from TestTable, the sentence following takes a somewhat different technique:

USE TestDB
GO
 
SELECT TOP 0 * INTO TableD FROM TestTable 
GO
 
SELECT * FROM TableD

As the query result indicates, using the Retrieve INTO command to select the top 0 rows from the base database results in the construction of an empty table with the same structure.

5. Some other ways of creating a table via SELECT INTO clause

It's also feasible to make a new table by copying only a few of the source table's columns:

USE TestDB
GO
 
SELECT Val INTO TableX FROM TestTable 
GO
 
SELECT * FROM TableX

The code above generates a new table with one of the base table's columns:

The following code produces a new table using only the Val column from the base database, as well as a new identification column and a constant value column:

USE TestDB
GO
 
SELECT IDENTITY (INT, 1, 1) AS ID, 1 AS IsUsed, Val INTO TableY FROM TestTable 
GO
 
SELECT * FROM TableY

In the Val column, the new table contains data from the base database, as well as newly produced identity values and a constant value of 1 in the IsUsed column:

Finally, it's worth noting that a table can be made without the use of a foundation table. For example, the code following uses SELECT INTO to construct a table by just selecting a number:

USE TestDB
GO
 
SELECT 1 AS Val INTO TableZ 
GO
 
SELECT * FROM TableZ

So, a one-column, one-row table is created:

To summarise, we learnt how to use the SELECT INTO clause to construct a new table depending on an existing table.

Is it possible to grant the following permissions to a user KLB in SQL Server if I don't want to grant the database role dB_ Ddladmin because it will enlarge the user's access? The following is an example:

USE AdventureWorks2014;
GO
GRANT CREATE TABLE TO klb;

Is it OK to authorize in this way? The answer is that “the specified schema name” dbo “either does not exist or you do not have permission to use it.”

The following permissions need to be granted for the login name KLB to actually create the table.

USE AdventureWorks2014;
GO
GRANT ALTER ON SCHEMA::dbo TO klb

However, this will increase the login KLB's permissions. In fact, you can overcome this problem by creating a new user schema in SQL Server.

CREATE SCHEMA test AUTHORIZATION klb

If the corresponding user mode already exists

USE AdventureWorks2014;
GO

GRANT ALTER ON SCHEMA::test TO klb

Compare the following scripts once you've authorised as described above, and you'll notice that KLB can build tables in test mode but not in dbo mode. This is also why the SQL Server design has been separated.

CREATE TABLE  dbo.TEST  (id int); -- error reported
GO
CREATE TABLE  test.TEST  (id int); -- normal
GO

Fill the SQL Server database with a significant amount of random data so that queries can be assessed for performance and execution.

Creating Large SQL Server Tables Filled With Random Data

Containing the help of an example, we will demonstrate how to create big tables with random data. tblAuthors and tblBooks will be the tables in our fictional library database. The first table will contain information about fictitious authors, while the second will have info regarding fictitious novels. The tables will be arranged in a one-to-many pattern, with each author having numerous books. Because the tblAuthors table has no foreign keys, we'll start by inserting a big amount of random data into it. Next, we'll go through how to add random data to a table with a foreign key.

Create an Example SQL Server Database

First we need to create the example library database and add the tables to it. Take a look at the following script:

CREATE Table tblAuthors
(
   Id int identity primary key,
   Author_name nvarchar(50),
   country nvarchar(50)
)
CREATE Table tblBooks
(
   Id int identity primary key,
   Auhthor_id int foreign key references tblAuthors(Id),
   Price int,
   Edition int
)

Three columns make up the tblAuthors table: Id, Author_name, and Country. Id, Author_id, Price, and Edition are the four columns of the tblBooks table. The tblBooks table's Author_id column is a foreign key column that refers to the tblAuthors table's Id column. This is how the one-to-many relationship between the two tables is implemented.

Adding a Large Amount of Random Data to the tblAuthors Table in SQL Server

Let's populate the tblAuthors table with information. Because this is a standalone table with no foreign keys, we've chosen to add records to it first. The tblBooks table, on the other hand, has a foreign key that points to the tblAuthors table. As a result, we must first enter a record in the tblAuthors table before moving on to the tblBooks table.

The following script inserts 12 thousand dummy records into the tblAuthors table. You can add more if you want.

Declare @Id int
Set @Id = 1

While @Id <= 12000
Begin 
   Insert Into tblAuthors values ('Author - ' + CAST(@Id as nvarchar(10)),
              'Country - ' + CAST(@Id as nvarchar(10)) + ' name')
   Print @Id
   Set @Id = @Id + 1
End

Take a look at the script above. We declare an integer variable @Id and set its value to 1. The INSERT statement is used inside the while loop to insert records into the tblAuthors table.

Take a look at the values that are being entered. We don't need to fill in any values for the Id column because we've turned on the identity property, which means the value for this column will be filled in automatically with each item. The Author_name and country fields require values to be entered. We utilise the string 'Author -' and concatenate it with the value of the @Id variable for Author_name. The CAST function is used to convert @Id from integer to string. Author - 1, Author - 2, up to Author - 12000 will be the values inserted for the Author_name field. We use the same method to populate the Country column with values.

Now if you select all the records from the tblAuthor column, you will get 12000 records. The table will look like this:

Id	Author_name	country
1	Author - 1	Country - 1 name
2	Author - 2	Country - 2 name
3	Author - 3	Country - 3 name
4	Author - 4	Country - 4 name
5	Author - 5	Country - 5 name
6	Author - 6	Country - 6 name
7	Author - 7	Country - 7 name
8	Author - 8	Country - 8 name
9	Author - 9	Country - 9 name
-
-
-
12000	Author - 12000	Country - 12000 name

Adding a Large Amount of Random Data to the tblBooks Table in SQL Server

Let's populate the tblBooks table with some information. Inserting data into the tblAuthors database is a little more difficult. This is because the tblBooks table's Author_Id field refers to the tblAuthors table's Id column. This means that the Author_Id column can only have values between 1 and 12000, which correspond to the Author's Id column values. For the Price and Edition columns, we must also add random values.

Check out the following script to learn how to solve this issue. The following is the explanation for this code.

Declare @RandomAuthorId int
Declare @RandomPrice int
Declare @RandomEdition int

Declare @LowerLimitForAuthorId int
Declare @UpperLimitForAuthorId int

Set @LowerLimitForAuthorId = 1
Set @UpperLimitForAuthorId = 12000

Declare @LowerLimitForPrice int
Declare @UpperLimitForPrice int

Set @LowerLimitForPrice = 50 
Set @UpperLimitForPrice = 100 

Declare @LowerLimitForEdition int
Declare @UpperLimitForEdition int

Set @LowerLimitForEdition = 1
Set @UpperLimitForEdition = 10

Declare @count int
Set @count = 1

While @count <= 20000
Begin 

Select @RandomAuthorId = Round(((@UpperLimitForAuthorId - @LowerLimitForAuthorId) * Rand()) 
+ @LowerLimitForAuthorId, 0)
Select @RandomPrice = Round(((@UpperLimitForPrice - @LowerLimitForPrice) * Rand()) 
+ @LowerLimitForPrice, 0)
Select @RandomEdition = Round(((@UpperLimitForEdition - @LowerLimitForEdition) * Rand()) 
+ @LowerLimitForEdition, 0)

Insert Into tblBooks values (@RandomAuthorId, @RandomPrice, @RandomEdition)
Print @count
Set @count = @count + 1
End

Take a look at the code in the example above. We add three variables at the start: @RandomAuthorId, @RandomPrice, and @RandomEdition. The values to be inserted into the Author Id, Price, and Edition columns of the tblBooks database will be stored in these three variables.

Next we created variables to store the upper limit and lower limit values for all Author_Id, Price and Edition columns. We want that the Author_Id columns should only have values between 1 and 12000 therefore the @UpperLimitForAuthorId variable is set to 12000 and the @LowerLimitForAuthorId variable is set to 1. Similarly, @UpperLimitForPrice variable is set to 50 and the @LowerLimitForAuthorId variable is set to 100 because we want the Price between 50 and 100. Finally, @UpperLimitForEdition variable is set to 10 and the @LowerLimitForEdition variable is set to 1 because we want the Edition to have values between 1 and 10.

Then we multiplied the result of higher limits – lower limits by the Rand() function, which produces values between 0 and 1. This returns the values that fall within the provided range. These figures, however, are in decimal. The Round function is used to convert it to an integer. The second attribute is set to 0. The number is now rounded to zero decimal places. The generated values are then inserted into the tblBooks table.

If you select all of the records in the tblBooks table, you'll discover that a total of 20000 records have been entered. The Author Id value will range from 1 to 12000, the Price value will range from 50 to 100, and the Edition value will range from 1 to 10:

Id	Auhthor_id	Price	Edition
1	8878	         56	2
2	9605	         71	5
3	3860	         61	8
4	7425	         81	7
5	4775	         77	5
6	66	         60	3
7	241	         78	9
8	10583	         93	2
9	7920	         96	8
-	-	         -	-
-	-	         -	-
20000	2096	         92	6

Your values will be different since the Rand function generates these numbers randomly.

You may need a continuous date range in your SQL queries in a variety of situations.

A time series chart that plots your activities on a daily basis is one example.

If you simply type pick date, count(1) from activities group by date order by date, you will notice that there are no entries for dates with no contributions.

Instead, a range of dates with 0s on dates with no contributions is required.

Depending on your database engine, there are several ways to accomplish this.

Example 1:

Option 1: Create a table that holds long number sequence:

The first method entails creating a lengthy table with approximately 1500 records (which can generate a date range for 3 years) Of course, you may change the amount as needed, but in most circumstances, you won't need more than a year's worth of data, so even 500 records will suffice.

Only an integer primary key should be stored in the table.

create table range(int primary key)

note that we have created one column of type integer, this will not take a lot of storage space, and yes we didn’t created a date column, the conversion will be done on the fly.

So to generate a date range between 2018–01–01 and 2018–12–31 in SqlServer.

select dateadd(day, 1, '2018–01–01') as [date] 
from [range] 
where dateadd(day, 1, '2018–01–01') = '2018–12–31'

This approach has many advantages:

• Works on all database engines (off course you need to replace the dateadd with corresponding function).
• Simple query and easy to understand/adapt to your need.
• No complex generation on the fly.

The only disadvange is that:

You need to create a table

Example 2: If I need to convert a date range to a collection of rows in a SQL Server table, I'll use this method. For instance, I have documentation that an employee took a vacation from 2020-08-01 to 2020-08-20. This single row must be divided into 20 rows, one for each vacation day. How can I achieve this quickly and scalable, given that my tables include thousands of data and the output could be millions of rows:

Solution

A typical requirement is to convert date ranges (or any other sort of range) into a set of rows. For example, the source system provides rows with a start and end date for a specified interval, but you'll need a transactional table in your database with a record for each day to make computations easier. You could, for instance, filter out weekends and holidays much more easily than if you merely used the interval's start and end dates.

Because a small set of ranges might result in a large number of rows, the term "exploding the table" is frequently used. If you have one row with the start date of 2020-01-01 and the end date of 2020-12-31, you will have 366 rows. Consider the scenario in which you must make a similar computation for millions of customers. Because the output might be quite huge, it's critical that the solution be quick and scalable. This eliminates T-SQL loops and cursors, which are row-based and unsuitable for large numbers of rows. "Starbursting" and "unpacking a relation on a time interval" are examples of other terms.

A solution is offered in this article using a "numbers table," sometimes known as a "tally table." Check out Aaron Bertrand's fantastic tips for more background information on this type of table:

Similar to the tip SQL Server Function to Return a Range of Dates, however it employs a recursive CTE that is not scalable and has a limit on the maximum number of recursions.

Sample Data

With the following SQL statement, we can create a simple table for holding the sample data:

CREATE TABLE dbo.EmployeeHoliday
(EmployeeID VARCHAR(10) NOT NULL
,StartDate DATE NOT NULL
,EndDate DATE NOT NULL);

Let's insert 2 rows into that table. Two employees taking a holiday, both with start and end date.

INSERT INTO dbo.EmployeeHoliday
SELECT [EmployeeID] = 'A', [StartDate] = '2020-06-01', [EndDate] = '2020-06-05'
UNION ALL
SELECT 'B','2020-12-15','2020-12-31';

A temporary table is created with the CREATE TEMPORARY TABLE statement.

A temporary table is one that is only visible during the current session and is immediately dropped when the session in which it was created ends.

Because temporary tables are not permanently saved in the database, they are handy in situations when you just need a table for a short period of time to execute or test something, and then you want it to disappear automatically.

1. Creating Local Temporary Tables

The CREATE TABLE command in SQL Server can be used to create local temporary tables. However, to declare the table as a local temporary table, we must add a single hashtag (#) sign to the beginning of the table name. In addition, random numbers will be appended to the table's name.

Within SQL Server sessions, LOCAL TEMPORARY TABLES are separate from modules and embedded SQL programmes. SQL Server stores LOCAL TEMPORARY TABLES in tempdb and deletes them when they are no longer needed.

For this implementation, we can follow the following syntax.

CREATE TABLE #table_name (
      column_1 datatype,
      column_2 datatype
)

Now, for example, consider the following code given below used to create a local temporary table named “SampleTempTable”.

CREATE TABLE #SampleTempTable (
    ID int PRIMARY KEY IDENTITY(1,1),
    FirstName varchar(255),
    LastName varchar(255),
    City varchar(255)
)

After successful query execution, the table will be created and it will appear under the “Temporary Tables” directory in the “tempdb” database.

2. Creating Global Temporary Tables

The CREATE TABLE command in SQL Server can also be used to create global temporary tables. However, instead of a single (#), we must use double hashtag (##) indications at the beginning of the table. Also, the table name will not have any random digits applied to it.

For this implementation, we can follow the following syntax.

CREATE TABLE ##table_name (
      column_1 datatype,
      column_2 datatype
)

Now, for example, consider the following code given below used to create a global temporary table named “SampleTempTable”.

CREATE TABLE ##SampleTempTable (
    ID int PRIMARY KEY IDENTITY(1,1),
	FirstName varchar(255),
    LastName varchar(255),
    City varchar(255)
)

And after creation, the table will also appear under the “Temporary Tables” directory in the “tempdb” database.

3. Creating a Temporary Copy of an Existing Table

Temporary tables are handy for testing SQL queries without changing the database.

Syntax:

CREATE TEMPORARY TABLE table_name (column definitions);

If you want to construct a temporary table from scratch, you can use the TEMPORARY keyword in the CREATE TABLE statement instead of the CREATE TABLE statement. For detailed syntax and examples, see the construct table chapter.

In the MySQL database, let's make a temporary clone of an existing table.

At the MySQL command prompt, type the following command and hit enter:

CREATE TEMPORARY TABLE persons SELECT * FROM persons;

The preceding statement generates a temporary table named persons from the result set of an existing base table named persons on the fly. Furthermore, because it is a temporary clone of the persons table, you can conduct any operation, such as INSERT, UPDATE, or DELETE, without fear of accidentally damaging the actual persons base table.

Tip: A temporary table and a permanent foundation table can have the same name. The permanent base table remains concealed until the temporary table is dropped if the name of the temporary table is the same as the current base table.

Note: Because temporary tables are session-specific, the same temporary table name can be used by two different sessions without conflicting.

A single field or a set of data that uniquely defines a record is referred to as a unique constraint. As long as the combination of values is unique, some of the fields can have null values.

A UNIQUE constraint is an integrity constraint that guarantees the uniqueness of values in a column or collection of columns. A column constraint or a table constraint can both be UNIQUE constraints.

CREATE or ALTER TABLE SQL can be used to create unique restrictions. After the column definitions in the CREATE TABLE statement, add unique constraints.

Syntax:

Create unique Contraint - Using a CREATE TABLE statement

1. To define a UNIQUE constraint for a column when you create a table, you use this syntax:

CREATE TABLE table_name(
    ...,
    column_name data_type UNIQUE,
    ...
);

2. In this approach, the UNIQUE keyword is used in the definition of the column where the uniqueness rule is to be enforced. MySQL refuses and issues an error if you insert or update a value that causes a duplicate in the column_name.

The syntax for creating a unique constraint using a CREATE TABLE statement in SQL Server is:

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)
);

Parameters:

• table_name: The name of the table that you wish to create.
• column1, column2: The columns that you wish to create in the table.
• constraint_name: The name of the unique constraint.
• uc_col1, uc_col2, ... uc_col_n: The columns that make up the unique constraint.

Example 1: The following CREATE TABLE statement defines a unique key constraint on the PhoneNo column of the Employee table.

Add Unique Constraint in Existing Table:

CREATE TABLE Employee(EmployeeID int,
    FirstName nvarchar(50) NOT NULL,  
    LastName nvarchar(50) NOT NULL, 
    EMail nvarchar(50),
   oneNo varchar(15),
ADD CONSTRAINT UNQ_Emp_Phone Unique(PhoneNo))

Example 2: In this example, we've created a unique constraint on the employees table called employees_unique. It consists of only one field which is the employee_number.

We could also create a unique constraint with more than one field as in the example below:

CREATE TABLE employees
( employee_id INT PRIMARY KEY,
  employee_number INT NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50),
  salary MONEY,
  CONSTRAINT employees_unique UNIQUE (last_name, first_name)
);

The VARCHAR(Max) as well as NVARCHAR(max) and VARBINARY(max) string data types were first introduced in SQL Server 2005 to replace the large object (LOB) data types TEXT, NTEXT and IMAGE respectively. All of these data types can store up to 2GB of data except NVARCHAR(max) that can store 1GB of Unicode characters. As you may guess, the maximum storage for these data types is the same as the ones being replaced.

A common question amongst beginners is: if the VARCHAR(max) data type can store up to 2GB why can't we declare a column or variable of type VARCHAR(10000)? Why are we limited to either declare a VARCHAR(8000) or VARCHAR(max)? The reason behind this is that VARCHAR(n) is intended to store the data in the row page. SQL Server stores data in 8KB pages (8,192 bytes), but the page header occupies the first 96 bytes of each data page leaving 8,096 bytes for data, row overhead, and row offsets, that’s why you cannot declare a value greater than VARCHAR(8000).

The effective maximum length of a VARCHAR in MySQL and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters.

Keep in mind that the limitation of maximum row size is 65,535 bytes.This states that including all columns it shouldn't be more than 65,535 bytes.

1. Advantages

One of the advantages of the VARCHAR(max) data type as a replacement of TEXT data type is that we can declare local variables to manipulate LOBs and even declare VARCHAR(max) parameters on functions and stored procedures. This is something that cannot be done with the TEXT data type. Furthermore, the VARCHAR(max) data type can be used inside string functions such as REPLACE, CHARINDEX or LEFT instead of using READTEXT, WRITETEXT, and UPDATETEXT commands to manipulate LOBs.

The following list includes the functions that support the VARCHAR(max) data type:

• STRING_SPLIT
• STRING_ESCAPE
• CONCAT
• PATINDEX
• CHARINDEX
• LTRIM and RTRIM
• UPPER and LOWER
• LEN
• LEFT and RIGHT
• REPLACE
• REPLICATE
• REVERSE
• SUBSTRING
• STUFF

2.Updating Data on VARCHAR(max) Columns

Something that not many people know is that the UPDATE statement allows us to append and replace data in VARCHAR(max), NVARCHAR(max) and VARBINARY(max) columns without using string functions.

There is an implicit WRITE function in the UPDATE statement for columns of VARCHAR(max), NVARCHAR(max) and VARBINARY(max) data type with the following syntax.

UPDATE MyTable SET
Lob_column_name.WRITE (expression,Offset,Length)

3.Limitations:

There is an obvious limitation on VARCHAR(max) columns which is that these columns cannot be indexed. If you think you need to place an index on a VARCHAR(max) column I suggest that you may need to review the table design.

Example 1:

The following script will create a test table with one VARCHAR(Max) column and an identity column, and then insert one row with 1,000,000 characters.

USE [TestDB]
GO

CREATE TABLE TestTable
    (
      ID INT IDENTITY(1, 1) ,
      TestData VARCHAR(MAX) ,
      PRIMARY KEY CLUSTERED ( ID )
    )
GO

DECLARE @str VARCHAR(MAX) = 'a'

INSERT  INTO dbo.TestTable
        ( TestData )
        SELECT  REPLICATE(@str, 1000000)
GO

Now we will append the text "0123" at the end of the string.

USE [TestDB]
GO

UPDATE  dbo.TestTable
SET     TestData.WRITE('0123', NULL, NULL)
WHERE   ID = 1;

SELECT  RIGHT(testdata, 10) ,
        LEN(TestData)
FROM    dbo.TestTable;

GO

As you can see on the next image the data was appended and the string length was increased.

Example 2: Here is a table with two columns, “one” varchar with the length of 32,765 and “two” with 32766.

Length = 32765+2 + 32766 + 2 = 65535.

CREATE TABLE IF NOT EXISTS `mytable` (
`one` varchar(32765) NOT NULL,
`two` varchar(32766) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Now let us increase the column length -

CREATE TABLE IF NOT EXISTS `mytable` (
`one` varchar(32767) NOT NULL,
`two` varchar(32770) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Above gives the following error:

#1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

The above itself states that:

The maximum row size is 65,535 bytes. If it exceeds, an error will be visible.

This blog entry discusses how to add an image to SQL Server for storage within the database, followed by how to use it in SSRS.

When including an image within a SQL Server Reporting Services report, you have 3 options:

• Embedded. The image is embedded within the report. This is my least favorite choice, as it’s not easily reusable and maintenance of a change would be a big headache if very many reports existed with individual embedded images.
• External. The image is loaded to the Report Project and may be reused among multiple SSRS reports. This approach was discussed in this ImagePaths in SSRS entry.
• Database. The image is loaded to a SQL Server database and, like option 2, may be reused among multiple SSRS reports. Because it’s stored in the database, it can be used for many other types of solutions as well. This is the most flexible of the 3 options - and the topic of the rest of this entry.

Example 1: Using OPENROWSET to Insert Image Into Table

1. First, let’s create a table in SQL Server Management Studio to hold the image file.

>CREATE TABLE dbo.Images
(
      [ImageID] [int] IDENTITY(1,1) NOT NULL,
      [ImageName] [varchar](40) NOT NULL,
      [OriginalFormat] [nvarchar](5) NOT NULL, 
      [ImageFile] [varbinary](max) NOT NULL
 )    

Note that the data type we’re using for the image is varbinary(max) instead of the image data type. That’s because the image data type is being removed in a future version of SQL Server.

2. Right-click this dog image and save it to your c:\ drive as “MyImage.png”.

3. Now let’s insert the dog image into the database table we created in step 1.

INSERT INTO dbo.Images
(
       ImageName
      ,OriginalFormat
      ,ImageFile
)
SELECT
      'Sample Image'
      ,'png'
      ,ImageFile
       FROM OPENROWSET(BULK N'C:\MyImage.png', SINGLE_BLOB) AS ImageSource(ImageFile);

Example 2: The easiest method to save images into a table is to execute OPENROWSET command with the BULK and SINGLE_BLOB option. First, let me create a new Table to save the photos.

Query to Insert Images into SQL Server is:

CREATE TABLE SaveFiles
(
    FileID INT IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(50) NOT NULL,
    Files VARBINARY(MAX) NOT NULL
)

Output:

Insert Images into SQL Server Example

In this example, we write a Server Query to insert an image into the table using the OPENROWSET:

Query to Insert Images into SQL Server is:

INSERT INTO [dbo].[SaveFiles] (Name, Files)
SELECT 'Home Page 2', 
	BulkColumn FROM OPENROWSET(BULK N'D:\LOGOS\Home Page.png', SINGLE_BLOB) image;

You can also write the above frequently asked query (SQL Insert Image) differently:

Query to Insert Images into SQL Server is:

INSERT INTO [dbo].[SaveFiles] (Name, Files)
SELECT 'Home Page 2', 
	* FROM OPENROWSET(BULK N'D:\LOGOS\Home Page.png', SINGLE_BLOB) image;

Let me open the SaveFile table to check whether we successfully inserted two images into the table or not.

Example 3: Insert one image into SQL Server:

We will first learn how to load 1 single image into a table in SQL Server.

We will load a file named 1.png that contains the image of your humble author into the table myimages:

Step 1: We will first create a table named myimages in SQL Server:

CREATE TABLE myimages(id int, img varbinary(max))

This table will have an integer (int) id and the image column named img. The data type that we are going to use to store images is the varbinary(max).

Step 2: We will now insert an image into the table myimages:

INSERT INTO adventureworks.dbo.myimages values 
(1, (SELECT * FROM OPENROWSET(BULK N'C:\img\1.png', SINGLE_BLOB) as T1))

The INSERT statement inserts the value 1 as the id and then inserts the image named 1.png from the folder img in the c drive. I am assuming that you have an image in that path. We are using the OPENROWSET to access remote data like OLE DB data sources.

Step 3: How to store multiple files into a table

We have a folder named img with 4 images. How can we insert all of them into a SQL Server table:

We will use PowerShell this time to do this task. If you have SSMS 17, you may need to read the next article to install PowerShell:

To simplify this task, we will truncate the table with the image inserted in the previous example:

Step 4: truncate table myimages

In PowerShell go to the SQL Powershell:

We will first open PowerShell and go to the folder with the images using the change directory command (cd):

Cd \
Cd img

We will store all file names in the variable files:

$files=dir -name

Dir lists all the files and folders from the current path (c:\img). -Name will show the file names.

Now, we will create a counter to insert the id into the table starting in the number 1:

$counter=1.

We will make a loop and invoke the sqlcmd to insert each image into the SQL Server table named myimages. We use a foreach statement. In this example, we have four files in our folder. That means that we will invoke the sqlcmd four times. The counter variable will be used to insert numeric values in the id column from 1 to 4. Note that in the INSERT INTO we are specifying the databasename.schema.tablename. By default, invoke-sqlcmd will be in the master database.

foreach ($file in $files) 
 
{
	invoke-sqlcmd "INSERT INTO adventureworks.dbo.myimages values 
    ($counter, (SELECT * FROM OPENROWSET(BULK N'C:\img\$file', SINGLE_BLOB) as T1))"
	$counter++
}

Note that we are concatenating the $file variable with the file names of each image. $counter++ will be used to insert the value 1 in the column id and 2 in the next iteration, 3 in the next one and so on.