DEV Community: andreasneuman

Introduction to Temporary Tables in SQL Server

andreasneuman — Wed, 26 May 2021 14:51:40 +0000

A temporary table in SQL Server, as the name suggests, is a database table that exists temporarily on the database server. A temporary table stores a subset of data from a normal table for a certain period of time.

Temporary tables are particularly useful when you have a large number of records in a table and you repeatedly need to interact with a small subset of those records. In such cases instead of filtering the data again and again to fetch the subset, you can filter the data once and store it in a temporary table. You can then execute your queries on that temporary table. Temporary tables are stored inside “tempdb” which is a system database. Let’s take a look at how you can use a temporary data in a simple scenario.

More at https://codingsight.com/introduction-to-temporary-tables-in-sql-server/

New in Delphi DAC: Support for RAD Studio 10.4.2 and Multiple Performance Improvements

andreasneuman — Thu, 25 Mar 2021 18:39:12 +0000

Following the release of RAD Studio 10.4.2 Sydney from last week, we are excited to announce support for the new versions of Delphi and C++ Builder IDEs in our data access components. DAC products are now also compatible with macOS Big Sur, iOS 14, and Android 11. Additionally, PostgreSQL 13 was supported in PgDAC.

Data access speed with default settings was significantly increased in LiteDAC and the SQLite provider. The performance of batch operations was improved in all DAC products. During batch processing, SQL statements are grouped into a single unit of work, known as a batch, and submitted to the database server in a single call, thereby reducing the network latency. We also reduced memory consumption in batch operations for InterBase and Firebird.

The LOBs read/write speed was improved for Oracle, SQL Server, DBF files, and ODBC drivers. The data fetch speed was also improved for Oracle and ODBC drivers. The PrefetchRows property, which allows you to set the number of rows to be prefetched during query execution, was supported in the Direct mode (previously available only in the OCI mode). The FindFirst, FindNext, FindLast, and FindPrior methods to search for records in a dataset using filters now work much faster in all DACs.

The Over-the-Wire (OTW) encryption feature of InterBase was supported in IBDAC to allow you to secure your data during the transmission process with SSL/TLS encryption. Also automatic detection of computed fields when generating update statements was improved in IBDAC.

In PgDAC, a single TPgConnection object can now be used in multiple threads, and a new property called MultipleConnections allows or denies creation of additional internal connections.

Three new properties were added in LiteDAC: JournalMode, LockingMode, and Synchronous. The Unicode standard was supported in the TVirtualQuery component. The LastWarningCount property and the OnWarning event were added in MyDAC to get the number of warnings received from the MySQL server and define an event handler method.

Devart Delphi Data Access Components are suites of components for direct access to common databases and cloud services and allow creating multi-platform database applications in Embarcadero RAD Studio, Delphi, C++Builder, Lazarus, and Free Pascal for Windows, Linux, macOS, iOS, and Android, both 32-bit and 64-bit.

To try out these features, you are welcome to download new versions of Devart data access components:
UniDAC 8.4 [ Download ]
ODAC 11.4 [ Download ]
SDAC 9.4 [ Download ]

MyDAC 10.4 [ Download ]

IBDAC 7.4 [ Download ]

PgDAC 6.4 [ Download ]
LiteDAC 4.4 [ Download ]
VirtualDAC 11.4 [ Download ]

SQL Server DELETE – Removing One or More Rows from a Table

andreasneuman — Tue, 23 Feb 2021 16:03:25 +0000

The right application of the DELETE statement for data removal is crucial, and it involves lots of issues. Still, there are standard practices of the DELETE statement usage that simplify all such tasks.

This article will explore some of the professional life scenarios to equip you with the most helpful tips to use the DELETE statement correctly.

About T-SQL Delete
First of all, we should get familiar with the T-SQL Delete statement in the simplest manner possible.

What is the T-SQL Delete Statement?
The Delete statement, as the name indicates, is a statement that helps us to delete data from the database table.

What is a Table?
A table is a structure that you create in a database to store your data. For example, we can have a table of books to store records related to those books.

What is a Database?
A database is an organized collection of data and the data structures to hold that data. In other words, data can be stored inside the database in the form of tables.

What kinds of data can be deleted?
We can delete one or more records (commonly known as rows) from a table using the delete statement.

Simple Definition
The Delete statement removes some or all data (rows) from a table.

Microsoft Definition
According to Microsoft documentation, the Delete statement removes one or more rows from a table or view in SQL Server.

Deciding whether to remove some or all rows
One might wonder how the statement defines whether to remove some or all of the data (rows) from a table. The answer lies in the criteria or conditions specifying what needs to be removed.

Simple Syntax
The simplest syntax of the statement is as follows:
Delete FROM WHERE

You need to provide the table name and the criteria/condition for the data (rows) deletion from the table.

Note: It is crucial to use the DELETE statement with a condition (WHERE Clause) although the condition requirement is not a must.

If you execute the DELETE table command without the WHERE condition, you will end up deleting all the rows (data) from the table. Hence, make it a habit to use the WHERE condition unless you want to delete all rows.

Compatibility
This statement is compatible with many versions of SQL Server, including the following:

SQL Server 2012 and upward versions.
Cloud-based SQL Server Database (Azure SQL Database).
Cloud-based SQL Data Warehouse (Azure Synapse Analytics).

Simple Deletion Scenarios
Now we are going to explore the Delete statement usage with several practical scenarios.

The Summary of Steps
-Setup a Sample Database.
-View the data.
-Delete the data.
-Insert more data back into the table.
-View the data before deletion.
-Delete the data based on a condition.
-View the data after deletion.
-Insert more data back into the table.
-View the data before deletion.
-Delete the data based on another condition.
-View the data after deletion.
-Insert the data back into the table.
-Delete the data based on two conditions this time.
-View the data after deletion.

Setup Sample Database (BooksSample)
We need a sample database to test and run the scripts. First, we need to set that sample database up, covering the following steps:

Create a sample database.
Create a table in the sample database.
Insert the data (two rows) into the database table.

Open SQL Server Management Studio or dbForge Studio for SQL Server and execute the following script to set up the sample database:

-- Connect to the 'master' database to run this snippet
USE master
GO

-- Create a new database if it does not already exist
IF NOT EXISTS (
SELECT [name]
FROM sys.databases
WHERE [name] = N'BooksSample'
)
CREATE DATABASE BooksSample
GO

USE BooksSample

-- Create the table book
CREATE TABLE [dbo].[Book]
(
[BookNumber] INT NOT NULL PRIMARY KEY,-- Primary Key column
[Title] VARCHAR(150) NOT NULL,
[Stock] SMALLINT NOT NULL

);
GO

-- Insert rows into table 'Book'
INSERT INTO [dbo].Book
VALUES
( -- First row: values for the columns in the list above
1, 'Learn SQL in 7 Days', 50
),
( -- Second row: values for the columns in the list above
2, 'Creating Databases in Minutes', 50
);

View the data (Book table)
Let us view the recently created and populated table in the database. Run the below script:

-- View data (rows) from the table (Book)
SELECT b.BookNumber, b.Title, b.Stock FROM dbo.Book b

The output is:

We can see the two rows of the table (Book). At the moment, these are all the data present in this table.

Delete the data

As we remember, there is a risk of deleting all rows in a table if we forget to mention the condition/criteria for the correct deletion.

Always use the WHERE Clause with the Delete statement to avoid accidental data loss. The only exception should be the case when you need to delete all data intentionally.

To delete all data (rows) from the sample database table, run the following script:

-- Delete all the data (rows) from the table Book
DELETE FROM dbo.Book

The output is:

View the data after the deletion

Now, we need to check if all the rows have been deleted:
-- View data (rows) from the table (Book)
SELECT b.BookNumber, b.Title, b.Stock FROM dbo.Book b

The results are:

This way, we have successfully deleted all the rows from the Book table. For that, we applied the DELETE statement without any deletion criteria/conditions.
Insert data back into the table (with the same title)
We can insert the data (rows) back into the table and then apply the DELETE statement based on some conditions/criteria.

This time, we choose to insert more rows, but deliberately having the same title:
-- Insert rows into table 'Book'
INSERT INTO [dbo].Book
VALUES
( -- First row: values for the columns in the list above
1, 'Learn SQL in 7 Days', 50
),
( -- Second row: values for the columns in the list above
2, 'Creating Databases in Minutes', 50
),
( -- Third row: values for the columns in the list above
3, 'Creating Databases in Minutes', 50
),
( -- Fourth row: values for the columns in the list above
4, 'Creating Databases in Minutes', 50
);

The output is as follows:

Note: To restore the deleted or changed data, you can use the dedicated software solutions. The dbForge Transaction Log solution allows you both to restore those data and view who and when deleted or altered them.

View the data before deletion

To view the data, execute the following script:
-- View data (rows) from the table (Book)
SELECT b.BookNumber, b.Title, b.Stock FROM dbo.Book b

The output is:

The output demonstrates that we have inserted three rows with the same book title by mistake. It is a problem. The simple solution is to delete the unnecessary rows, applying the specific condition for removing rows with duplicate titles.

Delete the data based on a condition (BookNumber)

Important: We may consider one of the following ways to solve this problem:

Delete by BookNumber
Delete by Title In my scenario, I choose not to delete by Title. If we delete by Title, we end up deleting all rows containing that title including those we need to keep. Hence, the recommended approach is to delete the table based on the BookNumber column.

If we look at the result set, we can easily understand that BookNumber: 3 and BookNumber: 4 are duplicate rows. We must delete them to keep the database consistent.

Again, the following options arise:

Delete where book number (BookNumber) is greater than 2.
Delete where BookNumber is 3 and 4. Let us choose the first option. However, remember that it is only valid if there are no rows after the duplicate rows we are aware of.

Execute the following script:
-- Delete all the data (rows) from the table Book where BookNumber is greater than 2
DELETE FROM dbo.Book
WHERE BookNumber>2

View the data after the deletion

Let’s check the table after deleting the data:

-- View data (rows) from the table (Book)
SELECT b.BookNumber, b.Title, b.Stock FROM dbo.Book b

The output is:

Insert more data into the table (more stock)
To put more data (row) related to the stock, we use the below script:

-- Insert rows into table 'Book'
INSERT INTO [dbo].Book
VALUES
( -- First row: values for the columns in the list above
3, 'Basic Data Structures', 60
),
( -- Second row: values for the columns in the list above
4, 'Advanced Data Structures', 0
)
GO

View data before deletion

Have a look at the table:
-- View data (rows) from the table (Book)
SELECT b.BookNumber, b.Title, b.Stock FROM dbo.Book b

The output is:

Delete the data based on a different condition

Assume that we need to delete out of stock books to keep more accurate information in the database. To do this, we should look for those rows with the Stock is 0.

We can use the DELETE statement with the condition based on the Stock column value 0:

-- Delete all the out of stock (Where Stock is 0) books (rows) from the table Book
DELETE FROM dbo.Book
WHERE Stock=0

View the data after the deletion

-- View data (rows) from the table (Book)
SELECT b.BookNumber, b.Title, b.Stock FROM dbo.Book b

Insert more data into table (more titles and stock)
We are adding two more rows into the table:

-- Insert rows into table 'Book'
INSERT INTO [dbo].Book
VALUES
( -- First row: values for the columns in the list above
4, 'Learn Azure SQL Database in 10 Days', 0
),
( -- Second row: values for the columns in the list above
5, 'Azure SQL Database Concepts', 1
)
GO

View the data before deletion

Check the rows before deleting anything further, based on the requirement:

-- View data (rows) from the table (Book)
SELECT b.BookNumber, b.Title, b.Stock FROM dbo.Book b

The table output is:

Delete the data based on two conditions

This time, we need to delete all books (rows) where titles contain the word SQL and they are out of stock (their stock value is 0).

In other words, we delete all the SQL-related out of stock books.

In this case, we must specify more than one condition with the DELETE statement. We have to ensure we are only deleting the out of stock books and only those books having the word SQL in their title.

View the following script:
-- Delete all the out of stock (Where Stock is 0) SQL related (Title contains SQL) books (rows) from the table Book
DELETE FROM dbo.Book
WHERE Title LIKE '%SQL%' and Stock=0

View the data after the deletion

We view the data for the final time:

Important Tip: Before you delete the data, run the SELECT statement based on the same condition you are going to use for deletion. This way, you ensure that your deleting activities will apply to the correct data.

For example, run the SELECT query first to ensure that you are only getting those rows that are meant for deletion:

SELECT * FROM dbo.Book
WHERE Title LIKE '%SQL%' and Stock=0
Once you are confident, you can turn your SELECT into the DELETE statement:

DELETE FROM dbo.Book
WHERE Title LIKE '%SQL%' and Stock=0
Congratulations!

You have successfully mastered the task of removing (deleting) one or more rows from a table according to the requirements.

Stay in touch for advanced deletion scenarios and other professional tips regarding the DELETE statement usage.

Things to do
Now that you can remove rows from a table successfully, you can train and improve your skills further:

*Try to delete rows where there is only one item in the stock.
*Delete all the books where the title contains the word Structure.
*Delete all the books (rows) except for book number (BookNumber) 1.

Originally posted at https://codingsight.com/sql-server-delete-removing-one-or-more-rows-from-table/
Author: Haroon Ashraf

Compare and Sync PostgreSQL & Amazon Redshift Databases

andreasneuman — Wed, 09 Sep 2020 13:52:05 +0000

dbForge Schema Compare for Redshift/PostgreSQL is a tool for easy and effective comparison and deployment of Redshift database structure differences. The tool is best suited when you need to compare Amazon Redshift databases quickly and efficiently, as well as to generate synchronization SQL scripts to update target database schemas.

The new solution also has such key features as:

Support for Amazon Redshift. The first version features Amazon Redshift support to compare and synchronize database schemas.
Limited Support for PostgreSQL. At the moment, the vendor also implemented the limited support for the following PostgreSQL databases: PostgreSQL, Amazon RDS for PostgreSQL, Azure PostgreSQL.
Support for Redshift Objects. The solution allows users to compare and sync the following Redshift objects: Schemas, Tables, Views, Function, Procedures, Comments, Privileges.
Identification of Object Dependencies. The tool allows users to sync objects they have not selected for synchronization but which are used by objects they added to synchronize.
Review of DDL Differences in Database Objects. Also, users can view DDL differences for each object pair to analyze the schema comparison result.
Execution of Query History. Users can get basic information about PostgreSQL queries executed in the database for a certain period of time.
Execution of Large Script. The large scripts can be executed without opening in the editor.

MySQL Database Documenter

andreasneuman — Wed, 15 Jul 2020 09:04:57 +0000

The Documenter feature of dbForge Studio for MySQL is a MySQL database documentation tool that automatically generates documentation in the HTML, PDF, and Markdown file formats. It will take you just a couple of minutes and a few clicks to document multiple MySQL and MariaDB databases with all their objects and properties. Documenter also includes a wide range of options to customize the generated documentation file to meet your specific requirements.

Documenter extracts an extensive database information, including MySQL object types, their details, and properties, as well as inter-object dependencies and DDL codes.

The tool allows selecting individual MySQL objects and their properties for documenting. Documenter also includes a wide collection of style templates and allows changing the documentation layout with various Bootstrap themes.

You can generate documentation in three file formats: HTML, PDF and Markdown. HTML and Markdown suit for databases to be published on the web, PDF is good for distributing to various systems and devices. All formats are searchable, which is very convenient especially for large databases.

Search across the generated documentation files for MYSQL database objects. As you type the name of a required object in the search field, Documenter highlights the matching text in the object tree. You can also navigate throughout the documentation via build-in hyperlinks.

5 Ways to Export Data from Salesforce to Excel

andreasneuman — Tue, 07 Jul 2020 07:51:17 +0000

Salesforce is a multifunctional and highly customizable CRM platform, which is used by millions of users to manage complex business processes and boost the productivity of sales, customer service, marketing teams, etc.

Microsoft Excel is a spreadsheet program, which is so familiar and comfortable for many users worldwide, and there are situations when it is much better and easier to work with data in the Excel worksheets rather than in the native UI of a cloud app.

However, it's not a trivial challenge to export huge data pieces from Salesforce to the Excel worksheets as you need built-in features or external tools, which allow exporting data to the Excel format.

We briefly describe 5 ways how to connect Salesforce to Excel, easily export data from Salesforce to Excel worksheets, pros and cons of these ways and typical use cases applied.

1. Salesforce Data Loaders

To export data using data loaders is the first and most standard way. Using these tools, you can export data from Salesforce to Excel as CSV files, make changes to these files in Excel and import changed CSV files back to Salesforce. You can find many of such data loaders on the Internet, and many of them have free versions. You can also try native Salesforce built-in data loader (first-party tool), which you can use after installing it on your PC. However, third-party data loaders such as, for example Skyvia Salesforce data loader, can be more robust than Salesforce’s native offerings. For example, you don’t need to download Skyvia on your computer or laptop as it is a cloud-based solution. You need only a web browser to access it and set minimal configuration to safely export and import data as CSV files.

2. Excel Original Interface

To get data from Salesforce to Excel through Excel original interface is a perfect solution for users of Microsoft Office Professional Edition. Unlike data loaders, in this case the data are displayed directly in the Excel UI, you do not need to use intermediate CSV files.

3. Excel Add-Ins

The third way to pull Salesforce data into Excel is to use Excel Add-Ins for Salesforce. Add-ins help you to connect Microsoft Excel to Salesforce, load data from Salesforce to Excel, instantly refresh data in an Excel workbook from Salesforce, edit these data, and update Salesforce from Excel. It enables you to work with Salesforce contacts, accounts, opportunities, leads, users, attachments, tasks, and other objects like with usual Excel worksheets, as well as to perform data cleansing and de-duplication, and apply all the Excel's powerful data processing and analysis capabilities to these data.

4. ODBC Drivers

Another way of performing Salesforce export to Excel is by using ODBС drivers. With ODBC drivers, the same as with add-ins, you export data directly into an Excel spreadsheet. However, note that unlike add-ins, ODBC drivers do not allow loading data modified in Excel back to the source.

5. OData Endpoints

OData (Open Data Protocol) is an OASIS standard that defines the best practice for building and consuming queryable and interoperable RESTful APIs in a simple and standard way.

ODBC Driver for Google BigQuery

andreasneuman — Fri, 12 Jun 2020 14:52:46 +0000

Devart ODBC Driver for Google BigQuery is a high performance and feature-rich connector for ODBC-compliant applications to enable you to access, analyze and report on your BigQuery data on Windows, both 32-bit and 64-bit. The driver processes standard ODBC function calls, submits SQL statements to BigQuery, and returns results to the application. You can work with BigQuery data warehouse like you would with a database – execute SQL statements to read, write, update, and delete billions of rows in very large tables.
Devart driver provides capabilities to establish a connection to BigQuery data directly through HTTPS. If you have no access to BigQuery directly through HTTPS, you have also an ability to connect through Proxy using the connection options: Host, Port, Username, Password.

Show and kill running processes in MySQL

andreasneuman — Tue, 26 May 2020 08:10:14 +0000

The KILL command terminates a connection thread by ID along with the related active query, if there is one. Then, to identify queries for deletion, you need to see processes on the server - and the SHOW PROCESSLIST command will be a fine solution. It's not an elegant way to fix database issues, but rather an effective last resort tool. There are 4 major reasons for that:

If a long-running query holds other transactions from executing your more relevant query
If a large number of faulty queries block viable queries
If there are orphan processes after a client was disconnected from a server
'Too many connections' message

None of these scenarios are great, so before executing KILL, make sure other solutions have been tried. But once you know the KILL method is necessary, you will have a few different options. But in this particular guide, we will focus on the more 'intuitive' way of showing and killing faulty queries using simple commands. You should keep in mind that KILL has two modifiers - CONNECTION and QUERY. KILL CONNECTION is essentially the same as KILL, while KILL QUERY terminates only the query for the specified connection ID and leaves the connection itself intact. https://www.devart.com/dbforge/mysql/studio/show-running-queries-in-processlist.html

Different types of JOINS in SQL Server

andreasneuman — Wed, 13 May 2020 16:04:48 +0000

JOINS fundamentals

In relational databases, such as SQL Server, Oracle, MySQL, and others, data is stored in multiple tables that are related to each other with a common key value. Accordingly, there is a constant need to extract records from two or more tables into a results table based on some condition. In SQL Server, this can be easily accomplished with the SQL JOIN clause.

JOIN is an SQL clause used to query and access data from multiple tables, based on logical relationships between those tables.
In other words, JOINS indicate how SQL Server should use data from one table to select the rows from another table.

Enjoying JOINS with SQL Complete

SQL Server JOINS are vitally important to master. As you progress from a database beginner to a more advanced user, you’ll continually need to fetch and combine data from more than one table. At this point, SQL Complete comes to the aid. Its code completion works well even for complex JOIN statements. You don’t need to memorize multiple column names or aliases, dbForge SQL Complete will suggest a full JOIN clause based on foreign keys, or conditions based on column names. These suggestions are available after the JOIN and ON keywords.

More than that, SQL Complete can prompt a complete SQL JOIN statement when you combine tables based on foreign keys. You can select a JOIN statement from the prompt list manually, in case you need a specific JOIN operation.

Monitor and Analyse SQL Server Performance

andreasneuman — Wed, 13 May 2020 15:01:01 +0000

dbForge Monitor is a FREE add-in for Microsoft SQL Server Management Studio that allows you to track and analyse SQL Server performance. The tool includes a variety tools for SQL Server diagnostics, including a bunch of SQL performance metrics allowing to see the comprehensive and clear picture of all processes taking place on the server, detect most expensive queries, waiting tasks, and performance bottlenecks. With dbForge Monitor, you will save hours of time spent on SQL Server performance analysis!

Data Import and Export Tools for PostgreSQL

andreasneuman — Fri, 17 Apr 2020 06:59:26 +0000

Enjoy simple and seamless data migration and benefit from an easy way to fill your PostgreSQL databases with external source data.

A salient feature of the tool PostgreSQL Import/Export, built into dbForge Studio for PostgreSQL, is that it supports file transfer between the most frequently used data formats (Text, MS Excel, XML, CSV, JSON, etc.) and allows saving templates for repetitive export and import jobs. With the tool, you can easily:

Migrate data from other servers
Customize import and export jobs
Save templates for recurring scenarios
Populate new tables with data

The PostgreSQL Import/Export functionality of dbForge Studio for PostgreSQL allows transferring data from PostgreSQL to any other DBMS as well as from any other DBMS to PostgreSQL using ODBC drivers.

I strongly recommend utilizing a high-performance and feature-rich Devart PostgreSQL ODBC Driver as it provides quicker and more stable access to PostgreSQL databases compared with standard ODBC drivers.

dbForge Studio for PostgreSQL has advanced built-in features for exporting and importing data for different cases and scenarios. The Data Import and Export functionality provided in the Studio significantly facilitates filling databases with data and migrating Postgres data between databases.

Creating Pivot Tables in PostgreSQL

andreasneuman — Thu, 05 Mar 2020 11:44:13 +0000

Pivot Table provides an interactive view of your data going significantly beyond the native crosstab function in PostgreSQL. With very little effort (and minimum queries) you can look at the same data from many different perspectives. This functionality comes in dbForge Studio for PostgreSQL along with other useful database management features. Try the tool and enjoy PostgreSQL pivoting without crosstab.

Extract the significance from a large, detailed data set
Slice and dice your database without a hitch
Make your data more comprehensive
Simplify data representation
Get all the metrics you need

Creating a Pivot Table

To create a pivot table, execute a query to select data you want to analyze and navigate to the query output. Right-click the data grid and select the Pivot table option from the Send to sub-menu. To add fields to the pivot table, simply drag them from the Data Source view to the required pivot table area.

Note:
Not all the data source tables are good for converting into pivot tables. The data source should contain a column with duplicated values, that can be grouped, and a column with numeric data, that can be used to calculate grand totals and custom totals.